Website

Data Migration Reconciliation: Proving the New System Holds What the Old One Did

November 11, 2026 · 7 min read · Etzal Group Team

A company goes live on a new ERP on a Monday. The migration script reported zero errors. On Wednesday, finance finds that total receivables are 340 million lower than in the old system. It takes nine days to find the cause: invoices in a foreign currency were skipped because of an unexpected field value, silently. Nine days of doubt about every number in the new system. Data migration reconciliation is the work that would have found that gap on the Saturday, before anyone relied on it.

Why "no errors" proves nothing

A migration can complete without an error and still be wrong.

  • Records skipped by a filter. A condition excluded rows nobody meant to exclude.
  • Records rejected and logged somewhere nobody looked.
  • Duplicates created by a rerun.
  • Values transformed wrongly. Dates shifted by a time zone, decimals truncated, a unit converted twice.
  • Links broken. Invoices that point to no customer.
  • Text cut off by a shorter field.
  • Codes mapped to the wrong thing. Old status 3 became "closed" when it meant "on hold".

None of these raises an error. Each is found only by comparing the result with the source.

Four levels of checks

Work from coarse to fine. Each level catches what the previous one cannot.

Level 1: record counts

For each type of data, count in the source and in the target.

DataSourceMigratedExcluded by ruleDifference
Customers4,8124,650162 inactive over 5 years0
Open sales invoices1,2041,20400
Stock items9,3309,31812 discontinued0

The rule: source equals migrated plus deliberately excluded. Every excluded record is explained by a rule agreed in advance, and listed. An unexplained difference of even one record is investigated, because one missing record usually means a class of records is affected.

Level 2: control totals

Counts can match while values are wrong. Sum the numbers that matter.

  • Total receivables, and by customer.
  • Total payables, and by supplier.
  • Stock quantity and stock value, by warehouse.
  • General ledger balances by account. Debits equal credits.
  • Open order value.
  • Number and total of open purchase orders.

Compare source and target for each. For financial data the standard is exact agreement, to the smallest currency unit. A difference that is "only" small is still a difference, and small differences are often two large errors nearly cancelling.

Break the totals down. If total receivables match and the per-customer totals do not, records have been attached to the wrong customers.

Level 3: record-level comparison

Totals can match while individual records are wrong. Compare detail.

  • Automated field-by-field comparison where you can: export both sides, match on a key, and list every field that differs. This checks every record, not a sample.
  • Sampling where automation is not practical. Choose records at random, and add deliberately awkward ones: the oldest, the largest, those with special characters, foreign currency, partial payments, long descriptions, unusual statuses.
  • Check the links. Open an invoice in the new system and confirm its customer, lines, payments and documents are all attached.

Record-level checks find truncated text, wrong dates and broken links that no total reveals.

Level 4: business reports

The final test is whether the business gets the same answers.

Run the same reports in both systems as of the cutover date:

  • Trial balance.
  • Aged receivables and aged payables.
  • Stock valuation.
  • Open orders by customer.
  • Any report management reads weekly.

Then have users walk through real work: find a known customer, check their balance and history, open a recent order, look up a stock item. People who use the data every day notice things no script was written to check.

Decide the rules before migrating

Reconciliation is only possible against agreed rules. Write these down first.

  • Scope. Which data is migrated, from which date. All history, or open items and balances only?
  • Exclusions. Inactive customers, closed periods, test records.
  • Transformations. How each old code maps to a new one. How units, currencies and dates are converted.
  • Cleansing. Which errors are fixed in the source before migration, and by whom.
  • Tolerance. For financial values, none. For anything else, state it.
  • Cut-off. The exact moment the old system stops accepting entries.

A difference can only be judged "expected" if the expectation was written before the run.

Clean before you move

Migration carries problems across faithfully. Duplicated customers, items with no unit, balances that were never reconciled in the old system all arrive intact.

  • Reconcile the old system first. If sub-ledgers do not agree with the general ledger there, they will not agree in the new one, and you will not know which side is wrong.
  • Remove duplicates and fix obvious errors in the source.
  • Decide what is not worth moving.

Data cleansing is the business's work, and it takes longer than expected. Start months before the migration, not weeks.

Rehearse

Run the whole migration, with reconciliation, at least twice before the real one.

  • Rehearsal 1. Finds mapping errors, rejected records, missing rules. Expect many.
  • Rehearsal 2. Confirms the fixes. Measures how long each step takes.
  • Dress rehearsal. The full cutover sequence against the clock, with the people who will do it.

Keep the reconciliation scripts and reports. By go-live they should run in minutes and produce the same sheet every time.

The sign-off sheet

One page per data area.

  • Data area and owner.
  • Source count, migrated count, excluded count with reasons.
  • Control totals, source and target.
  • Sample size and result.
  • Reports compared.
  • Open differences, each with an explanation and a decision.
  • Signature of the business owner and date.

Go-live proceeds when every sheet is signed. A sheet with an unexplained difference is not signed.

When totals do not match

  1. Do not adjust the target to make it match. That hides the cause.
  2. Break the total down until the difference sits in a small group: one customer, one month, one currency, one status.
  3. Compare that group record by record.
  4. Find the rule that explains it. A filter, a mapping, a rejected batch.
  5. Check for others like it. One skipped foreign-currency invoice means checking all of them.
  6. Fix the migration, rerun, reconcile again.

Only correct data by hand in the target as a documented last resort, with the business owner's approval.

Cutover weekend

  • Freeze the old system at the agreed moment. Nobody enters anything after.
  • Take the final source totals at that moment and keep them.
  • Run the migration.
  • Run all four levels.
  • Sign off, or roll back. Decide in advance the hour by which sign-off must happen, and what rolling back means.
  • Keep the old system available read-only for a period, for queries and audits.

Common mistakes

  • Treating a clean log as proof.
  • Counts only, no totals.
  • Totals only, no detail.
  • Technical team signing off for the business.
  • No rehearsal.
  • Rules decided after seeing the differences.
  • Fixing the target by hand.
  • No rollback decision time.
  • Switching off the old system too soon.

We include reconciliation scripts, rehearsals and sign-off sheets in every migration we run as part of Enterprise Software projects. If a migration is on your plan and reconciliation is not yet written into it, contact us.

Summary

A migration is proven by comparison, not by the absence of errors. Agree scope, exclusions and mappings first, and clean the source. Then check four levels: record counts, control totals broken down by group, record-level comparison including awkward cases, and business reports with real users. Rehearse at least twice, have business owners sign each data area, investigate every difference to its cause, and keep the old system readable after cutover.

Frequently asked questions

What is data migration reconciliation?

It is the set of checks that prove data in the new system matches the old one after a migration: the same number of records, the same totals, the same detail on sampled records, and the same answers on key business reports. It turns "the script finished" into evidence that the business can rely on the new system.

Who should sign off on migrated data?

The business owners of the data, not the technical team alone. Finance signs for balances, receivables and payables. Operations signs for stock and open orders. Sales signs for customers and open deals. The technical team produces the evidence, and the people who will use the data confirm it.

How many trial migrations are needed before go-live?

At least two full rehearsals, more for large or messy data. The first finds the problems. The second confirms the fixes and measures how long the cutover takes. Going live on the first full run means discovering the problems in production.

Author

Etzal Group Team

Etzal Group Team, helping businesses grow with technology. Contact us.

Related service

Website Development

Fast, SEO-ready websites that turn visitors into customers.