Insights
Reconciling totals after a migration: the method that earns sign-off.
Row counts matching is where reconciliation starts, not where it ends. Finance, operations, and compliance sign off on totals they recognize. This is the method used in Ascendant Systems delivery to turn 'the numbers look close' into a documented, repeatable comparison.
three levels
Compare at three levels
- Structural: tables, columns, types, and constraints exist as agreed. A pre-ingestion structure check, such as the one FileSpecGuard performs on delimited files, belongs here.
- Volumetric: row counts per table and per load period, source versus target, with the extraction timestamp recorded for both.
- Semantic: control totals and key business measures computed the same way on both sides for an agreed set of periods.
choose measures
Choose measures people already watch
Ask each report owner for the two or three figures they would notice if they were wrong: month-end balances, claim counts by status, revenue by region. Those become the semantic checks. Measures nobody looks at produce reconciliation theater; measures people defend produce trust.
tolerances
Set tolerances before you see the results
Decide in advance what counts as a match: exact for counts and keys, a stated absolute or relative tolerance for money and quantities where rounding rules differ between systems. Write the tolerance next to each check. Deciding tolerances after the comparison invites the team to move the goalposts to the results.
explain every difference
Explain every difference, then classify it
- Source defect: the legacy system was wrong; document it and fix the source or accept it.
- Transformation defect: the new pipeline is wrong; fix and rerun.
- Definition change: the measure is intentionally computed differently; get the owner's written acceptance.
- Timing: the two extracts were taken at different moments; re-extract together and compare again.
example checks
Example checks for a claims or finance migration
- Row count per table per load date: source versus Raw, exact match.
- Paid claim count by service month: legacy report versus Presentation view, exact match.
- Sum of paid amount by service month: both systems, absolute tolerance agreed with finance, usually one currency unit per month.
- Distinct member or account count at period end: exact match.
- Maximum load timestamp in Raw: must equal the extract timestamp recorded in the run log.
- Open items by status: exact match, because a single misclassified status changes an operational queue.
make it repeatable
Make the comparison a job, not a spreadsheet
Encode the checks as queries or a small script that runs against both systems and writes results to a reconciliation table with the run time, the check name, both values, the tolerance, and pass or fail. Run it every load during parallel operation. After cutover, keep the volumetric and semantic checks running against the new system's own expectations; they become the data-quality monitor that catches the next upstream change.
Store the reconciliation table where report owners can read it, and alert on any failed check the same way the pipeline alerts on a failed load. A reconciliation nobody sees is a reconciliation nobody trusts.
sign off
Sign-off that survives an audit question
The deliverable is a reconciliation report per period: the checks run, the results, the classified differences with owner acceptance, and the tolerance rules. When an auditor or executive asks why a figure changed, the answer is in the report, not in someone's memory. Ascendant Systems includes this report in the acceptance criteria of every migration engagement.
Next step
Bring the problem behind the article
If one of these situations looks like yours, describe the outcome you need and what is getting in the way. The first exchange decides whether a responsible scope can be defined.