Data migration testing: what a passing test actually proves

The load report said 100%. Two hundred and forty thousand records, zero rejects, eleven minutes. The programme manager forwarded it to the steering committee with one line: “migration validated”. Six weeks later, purchasing discovered that every supplier lead time in the new ERP was expressed in days where the source had stored weeks. Nothing had failed. Every record had loaded. The number was simply wrong by a factor of seven, on all 240,000 of them.

That report was not a test result. It was a receipt.

A load report tells you about the chain, not the data

A migration chain reports what it can observe: did the target system accept this record, yes or no. That is a genuine signal, and it is the one everybody looks at, because it comes for free and it produces a percentage.

It says nothing about whether the value that arrived means what it meant before. A lead time of 6 loads exactly as cleanly whether the unit is days or weeks. A cost centre mapped to the wrong parent loads cleanly. A part flagged active that should have been obsoleted three years ago loads cleanly. The target system checks its own constraints. It has no opinion about your business.

This is the gap that makes migration testing its own discipline rather than a phase of the load. Everything below is about closing it.

Four levels, and what each one establishes

The levels are cumulative. Skipping one does not make the migration faster, it makes the failure later.

Level 1: the record was accepted. The load report. Cheap, automatic, necessary. Proves the chain runs and the target constraints are satisfied. Proves nothing about meaning.

Level 2: the counts reconcile. For every entity family, the number that left the source equals the number that arrived, plus the number deliberately excluded, plus the number rejected. That third term is the one people forget: an exclusion rule that quietly drops 4,000 records will balance perfectly against a total nobody recomputed. Reconciliation only works if the expected count is derived independently, from the source, before the load.

Level 3: the values are equivalent. Field by field, on the full set, comparing source value to target value through the transformation rule that was supposed to apply. Not equality: equivalence. A code that legitimately changes format still has to resolve back to the same thing. This is where the lead time in weeks gets caught, and it is the level most programmes skip, because it is the first one that costs real engineering.

Level 4: the business behaves. Run a real process end to end in the target. Take a purchase order through approval. Explode a bill of materials and compare the result to the source system’s explosion, line by line. Post a movement and check the accounting entry. This is the only level that tests the interactions between entities, and interactions are where the expensive errors live.

Counting is not comparing

Reconciliation by count is popular because it is easy to automate and it produces a green tick. It catches records that vanished. It catches nothing else.

The useful version compares aggregates that depend on values rather than on cardinality. Sum the inventory quantity per warehouse in both systems. Sum the open order value per customer. Count the distinct units of measure in use per part family. Any of those three would have caught the lead time error on day one, because a units-of-measure distribution that shifts between source and target is not a rounding artefact, it is a rule that fired where it should not have.

Pick aggregates that a wrong transformation cannot preserve by accident. A total that survives every plausible bug is not a control, it is decoration.

The sample trap

Every migration is tested on a sample at some point, and the sample is almost always chosen the same way: records the team can reason about. Clean ones. Ones that a developer can trace by hand. It is a reasonable way to develop and a disastrous way to validate.

The problem is not that samples are small. It is that they are selected, and the selection criterion is inversely correlated with risk. The records that break a migration are the ones nobody can explain: the part created in 2008 by someone who left, carrying a status that exists in no current documentation. Those records are excluded from samples precisely because they are hard to reason about.

We covered this in the context of proofs of concept on PLM estates, where 500 easy parts predicted nothing about 4,200 broken references. The pattern is identical on the ERP side.

If you test on a subset, select it adversarially: the oldest records, the ones with null values in fields that are now mandatory, the ones with the longest strings, the ones with the most relationships, the ones created by systems that no longer exist. A subset chosen for being difficult tells you something. A subset chosen for being clean tells you only that your code compiles.

Test the exceptions, not the happy path

A useful way to size the testing effort: at the end of the audit, you know roughly what share of records pass on a deterministic rule and what share needs a decision. Call it 95/5 on a typical scope.

The 95% needs one good test at full volume. The 5% needs a test per rule, because each of those records is there for a different reason, and each arbitration is a small piece of business logic that somebody wrote under time pressure. That 5% will consume most of your testing budget, and it is correct that it does. It is also where the error queue and replay mechanism stops being an architectural preference and becomes the thing that makes testing possible at all: you cannot iterate on 12,000 exceptions if each attempt requires rerunning the whole batch.

The dry run is the only test that counts

Everything above can be run in a comfortable environment, on a Tuesday, with time to investigate. The dry run is different: full volume, complete chain including the target load interfaces, and the cutover clock running.

It exists to test the things no unit test reaches. Whether the load actually fits in the window. Whether the target system’s own performance degrades at 300,000 records the way it did not at 30,000. Whether the person who has to make the rollback call is reachable at 4am. Whether the delta capture, the mechanism that picks up source changes made after the last full pass, works when the source has genuinely kept changing rather than when someone simulated it.

A dry run that finishes early and cleanly is not a wasted rehearsal, it is the result you paid for. A dry run that is skipped because the schedule slipped is the single most reliable predictor of a difficult go-live we have seen.

The number that predicts the outcome

If you want one question to ask about a migration’s test coverage, it is not what percentage passed. It is: how many times has the complete migration been executed end to end?

Two or three passes means the exceptions are still being discovered in production. Twenty or thirty means they were discovered in week six, when they cost a fix instead of an incident. On the programme where the lead times were wrong, the answer was one. The chain had been run once, on the full data set, and the report was green.

The count is a proxy for something harder to measure: whether the migration is a repeatable system or a one-off event. Repeatable systems get tested, because testing them is cheap. One-off events get validated, once, by a report that says 100%.

Testing does not make a migration safe. It makes the failures happen early, in an environment where they cost engineering time rather than production. That is the entire trade, and it is why the phase before go-live in our methodology is a rehearsal and not a review.

For where testing sits in the full sequence, from audit through to cutover, see the ERP data migration guide or, if you are moving a product structure rather than a management system, the PLM data migration guide.