A data migration isn't done until every row is accounted for

You moved data from one system to another. Now someone has to say it worked, and put their name on it.

The usual evidence is a row count and a few spot checks. "Source had 48,212 rows, target has 48,212 rows, I opened ten and they looked right." That sentence has signed off more migrations than any test suite, and it is worthless, because a row count cannot see the two failures that actually hurt:

• rows that silently vanished (a join dropped them, a filter ate them, a key collided), and
• rows that silently appeared (a default row, a duplicate insert, a fixture nobody removed).

If 300 vanish and 300 appear, the counts match. Spot checks find neither, because you spot-check the rows you thought of, and the rows you thought of are the ones that exist.

Every row lands in exactly one bucket

The fix is not more spot checks. It is an accounting identity: every source row and every target row must land in exactly one bucket, and the bucket totals must equal the raw row counts read from each file. If they don't, the tool refuses to write a report at all.

Source side:

bucket	meaning
matched	present in the target, every compared field equal after the normalisation you declared
changed	present in the target, at least one compared field differs
unverified	present, but a compared field was blank where blank means "not knowable"
missing	no target row, and no declared rejection
rejected	you declared it dropped, with a reason
rejected_but_present	you said you would drop it. It is in the target anyway
duplicate_source	the key occurs more than once in the source, so no occurrence is authoritative
ambiguous_target	the target holds several rows for this key, so there is nothing single to compare
blank_key	the key columns are empty

Target side:

bucket	meaning
accounted	attributed to exactly one source row
orphan	present in the target, absent from the source — something invented this data
duplicate_target	the key occurs more than once in the target
ambiguous_source	the source holds several rows for this key, so this row cannot be attributed
blank_key	the key columns are empty

orphan, rejected_but_present and the two ambiguous buckets are the whole reason to do this. They are the failures a row count cannot see, and most reconciliation scripts never look for them, because the person writing the script was thinking about the rows that should match, not the rows that should not exist.

Refusal is a feature

The rule that makes this work is the one that feels wrong at first: if the buckets don't reconcile, write nothing and exit non-zero.

A partial report with a plausible-looking total is worse than no report, because the plausible total is the one that ends up in the sign-off email. So the invariant is checked last, after all the bucketing, against the raw counts read from the files with no parsing cleverness in between:

rows_read(source) == sum(source buckets)
rows_read(target) == sum(target buckets)

Fail either, exit 2, no output. Put --strict in CI and a migration that stops reconciling fails the build instead of surfacing as a support ticket in March.

Three decisions that are usually made by accident

Blank is not zero, and blank is not a match. A missing balance and a balance of 0.00 are different facts. If the old system never captured a field, declare it — blank_policy: unknown — and those rows land in unverified rather than quietly counting as matched. You get a smaller verified number and a true one.

Date order is declared, never guessed. 03/04/2026 is March 4th or April 3rd depending on who exported it, and a tool that guesses will guess consistently wrong on exactly the twelve days a month where it matters. Say dmy or mdy in the spec. If a value can't be parsed under the declared order, that's a finding, not a coercion.

Values are text until you say otherwise. Read everything as strings. Leading zeros survive (02116 is a Boston ZIP, 2116 is not), a 16-digit account number never turns into 1.2345E+15, and 1.10 does not silently equal 1.1 unless you declared a numeric comparison with a tolerance. Most "the migration corrupted our data" stories are actually "the reconciliation script parsed the data on the way in."

The output is byte-stable on purpose

The report's content hash covers everything except the timestamp. Re-run it next quarter on the same inputs and you get the same hash, so the sign-off can cite a number that anyone can re-derive offline, and a "did the data change since we signed?" question has a one-command answer. The HTML report has no scripts, no external fonts and makes no network requests — enforced by a test — so it can be attached to a ticket and still open in five years.

Where to get it

I packaged this as the Migration Acceptance Kit: one Python file (reconcile.py, standard library only, no network access, no telemetry), its 74-test suite, a written acceptance procedure, a spec reference, worked examples, and a sign-off template. It is US$240 for one organisation, delivered automatically on payment. If you would rather build your own, the bucket tables above are the spec — the hard part is deciding to refuse, not the code.

One command:

python3 reconcile.py --spec specs/customers.json \
  --source export-source.csv --target export-target.csv \
  --rejects agreed-exclusions.csv --out reports/2026-09-15 --strict

Exit 0: reported. Exit 1: reported, but not clean under --strict. Exit 2: refused — and that refusal is the point.