A green load job proves very little
A migration can finish without a single technical error and still be wrong. Records can arrive in the wrong state, relationships can be lost, and values can be mapped to something that looks plausible. The technical log cannot tell you that, because it knows about the load and not about the business.
That is why I treat reconciliation as its own piece of work, with its own owner, planned from the start of the project and not squeezed in before cutover.
Two checks that work together
For reconciliation I rely on two kinds of check, because each catches what the other misses.
- Spot tests on specific business data. We pick particular records that the business knows well, a customer, an order, a contract, and compare them field by field between the old and the new system. This catches wrong mappings and lost relationships.
- A rule for what counts as a match. Agreed before the comparison starts, including which differences are tolerable and which are not.
- High-level KPI comparison. We compare totals that the business already trusts, in the old and the new system, to confirm that everything was migrated. This catches what is missing in bulk.
For the KPIs I choose totals the business already reports on and trusts, for example the number of active customers, open orders or open balances. A figure nobody recognises is a poor basis for a conversation.
Spot tests alone can miss a missing batch. Totals alone can hide two errors that cancel each other out. Together they give a conclusion I am willing to put my name on.
Exceptions will exist, so plan for them
No real dataset migrates cleanly. There will be records that do not fit the target model, and I would rather say so at the start than explain it at the end. We handle these exceptions through manual corrections.
Manual does not mean informal. Each exception goes on a list with the record, the reason, the correction and the person who made it, and the list is shared with the client's data owner. Corrections made quietly are the ones that surface later as a surprise. A visible list also shows the client how clean the data really was, which is useful for them long after the project ends.
I prefer to run the reconciliation on trial migrations well before cutover. Mapping problems then appear while there is still time to fix them, and the business learns what the checks look like. The final run confirms rather than discovers.
Who signs
Before cutover I also agree what happens if reconciliation fails: continue with a known list of corrections, or hold. That decision is much easier to take calmly when it was made in advance.
The business should own the conclusion. The delivery team prepares the comparison and explains the exceptions, but the person who owns the data says whether it is right. When the sign-off sits with the business, nobody finds out in production that a number is different from the one they remember.