How to Prove Your Data Migration Actually Worked, Not Just Ran

A migration script that finishes with exit code 0 tells you the process didn't crash. It does not tell you whether the data on the other side is correct. Those are different claims, and the gap between them is where migrations quietly go wrong: a row count off by a few hundred, a currency column that got summed instead of averaged, a foreign key that pointed at the wrong tenant after an ID remap.

None of those show up as an error. The job finishes. The dashboard goes green. Three weeks later someone notices a customer's order history is short, or a wallet balance is a few cents off from what it should be across a thousand accounts, and now you're debugging a migration that already shipped instead of one you can still roll back.

The only real proof a migration worked is a reconciliation report: a document, generated by a query against both databases, that states in numbers what moved, what didn't, and what's different. We migrated over a hundred tenant databases off a platform we were decommissioning, and no tenant cut over without one. This post is how we build that report and how we use it as a gate rather than a formality.

"It Ran Without Errors" Is Not Proof of Anything

Most migration tooling reports success at the level of the transaction: rows were inserted, no constraint violations, no timeouts. That's a necessary condition, not a sufficient one. A script can insert every row from the source and still be wrong if a join condition silently dropped a subset, a type coercion truncated a decimal, or a default value filled in for a column that should have carried a real value.

We've written more about the mechanics of moving a live database without downtime in our post on migrating without a maintenance window. That post is about not breaking the site while data moves. This one is about a separate question: once the move is done, how do you know the numbers are right? Those are two different failure modes and they need two different sets of checks.

What a Reconciliation Report Actually Verifies

A reconciliation report is not a single check. It's a small set of comparisons run against source and target, per tenant if you're on a database-per-tenant model (see our take on shared schema versus database per tenant if you haven't settled that question yet). At minimum it should cover:

• Row counts per table per tenant. Not a global count — a global count of 1,000,004 can hide a tenant that lost 40 rows and another that gained 44.
• Sums of monetary and quantity columns. Order totals, inventory on-hand, wallet balances. A row count can match while the sum is wrong if a decimal column got miscast.
• Checksums on stable columns. Hash a concatenation of columns that shouldn't change (email, SKU, external ID) and compare hash sets between source and target, not just counts.
• Referential integrity across the boundary. Every foreign key in the target should resolve to a row that also migrated, especially after any ID remapping.
• Timestamp of the last write on each side. If the source kept accepting writes after the export snapshot, your report needs to know how far behind it is, or the comparison is meaningless.

That last one is the check people skip most often and regret. If the source system is still live during the copy, you are not comparing two static snapshots, you're comparing a snapshot to a moving target, and the report needs to account for the delta explicitly rather than treat any mismatch as a bug.

Write the Report Before You Write the Migration

The report should exist before the first row moves, run against the source alone as a baseline, and then run again against the target after cutover using the identical query logic. If you write the reconciliation query after the migration, you tend to write it to confirm what you already believe happened, which defeats the purpose.

A minimal version, run on both sides and diffed, looks like this:

select
  tenant_id,
  count(*)                       as row_count,
  sum(total_cents)               as total_cents_sum,
  max(updated_at)                as last_write,
  md5(string_agg(sku || '|' || external_id, ',' order by id)) as row_hash
from orders
group by tenant_id
order by tenant_id;

Run that against the source before the export and against the target after the load. Diff the output. Any tenant where row_count, total_cents_sum, or row_hash don't match is not migrated correctly yet, full stop, regardless of what the migration tool's own logs say.

Make the Report a Gate, Not a Suggestion

A reconciliation report only does its job if a mismatch actually stops the cutover. The decision rule we use is blunt: a tenant does not get pointed at the new system until its reconciliation report shows zero unexplained diffs. Explained diffs — a known in-flight order, a timestamp delta smaller than the export window — get logged with a reason, not waved through silently.

On the decommissioning project mentioned above, this gate was mechanical, not a person eyeballing a spreadsheet: the pipeline that flipped a tenant's DNS and connection string to the new database would not run unless the reconciliation job for that tenant had exited clean within the last hour. That's the same instinct behind giving support staff a constrained admin console instead of raw database access — you don't want the safety check to depend on someone remembering to look.

The report doesn't have to be fancy. A cron job, a diff, and a Slack message that says "tenant 4417: 2 rows short on line_items, blocking" is more useful than a beautiful dashboard nobody checks before hitting the cutover button.

Where Reconciliation Reports Don't Save You

They catch data loss and data corruption. They do not catch semantic drift — a case where every row copied correctly but the new system interprets it differently. If the old platform stored discount amounts as positive numbers and the new one expects negatives, your row counts, sums, and checksums can all match perfectly while every invoice on the new platform is wrong. That's a business-logic bug, not a migration bug, and no comparison query catches it. You need a handful of manually verified test cases across real tenant data for that, separate from the automated report.

They also get expensive on tables under heavy concurrent write load during the migration window. If a table takes writes every second and your export takes ten minutes, the source and target will never line up exactly, and you either need a write freeze for that table specifically, a change-data-capture approach that reconciles the delta after the fact, or a tolerance band in the report that treats small, timestamp-bounded differences as expected rather than failures. Building that tolerance correctly is more work than the happy-path query above, and it's worth scoping honestly rather than pretending a static snapshot comparison will hold up against a live table.

Run the Report Again a Week Later

A clean reconciliation report at the moment of cutover proves the migration worked at that moment. It doesn't prove the new system keeps working. Dual-write bugs, a webhook that's still pointed at the old database, a background job that never got its connection string updated — these show up as drift over the following days, not at cutover.

We run the same reconciliation query again a week after cutover, this time comparing the target's own current state against what the report said it should be given known activity since cutover. If a tenant's numbers drift for a reason that isn't in the activity log, something is still writing to, or reading from, the wrong place. That second run has caught more real bugs for us than the initial cutover check, because it's the one that catches the systems nobody remembered were still talking to the old database.

If a migration is on your roadmap and you're not sure what your reconciliation report needs to check for your specific tenancy model, that's a scoping conversation worth having before the first row moves — see what we build or start one with us.