How Do You Prove a Data Migration Is Exact?
The Green Tick That Proves Nothing
Every migration I have tested ends with the same question from someone senior: is the data all there? The honest answer is rarely yes or no. It is “here is what we checked, and here is what that proves.”
On a recent programme I was QA Lead on a large legacy reporting migration for a UK public sector client. A traditional on-premises ETL and analytics stack was moving to an Azure Databricks medallion architecture (Bronze, Silver, Gold), with a modern BI layer replacing the old reporting tool.
Before anyone could trust a new report, one thing had to be proven. The legacy data warehouse had to have been copied across exactly.
Not approximately. Not “the row counts match”. Exactly: every table, every column, every value.
This post covers how we did that across more than 900 tables, and what I would tell anyone facing the same job. My view is simple. Migration testing is not mainly a technical problem. It is an evidence problem.
Why Aren’t Row Counts Enough?
A matching row count is the most common sign-off check, and the weakest. A table can have exactly the right number of rows and still hold systematically wrong values. It can pass a schema check and still have its text fields silently truncated. Each individual check answers one narrow question. None of them, alone, answers the one that matters: is this the same data?
Why Not Just Sample?
The estate was two production source environments with over 900 tables between them. They ranged from small reference tables to fact tables with hundreds of millions of rows.
At that scale the usual answer is to check a sample and extrapolate. For this client that was not acceptable. In sensitive operational records, a dropped or truncated column is not a rounding error. It is a gap in the evidence.
But the opposite extreme does not work either. A full row-by-row comparison of every table, repeated every time the migration is re-run, would cost far too much compute and far too much time. So the real problem was this: how do you get total coverage without total cost?
Make Every Table Earn the Expensive Checks
Instead of one pass/fail test, I built a gated, six-level reconcile pyramid. Each level answers a narrower question than the one before it, and each costs more to compute. Every table gets the cheap checks. A table only moves up to the expensive ones when there is a reason to.

That gating is what made full coverage affordable. Assurance stays deep while cost grows nowhere near in line with the size of the estate.
How Do You Run 900 Tables Without Clicking Run 900 Times?
The reconcile logic was only half the build. The other half was making it run at scale with no one triggering notebooks by hand.
- Size profiling first.
A profiling step classified every table by size and shape, then gave it a depth plan: direct (small, run every level), escalating (medium, gate each level), or L3 only (very large, cap the pyramid so row-level checks don’t run for hours).
- Generated jobs, not manual runs.
A job generator read that control list and wrote out Databricks job definitions per environment, passing each table’s depth plan into its job parameters.
- One deployment path.
Everything shipped through the programme’s existing Databricks Asset Bundle, with per-environment variables. The same code deployed to dev, test and pre-production with no hard-coded catalogue names.
- One results surface.
Every result went to the shared test results table used across the programme. Migration reconcile and integration testing could be queried and shown on dashboards side by side.
What Happens When the Test Is Wrong?
Reconciliation frameworks are noisy. Collation rules, encoding artefacts and casting quirks all produce failures that look like migration defects but are not. Unmanaged, that noise goes one of two bad ways. Either people learn to ignore red results, which defeats the point of testing. Or sign-off stalls on things that were never broken. Our rule was that no failure got waved through. Each one had to be root-caused and evidenced. Most of what we found turned out to be on the test side:
- Fields that looked empty but weren’t.
Flag columns that seemed blank held invisible null characters, so our emptiness check counted them differently on each side. The data was identical. The check needed to strip those characters first.
- Truncation we caused ourselves.
Long-text failures traced back to our own framework casting text to a 4,000-character type before comparing. The migration hadn’t truncated anything. Our test had.
- Collation, not corruption.
Distinct-count mismatches came from the source database’s case-insensitive collation. The row-hash level passed on the same tables, which proved the stored values matched.
The layers check each other. When a cheap check and an expensive one disagree on the same table, the disagreement tells you where to look. It also lets you prove that a failure is an artefact, not just assert it.
What Did We End Up With?
The full pyramid now runs as scheduled Databricks jobs across both production source environments: 423 tables in one and 483 in the other. No table needs a manual check. Every failure the framework raised was investigated and closed with evidence. That gave the programme something a sampled or manual check never could: a defensible statement that the migration was complete and correct across the whole estate. Silver and Gold work could then build on a proven foundation.
Five Practical Actions
If you are about to test a migration of any size, this is what I would pass on:
- Define “exact” before you test. Agree what parity means (schema, counts, values, text length) and which check proves each one.
- Layer your checks and gate them. Run cheap checks on everything. Save expensive row-level comparison for tables that have earned it.
- Profile before you build. Classify tables by size and shape first. I learned this one the hard way: we found the tiering need mid-build and had to retrofit it into the job generator. It should have shaped the design from day one.
- Treat every failure as a question, not a verdict. Root-cause it, decide whether the problem is in the migration or the test, and keep the evidence either way.
- Automate the evidence, not just the tests. Write results somewhere queryable and shared, so sign-off rests on a record anyone can inspect, not on someone’s word.
Proof Is the Deliverable
In a migration it is easy to treat testing as the step between build and go-live. On this programme, the evidence was the thing being delivered. Reports, models and dashboards all inherit the trust, or the doubt, of the data under them.
Next time someone asks me whether the data is all there, I want the answer to be the same as it was here. Not “we think so”, but “here is the proof”.