It is always worse than expected
Every dataset has surprises: duplicate records, missing required fields, dates in three formats, and a column that means something different for records created before a certain year.
Profile the actual data before estimating the migration. Estimating from a description of the data rather than from the data itself is how migrations double in cost.
Profile before planning
- Count records, and count distinct values in key fields
- Find the nulls and the empty strings
- Look for duplicates by every plausible key
- Check date and number formats for consistency
- Find the records that break your assumptions
Migrate repeatedly
The migration should be a repeatable script, run many times into staging, not a one-off manual exercise. That way the final run is the twentieth rather than the first.
- Rerunnable from a clean state
- Logging every record that could not be migrated, with the reason
- Producing a reconciliation report automatically
- Fast enough to run within your cutover window
Reconcile before going live
| Check | Why |
|---|---|
| Record counts by type | Nothing silently dropped |
| Financial totals | Money must balance exactly |
| Spot checks on real records | Fields mapped correctly |
| Relationship integrity | Nothing orphaned |
| Date ranges | Oldest and newest as expected |
Decide what not to migrate
Not everything needs moving. Historical records nobody uses can stay in a read-only archive rather than complicating the new system's data model.
That decision frequently halves the migration effort, and it is worth having the conversation explicitly rather than defaulting to everything.