3 July 20255 min read

Reconciliation: proving the migration was correct

By Isak La Fleur Engdahl

Photo: Unsplash

There's a moment late in every migration when someone says "the load ran clean – we're done." It's a trap. A clean load proves the data went in. It says nothing about whether the data is right. The thing that closes that gap – and the thing that actually gets a migration signed off – is reconciliation: proving, with evidence, that what landed in the new system faithfully represents what left the old one.

Reconciliation is the least glamorous part of a migration and the part auditors and CFOs care about more than any other. It's also where I've seen the most "successful" migrations quietly turn out to be broken. So here's how it actually works.

Why "it loaded" isn't "it's correct"

A load can succeed and still be wrong in ways no error log will show you:

Rows silently dropped. A filter, a join that didn't match, a rejected batch you didn't notice – 1,000,000 rows left, 998,400 arrived, and nothing threw an error.
Values transformed wrongly. Amounts loaded in the wrong currency or scaled by 100, dates with the month and day swapped, a status code mapped to the wrong target value. Each row is "valid"; the data is nonsense.
Duplication. A re-run that appended instead of replacing, so balances are doubled.
Truncation and encoding. A name cut to the target field length, å/ä/ö mangled by a charset mismatch.

Every one of these passes a "did it load" check. Reconciliation exists to catch them before the business does.

The layers of reconciliation

Reconciliation isn't one check – it's a stack, from cheap-and-coarse to precise-and-expensive. You want all of it, because each layer catches a different class of error.

1. Record counts

The first and crudest question: did the same number of things come across? Count source records, count target records, account for every difference. The key word is account for – the numbers rarely match exactly, and that's fine if every gap is explained:

Rows deliberately filtered out of scope (closed accounts, ancient history).
Records merged by deduplication (three duplicate customers became one – count drops by two, on purpose).
Rows legitimately rejected and parked for fixing.

An unexplained count difference is a defect. A fully reconciled count difference is evidence of control. "We loaded 998,400 of 1,000,000; 1,400 were out-of-scope closed accounts and 200 deduplicated – here's the list" is exactly what good looks like.

2. Control totals

Counts catch missing rows; they miss wrong values. For that you sum the things that must balance and compare source to target:

Financial totals – total AR, total AP, trial-balance sums, sub-ledger totals by account. These must tie out to the cent.
Quantities – total inventory units by location, total open-order quantity.
Hash totals – even sums of an otherwise meaningless numeric key, to detect drift in fields that don't have a natural "total".

Control totals are where wrong-currency, wrong-scale and duplication errors surface, because they move the sum even when every individual row looks plausible.

3. Financial tie-out and the close

For anything touching finance, reconciliation has to satisfy accounting, not just data engineering. The migrated opening balances must agree with the legacy system's closing balances, the sub-ledgers (AR, AP, inventory, fixed assets) must reconcile to the general ledger, and the new system must be able to produce a trial balance that balances. This is the layer auditors examine, and it's the one that lets finance trust the first close on the new system. Get this wrong and the migration's problems land squarely on the CFO's desk.

4. Sample-level / row-level checks

Totals can hide compensating errors – one account too high, another too low, sum unchanged. So you also pull samples and compare field by field, source row against target row: high-value accounts, the largest customers, a random sample for coverage, and known edge cases (the customer with the protected identity, the order with every optional field populated). This is the layer that catches the swapped date and the truncated name that a total sails right past.

5. Business / functional validation

The final layer isn't a comparison at all – it's whether the migrated data works. Can finance run the close? Can a user raise an order against a migrated customer and have it post correctly? Do the reports the business actually relies on produce sensible numbers? This is UAT's job, and it validates that the data is not just faithfully copied but fit for use.

Make it evidence, not vibes

The output of reconciliation isn't "looks good" – it's an artifact: a reconciliation pack that states, per object, what was in scope, the source and target counts, the control totals and any explained variance, the financial tie-out, and the sample results. It's signed by the people who own the data, not just the people who moved it. That pack is what turns "trust us" into "here's the proof", and it's what the auditor will ask for.

Three things make it trustworthy:

Reconcile against the source, not against itself. Counting your own output proves nothing; the comparison must be to the legacy system.
Automate it and run it every mock run. Reconciliation you only do once, at the end, is reconciliation you can't fix in time. Build the checks early, run them on every trial load, and watch the variances shrink toward go-live – that trend is your readiness signal.
Define "acceptable" up front. Financial totals tie to the cent; some descriptive fields might tolerate a known, documented variance. Agree the thresholds with the business and finance before cutover, not in the argument afterwards.

Where it sits in the project

Reconciliation isn't a phase at the end – it's a framework you build early and run continuously. Each mock run produces a reconciliation pack; the variances tell you what's still broken; you fix the mapping or the cleansing and run again. By the real cutover, reconciliation isn't a nervous discovery – it's the confirmation of something you've already proven three times. That repeated, evidenced agreement between source and target is, in the end, the only honest definition of a successful migration.

A clean load is a claim. Reconciliation is the proof. Don't sign off on the claim.

Planning a migration and want reconciliation built in from the start, not bolted on at the end? Get in touch – I'm happy to share how I work.