Moving Years of Records Without Losing Any

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

1. Count records, and count distinct values in key fields
2. Find the nulls and the empty strings
3. Look for duplicates by every plausible key
4. Check date and number formats for consistency
5. 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

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.

Frequently asked questions