How to Validate Data After a Migration
Validating data after a migration means proving three things in order: that the totals reconcile, that individual records match their source, and that the business can actually transact on them. Most teams do the first, sample the second, and skip the third, which is why defects surface in the first month-end close rather than in testing. On an ERP data migration, that skipped layer is the most common reason a program that looked finished turns out not to be.
Key takeaway: Reconciliation proves nothing about record accuracy, and record accuracy proves nothing about whether the system works. A migrated ledger can tie to the penny and still make it impossible to receive against an open purchase order. You need all three layers, and you need the third one before go-live, not after.
The three layers of post-migration validation
Post-migration validation is not one activity. It is three distinct tests answering three different questions, and passing one tells you almost nothing about the others.
Post-migration validation is the process of proving that data loaded into a target system is complete, accurate, and functional, verified through reconciliation of aggregate totals, record-level comparison against source, and execution of real business workflows against the migrated data.
Reconciliation answers did everything arrive. Comparison answers did it arrive correctly. Workflow testing answers can we operate on it. Teams conflate the first with the third constantly, because a clean reconciliation report feels like proof and is easy to produce. It is the weakest of the three tests. Every record can be present and correctly valued while a required field the target needs for posting sits empty, and no total will ever reveal that.
Run them in order but scope them together. The reconciliation you design determines what your comparison has to explain, and the workflows you plan to test determine which fields actually matter enough to compare closely.
Layer one: reconciliation
Reconciliation compares aggregate measures between source and target and explains every difference. The discipline is not producing matching numbers; it is being able to account for the ones that do not match.
Migration reconciliation verifies that record counts and control totals in the target system equal those in the source, adjusted for intentional exclusions, deliberate merges, and transformations that legitimately change values.
The reconciliation set that covers most ERP and CRM migrations:
Row counts by object, with the equation stated explicitly. Source count equals target count plus intentionally excluded plus deliberately merged plus legitimately rejected. If you cannot write that equation for an object and have it balance, you do not have a reconciliation, you have two numbers.
Control totals on every monetary field. Trial balance debits and credits, AR aging total, AP total, inventory extended value, open order value. These must tie exactly, not approximately, and rounding differences are a finding rather than a tolerance.
Distinct value counts on coded fields. If the source has 47 distinct customer classes and the target has 44, three values were silently mapped away and you want to know which.
Duplicate checks on natural keys in the target. Migration is a common way to create duplicate customers and items that did not exist in the source, particularly on multi-source consolidations.
Date range boundaries. Minimum and maximum transaction dates per object, which catches truncated history and off-by-one fiscal period errors that totals will not.
Build the reconciliation package as evidence, not as a check. Someone (an auditor, a controller, a regulator) will eventually ask you to prove the migration was complete, and the artifact that answers that question is worth far more produced at cutover than reconstructed a year later.
Layer two: record-level comparison
Comparison verifies that individual records carry the correct values, which is the layer where sampling does the most damage. The efficient technique is hashing rather than field-by-field inspection.
Record-level comparison verifies field values in the target against their source equivalents, typically by normalizing both sides to a common representation, computing a hash per record, and diffing the hash sets to isolate mismatches.
Normalize both sides first: trim whitespace, standardize case, apply the same date and decimal formats, resolve the transformations you deliberately applied. Then hash each record and compare the sets. What comes back is the exact list of records that differ, without inspecting fields you already know are fine. From there you triage: expected differences from documented transformations, and everything else, which is your defect list.
For fields where a transformation legitimately changes the value, compare against the expected output of the rule rather than against the source. This is the step teams skip, and it is why transformation bugs survive comparison testing: if you exempt transformed fields from comparison, you have exempted exactly the fields most likely to be wrong.
One principle worth holding firmly: a record that was found broken and then corrected is not a record that passed. It is a record that failed and was fixed, and it needs to run through the full check again. Fixed is not the same as pass, and treating them as equivalent is how a correction that breaks something else downstream reaches production.
The math on sampling
Sampling fails at post-migration validation for a reason that is arithmetic rather than philosophical. The defect classes that stall a program are rare by definition, and rare defects need enormous samples to catch reliably.
To have a ninety-five percent chance of catching at least one instance of a defect class, you need a sample size of roughly ln(0.05) / ln(1 - p), where p is the share of records carrying the defect.
| Defect prevalence | Records affected in 500,000 | Sample needed for 95% detection |
|---|---|---|
| 1 in 100 | 5,000 | ~300 |
| 1 in 1,000 | 500 | ~3,000 |
| 1 in 10,000 | 50 | ~30,000 |
| 1 in 100,000 | 5 | ~300,000 |
A defect affecting fifty records out of half a million requires a thirty-thousand-record sample for reliable detection. And that class of defect is not harmless because it is rare: the fifty records carrying an undocumented business rule are frequently your fifty largest customers, your regulated product lines, or your open contracts. Rarity in the data is not the same as unimportance to the business, and sampling treats them as identical.
The conclusion is not that sampling is useless. It is that sampling is a smoke test, and no volume of smoke testing substitutes for running the full set. Once comparison is automated, the marginal cost of validating five hundred thousand records instead of five hundred is compute, which is cheap relative to a delayed cutover.
Layer three: workflow testing
Workflow testing executes the transactions the business will actually run and confirms they complete. This is the layer that catches what the other two structurally cannot, and it is the one most often deferred until after go-live.
Workflow testing in a migration context executes real business processes (posting an invoice, receiving against an open purchase order, running a period close) against migrated data in a staging environment, to confirm the data supports operations rather than merely conforming to the schema.
The test set should mirror your actual operating calendar rather than a generic checklist. On a typical ERP migration that means: post a sales invoice and confirm it hits the right accounts; receive partial quantity against an open purchase order and confirm the remainder stays open; ship a partial sales order; apply a credit memo against an existing invoice; run the AR aging and tie it to the reconciliation total; run an MRP or planning pass and confirm it produces sensible suggestions; execute a full period close.
Then run the same set against your hardest records rather than your cleanest. The open PO with three partial receipts and a price change. The customer with a credit hold and a payment plan. The item with two units of measure and a substitute. These are the records that expose the gap between a schema-valid load and an operable system.
Structural validity is necessary and not sufficient. A record can satisfy every constraint the target enforces and still make a transaction impossible, because the target enforces the schema but the business enforces the process.
The seven checks that belong before the load
Everything above describes validating after a load, which is what the query usually means and what most programs actually do. It is also the expensive version. Each of these checks can run against staged data before the target ever sees it, and the ones that can are cheap by comparison.
A pre-load validation gate runs staged data against the target's constraints before load, so that failures are corrected in staging rather than diagnosed from load errors and rolled back.
The catalog that covers most rejections, and the one Settle runs as a gate before any load:
- Type and length: every value fits the target's declared type and field width
- Not null: every required field is populated
- Primary key uniqueness: no duplicates on the target's key
- Foreign key resolution: every reference resolves to a record that will exist at load time
- Enum membership: every coded value is one the target permits
- Row counts: staged counts reconcile to source counts with exclusions accounted for
- Source-to-staged diff: the transformation did what the rule said it would
Six of the seven are mechanical and none of them require the target system to be involved. The economics are the whole argument: a foreign key that will not resolve costs a configuration change if you find it in staging and a rollback plus reload if you find it from a load failure. This is the mechanism behind the reload cycles that drive migration cost: every exception class caught at the gate is a cycle that does not run.
Decision framework: when validation fails
If reconciliation fails but comparison passes, you have a completeness problem: records did not arrive, or arrived and were rejected silently. Check the load logs before you touch the mapping.
If comparison fails on transformed fields only, you have a rule defect. Fix the rule in staging and reload the affected object rather than patching records in the target, because patched records will not survive the next reload.
If comparison fails on untransformed fields, you have an extraction or encoding problem: character sets, truncation, date formats. Go back to extraction.
If everything passes but workflows fail, you have a business rule the target enforces that nobody documented. This is the expensive category and the one worth spending your investigation budget on, because it usually means an entire class of records is affected rather than the one you found.
If validation fails after go-live, stop and assess blast radius before correcting anything. Records that have already been transacted against cannot be corrected the same way as records that have not, and a bulk fix applied to a live ledger is how a data defect becomes an accounting problem.
Doing this before cutover instead
This is the problem Settle was built around. The engine proposes mappings with confidence scores, runs the seven-check gate against the full staged dataset, loads to staging, and then executes the reconciliation, comparison, and workflow layers automatically, with the evidence package produced as output rather than assembled by hand.
Every check reported against the full dataset, with failures isolated to the exact records.
The point is not that automation validates better than a careful analyst. It is that automation validates the whole set, repeatedly, at a cost that makes running it in week two rather than week twenty rational. What it does not do is decide what a record is supposed to mean when the source and the business disagree. That still needs an owner.
What a missed validation layer costs
On one implementation, reconciliation was clean. Every total tied, every count balanced, and the comparison run against our sample came back without findings. We went into the dress rehearsal confident and discovered that a category of open orders could not be received against, because a field the source treated as optional was required by the target's receiving process. The records were all present. They were all correctly valued. They were all unusable for the one transaction the warehouse needed to run on day one.
We had validated the data and never validated the operation. That distinction cost the program a cutover window.
| Layer | What it catches | What it misses |
|---|---|---|
| Reconciliation | Missing or duplicated records, value drift | Anything at record level |
| Record comparison | Wrong values, transformation defects | Anything about operability |
| Workflow testing | Unusable but valid records | Rare records not in the test set |
| All three, full dataset | Each of the above at population scale | Decisions nobody made |
Frequently Asked Questions
How do you validate data after a migration?
Validate in three layers. Reconcile aggregate counts and control totals between source and target, explaining every difference. Compare records individually, typically by normalizing and hashing both sides and diffing the results. Then execute real business workflows against the migrated data to confirm the system is operable, not merely schema-valid. Passing any one layer does not imply the others.
What is the difference between reconciliation and validation?
Reconciliation is one component of validation. It compares aggregate measures (row counts, control totals, balances) and confirms they agree between source and target. Validation is the broader activity that also includes record-level accuracy and functional testing. A migration can reconcile perfectly and still be unusable, which is why reconciliation alone is not sufficient evidence of a successful load.
Should I validate a sample or the full dataset?
Full dataset, wherever the comparison can be automated. To have a ninety-five percent chance of catching a defect affecting one record in ten thousand, you would need a sample of roughly thirty thousand records, and rare defects are frequently concentrated in your most important accounts. Sampling is a reasonable smoke test and a poor substitute for population-scale comparison.
What should be in a data migration audit trail?
The reconciliation package with the count and control total equations for every object, the transformation rules as executed with version history, the exception log showing what failed and how it was resolved, the record-level comparison results, workflow test evidence with sign-off, and the go/no-go decision record. Produce it at cutover. Reconstructing it later for an auditor is substantially more expensive than generating it as you go.
How long does post-migration validation take?
On a manual process, validation and reconciliation commonly consume several weeks per full load cycle, which is a large share of why implementations run four to eight cycles. Automated full-dataset validation reduces the per-cycle cost to hours, which changes the calculus on how early and how often you run it. The constraint then becomes how quickly the business can adjudicate exceptions, not how quickly checks execute.
Can we validate data after go-live instead?
You can, and many programs end up doing so, but corrections become substantially harder once records have been transacted against. A wrong value in a staging environment is a reload. The same value in a live ledger with postings against it is an accounting correction with an audit trail. Validate before cutover and use post-go-live monitoring to catch what the cutover validation could not.
Bringing it together
Validating a migration means proving completeness, accuracy, and operability, and the third one is where programs get caught because it is the only layer that cannot be inferred from the other two. Run all three against the full dataset, run them before cutover rather than after, and keep the evidence.
If you are planning a cutover and want the validation layer proven against your real data rather than a sample, Settle will run it as a fixed-price engagement. Book a scoping call.