Data Migrations: How to Validate That Nothing Was Lost

The go-live looks clean. Production data is in the new system. Users are logging in. Six weeks later, someone finds an entire category of historical records that did not make the migration.

This scenario is common enough that validation frameworks for data migrations should be treated as non-negotiable, not optional.

Row count validation is not enough

The first instinct is to count rows: old system had 50,000 customer records, new system has 50,000 customer records. Done.

This misses every case where records were incorrectly transformed, truncated, or mapped to wrong categories. A record can exist in both systems and still be wrong.

The validation framework

Start with referential integrity: every foreign key in the new system resolves to a valid parent record. This catches broken relationships that make data invisible to the application even when it exists in the database.

Next, field-level sampling: for a statistically meaningful sample of records, compare every field value between source and target. Automated comparison scripts running against both systems simultaneously make this manageable.

Aggregate reconciliation: key financial and operational totals must match. Total revenue by period, total inventory value by category, total units shipped by customer. If the aggregates match, the detail is likely correct. If they do not, you have a known discrepancy to investigate.

Business rule validation: any transformation applied during migration needs to be tested against edge cases. What happened to records with null values in required fields? How were records with special characters in key fields handled?

Run validation in production, not just test

Validation done only in the test environment is not real validation. Data that seemed clean in test often reveals anomalies in production because test environments rarely replicate the full history and edge cases of production data.

We build validation frameworks into every migration engagement we manage. If you have a migration coming up and want to talk through the validation approach, reach out.