Testing a data pipeline against the spreadsheets it replaced

· 9 min read

I spent a stretch building and running an operations analytics tool whose job was to replace a stack of spreadsheets. Someone would log into a vendor portal every morning, download half a dozen workbooks, drop them in a shared folder, and the tool would read them and draw the dashboards. The goal was to cut out the human and the download: pull the same facts from the vendor's warehouse directly, rebuild the same tables, serve the same pages.

The interesting problem was not writing the queries. It was proving the new numbers matched the old ones, because until you can show that, nobody will let you turn the spreadsheets off. And they are right not to.

What I did not expect was where that proof would end up. Not with my pipeline finally matching the workbooks, but with the workbooks turning out to be wrong.

The spreadsheet is the specification#

This is the part I underestimated. The workbook people were already using was not a rough report that my pipeline would improve on. It was the spec. Its column names, its column order, its definition of what a "day" is, and every undocumented quirk in it were the contract, because that is what everyone downstream had built their habits and their reconciliations around.

So the first artifact was not a dashboard. It was a script that produced a workbook from the warehouse in exactly the shape of the vendor's workbook, so the two could be diffed side by side. One report had a fifty-one column layout because the vendor's export had fifty-one columns. A separate twenty-seven column version existed for the ingestion path, which needed different things. The wide one was not for any machine to read. It existed so a human could put two files next to each other.

Column order turned out to be load-bearing enough that it got frozen in a test. A committed header-row-only workbook, generated from the same constant the exporter uses, is checked against the live export. Change the column list and a test fails rather than the layout quietly drifting away from the thing it is supposed to mirror.

Parity means reproducing the conventions#

The part that actually taught me the spec was the mismatches.

Two internal monitoring accounts were excluded from the vendor's export. Not for any principled reason, they were just noise someone had filtered out long ago. My pipeline had to exclude them too, or every total came out slightly wrong. The exporter takes a flag to put them back, specifically so you can do a line-by-line comparison against an unfiltered workbook and have the rows line up.

Some columns exist in the vendor's file and have no equivalent in the warehouse at all. Those get written as blank rather than zero, because a zero is a claim and a blank is an absence, and someone reading the comparison needs to see which one they are looking at.

None of this is engineering in any satisfying sense. It is archaeology. But each of these was a thing nobody could have told me, because nobody knew it consciously. The only way it surfaces is by generating your version, putting it beside theirs, and asking why row four disagrees.

A fallback that changes meaning is worse than an error#

One export had a helpful behaviour: if the warehouse table it wanted was missing, it would fall back to an older ingested table that had roughly the same columns.

That is a reasonable instinct and it was exactly wrong here. The output still had the right shape, the right headers, plausible numbers, and different semantics. During a parity exercise that is the worst possible outcome, because a shape-correct file with different meaning does not look like a failure. It looks like a disagreement between your pipeline and the spreadsheet, and you go hunting for a bug in the query that is not there.

That path now fails loudly when the landing table is absent. Relatedly, when an export comes back empty it prints diagnostics to stderr: which table was missing, what date range actually exists against the one you asked for, and a copy-paste command to backfill the window. An empty file with no explanation looks exactly like an empty file for a window where nothing happened, and those two need opposite responses.

Then the disagreements started pointing the other way#

Once my own sources of false mismatch were gone, the remaining disagreements were real ones. I assumed they were mine. A good number of them were not.

Because the pipeline read the vendor's warehouse directly rather than their report of it, I could do something the workbook could not: take a total that disagreed and walk down to the individual rows behind it, on both sides. Doing that over and over turned up cases where the vendor's own report did not reconcile against the vendor's own warehouse. Aggregates that were not the sum of their parts. Rows present in one report and missing from a related one.

That reframes the whole exercise. I had been treating parity as a test of my pipeline, with the spreadsheet as the reference implementation. It is better understood as a differential test between two implementations over the same underlying data, where either side can turn out to be the broken one.

The useful output was not that we matched. It was a set of tables I could defend row by row, which was more than the reports we had been copying could do. Once you can walk someone from a headline number down to the rows that produce it and the incumbent cannot, the argument about which to trust does not last long.

When you cannot reach parity, measure the gap#

Not every disagreement resolves into somebody's bug. One headline metric never fully reconciled: the vendor computed it one way, the warehouse another, and settling which was correct needed a decision from people rather than from me.

The tempting options are both bad: block the migration until it is resolved, or quietly ship and hope nobody checks that column.

What we did instead was scope the sign-off explicitly. The automated parity check asserts equality on the columns that are agreed, and that metric is excluded from it by name. Separately there is a reconciliation command that takes the engineering workbook and the vendor workbook and reports column totals plus a per-key diff, keyed on the natural spine of the report. It does not pass or fail. It tells you how far apart the two are, on which keys, today.

That turned an argument into a number. "It does not match" became "these totals differ by this much, concentrated in these rows," which is a thing people can actually make a decision about.

The file drop is a hostile interface#

The pipeline still ingests dropped files, because the migration is incremental and some sources never moved. A folder that humans and sync clients both write into is a messier input than it sounds:

for p in files:
    name = p.name.lower()
    if name.startswith("~$") or "conflicted" in name:
        continue
    stem = p.stem.lower()
    if p.suffix.lower() != ".parquet" and stem in parquet_stems:
        continue

The first rule skips Excel's lock files, the ones that appear when someone has the workbook open. The second skips the copies a sync client leaves behind when two people save at once. The third says that if a .parquet and an .xlsx share a stem, the parquet wins, which is how a folder can be migrated file by file without a flag day.

Nothing there is clever. It is a list of things that actually turned up in a shared folder, each added the day it broke an ingest. I keep it in mind whenever someone describes file drop as the simple option.

Staleness is a product feature#

The last thing surprised me, and it is the one I would keep from the whole project.

A spreadsheet carries an implicit freshness signal. You downloaded it this morning, so you know it is from this morning. If the download failed you know that too, because you were there.

A dashboard destroys that signal completely. It renders exactly the same whether the data behind it is current or stopped updating days ago. The failure mode of a broken pipeline is not an error page, it is a confident chart of stale numbers, and someone making a decision on it has no way to tell.

So the tool watches its own lag. It compares the most recent date it holds against today, and past a configurable threshold it sends an alert saying how many days stale it is and what the latest date actually is. There is a cooldown so a persistent problem does not fire every cycle, and one wrinkle worth stealing: the cooldown only suppresses a repeat if the latest date has not moved. If the data advances and is still behind, that is new information and it alerts again.

I originally filed this as an ops concern. It is not. Replacing a spreadsheet means taking away a trust signal people were relying on without naming it, and if you do not put a replacement in the product, you have made the reporting worse in a way that will not show up until someone acts on a stale number.

What transfers#