How to validate data after a database migration

A matching row count is useful, but it does not prove that a migration is correct. A stronger check reconciles source and target records by key and inspects what is missing, unexpected, changed or duplicated.

1. Export comparable source and target datasets

Start from two snapshots that represent the same logical population: one from the source system and one from the migrated target. Keep stable identifiers and the fields whose values should survive the migration.

CSV is often enough for an independent reconciliation because it decouples the check from the migration code itself. The validation process can then inspect the result without relying on the same transformation logic that produced it.

2. Choose the record key carefully

The key determines which source row corresponds to which target row. Use a stable primary key or business key. If uniqueness depends on more than one field, use a composite key such as company plus customer code.

Do not silently resolve duplicate keys. A duplicated key makes matching ambiguous and should be reported for review.

3. Check missing and extra records

A source key that does not exist in the target is a missing record. A target key that did not exist in the source is an extra record. Both can be important even when total row counts happen to match.

For example, one lost source row and one unexpected target row can cancel each other numerically while still representing two migration defects.

4. Compare values for matched keys

Once a key exists on both sides, compare the mapped fields. Report the record once as changed while preserving the individual field differences so that remediation remains understandable.

Column names can change during a migration, so source-to-target mapping should be explicit rather than assuming identical schemas.

5. Normalize only what is intentionally equivalent

Whitespace, letter casing and NULL-versus-empty-string behavior can create noisy differences. Apply normalization rules deliberately and keep the original values available for review.

A normalization rule should affect comparison, not rewrite evidence. If two values compare as equal after trimming, the original source and target values should still remain untouched.

6. Produce an issue report

A useful reconciliation report separates changed, missing, extra and duplicate records. Exporting those issues as CSV gives developers, DBAs and business owners a compact remediation list that can be attached to a migration ticket or validation record.

Migration validation checklist

• Source and target snapshots represent the same population.
• A stable single or composite key is selected.
• Duplicate keys are identified before matching.
• Missing source records are listed.
• Unexpected target records are listed.
• Mapped values are compared for matched keys.
• Normalization rules are explicit and limited.
• Differences are exported for review and remediation.