A post-cutover gate that reconciles the primary keys and compares the column aggregates between the legacy table and the new warehouse. The migration here reports success: 12,000 = 12,000, every cell parses, nothing is null. It is also short 42 orders and $5,955.90. Offline, standard library only, no keys.
The verification most teams actually run at cutover is SELECT COUNT(*) on both sides — a scalar comparison of two sets it never looks inside. Drop 42 orders and replay 42 others and the count is identical. Floor the cents on the money column and every row is still money. Load local timestamps as if they were already UTC and every row is still a valid datetime, just on the wrong day. The cutover is signed off, and the discrepancy surfaces three weeks later in a finance reconciliation, after the legacy database has been decommissioned.
| 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 |
before (row count): 12,000 = 12,000, 0 issues, cutover signed off after (this gate): 96 findings, -5,955.90 unaccounted (-10.3 bps), cutover blocked
migration-verify · legacy orders -> new warehouse, 'migration succeeded'
========================================================================
clean migration · key findings: 0 · parity findings: 0 → PASS ✅
shipped migration · naive 'do the row counts match?' issues: 0 (12,000 = 12,000) → PASS (signs off) ❌
------------------------------------------------------------------------
migration-verify on the SAME pair:
key findings: 84 by kind: {'missing': 42, 'orphaned': 5, 'duplicated': 37}
parity findings: 12 by column: {'customer_name': 1, 'order_ts': 5, 'total_amount': 4, 'coupon_code': 2}
------------------------------------------------------------------------
smoking guns
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
The clean migration — the same rows written in a different physical order — reports 0 key findings, 0 parity findings, PASS on the same run. A gate that cries wolf on a correct cutover gets switched off before it ever catches anything.
| # | what happened | what the count sees |
|---|---|---|
| 1 | churn that nets to zero — 42 dropped, 37 replayed, 5 orphans minted by a retry | 42 = 37 + 5, so nothing |
| 2 | the money column lost its scale — cents floored by the cast, 11,803 of 11,921 amounts changed | every row is still money |
| 3 | local Asia/Seoul timestamps loaded as if already UTC — a fixed -9h offset | every row is still a valid datetime |
| 4 | customer_name VARCHAR truncated 43 → 32; coupon_code NULL arrived as '' | every row is still non-empty |
Number 4 is the one that reaches a dashboard fastest: COUNT(coupon_code) now counts empty strings, so "% of orders with a coupon" reads 38.1% in legacy and 100.0% after migration — a metric that moved 62 points without anybody touching a coupon.
The suite also asserts the negative: the row-count check must miss all four, so the cutover this gate blocks is a real one and not a story. A gate that waves a bad migration through fails its own CI.
Key-set reconciliation compares the two sets of primary keys and splits the answer three ways — missing, orphaned, duplicated. Every finding carries the key, so the output is a work list rather than a number.
Aggregate parity compares, per column, the handful of aggregates loaders actually break — sum · min · max · null_count · distinct_count · max_length — with a type-aware tolerance. Money gets basis points, because DECIMAL and float disagree in the last place and nobody should be paged for that; strings and counts get none.
Two checks are not per-column aggregates and catch the quietest bugs. money_delta() re-adds the money column over the 11,921 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: daily revenue disagrees on 91 of 91 days.
The sample is order_id · customer_name · order_ts · total_amount · coupon_code · status — 12,000 orders across 91 days. Order tables are where these four failures 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 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.
docker compose up # row count vs reconciliation + parity (offline) make demo # same, without Docker make test # the migration tests (pytest) make gate # the gate on the shipped migration — exits non-zero, cutover blocked make gate-clean # the no-false-alarms case: a correct migration must exit 0
· 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 float32 round-trip of a DECIMAL(10,2) total loses roughly 0.0006 bps, invisible at any realistic row count; the scale drop is the version of this bug that moves money. · Aggregate parity is a screen, not a proof: two different sets of values can share a sum and a distinct count. Row-by-row hashing on the matched keys is the next layer, deliberately out of scope — this is the check 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 genuinely tz-aware columns need more than a modal delta. · CSV stands in for both sides; a DB cursor drops 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.