Data reconciliation after migration: proving the totals match

Record counts tell you whether rows arrived. If the source held 4,120 customers and the target holds 4,098, twenty-two rows sit elsewhere, perhaps in a rejected import file or merged away as duplicates. Counts are quick. They are blind to wrong values.

Control totals add up a numeric field on both sides, such as open receivables, stock value, payables or the value of confirmed sales orders. A total can match while two rows carry swapped amounts, so it proves far less than it first appears to prove. Treat it as a smoke alarm. It cannot point to the fire.

Sample tracing picks real records and follows each from the source file to the new screen, catching wrong dates, clipped names and lost decimals that no total sees. Those faults live in single rows.

Do not invent new targets. Use the aged debtors report, the stock valuation, the creditors list and the trial balance your accountant already signed, because those are the yardsticks the business already believes. Everything else is opinion.

Run the same report from the new system as at the same date and work out the difference per customer, per item and per ledger account. A zero total above a long column of small offsetting differences is not a pass, however tidy the report looks to the finance team and the owner.

Stock deserves its own care. Compare quantity by item and location first and value second, since average cost and first in first out can differ between systems and shift the whole warehouse valuation.

A headline figure answers only whether the sums agree, and it cannot say which rows are wrong or whether two errors are quietly cancelling each other out. Swapped digits hide easily. Two such invoices leave a perfect total.

An exception list is the opposite. It names each row that failed to load, changed value on load, or has no match on the other side. Each entry carries the key, the field, both values and a cause, which gives the clean-up team a worklist that an owner can act on without calling a meeting.

That list is also your to do list. A clean total alone is only a claim. A short list with owners is evidence, and a small team can work through it and close it before cutover week.

Reconcile after every test load, after the final load, and again after the first month end, using the same sheet each time so the results can be compared. Do not skip the dull runs. They catch the drift.

Give each exception a status of loaded, corrected at source, accepted with reason, or discarded, so that nobody has to ask what happened to a row. Only the data owner may close an item. The date goes beside it.

Date and sign the final run. Then pick the three reports you trust most, such as aged receivables, stock valuation and the trial balance, and rerun them for last month today.

The person signing should see the counts, the totals, the traced samples and the open exception list with every item named. A signature on a single total is weak evidence.

Keep the reconciliation workbook, the frozen source files and the old reports together. If an auditor or a customer queries a balance in a year, you can show exactly which file and which row it came from. Storage is cheap. Memory is not.

Note any difference you accepted, with the amount, the reason and whoever agreed to it, written by name, so the decision survives staff changes. Accepted differences should be rare and small.