Migration Testing Strategy

A migration without a testing strategy is gambling. You are betting that the scripts work perfectly, that no data gets lost, and that the application runs fine on the new database. Spoiler: that bet almost never pays off.

Migration testing has three clear phases. Each phase has a specific purpose. Skip any phase and you are flying blind.

The Three Phases

Phase 1: Pre-Migration Testing

Before anyone touches the migration button, you establish your baseline. This is your "before photo." Without it, you have nothing to compare against after the migration.

1. Record counts — total rows in every table being migrated
2. Data profiling — distinct values, NULL counts, min/max ranges per column
3. Checksums — hash or checksum of critical columns to detect silent changes
4. Sample snapshots — full row dumps of 50-100 representative records
5. Data quality issues — document known problems (duplicates, orphans, bad data)
6. Backup verification — confirm backups exist and can actually be restored
7. Mapping document review — source field to target field mapping, transformation rules
8. Dry run on test environment — run the full migration on a copy of production data

Phase 2: During Migration

The migration is running. Your job is to monitor, not interfere. Watch for failures, track progress, and be ready to call a rollback if things go wrong.

• Monitor migration logs for errors, warnings, and skipped records
• Track progress — how many records processed vs total expected
• Watch for table locks that block other operations
• Check disk space — migrations can create temporary tables that eat storage
• Verify no writes happening to source during migration (read-only mode or downtime window)

Phase 3: Post-Migration Testing

The migration finished without errors. Great. But "no errors in the log" does not mean "no problems in the data." Now comes the real testing.

1. Record count matching — source counts vs target counts for every table
2. Data integrity checks — checksums, sample comparison, field-by-field validation
3. Referential integrity — all foreign keys resolve, no orphan records
4. Transformation verification — split columns, merged fields, type conversions
5. Application smoke test — log in, perform key workflows, check reports
6. Performance comparison — critical query times before vs after migration
7. Rollback test — can you actually roll back if needed?