How to prove a data platform migration is correct

Why a successful run proves very little

Most migration programmes track progress in terms of what has been built and deployed: tables created, notebooks converted, pipelines scheduled. Those are useful delivery metrics, but none of them says anything about correctness. A notebook with a join on the wrong key executes successfully. So does a pipeline that silently drops rows with a null in a filter column, or one that loads yesterday’s snapshot instead of today’s.

The same applies to automated code conversion. A figure such as “95% of the code converted automatically” describes how much syntax was translated. It does not describe how much of the workload has been shown to behave correctly, and the remaining 5% is often where the business rules that matter most are hidden. The only metric that matters for cutover is how much of the estate has been demonstrated to produce the intended result.

Validation therefore has to compare outputs, not just observe execution. The rest of this page describes how we structure that comparison.

Compare three systems, not two

The obvious comparison is legacy against target. It is necessary but not sufficient. When the two disagree, a two-way comparison cannot tell you which one is right. Legacy warehouses accumulate history, apply filters nobody remembers and derive columns that no longer match the source. Sometimes the new implementation is the correct one.

Bringing the operational source into the comparison changes that. With source, legacy and target side by side, every business key falls into one of seven regions, and each region points to a different explanation.

Summing all of this into a single “row count difference” hides the information. A table can have identical row counts in legacy and target while hundreds of keys sit in regions 3 and 4, cancelling each other out.

A practical detail that causes more false alarms than any defect: the three systems must be compared at the same cut-off. Comparing a target loaded this morning with a legacy snapshot from last night produces differences that are nobody’s fault. Aligned extraction points, or captured snapshots with a visible timestamp, are part of the validation design.

Levels of validation

Validation is not one check but a stack of them. Each level catches problems the previous one cannot see.

1. Structural. Tables, columns, data types and nullability exist as expected. Cheap, and catches missing columns and silent type changes, such as a decimal that became a float.
2. Row counts. Useful as a first signal, misleading as a conclusion, for the reason described above.
3. Key coverage. The seven-region comparison on business keys, including duplicate keys in any of the three systems.
4. Column-level comparison. For keys present in both legacy and target, which attribute values differ, and for how many keys. Null distributions and distinct-value counts per column reveal problems in columns that are rarely inspected.
5. Aggregates. Sums, averages and counts by meaningful dimensions such as period, entity or product. Numeric tolerances belong here: distributed engines can sum floating-point values in a different order and produce differences in the last decimals. Such differences should be allowed explicitly with an agreed tolerance, not ignored implicitly.
6. Business rules. Invariants the business states in its own terms: every contract has exactly one active owner, revenue equals the sum of its lines, no order predates its customer.
7. History. For slowly changing dimensions: one current record per business key, no overlapping or gapped validity periods, consistent effective dates and current flags, and unknown or default members present where facts expect them. SCD2 behaviour is one of the most frequent sources of subtle regressions, because a pipeline can be correct for today’s data and wrong for history.
8. Downstream outputs. The figures in the reports and semantic models that people actually use, reconciled against the legacy equivalents. This is the level business owners care about, and the one most often skipped.

For data warehouses, the fact-to-dimension relationships deserve their own checks: facts whose dimension keys resolve to the unknown member, surrogate keys that differ between legacy and target but must still join consistently, and the behaviour of late-arriving dimensions.

Classify differences instead of failing them

A migration with no differences at all is rare, and not always desirable. The useful output of validation is therefore not a red or green flag but a classification of every difference found.

Classification turns a list of failures into a manageable backlog. It also stops the same accepted difference from being investigated again in every test cycle.

Validate according to risk

Not every table deserves the same scrutiny. Financial, regulatory and customer-facing outputs justify full column-level comparison, aggregate reconciliation and business sign-off. Shared dimensions deserve thorough history checks, because every downstream fact depends on them. Short-lived intermediate datasets often need no more than structural checks and key coverage, as long as the outputs built on them are validated properly.

Setting these tiers explicitly at the start makes the validation effort predictable, and makes it possible to explain to an auditor why a given table was tested the way it was.

Evidence a business owner can review

Test results written for engineers rarely help a finance controller decide whether a migrated report can be trusted. An assertion failure in a Spark log is not a basis for sign-off.

The overlap view above works well with business stakeholders because it answers questions they can reason about: which records exist in both systems, which exist only in the old one, which only in the new one, and for which records the values differ. A controller who sees that the keys only in legacy all predate a known policy change can close the question in a meeting. The same finding in a log file would take days to escalate.

The principle: technical validation should produce evidence that the person accountable for the data can review and accept.

Parallel running

Running legacy and target side by side for a period, and comparing their outputs after every load, catches problems that one-off testing misses: incremental logic that only fails on specific days, month-end processing, late-arriving data, and drift that builds up over several runs.

It is a risk-based technique, not a rule. A reporting mart rebuilt from scratch every night may need a few comparison cycles. A warehouse with complex incremental and historical logic may need several month-end closes. What matters is that the exit criteria are agreed in advance, for example a number of consecutive runs without unexplained differences in the highest-risk tables, so that the end of the parallel run is a decision rather than a negotiation.

Leave evidence behind with every release

Each release should leave a record that answers, months later: what was tested, against which run and code version, which differences were found, which were accepted and by whom, and which remain open. When the same checks with the same thresholds run on every cycle, a defect confirmed fixed has been confirmed with the same method that found it.

This evidence is also what turns continuous deployment into safe deployment. A pipeline that deploys successfully can still remove a column a report depends on, or change the grain of a table. Deployment automation that knows which consumers are affected by a change, and whether validation has passed, can require explicit approval for breaking changes instead of applying them silently. Automated deployment should not mean automated destruction.

Our Migration Testing Workbench is built around these principles. The approach itself does not depend on any particular tool.