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
- The thing you are replacing is the specification. Not the docs, not the ticket. Generate your output in its exact shape, put them side by side, and let the disagreements teach you the spec nobody could recite.
- Reproduce the conventions before you question them. Match the arbitrary exclusions and the odd definitions first. You cannot argue about whether a rule is right until your numbers agree with it.
- Do not assume the incumbent is correct. Parity work is a differential test between two implementations, not an exam you sit. Once your own false mismatches are gone, some of what remains is the other side's bug, and reading the source rows is how you tell which is which.
- A fallback that preserves shape and changes meaning is a trap. Failing loudly beats producing a plausible file that quietly answers a different question.
- When parity is unreachable, ship a number instead of a verdict. A quantified drift report unblocks a migration that a pass/fail gate would hold hostage to a decision that is not yours to make.
- Ask what implicit signal the old thing carried. Freshness was free in a workbook and invisible in a dashboard. Whatever your replacement drops silently is the thing that will bite you.
Top comments (0)