Data Migration: How to Move Systems Without a Disaster

Migrations fail on data, not on code

The transformation logic is rarely the hard part. What causes migration disasters is the source data being different from what everyone believed: fields used for a second purpose, records with impossible dates, a customer with 400 addresses, encoding problems in names.

None of that appears in a specification. It appears when you profile the data, which is why profiling comes first.

Step one: profile before you plan

1. Count records per entity, and compare with what people believe the counts are
2. Check completeness per field — the ones that are 40% empty tell a story
3. List distinct values in every field that should be constrained, and expect surprises
4. Find the outliers: oldest record, largest text, most relationships, strangest characters
5. Identify duplicates before rather than after they arrive in the new system

Profiling routinely changes the plan. Discovering that a “notes” field has been used to store delivery instructions for six years is the kind of thing that reshapes a migration — and it is far better discovered in week one.

Step two: rehearse the whole thing, twice

A rehearsal is the complete migration into a copy of the target, with real data at real volume. The first one finds the data surprises. The second confirms the fixes and gives you a reliable timing.

Timing matters more than teams expect. If the migration takes eleven hours and your cutover window is four, that is much better known two weeks before than on the night.

Step three: reconcile properly, not by spot check

• Record counts per entity, source versus target, matching exactly or explained exactly
• Financial totals, which must match to the penny
• Checksums on key fields across the full set
• Relationship integrity — no orphaned records
• A sample of complete records compared field by field, chosen to include the weird ones

“We looked at twenty records and they seemed fine” is not reconciliation, and it is what most projects actually do.

Step four: cut over with a way back

Freeze changes in the source, run the final migration, reconcile, then open the new system. Keep the old one available read-only for at least three months — someone will need a record that did not migrate cleanly, and having it available turns a crisis into an inconvenience.

Have a rollback plan written down and agreed before the night, including the point of no return. Deciding at 2am whether to roll back is not a decision anyone makes well.

What to leave behind

Not everything deserves to come. Archive rather than migrate anything that is finished, inactive for years, or known to be junk. Migrating rubbish costs time, extends the window and pollutes the new system on day one.

Agree the archive rules with the business rather than deciding technically, and keep the archive accessible — the objection to archiving is almost always “what if we need it”, which is answerable.

Frequently asked questions