Data migration testing: what a passing test actually proves

Consider this example: a load report shows 100%, with no rejects. The source supplier lead time is 6 weeks. The target field expects days, but the migration copies 6 without conversion. The record is accepted, yet the lead time is seven times too short. The report can be green while the data is wrong.

That report confirms that the records were accepted. It does not validate their business meaning.

What target acceptance establishes

A load report identifies which records the target accepted or rejected, along with the diagnostics its interface exposes. This is useful initial evidence. A migration chain can also produce count, value and relationship checks when those controls have been designed into it.

Acceptance alone does not establish that the loaded value retains the intended meaning. A lead time of 6 may be accepted despite the wrong unit. A cost centre may reference an existing but inappropriate parent, or a part may remain active despite an obsolescence decision. The target applies the technical and business constraints configured for the interface in use. It does not automatically verify every mapping rule between the two systems.

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. Each covers defects that the others may miss.

Level 1: the record was accepted. The load report confirms that the checks applied by the target interface passed. It does not establish business equivalence or operational readiness on its own.

Level 2: the counts reconcile. Every record in the source scope needs a traceable disposition: migrated, excluded by an approved decision, or still unresolved. This tracking must also identify records that were never processed. Retain source-to-target identifier mappings, including cases where several source records merge or one rule produces multiple target objects.

Equal counts between retained source records and target objects are expected for a one-to-one mapping. With merges or splits, totals may differ or even coincide without proving that the transformation is correct. Derive the expected count and identity of target objects from the approved merge, split and exclusion rules, then compare them with the loaded result. A silent exclusion of 4,000 records can remain invisible if the expected count is copied from the load report. Establish it independently from the source scope and approved decisions.

Level 3: the values are equivalent. Across the full migrated scope, compare each target value with the expected result derived from the approved mapping. That result may come from a source field, several grouped records, a reference dataset or an approved default. A code whose format changes must identify the intended object. This is where the lead time in weeks gets caught, provided the conversion rule has been translated into an explicit check.

Level 4: the business behaves. Run the agreed business scenarios end to end in the target. Take a purchase order through approval. Explode a bill of materials and compare it with the expected source-derived result, using equivalent configurations, revisions and effectivity conditions under the mapping. Post a stock movement and check the accounting entry. This level checks interactions between entities in business processes, complementing technical relationship and value checks.

Counting is not comparing

Reconciliation by count is popular because it is easy to automate and it produces a green tick. It reveals count discrepancies, such as missing or duplicate records. It does not establish equivalence of values.

Complement counts with checks of the values at risk. For supplier lead times, compare each part-supplier pair after normalising both values to the same unit: 6 weeks must become 42 calendar days, not 6 days. If the target uses a working-day calendar, define and test that conversion rule too.

Inventory quantities per warehouse and order values per customer remain useful controls for their own domains. They can stay unchanged when only a lead time is wrong. Even the count of distinct units can remain 1 before and after, so it cannot establish that a conversion occurred.

Choose controls that can detect the errors you anticipate. Combine detailed comparisons with aggregates: opposite errors can cancel each other out in a total.

The sample trap

A sample helps development, especially when its records are easy to explain and trace by hand. It is insufficient for acceptance if it leaves out complex cases present in the actual scope.

A sample selected solely for simplicity under-represents risk. The part created in 2008 by someone who left, carrying a status absent from current documentation, may never enter the tests. Cover nominal rules as well as cases that are difficult to explain.

Our article on Windchill to 3DEXPERIENCE migration pitfalls explains the limits of proofs of concept selected for simplicity. The same precaution applies to ERP data: include difficult cases before sizing the wider effort.

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 restricted to clean cases does not test exception handling.

Test nominal rules and exceptions

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. For illustration, assume a 95/5 split; the actual distribution must be measured on your scope.

Nominal rules need tests and full-volume checks. Group exceptions by cause and business decision, test each resolution rule, then verify its application to every affected record. A small share of exceptions can account for a large share of the testing effort when it concentrates difficult decisions.

An error tracking and replay mechanism reduces the cost of successive corrections. Replaying 12,000 exceptions and their dependencies may cost far less than rerunning the entire scope for every attempt. Batch processing with checkpoints and asynchronous architectures can both support this. Measure recovery times and verify that replay introduces neither duplicates nor inconsistencies.

The dry run checks cutover at real scale

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 checks what a unit test cannot cover: whether loading fits the cutover window, how the target behaves at 300,000 records instead of 30,000, or whether the rollback decision owner is reachable at 4am. It also exercises delta capture from the last instant covered by the extraction to the source write freeze, using a representative scenario of changes during that interval. In-flight transactions and any writes authorised after the freeze need explicit handling.

A dry run that finishes early and cleanly is not a wasted rehearsal, it is the result you paid for. Skipping the dry run because the schedule slipped leaves cutover duration and coordination without a complete rehearsal.

Repeat, then measure what the rehearsals demonstrate

The central question is: which controls ran against which scope, with what results and what remaining discrepancies? Record scope-level iterations and complete rehearsals separately.

The pass count indicates how repeatable the migration is, but it is not an acceptance criterion on its own. Track scope coverage, unresolved discrepancies, business results and cutover duration as well. Repeating an incomplete control twenty times will still miss a lead-time conversion it never checks.

Verify repeatability by retaining rule versions, source extracts, control results and timings. After a correction, rerun the affected controls and regression scenarios before the final rehearsal on the version intended for cutover.

Testing cannot guarantee an incident-free migration. It helps teams find errors early, in an environment where corrections take engineering time before affecting production. That is why the phase before go-live in our methodology includes a complete cutover rehearsal.

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.

The share of exceptions needs to be measured. A readiness audit identifies the rules and business decisions to cover before sizing the testing effort.