A post-migration verification gate for the losses a row count cannot see. Reconciles primary keys and compares column aggregates between the legacy table and the new warehouse, so a cutover that reports success but is short 42 orders and $5,955 gets blocked instead of signed off.
Runs offline.
docker compose upand CI use the standard library only — no keys, no network, zero runtime dependencies.
"The counts match" is not "the data moved." The verification almost every team actually runs at cutover is SELECT COUNT(*) on both sides, and it is a scalar comparison of two sets it never looks inside. Drop 42 orders and replay 42 others and the count is identical. Change the numeric type on the money column and every row still looks like money. Load local timestamps as if they were already UTC and every row still parses as a valid datetime — it is just on the wrong day. The migration passes, the cutover is signed off, and the discrepancy surfaces three weeks later in a finance reconciliation, when the legacy database has already been decommissioned.
docker compose up # row count vs reconciliation + parity (offline)
make demo # same, without Docker
make test # the migration tests (pytest)
make gate # run the gate on the shipped migration (exits non-zero = blocked)
make gate-clean # the no-false-alarms case: a correct migration must exit 0The same migrated table, verified two ways:
| check | verdict on the shipped migration |
|---|---|
naive do the row counts match? |
0 issues → PASS (12,000 = 12,000, cutover signed off) |
| migration-verify | 84 key findings + 12 parity findings → BLOCKED |
The output names what was lost, not that something was:
1. churn · 42 orders never landed, 37 arrived twice, 5 came from nowhere — and COUNT(*) still matches
2. money · on the 11,921 keys present on both sides, 11,803 amounts changed: -5,955.90 (-10.3 bps of 5,788,394.90)
worst · ORD-2025-000057: 720.99 -> 720.00 (-0.99)
3. calendar · 1,416 of 11,921 matched orders (11.9%) land on a different calendar day; dominant offset -9h on 11,921 rows
daily revenue disagrees on 91 of 91 days; worst 2025-02-11: 63,832.24 -> 56,364.00 (-7,468.24)
4. columns · customer_name [max_length] 43 -> 32
coupon_code [null_count] 7432 -> 0
'% of orders with a coupon' reads 38.1% in legacy, 100.0% after migration — COUNT(coupon_code) now counts ''
Meanwhile the correct migration passes untouched — 0 key findings, 0 parity findings, PASS ✅, even though its rows are written in a different physical order. No false alarms.
- Key-set reconciliation (
reconcile.py) — a row count is a scalar, a key set is evidence. Comparing the sets answers which orders arrived and splits the answer three ways:missing(in legacy, never landed),orphaned(in the warehouse, no such key in legacy),duplicated(a replayed batch). Every finding carries the key, so the output is a work list. - Aggregate parity (
parity.py) — per column, the aggregates that loaders actually break:sum · min · max · null_count · distinct_count · max_length, compared with a type-aware tolerance. Money gets a tolerance in basis points, becauseDECIMALand float arithmetic disagree in the last place and nobody should be paged for that; counts and strings get none. Two checks are not per-column aggregates and catch the quietest bugs:money_delta()re-adds the money column over the keys present exactly once on both sides, so the row churn cannot be blamed for what the type change did;date_buckets()groups revenue by calendar date, where a fixed timezone offset stops being invisible.
Run it as a gate: migration_verify.run exits non-zero on any finding, so it slots into the cutover runbook or CI. The included workflow proves the correct migration passes and the shipped one is blocked — a gate that waves a bad migration through fails its own CI.
The sample is the table a migration is least allowed to get wrong: order_id · customer_name · order_ts · total_amount · coupon_code · status. Order tables are where the four failures above are not hypotheticals — a replayed batch during a dual-write window, a target DDL that dropped the scale on the money column, a legacy application that always wrote local time into a naive DATETIME, a narrower VARCHAR in the new schema. They are also the table where being wrong is expensive and where nobody notices for a month, because every downstream number still looks like a number. The same two layers work on any keyed table.
The verification is a test suite, not a promise: the correct migration must pass with zero findings, each of the four losses must be caught individually, and the row-count check must demonstrably miss all four. CI additionally regenerates the fixtures from scripts/make_data.py and fails if they are not byte-identical — the data is reproducible, not hand-tuned.
8 passed # pytest
0 → 96 findings # row count PASS vs gate BLOCKED, computed from data/
-5,955.90 (-10.3 bps) # money missing on the keys present on both sides
1,416 orders (11.9%) # moved to a different calendar day by a -9h offset
data reproducible # make_data.py is deterministic
· Synthetic data (12,000 generated orders, one legacy table, one target). A demonstrator of the
method, not a benchmark, and not a report on a migration I ran for a client.
· The money bug is modelled as a lost numeric scale — cents floored by the cast. A true IEEE-754
float32 round-trip of a DECIMAL(10,2) order total loses ~6e-8 relative, roughly 0.0006 bps, which
is invisible at any realistic row count. The scale drop is the version of this bug that moves money,
so it is the version demonstrated here.
· Aggregate parity is a screen, not a proof: two different sets of values can share a sum and a
distinct count. A full row-by-row hash comparison on the matched keys is the next layer and is
deliberately out of scope — this is the check that is cheap enough to run on every cutover.
· The timezone check assumes a naive timestamp column and a single fixed offset. Per-row offsets,
DST boundaries, and columns that are genuinely tz-aware need more than a modal delta.
· CSV stands in for both sides; a DB cursor would drop into io.py unchanged, but connection handling,
chunking for tables that do not fit in memory, and sampling strategy are not implemented.
· It verifies the data boundary. It does not repair the migration, roll it back, or find the line of
ETL that caused any of this — it names the column and hands you the keys.
migration_verify/ io.py · naive.py · reconcile.py · parity.py · run.py (gate) · demo.py
data/ legacy_orders.csv · migrated_orders.csv · migrated_orders_clean.csv
scripts/make_data.py deterministic fixture generator
tests/ test_migration.py
Dockerfile · docker-compose.yml · .github/workflows/ci.yml
MIT © 2026 Jigon Yoo