Migrating data from an old system: the part everyone underestimates
Why moving data between systems takes longer than building the new one, and the process that makes it predictable.
The short answer
Every project that replaces a system has a line in the plan called data migration, and it is the line that overruns. The reason is that the old data is dirtier, stranger and more meaningful than anyone remembers. Duplicate customers under three spellings. A notes field that has been used for delivery instructions, credit warnings and birthdays. Dates in two formats. Records that reference products deleted years ago. None of it can simply be moved; each oddity must be decided. The process that makes migration predictable is rehearsal: profile the data, map and decide the rules, build a repeatable script, run it against fresh copies until it runs clean and reconciles, and only then run it for real in a planned window.
The process
| Stage | What happens | Output |
|---|---|---|
| 1. Inventory and profile | List every entity and field; count records; measure empties, duplicates, formats, orphans | A data profile that surprises everyone |
| 2. Decide scope | With the business: what migrates, what is archived read-only, the cut-off | A written scope |
| 3. Map and rule | Field by field: where it goes, how it is transformed, what happens to each oddity | A mapping document with rules for every exception |
| 4. Build the script | A repeatable, idempotent migration that logs every decision it makes | Code, in the repository |
| 5. Rehearse | Run against a fresh copy; reconcile; fix; repeat, many times | A clean run with reconciliation matching |
| 6. Validate with people | The users check their own records in the migrated copy | Sign-off per area |
| 7. Cut over | Freeze the old system, run the final migration, reconcile, switch | Go-live with numbers that match |
| 8. Archive | Old data kept read-only and reachable for the required period | A simple archive with search |
Why rehearsal is the method
- The first run always fails on something the profile missed. That is expected.
- Each run improves the rules and the script; the script is code, so improvements accumulate.
- Timing becomes known: the final run’s duration is measured in rehearsal, so the cut-over window is real.
- Reconciliation becomes routine: the same checks after every run, so the final one is a formality.
- People validate on a copy, not on live data, and their findings feed the rules.
- The final run is the boring one.
Deciding what not to migrate
Old records that nobody will act on do not need to fit the new model. Closed cases older than the retention requirement, historic orders, former customers: an archive that is read-only, searchable and kept for the required period serves every real need at a fraction of the effort. Agree the cut-off with the business and the compliance requirements, and the migration shrinks to the data that matters.
What this means for you
Expect data migration to be a project inside the project: profiled, scoped, mapped, scripted, rehearsed until clean, reconciled with numbers, validated by your people and cut over in a measured window, with old data archived rather than forced. Insist on rehearsals and reconciliation. The migration that has been run ten times on copies is the one that is uneventful on the day.
Frequently asked questions
Why can we not just export and import?
Because the two systems model the world differently, and the old data contains a decade of exceptions: duplicate customers, free-text fields used for three purposes, dates in several formats, records that reference things that no longer exist. Each must be mapped and each oddity decided. Export and import is the last step of a process, not the process.
How do we know the migration worked?
Reconciliation: record counts per entity match after accounting for deliberate exclusions; totals such as outstanding balances match; a random sample of records is compared field by field; the people who know the data check their own customers and cases. Written down, per rehearsal, until the numbers agree.
Should we migrate everything?
Rarely. Old records that nobody will act on can be archived read-only in a simple form rather than forced into the new model. Deciding the cut-off with the business, and what stays reachable in the archive, often removes half the migration's complexity and most of its risk.