ERP data migration: reconciliation evidence that survives an audit

Data migration is the area where problems are most reliably discoverable in advance and most reliably discovered afterwards instead. The reason is that reconciliation is tedious, unglamorous, and easy to defer while more visible work proceeds.

What reconciliation actually requires

A reconciliation is a comparison of totals between source and target, at a level of granularity where a discrepancy would be meaningful, with every variance explained.

Three levels are worth insisting on:

• Record counts. How many customers, vendors, materials, open orders in source; how many in target; what accounts for the difference.
• Financial totals. Open AR, open AP, inventory value, trial balance. These must tie exactly, and "rounding" is not an explanation.
• Sample-level integrity. A statistically meaningful sample checked field by field, because totals can agree while individual records are wrong in offsetting ways.

Back to the top

The signature problem

Ask who signed the reconciliation. On many programmes the answer is the data migration lead — the person who performed the migration.

That is a self-certification. The signature that carries information belongs to finance, because finance is who will be asked to explain the opening balances to an auditor, and finance therefore has an incentive to actually check.

Back to the top

Failure patterns that surface after go-live

1. Open items that migrated as closed. Receivables vanish from collections. Cash flow forecasts silently overstate.
2. Duplicate master data. Two vendor records for one supplier, so payments split, terms diverge, and spend analysis becomes unreliable.
3. Truncated fields. Addresses and descriptions cut at the target field length. Discovered when deliveries go to the wrong place.
4. Historical data assumed present. The business expects three years of history; two were migrated; nobody wrote the decision down.
5. Unit-of-measure and currency conversion errors. Quantities and values that are individually plausible and collectively wrong.

Back to the top

Migrate at volume, early

A migration tested on a thousand records tells you the mapping works. It tells you nothing about runtime, about the error rate at scale, or about what happens when the load window is not long enough.

The first full-volume migration rehearsal is one of the highest-value events on an ERP programme, and it should happen while there is still time to act on what it reveals.

Ask for: the duration of the last full-volume load, the error count, and whether that duration fits inside the cutover window with contingency.

Back to the top

Decide what not to migrate

Not all history needs to move. Deciding deliberately to leave data behind — with an archive strategy and an agreed retrieval route — is sound practice. Leaving it behind by accident, and discovering the gap during the first month-end, is not.

The test of a good decision here is whether it is written down and whether the people who will miss the data agreed to it.

Back to the top

Cleanse before you migrate, not after

Every migration surfaces data quality problems that predate the programme: duplicate records, missing attributes, values that mean something to one team and nothing to anyone else.

There is a strong temptation to migrate first and clean afterwards, because migration has a deadline and cleansing does not. This reliably produces the worst outcome — the new system inherits every legacy problem and gains the authority of being new, so the bad data is now trusted.

Cleansing needs an owner in the business, not in IT. Only the business knows which of two duplicate customer records is the real one.

Back to the top

The three-way check

For financial data, a reconciliation that compares source to target is necessary but not sufficient. A stronger check involves three points:

1. The legacy system balance at extract.
2. The extract file contents.
3. The loaded target balance.

Comparing only the first and third can mask compensating errors — records lost at extract and duplicated at load can produce a total that ties while the detail is wrong. Checking the file in between catches this.

Back to the top

Cutover data is a separate problem

Most attention goes to master data and opening balances. The data that moves during the cutover window itself gets less, and it is where late problems cluster.

• Transactions in flight when the legacy system freezes — partially picked orders, goods received but not invoiced, payments in progress.
• Documents created in the freeze window through manual workarounds, which must be entered afterwards by someone with a documented procedure.
• Sequence numbers that must continue rather than restart, particularly for anything with statutory or audit significance.

Each of these needs a named owner and a written procedure before the freeze begins, not a decision made at 3am by whoever is on the bridge call.

Back to the top

Archive and retrieval

Data left behind still has to be reachable. Audit, tax, warranty, dispute and regulatory requirements do not stop applying because a system was decommissioned.

Before go-live, establish what the retention obligation is in each jurisdiction you operate in, where non-migrated data will live, who can retrieve it, and how long retrieval takes. "It is in the old system" is only an answer for as long as the old system runs, and the licence for it usually has an end date somebody has already scheduled.

A practical test: ask someone to retrieve a specific historical transaction from three years ago. Time how long it takes. That is your real retrieval capability. Back to the top

How many rehearsals is enough

A common question, and the honest answer is that it depends on what the last one revealed rather than on a fixed count.

The useful test is convergence: has the load duration stabilised, has the error count fallen to a level you can clear inside the window, and did the last rehearsal surface anything new? When two consecutive rehearsals produce substantially the same result with no new surprises, you have a repeatable process.

When each rehearsal is still finding new issues, you are not ready — regardless of how many you have run or how close the date is.

Back to the top