Data Migration Verification: Proving Every Record Made It
Why "It Looked Right" Is Not Enough
Most failed migrations are discovered weeks after cutover, not during it. The demo looks correct, the new dashboard renders, a few familiar account balances tie, and the project declares victory. Then a customer asks why their statement changed, or a treasury team finds a currency conversion that was applied twice, or a regulator's thematic review surfaces rows that were silently dropped. A migration is not verified until you can show that what is in the target matches what was in the source for every record in scope, and that nothing was lost or silently altered on the way. That is the entire purpose of data migration verification.
For regulated enterprises the question is not whether the new system seems fine. It is whether you can demonstrate it. A bank, insurer, or lender answering to a prudential regulator cannot rest on "we sampled 500 accounts and they looked good." The regulator asks for evidence: which records moved, how each field was transformed, what was checked, what was rejected, what was manually corrected, and who signed off on the result. Spot checks are not enough for regulators because a sample hides exactly the rows that carry the risk: exceptions, boundary cases, dormant accounts, and manual overrides.
The cost of getting this wrong arrives late. A mis-stated balance, a dropped loan, or a fee applied twice can sit quietly for months and surface only in an annual close or a supervisory review, by which point the consultants are gone and nobody can reconstruct what the pipeline did. Verification is the only thing that keeps a late discovery from becoming an unexplained one.
"It looked right" is an observation. Data migration verification turns that observation into evidence a regulator can test.
The Verification Hierarchy: What Each Method Proves
Verification is a hierarchy of methods, each with a different burden of proof, a different cost, and a different failure mode. The three levels to keep distinct are reconciliation, sampling, and full record-level proof.
Reconciliation: prove the whole, not the parts
Reconciliation compares aggregate values between source and target and requires them to tie. The core checks are record counts, control totals, financial sums such as outstanding balances, and hash digests over key columns to catch changes that balance each other out. Reconciliation is fast, cheap, and indispensable, and it proves exactly one thing: that the whole is intact. It does not prove that any individual record is correct.
A strong reconciliation spec runs at least four tiers, each catching a different class of error:
total row count per entity, which catches dropped or duplicated tables;
sum of monetary columns per entity, which catches balance drift;
count of records per status or product, which catches misclassification at scale;
hash digests over identity and value columns, which catch silent value changes that leave counts and sums untouched.
Most teams stop after the first two or three tiers, which is why most "reconciled" migrations still contain silent errors. The hash tier catches the case of two offsetting errors: one record gained exactly what another lost, so the total still ties while two customers hold wrong balances.
Two rules keep reconciliation honest. Compare against a frozen snapshot of the source, and wherever two systems compute the same figure differently, such as accrued interest under different rounding rules, document and approve the reconciliation rule before cutover.
Sampling: diagnose, do not certify
Sampling is a diagnostic, not a proof. A statistically drawn sample tells you how the migration is performing overall and where defects cluster, but it can never tell a regulator that every record made it. A random sample of a few thousand rows from a forty-million-row migration can flag common defects with high confidence, but it will almost certainly miss the rare, high-impact tail defects, and the tail is where production failures live. Those numbers are illustrative; the point is structural. A sample estimates the error rate in the population you already expect to be mostly fine, while the risk concentrates in the rows you least expect to be fine.
If you use sampling, use it to tune the verification effort and prioritize full checks on the riskiest populations. Stratify by product, vintage, status, and value, and oversample the edges. Treat any defect found in a sample as a signal to expand to a full check of that population, not as a one-off fix. Sampling finds the pattern; full proof closes it.
Full proof: account for every record
Full proof means every record is accounted for, end to end. The strongest form is a record-level comparison: for each source record, a matching target record exists with the same identity, the same transformed values, and the same lineage. It is the only level a regulator can fully re-perform without leaning on sampling assumptions or a team's assertion, and it is what provable migration means in practice.
A sound program runs all three, because each answers a different question: reconciliation proves the whole is intact, sampling directs the effort, and full proof closes out the populations that matter. The rest of this article turns full proof into checks you can actually run.
The Audit Trail: Five Questions Any Record Must Answer
Evidence is what turns a correct migration into a defensible one, and the useful test is concrete. For any record a regulator points at, the audit trail must answer five questions: where did it come from, what happened to it, who or what changed it, when, and how do you know the result is right. If you cannot answer all five for a single disputed record, the migration is finished but not verified.
Where did it come from: the mapping
The source-to-target mapping is the first half of the answer. It specifies, field by field, where each source value lands in the target and which transformations apply, and it lives under version control so a past decision can be replayed later. Write it at field level, not table level: customer_id may map directly, while account_balance is converted from minor units to major units and interest_rate is recalculated under a new rounding rule. If the mapping is ambiguous, every downstream check inherits the ambiguity.
The mapping is also the anchor for lineage, which ties every target record back to its source or to a documented origin such as a default or a manual correction, and which must be re-runnable, so the "where did it come from" answer becomes a path an auditor can walk rather than a claim.
What happened and when: the transformation log
The transformation log is where verification gets its teeth, and it is the artifact most teams treat as optional. It records what the pipeline actually did to each record: the input value, the rule applied, the output value, the rule version, and a timestamp. That version detail matters more than it looks. Rules change during a migration, and the only way to know whether a given record was transformed under the current rule or an earlier one is to log the version alongside the value.
A concrete example makes the point. Suppose a retail banking migration moves account balances from a legacy system that stores values in minor units to a target that stores them in major units, so the balance is divided by 100. That rule might exist in three versions over the life of the project: version 1 handled positive balances only, version 2 added negative-balance handling, and version 3 added the exception for zero-balance accounts flagged as dormant. The transformation log must be able to say, for a specific account, that its balance was converted from minor to major units using rule version 3, producing the value now stored in the target. Without the version, you cannot distinguish processed under the approved rule from processed under the rule you hoped you had fixed.
That is the difference between "we migrated the balances" and "we can show, for account 400021, exactly which rule, version, actor, and timestamp produced the stored value." A regulator's follow-up is rarely "is this right." It is "show me how you know."
Who or what changed it: the actor
Every change should carry its actor, whether an agent, a human analyst, or an approved correction. Automation makes this cheap to capture but not automatic: an agentic pipeline logs actions by default, while a manual one requires discipline. Accountability attaches to a name or a system, not to a process.
How do you know it is right: the re-runnable check
The last question separates verification from documentation. Every proof should be re-runnable: the reconciliation that produced a hash digest, the comparison that matched a source record to a target record, and the lineage trace that connected them should each be reproducible from the artifacts alone. The dated, immutable outputs of every check are the raw material of that re-run, and they should be stored with the care you give financial records, because in an audit they are financial records.
Testing, Dry Runs, and the Defect Rule
Verification is designed in before the first record moves, and it follows a progression: unit tests on transformation logic, integration tests on the pipeline, and end-to-end tests on a full copy of production data.
Test environments and test data
Test against a realistic, frozen copy of production, not a synthetic fixture. The defects worth finding live in the messy rows: nulls, duplicates, negative balances, multi-currency positions, odd characters in free-text fields, and records untouched for years. A synthetic dataset is clean by construction, so it teaches you nothing.
Dry runs
A dry run is a full rehearsal. Run the entire migration against the frozen copy, then reconcile the result as if it were the real cutover, with the same checks and the same bar. A dry run that passes the reconciliation bar you will apply to production is the single best predictor of a clean cutover. Iterate until the dry run passes twice with no unexplained differences, and treat the second clean run, not the first, as the readiness signal.
Defect handling
Every failed check goes into a defect log with severity, root cause, affected population, and a retest plan. Two rules make the log more than a to-do list. First, never fix a row without fixing the rule that produced it, because a row fix without a rule fix just re-creates the defect on the next load. Second, never close a defect until the full affected population has been re-extracted and re-verified; fixing one representative row tells you nothing about the rows you have not seen.
Parallel run: a cross-reference, not a detour
Parallel run migration, running old and new systems side by side and comparing outputs before switchover, is a core piece of the broader migration story. It catches logic differences that structural reconciliation misses, such as two systems applying a fee or a rounding rule differently. For the staged approach and the exit criteria that trigger cutover, see Core Banking Migration Without the Outage: A Provable Approach.
Sign-Off and Assurance
Assurance is the formal layer that sits on top of verification, where accountable owners certify that the controls ran and an independent party can re-perform them. The governance detail belongs to the migration program itself: who signs, and when, is staged across data owners, finance, operations, and risk, and the full sequence is laid out in Core Banking Migration Without the Outage: A Provable Approach. The evidence pack, the dated and versioned assembly of mappings, logs, reconciliations, defect resolutions, and sign-off decisions, is covered there and in What Is Certified Agentic Migration (CAM)?, which explains how an auditor opens the pack and traces any record through CAM without taking the vendor's word.
After cutover
Verification does not end at go-live. Run reconciliation on a scheduled basis after cutover, monitor for data quality drift, and keep the audit trail alive for the records that change. A migration is only as provable as the trail you can still produce a year later. That ongoing discipline is verification's end game: not a one-time clean cutover, but a system that can keep answering the five questions for as long as the data lives.
A Verification Checklist
Use this as the minimum bar before any sign-off:
Field-level source-to-target mapping, versioned and approved
Control totals and financial sums reconciled at entity and product level
Hash digests over identity and value columns, compared across source and target
Full record-level proof for high-risk populations, not samples
Stratified sampling used for diagnostics and defect discovery only
Transformation log recording input, rule, output, rule version, and timestamp per record
Lineage for every target record, including defaults and manual corrections
At least two clean dry runs against a frozen production copy
Parallel run migration for core systems before cutover
Defect log with severity, root cause, and full re-verification of affected rows
Evidence pack assembled, dated, and approved by the accountable owners
Run every item, and you can stand in front of a regulator and answer the only question that matters: can you prove every record made it? With data migration verification done properly, the answer is yes, and you can show your work.