Why data migration goes wrong
Migration is usually planned last and discovered to be hard at the worst moment. The new system is built and tested with tidy sample data. Then the real data arrives, with its duplicates, missing fields, retired codes and free-text notes holding information that should have been structured. Go-live slips, or happens with errors that take months to find.
The remedy is to treat the move as a workstream that starts with the project, has its own plan and is owned by someone in the business who knows what the data means. Developers can move data. Only the people who use it can say whether it is right.
Decide what moves, what is archived and what is left
| Kind of data | Usual decision | Question to settle |
|---|---|---|
| Master data: customers, suppliers, items, staff | Move, after cleaning | Which records are active? Who resolves duplicates? |
| Open transactions: orders, invoices, jobs in progress | Move, in their current state | What is the cut-off, and how are part-finished items handled? |
| Closed history | Move a recent period, archive the rest | How far back do people really look, and what must be kept by law? |
| Documents and attachments | Move those linked to moved records | Where are they stored, and are the links reliable? |
| Balances and totals | Move as opening figures | Agreed with finance, as at the cut-off date |
| Users and permissions | Recreate, do not copy | A good moment to remove accounts nobody uses |
| Configuration and reference lists | Rebuild in the new system | Old codes rarely fit a new structure |
Retention periods for financial, employment and customer records are set by law and differ by country. Confirm yours with your accountant or legal adviser before deciding what to leave behind, and keep the archive searchable.
Profile the data, then clean it at the source
Profiling means measuring what is really in the old system before making plans. How many records of each kind? How many have an empty required field, an impossible date, a code that no longer exists? How many customers appear twice? The results are always worse than expected, and knowing early turns a crisis into a task list.
- 01
Measure
Run counts and quality checks on every table in scope, and share the findings with the business owner.
- 02
Decide rules
For each problem, agree a rule: merge duplicates by which key, fill missing values with what, drop records older than when.
- 03
Clean in the old system where possible
Corrections made at the source benefit daily work now and survive every rehearsal. Corrections made in a script must be repeated and maintained.
- 04
Assign the work
Much cleaning needs human judgement. Give named people a list and a deadline, well before cutover.
Write the mapping, field by field
The mapping document states, for every field in the new system, where its value comes from and how it is transformed. It is tedious and indispensable, because it is the only place where the business can review what will happen to its data before it happens.
- Source and target. Each target field with its source field, or the rule that derives it.
- Transformations. Splitting a name, converting units, translating old codes to new ones, reformatting dates and phone numbers.
- Defaults. What a field receives when the source is empty, agreed with whoever relies on it.
- Rejections. What makes a record unfit to load, and where rejected records go for review.
- Identifiers. Keep the old identifier on each new record. It is what makes reconciliation, and later questions, answerable.
- Order of loading. Customers before their orders, items before their stock.
Rehearse the whole move
A migration that has never been run in full will not work the first time. Plan at least three complete trial runs into a test copy of the new system, each using a fresh extract of real data.
The first run finds the mapping errors. The second proves the fixes and measures how long the move takes, which decides how much downtime to plan. The third should be a dress rehearsal of cutover, performed by the people and in the sequence planned for the real day, followed by users testing their own work on the migrated data. Continue rehearsing until a run completes with no surprises.
Reconcile: prove that the data arrived
| Check | What is compared | Catches |
|---|---|---|
| Record counts | Number of records per type, old against new, with rejections explained | Records dropped or loaded twice |
| Control totals | Sums that matter: balances, stock quantities and values, open order amounts | Wrong amounts, lost decimals, currency or unit errors |
| Field-level samples | A random set of records checked field by field against the source | Mapping and transformation mistakes |
| Relationship checks | Every order has a customer, every line an item | Broken links between records |
| Business scenarios | Users perform real tasks on migrated records | Data that is present but unusable |
| Reports side by side | A key report run in both systems as at the cut-off | Differences nobody thought to test |
Each check should produce a short written result that a named person signs. Differences are either corrected or explained and accepted. That record is what allows finance, auditors and management to trust the new system from its first day.
A worked example
Moving a wholesaler to a new order system
This example is invented to show the sequence and the kind of findings to expect. A wholesaler is replacing an old order and stock system.
- ScopeActive customers and items, open orders, stock on hand and two years of invoices move. Older history stays in a read-only archive.
- ProfilingOf 9,400 customer records, 2,100 have had no order in five years and 380 are duplicates. The sales office spends three weeks merging and marking inactive records in the old system.
- First rehearsalStock value in the new system is lower than in the old. The cause is a unit conversion: some items are stocked in boxes and sold singly. The mapping is corrected.
- Third rehearsalCounts match, stock value matches to the cent and open order totals match. The move takes five hours. Warehouse staff pick ten real orders in the test system without difficulty.
- CutoverOrder entry stops on Friday evening. The move runs overnight, finance signs the control totals on Saturday morning and the business opens on Monday in the new system.
The unit error found in the first rehearsal would have misstated stock on day one. Rehearsal turned it into a line in a mapping document.
Plan the cutover and the way back
- 01
When does work stop in the old system?
What to decideA freeze time after which nothing is entered, announced well ahead.
- 02
Who does what, in which order?
What to decideA runbook with owners and expected durations, proven in the dress rehearsal.
- 03
What must be true to go ahead?
What to decideReconciliation thresholds agreed in advance, so that the decision at the end of a long night is not a matter of opinion.
- 04
How do you go back?
What to decideThe old system kept intact and a decision deadline. After users begin entering new data, going back becomes far harder.
- 05
What about work done during the move?
What to decidePaper forms or a holding spreadsheet for anything that cannot wait, entered afterwards by a named person.
- 06
How long is the old system kept?
What to decideRead-only, for reference, for an agreed period.
Migration and reconciliation checklist
Data migration checklist
0 of 11 checkedWhat to do next
Run the counts. Ask for the number of records in each main table of your current system, and how many of them have been touched recently. That one report usually reshapes the scope and shows where cleaning should begin.
Our data migration page explains how we plan and run these moves. If the migration is part of replacing an older system, the guide to planning a legacy software modernization project covers the wider plan. You can also tell us what you are moving.
Common questions
How long does a data migration take?
The move itself may take hours. The preparation takes far longer, and depends on how many kinds of data are in scope and how clean they are. Profiling, cleaning, mapping and rehearsals should run alongside the build of the new system from the start. Projects that begin migration work only when development ends are the ones that slip.
Should we migrate all our historical data?
Rarely. Old history carries old errors, lengthens every rehearsal and often does not fit the new structure. Move what people use in daily work and what the law requires to be available, and keep the rest in a searchable archive. Ask users how far back they really look. The honest answer is usually much shorter than the first one.
Who is responsible for data quality in a migration?
The business that holds the data. A development company can profile it, report problems, apply agreed rules and move it accurately. It cannot know which of two duplicate customers is the real one. Name an owner on your side with the authority to decide, and give the cleaning work to people who know the records.
What is the difference between validation and reconciliation?
Validation checks that each record meets the rules of the new system as it is loaded: required fields present, formats correct, references valid. Reconciliation compares the new system with the old after loading, to prove that everything expected arrived and that totals agree. You need both, since data can be perfectly valid and still be incomplete.
Can we run the old and new systems in parallel instead?
A short parallel period is a useful safeguard, with both systems processing the same work and results compared. Long parallel running is costly, because every transaction is entered twice and staff quickly favour one system. Use it for a defined period on the areas of highest risk, with a clear date on which the old system becomes read-only.