01
Related
Where to read next on the same subject.
02
Short answer
A warehouse migration is proven by layered reconciliation tests run continuously between source and target: counts, sums and aggregates, sampled field-level joins, and the business rules operators actually use, each with a tolerance declared before the test runs. The cutover gate is a test report, not a steering call.
The first migration I watched fail had beautiful row counts. Every table matched to the digit, the dashboard was green, and the team scheduled the cutover. Three days after go-live, finance reported that monthly revenue had shifted by a rounding error that was not a rounding error. The counts had passed because the tests had counted rows. Nobody had summed the money column, and a silent type coercion had been shaving fractions off every record at scale. The correction took an afternoon. The trust took a quarter.
That story is why I reflexively distrust a migration whose only evidence is a count. Counts prove that rows arrived. They say nothing about whether the rows are still true.
03
The five layers
Counts come first because they are cheap and they catch the loud failures: the partition that did not load, the date range that came up short, the reject file nobody appended. Row counts per table, per partition, plus distinct-key counts on natural identifiers, plus the reject-on-load totals reconciled back to source volumes. This layer belongs in every migration and it belongs first, at every load, not at the end.
Sums and aggregates come next, because they catch what counts cannot. Revenue, quantity, and balances aggregated by month on both systems and compared. Checksums on the high-volume tables where per-row comparison would take a day. This is the layer where type coercions show up as small persistent deltas, and where a rounding rule that differed between platforms becomes visible as a trend instead of a surprise. Declare the tolerance before running the test: sums within a hundredth of a percent, balances to the unit, and write the numbers down before anyone sees a result, because tolerances written after results are negotiations.
Sampled joins catch the mapping errors that aggregates average away. Pull random keys, join them across systems, and compare field by field with the diffs visible. Stratify the sample: recent records, deep history, and the edge cases such as customers who changed names, products that were rekeyed, transactions that straddle a boundary. One hundred well-chosen sampled rows have found more encoding and mapping defects on my projects than any other test, because they compare the data as the rows actually landed rather than as they summarize.
Business rules are the layer the operators audit with. Gross to net still ties out. The KPI definitions return equal values on both systems for the same inputs, which quietly tests the migration and the definitions in one pass. Sample the reports people actually run and execute them on both engines. The comparison that matters is whether the planning meeting sees the same number.
Tolerances and exceptions wrap the four layers. Each test carries its tolerance and its severity in advance: what counts as a pass, what counts as a warning, and what stops the load. Exceptions get an owner, a scope, and an expiry, and an accepted exception is a decision with a name on it, made in a review, not a shrug in a thread.
04
Run them continuously, gate on the report
Reconciliation that runs once, at cutover, is theater. Run the layers after every load. Store the results so the trend is visible, because a delta that grows nightly tells a different story than a delta that appeared once. Page the migration owner on failures, with the layer and the delta in the message.
The cutover gate is then a document, not a debate. Every layer green, or every red carrying an accepted exception with an owner and an expiry. If a steering committee wants to cut over against a red with no exception, that is their decision to make and it should be uncomfortable to make it, because the report makes the discomfort impossible to hide.
05
Where the tests come from
Most of this needs no special tooling. The count and sum layers are SQL that any engineer can write and version. The sampled joins need a small script that pulls random keys from both systems and diffs the rows, and the diff output should go to a human-readable table, because the point is that a person reads it. The business-rule layer is just the operators' own reports, run twice.
The effort sits less in the SQL than in the discipline of declaring tolerances first, running the tests on every load, and treating an exception as a decision. Teams that skip the discipline usually discover, at exactly the wrong moment, that one of their "identical" systems held different truths all along.
06
What good looks like
The cutover review takes its data from the test report: five layers, trend lines, the exception list with owners. Finance has seen the sums comparison. An operator has run their own report on both systems and signed it. The migration proceeds, or waits, on numbers that everyone in the room has already seen. Nobody discovers a discrepancy in week two. That is the whole point of the five layers.
07
Choosing what to test first
A warehouse estate has more tables than any team can reconcile exhaustively, and the triage is part of the method. Rank the estate by what the business decides with it. The tables under the revenue report, the replenishment calculation, and the regulatory filings get the full five layers. The tables that feed archival reporting get counts and sums. The tables nothing reads get a count, a note, and a conversation about retirement, because a migration is the cheapest moment a platform will ever have to lose weight.
Coverage is declared, not discovered. The evaluation document lists which tables carry which layers, and the percentage of decision-relevant volume covered. Undocumented tables surface during this exercise, and each one gets a decision: test it at the layer its consumers deserve, or record why it has no consumers and mark it for retirement. I have never finished this triage without finding at least one load-bearing table whose purpose nobody could state, which is exactly the kind of surprise better found during a migration than after one.
The sampled-join layer deserves its triage note too. Sample where the risk lives: the tables with known encoding history, the keys that were renumbered in 2019, the rows written by the retired team whose tooling nobody remembers. Random sampling across the whole estate finds general defects. Stratified sampling toward the history finds the specific ones, and the specific ones are what fail audits.
Re-run the triage at cutover minus one cycle. Data ages, consumers change, and a table that mattered at planning may be quiet by cutover, while a report built last month needs a layer it did not have. The triage document is versioned like everything else, and its changelog is short, readable, and occasionally embarrassing, which is the point.
08
Related
The migration-cost guide explains what migrations actually charge. The economics guide covers the cutover decision itself. The PeopleStrong case study shows layered reconciliation across a live Cloudera to BigQuery program.