Data migration is scheduled as a technical task near the end of the project and then becomes the reason the project is late. The pattern is consistent enough to be predictable: nobody owns the data, cleanup starts six weeks before go-live, and the team discovers a decade of duplicate customers, items with no cost and inventory records that disagree with the warehouse — all after the go-live date has been announced to the board.
This guide covers what actually needs to move, how to profile and clean data before anyone touches a mapping document, how to run trial loads properly, how to reconcile in a way that would satisfy an auditor, and how to structure cutover so it takes a weekend rather than a fortnight.
Why Migration Deserves More Attention Than It Gets
Three things make data migration disproportionately dangerous.
• Errors become permanent and invisible. A wrong cost on an item in a spreadsheet is an annoyance. The same wrong cost inside an ERP flows into margin reports, valuation, pricing decisions and financial statements, and everyone trusts it because it came from the system.
• The scale is unknowable until you start. No one can estimate how dirty their data is from memory. It always turns out to be worse, and the difference between expectation and reality is where the schedule goes.
• It sits on the critical path with nothing after it. Every other task can be deferred or descoped. Migration cannot — you cannot go live without data.
The practical consequence: data cleanup is a prerequisite, not a project deliverable.Starting the ERP project before addressing it does not fix it faster; it just moves the discovery of the problem to a more expensive moment.
What Actually Needs to Move
The instinct is to bring everything. Resist it. Migrating twenty years of history multiplies effort, extends testing and imports every historical error into a system people are supposed to trust.
| Data category | Examples | Usual decision |
| Master data | Customers, suppliers, items, chart of accounts, employees, price lists | Migrate — this is essential |
| Structural data | Bills of materials, routings, warehouse locations, tax codes | Migrate — required for the system to function |
| Open transactions | Unpaid invoices, open purchase and sales orders, work in progress | Migrate — the business depends on them |
| Opening balances | Trial balance, inventory quantities and values, fixed asset registers | Migrate — reconciled to the last closed period |
| Recent history | Last one to two years of closed transactions | Migrate selectively, if there is a genuine operational need |
| Deep history | Older closed transactions, superseded records | Archive — keep the legacy system readable instead |
| Documents | Attachments, contracts, drawings, certificates | Migrate selectively, or link to a document store |
The test for historical data is simple: what specific business process breaks if this is not in the new system? “We might want to look at it” is answered by archiving and read-only access to the legacy system, which costs a fraction of migration and testing.
| The archiving decision people regret skippingKeep the legacy system, or a full export of it, in a readable state for as long as your record-retention obligations require.It is far cheaper than migrating history, and it answers the audit question that will eventually be asked. |
The Migration Process, Stage by Stage
Stage 1: Profile before you plan
Data profiling means measuring the actual state of your data rather than assuming it. Run these counts early — in the first month of the project, not the last.
• Total records per entity, and how many are genuinely active in the last twelve months.
• Duplicate rate on customers, suppliers and items, tested on more than exact name matches.
• Completeness: percentage of records missing tax identifiers, addresses, costs, units of measure.
• Consistency: how many different formats exist for the same field — phone numbers, country names, units.
• Referential integrity: transactions pointing at records that no longer exist.
• Outliers: negative quantities, zero costs, future-dated records, absurd values.
Profiling converts a vague worry into a number. “We have 14,200 customer records, 6,800 active in the last year, and an estimated 11% duplicate rate” is a plan. “Our customer data is a bit messy” is not.
Stage 2: Decide the cleansing rules
Cleansing is a business decision, not a technical one. Someone with authority must decide what a duplicate is, which record survives, and what happens to records that cannot be repaired.
• Deduplication rules. Which fields determine a match, how close is close enough, and which record is the survivor when two conflict.
• Standardisation rules. One format per field — country codes, units of measure, address structure, naming conventions for items.
• Completion rules. What to do when a mandatory field in the new system is empty in the old one: derive it, default it, or exclude the record.
• Exclusion rules. Which records simply do not come across — inactive customers with no balance, obsolete items with no stock.
• Ownership. Who signs off each entity. Finance owns the chart of accounts and balances; operations owns items and BOMs; sales owns customers.
Clean at source wherever possible. Fixing records in the legacy system means the improvement is real for as long as you still operate it, and it prevents the same errors being re-created during the final weeks.
Stage 3: Map every field explicitly
The mapping document is the contract between old and new. For every field it should record: source system and field, target system and field, transformation applied, default when empty, validation rule, and who approved it.
Mapping surfaces the awkward questions early, which is its real value. Old system has one address field and the new system has five. Old item codes are twelve characters and the new system allows twenty — do you renumber now or preserve the legacy codes forever? These decisions are cheap in month two and expensive in month eight.
Stage 4: Trial load, repeatedly
Load into a test environment early, while there is still time to fix what breaks. Expect to run this three to five times, each iteration finding fewer problems than the last.
1. First load: deliberately small. One hundred customers, one hundred items. Find the structural problems fast.
2. Second load: full volume of one or two entities. Find the performance issues and the edge cases hiding in the long tail.
3. Third load: everything, into the environment used for user acceptance testing. Users must test against realistic data, not invented data.
4. Dress rehearsal: the full cutover procedure, timed end to end, against a recent copy of production. This is the load that tells you whether your cutover window is realistic.
Automate the load scripts. Manual loading cannot be repeated reliably, and you will need to repeat it. Scripted loads also mean the final production run is an execution of something already proven rather than a first attempt under time pressure.
Stage 5: Validate and reconcile
Eyeballing a few records is not validation. Three layers are needed.
| Layer | What it checks | Method |
| Technical reconciliation | Nothing was lost or duplicated in transit | Record counts and control totals per entity, source versus target |
| Financial reconciliation | Balances agree exactly | Trial balance, AR and AP ageing, inventory value tied to the last closed period |
| Business validation | The data is actually correct, not merely present | Process owners open records they know well and confirm them |
The third layer is the one most often skipped and the one that catches the errors that matter. Ask each process owner to open ten customers, ten items and ten open orders they know intimately, and confirm the detail is right. A count that matches proves nothing if every record is wrong in the same way.
The Areas That Cause the Most Trouble
Opening balances
Finance data must reconcile exactly, not approximately. Migrate the trial balance as at a clean cut-off, usually the last closed period. Bring across open receivables and payables at document level rather than as a single balance, because customers pay against invoices and you will need the detail for collections and remittance matching. Fixed assets need cost, accumulated depreciation and remaining life, or the depreciation charge will be wrong from the first month.
Inventory
Quantities must match the physical warehouse, not the old system’s opinion of it. Do a full physical count immediately before cutover. Migrating stock records that disagree with reality destroys confidence in the system in the first week, and it is a very hard reputation to recover.
Valuation is the second half of the problem. Confirm which costing method the new system uses and ensure the migrated values are consistent with it. A mismatch between migrated cost and the new system’s costing logic produces variances that nobody can explain later.
Bills of materials and routings
For manufacturers, this is the highest-risk data of all. BOMs must reflect what is actually consumed on the floor, including scrap and packaging, not what the engineering drawing says. Errors propagate into material planning, costing and backflushing simultaneously, and they compound silently.
Lot and serial history
Regulated industries need traceability across the cutover boundary. Decide before migration how a recall investigation will work when the batch was produced in the old system and shipped from the new one. Usually this means migrating lot genealogy for material still in stock, and keeping the legacy system accessible for anything already shipped.
Open orders and work in progress
Partly completed transactions are awkward because they exist in a state neither system was designed to receive. Decide the policy early: close as much as possible in the legacy system before cutover, and migrate only what genuinely cannot be closed. Partly delivered sales orders, partly received purchase orders and partly completed work orders each need an explicit rule.
Cutover: The Weekend Itself
Cutover is the sequence that moves you from old to new. It should be a rehearsed procedure, not an improvisation.
5. Freeze the legacy system at an agreed moment. Communicate it widely — every department, plus customers and suppliers if document formats change.
6. Complete outstanding transactions in the old system where possible: post invoices, receive goods, close what can be closed.
7. Run the final physical inventory count and adjust the legacy records to match.
8. Extract, transform and load using the scripts already proven in the dress rehearsal.
9. Reconcile. Counts, control totals and financial balances. Do not proceed past this point on partial reconciliation.
10. Business validation sign-off. Named process owners confirm their area, in writing.
11. Open the new system to users, with support staff physically present or immediately reachable.
12. Keep the legacy system available read-only for reference and audit.
Agree rollback criteria in advance — the specific conditions under which you would stop and return to the legacy system. Deciding this under pressure at three in the morning produces bad judgement. Written criteria produce a decision.
Who Owns What
| Role | Responsibility |
| Data owner | Accountable for overall data quality and readiness; escalates blockers; the single most commonly omitted role |
| Process owners | Define cleansing rules for their area, validate loaded records, sign off before go-live |
| Technical lead | Builds extraction and load scripts, manages transformations, runs the loads |
| Finance lead | Reconciles opening balances; the only person who can sign off financial data |
| Project manager | Schedules the load iterations, tracks defects, protects the timeline |
| Implementation partner | Advises on target structures and known pitfalls; cannot decide your business rules |
Mistakes That Cost the Most
13. Starting too late. The most expensive and the most common. Profiling should happen in month one.
14. Migrating everything. Effort, testing and risk all scale with volume, and most historical data is never queried.
15. Cleaning after loading. Fixing data inside the new system is slower, and dirty records get used before they are corrected.
16. Manual loads. Cannot be repeated reliably, cannot be rehearsed, and cannot be audited.
17. Reconciling only counts. Matching totals with uniformly wrong detail is worse than an obvious failure, because nobody catches it.
18. Skipping the physical count. Inventory that disagrees with the warehouse on day one destroys trust in the whole system.
19. No named data owner. Data quality becomes everyone’s responsibility, which means nobody’s.
20. Assuming the partner will handle it. They can build the scripts. They cannot decide which of your two customer records is the real one.
How Long It Takes
| Activity | Typical share of migration effort |
| Profiling and analysis | 10–15% |
| Cleansing and deduplication | 30–40% |
| Mapping and transformation design | 15–20% |
| Building and testing load routines | 15–20% |
| Trial loads and defect resolution | 10–15% |
| Final cutover and reconciliation | 5–10% |
Cleansing dominates, and it is the part that cannot be compressed by adding technical resource, because it requires business decisions from people who have other jobs. That is the real reason migration timelines slip.
Frequently Asked Questions
How much historical data should we migrate?
As little as the business genuinely needs. Master data, structural data, open transactions and opening balances are essential. Beyond that, migrate history only where a specific process would break without it — and archive the rest with read-only access to the legacy system.
When should data cleansing start?
In the first month of the project, ideally before it. Cleansing is the largest component of migration effort and depends on business decisions from busy people, so it cannot be compressed by adding technical resource later.
Should we clean data in the old system or the new one?
In the old system wherever possible. The improvement is real for as long as you still operate it, it prevents new errors being created during the final weeks, and it means the load routine carries clean data rather than correcting it in flight.
How do we know the migration was successful?
Three tests must all pass: record counts and control totals match between source and target; financial balances reconcile exactly to the last closed period; and named process owners have opened records they know well and confirmed the detail is correct. Passing only the first two is a common and dangerous false positive.
Can we go live with imperfect data?
You will, because perfect data does not exist. The distinction that matters is between known and unknown imperfection. Documented gaps with a remediation plan are manageable. Undiscovered errors that surface as wrong invoices and wrong stock in week one are not.
What happens to the old system after go-live?
Keep it running read-only for as long as your record-retention obligations require, or take a complete, verifiable export in a format you can still open. Decommissioning it before an audit cycle has passed is a decision that gets regretted.
Who should own data migration?
A named internal data owner with authority to make decisions, supported by process owners per functional area. The implementation partner builds and runs the technical process, but only your people can decide which duplicate record is correct or how an incomplete item should be classified.
Do we need a physical inventory count before cutover?
If you hold stock, yes. Migrating inventory records that disagree with the physical warehouse undermines confidence in the system from the first day, and once staff stop trusting the stock figures they revert to checking manually — which removes most of the benefit you paid for.
Conclusion
Data migration is not a technical exercise appended to the end of an ERP project. It is a business exercise that determines whether anyone trusts the system afterwards, and it needs an owner, a start date in month one, and business decisions made by people with authority to make them.
Profile early so the scale is a number rather than a worry. Move less than you think you need and archive the rest. Automate the loads so they can be rehearsed. Reconcile financially and validate with the people who know the records. Do that and cutover becomes a controlled weekend rather than the crisis that defines the project.
Leave a Reply