The API That Lied: Untangling the Chaos of Government Healthcare Data

Here is a look back at a rather chaotic journey my team and I took to domesticate a wild data pipeline. The source was a government healthcare provider; the medium was an ever-changing barrage of CSV and Excel files.

Countless megabytes of data have flowed through these exports over the years—both critical patient histories and sorrowful data-entry inconsistencies. When a seemingly simple weekly data import started silently corrupting our database, I was asked to step in and make the system reliable. Things can often be repurposed into something new and robust. I inherited this mindset from my grandfather, and I decided it was time to turn this brittle, terrifying process into a resilient architecture.

Before I landed on the final design, I actually started the project the way many of us do when under a deadline: I wrote a Python script to read the weekly incremental Excel batches and load them directly into our SQL database. I trusted the files at face value. I trusted that the columns would not change.

I was wrong.

One week, without any warning or documentation, the Excel file supplier simply changed the column order. They swapped the positions of Immunoglobulin A (IgA) and Immunoglobulin G (IgG). Because my code was reading the file blindly and trusting the column index, we successfully (and silently) imported thousands of lab results into the completely wrong medical columns.

To make matters worse, my testing strategy was a massive hurdle. I had written dozens of unit tests using Python's unittest.mock to fake the incoming data and test my internal parsing logic. I realized mocking external data is a trap. My tests were blindly passing because they were testing a fantasy version of the API, while the real production pipeline was corrupting our database. Furthermore, I was testing internal utility functions rather than the public interface. When the supplier changed the file structure, my internal tests were entirely bypassed.

There was a lot to figure out for this project, and my time wasn't well spent fighting fake database mocks or manually auditing Excel files every Friday morning.

2. Time Travel and Misunderstood Chemistry

Beyond the shifting columns, the data itself was a constantly moving target. The provider offered full data exports, incremental exports, and entirely overlapping exports.

We expected the incremental files to be a standard delta—just the events that occurred since the last batch. Instead, we found ourselves dealing with bizarre historical anomalies. We started getting ICD-10 diagnoses for participants that happened 20 years ago, which had never existed in any of the previous full datasets. It was as if the data was traveling through time, suddenly materializing in a Tuesday delta file.

And then there was the chemistry lesson. I'll never forget the day we realized a huge chunk of lab data was missing. Pandas, in its eager helpfulness, had seen the string "NA" in the lab results column and aggressively converted it to a null value (NaN). In this specific hospital system, however, "NA" did not stand for "Not Available"—it stood for Natrium (Sodium).

3. The Architecture: Embracing Medallion & Quality Scaffolding

Writing endless if/else statements to catch column shifts and missing values was exhausting. Simplicity won out, and I decided to pivot our entire data strategy. We needed dynamic engineering systems to catch bad data in flight. I structured this around a strict Medallion Architecture, fortified by four pillars of data quality.

Here is how the final flow works:

Bronze Layer (The Raw Truth)

We drop the government CSVs and Excel files exactly as they arrive into raw storage. We do not apply any formatting. If the file is broken, it stays broken here. This is our time capsule.

Silver Layer (Contracts & The Quarantine)

This is where the magic happens. A Python process picks up the Bronze data and passes it through strict Pydantic models. This layer enforces two critical pillars:

Data Contracts: Upstream producers changing data structures without warning is a nightmare. Our Pydantic schemas act as programmatic, version-controlled agreements. Instead of trusting column indexes, we map columns explicitly by dynamic name matching. If the supplier attempts to send a file that violates this schema (like casting an integer to a string), the pipeline fails at the boundary before breaking our systems.
The Quarantine (Dead Letter Queue): Continuous streams cannot be profiled manually. However, if a single row out of a million has a typo, we shouldn't halt the entire pipeline. We implemented a Dead Letter Queue (DLQ). When a row fails our Pydantic type match, we skip the insertion for that specific row and route it to a Quarantine table. This ensures the main database remains pristine while preserving the rejected data for debugging.

This is also where we intercept the "NA" values and correctly label them as Natrium before Pandas can destroy them. To handle the overlapping 20-year-old ICD-10 codes, we run an "upsert" (update or insert) based on unique patient-event hashes, rather than blindly trusting the export date.

Gold Layer (Business Logic & Observability)

Once the data has survived the Silver quarantine, it is aggregated and pushed to the clean tables our analysts actually query. Here, we enforce our third pillar:

Data Observability: What happens when data perfectly matches the contract schema, but is logically insane? For example, what if a batch of patients suddenly all register as 115 years old? 115 is a valid integer, so the Silver schema passes it, but it is statistically improbable. We built a "flagging" system that constantly calculates statistical baselines (medians, averages, distribution). The swapped Immunoglobulin disaster could have been caught instantly by this system, because IgG values have a completely different normal range in g/L than IgA. Observability flags these deviations without outright denying the records.

The Control Tower (Data Lineage)

When observability flags an anomaly, identifying exactly where the bad data originated is incredibly difficult. We introduced Data Lineage to handle our derivatives and source mapping. It provides a visual map of the data flow. When a metric breaks, lineage allows us to trace backward to the specific upstream batch that caused it, and forward to notify the analysts whose dashboards might be affected.

4. Testing the Reality

With the pipeline in place, I threw out my old unittest boilerplate and brought in pytest to test the actual reality of the system:

Real Infrastructure: Instead of faking database connections, I used disposable Docker containers. A pytest fixture with the yield keyword spins up a pristine PostgreSQL database, runs the data load, and destroys it when finished.
Automated Snapshots: To avoid typing out massive dictionaries to assert against, I used inline-snapshot. The computer writes the expected output for me, saving me hours of manual test maintenance.
Expected Failures: When I found bugs in the supplier's historical data that I couldn't immediately fix, I marked the tests with @pytest.mark.xfail. When the supplier quietly fixes it three years from now, the test will unexpectedly pass and alert me.

You get this absolutely perfect, natural sense of relief knowing the data is clean, validated, and historically accurate before it ever reaches a dashboard.

5. Lessons Learned

After months of debugging overlapping exports and rogue Excel files, we finally built a system we can trust. Here is what I took away from the ordeal:

Treat Excel files as hostile entities. Never use positional indexing (iloc) when importing spreadsheets from external suppliers. Always map by column name, and verify the schema programmatically before allowing a single row of data into memory.
Never trust external data. Fail fast and loudly. A broken pipeline is infinitely better than a pipeline that successfully loads garbage data. Schema validations must happen at the door.
Visibility is just as important as execution. Code that runs silently in the dark is a ticking time bomb. Observability and DLQs ensure we know exactly what is failing, without starving the downstream consumers of good data.
Context matters as much as syntax. An integer is not just an integer; it represents a reality. Checking aggregate statistics and flagging out-of-range values saves you from logically valid, but practically disastrous, data imports.
Mocking is a trap. Testing your logic against a fake, static representation of an API is just writing an elaborate fiction. Stop writing unit tests for hidden utility parsing functions, and test the actual file ingestion endpoints using real, disposable infrastructure.