Data migration validation: prove that business meaning survived

Equal row counts can conceal a failed migration. Two omitted invoices and two duplicated invoices leave the count unchanged. A customer identifier can survive while pointing to the wrong account. A timestamp can look plausible after conversion and still move a transaction into the wrong reporting period.

A defensible data migration therefore needs an acceptance argument about business meaning, not just a successful transfer log. The destination must preserve the relationships and rules on which applications, reporting, and operations depend. Validation should make that claim testable and expose the exceptions that remain.

Consider a hypothetical distributor moving orders, invoices, and customer records into a new platform. Its management team needs outstanding balances and fulfillment status to remain reliable. The source continues changing during preparation, several legacy status codes have no direct equivalent, and some customers have duplicate identifiers. These conditions determine the reconciliation design long before the transfer begins.

Agree on the population and comparison boundary

Start by defining which records belong in the move. Active customers, archived invoices, canceled orders, and transactions awaiting adjustment may have different inclusion rules. A missing record is a defect only when it belongs in the agreed population, but an undocumented exclusion can remove an important liability or operational obligation without attracting attention.

Choose a consistent comparison point. If the source continues receiving updates, comparing its current state with an older destination snapshot creates differences unrelated to transformation correctness. Use an extraction boundary or a documented change position appropriate to the migration method. Record which updates the destination has incorporated so reviewers can distinguish timing gaps from actual defects.

Account for relationships crossing that boundary. An archived customer may still own an open invoice. Excluding the customer while moving the invoice leaves a financial record without a usable owner. A migration scope expressed as independent table filters is insufficient when the business population depends on relationships between tables.

Make acceptance responsibility explicit. Engineering can establish that a query ran correctly, while the finance owner decides whether a balance interpretation is acceptable. These roles should agree on the comparison rules before reviewing an exception report. Otherwise, unresolved policy questions arrive during cutover as apparent technical failures with no authorized decision-maker.

Identify the invariants that must survive

An invariant is a relationship or condition that should remain true across the move. For the distributor, an invoice must retain its customer association, currency, outstanding amount, and relevant payment history. The destination can use a different schema while preserving these properties. Exact representation is less important than correct interpretation.

Write the rules at a level that can detect a meaningful error. A total outstanding balance is useful, but positive and negative errors can offset each other. Break the comparison down by account, currency, period, and status where those dimensions affect operations. The objective is to reveal compensating mistakes without drowning reviewers in low-value comparisons.

Include permissions and visibility when they govern use. Moving a record correctly does not make it safe if an unauthorized business unit can now view it. Likewise, a record inaccessible to the team responsible for collection is operationally missing even when its database row exists. Application access is part of preserving the business relationship.

Separate required invariants from intentional changes. The distributor may decide to consolidate duplicate customer identifiers, but every dependent invoice must follow the approved mapping. Treat consolidation as a reviewed business transformation with a retained cross-reference, rather than a convenient cleanup step that makes reconciliation harder after the original identifiers disappear.

Combine checks that fail in different ways

Use population coverage to detect omissions and unexpected additions. Compare stable keys where available, then investigate unmatched keys before trusting aggregate equality. A count establishes quantity; key coverage establishes membership. Neither alone proves that values or relationships are correct, so each should occupy a defined place in the acceptance argument.

Add field and relationship comparisons for consequential data. Check currency, identifiers, status meaning, numeric precision, and parent-child associations. Test unusual but legitimate values, such as old dates, negative adjustments, and long reference strings. These cases often reveal conversion assumptions that ordinary records never exercise.

Checksums can accelerate comparison when the chosen representation is comparable. Across different schemas, canonicalization rules must preserve distinctions that matter. Normalizing every empty string to null may be acceptable for one field and wrong for another. A matching hash establishes agreement under those rules, not independent proof that the rules themselves are correct.

Keep validation independent of transformation logic

A transfer script and its validator can share the same mistake. If both derive outstanding balance from the wrong source field, their results agree convincingly. Design critical acceptance checks from independently reviewed definitions, and use authoritative source values or business calculations where practical. Independence is about reasoning as well as separate code.

Review mappings with domain owners. A legacy status called closed might mean paid in one subsystem and canceled in another. Mapping both to one destination status can alter collection and fulfillment behavior. Capture the source context needed to interpret the code rather than relying on the label alone.

Retain the original value when applying an approved correction, where retention is appropriate and permitted. This supports explanation of why a destination differs and prevents corrections from becoming invisible. Keep the transformation rule, version, and input reference so a reviewer can reproduce the result without depending on the engineer who wrote the job.

The lineage discipline described in metric governance is relevant here. A trusted result needs a traceable path from source meaning through processing to use. That path should distinguish faithful transfer, deliberate correction, and unresolved ambiguity. Treating all three as equivalent makes later reporting disputes almost inevitable.

Manage exceptions as decisions, not a percentage

An aggregate pass rate is a poor acceptance rule when failures have unequal consequences. A small set of unmatched high-value invoices can matter more than many cosmetic text differences. Classify exceptions by business effect, affected population, reversibility, and the possibility of a wider systematic error.

Give each exception category an accountable owner and a proposed treatment. Options may include correcting the source, changing a mapping, accepting a documented limitation, or excluding a population from the current wave. Avoid silently inserting defaults to satisfy destination constraints. A plausible invented value can be harder to detect than an explicit missing value.

Investigate patterns before fixing records individually. Several discrepancies sharing one source system or date range may indicate a common extraction or interpretation problem. Repairing only the visible examples can leave the same defect elsewhere. Use the exception analysis to refine the population checks and then rerun the relevant comparisons.

Decide what blocks migration and what can be resolved afterward. Post-migration treatment needs a safe operating arrangement: affected records may require restricted use, a visible warning, or a manual reconciliation queue. An accepted exception is not a closed issue simply because the project has moved to the next stage.

Test the workflows that consume migrated data

For the distributor, verify that an authorized operator can locate an invoice, see its payment history, interpret the balance, and take the correct collection action. A database comparison cannot establish that the new application applies the correct filters or presents the right relationship. Test the complete workflow with records chosen for business risk.

Include negative and boundary cases. A canceled order should not reappear as ready for dispatch. A consolidated customer should not inherit another customer’s permissions. An adjustment spanning reporting periods should retain the agreed accounting interpretation. Representative happy-path demonstrations are useful, but they cannot establish these constraints.

Test integrations and scheduled processing that may modify the destination after loading. An application job can overwrite an otherwise correct migrated value using an obsolete assumption. Validation must account for the operating system that consumes the data, not only the destination immediately after import.

Connect acceptance to cutover and rollback planning. The final comparison should run against the migration boundary that actually precedes switching authority. Rehearse its runtime and resource requirements so a supposedly quick acceptance check does not consume the entire maintenance window or compete with the transfer itself.

Make the acceptance package reproducible

Retain the population definition, extraction boundary, mapping versions, validation queries, and classified exceptions. Include the destination version and the conditions under which checks were run. A reviewer should be able to understand what was accepted and rerun the relevant evidence without reconstructing the project from chat messages.

Recovery preparation needs a documented operating plan, named decision owners, and an exercise that tests the proposed recovery sequence. For this migration, the concrete requirement is to know how the accepted state can be restored and how work occurring after the move will be handled. A backup is necessary in many designs, but its existence alone does not establish a workable business recovery path.

Keep validation after cutover for the period required to expose delayed dependencies. Scheduled imports, reporting cycles, and payment updates can reveal issues absent from the initial checks. Agree on the owner of this monitoring and the conditions for closing migration exceptions, rather than ending responsibility when the transfer team finishes.

The distributor can accept its migration when balances, relationships, permissions, and critical workflows are supported by coherent evidence at an agreed boundary. Equal counts remain one useful signal. They become trustworthy only as part of a wider explanation of what moved, what changed intentionally, and what the organization can now rely on.