ERP data migration: cleansing, mapping and cutover strategy

A field-tested approach to ERP data migration — scope by object, cleanse at source, mock-load repeatedly, and reconcile with signed-off control totals.

Data migration is where ERP programmes slip. The work is not technically difficult; it is that data quality is always worse than anyone believed and the discovery happens late.

Scope by object, not by table

Build an object list with an owner for each: chart of accounts, cost centres, customers, suppliers, materials, BOMs, routings, open AR/AP, open sales and purchase orders, inventory balances, fixed assets, and historical GL balances. For each object record: source system, volume, owner, cleansing rules, load method, and the reconciliation control total.

Decide how much history you move

The default answer should be: opening balances plus open transactions, with history left in an archive or a reporting store. Migrating years of closed transactions multiplies effort and risk for benefit that a read-only archive usually delivers better.

Cleanse at source

Fix data in the legacy system where the business owners already work, not in a spreadsheet in the middle of the pipeline. Publish a weekly data-quality scorecard per object — duplicate customers, missing tax IDs, materials without a unit of measure — and make the owners accountable for the trend.

Mock loads: at least three

1. Mock 1 — proves the pipeline runs end to end. Expect a low success rate; the goal is error catalogue coverage.
2. Mock 2 — proves the mapping. Business users validate samples against the legacy system.
3. Mock 3 (dress rehearsal) — full volume, timed, executed by the people who will do it at go-live, with the real runbook and the real freeze.

Record the elapsed time of every step in the dress rehearsal. That timing is your cutover plan; anything else is a guess.

Reconciliation

• Every object has a control total agreed before the load: record counts, sum of balances, sum of quantities.
• Finance signs off the trial balance, AR/AP ageing and inventory valuation — not IT.
• Keep an exceptions register with an owner and a due date for every unmigrated record.

Cutover runbook

A good runbook lists each task with: sequence number, owner, duration, dependency, rollback trigger and validation step. Include the business freeze windows, the final legacy extract, the load sequence, the reconciliation checkpoints and the explicit go/no-go decision point with named decision makers.

Hypercare

Plan two to four weeks with the migration team still available, daily triage, and a fast path to correct data errors. Most post-go-live "the system is wrong" tickets are data issues, and they cluster in the first month-end close — make sure the team is still there for it.