Aggregate-Only Reconciliation

A ledger replacement runs in parallel for five months. The comparison job sums each account class each night and the two sides match to the penny every time. Three weeks after cutover, 900 customers turn out to have had interest posted to the wrong class since month two - and the daily totals were always correct, because every penny that left one bucket arrived in another inside the same sum.

An aggregate is a lossy function. For any total there are infinitely many record sets that produce it, and the sets that differ from the truth by a net zero are precisely the ones a total cannot distinguish. A mis-mapping between two codes in the same group, a sign flip paired with a matching omission, a rounding rule applied at the wrong level, a double entry with a compensating absence: all invisible.

The damage is not the undetected error. It is the confidence. Five months of green nights is what authorises a cutover, which makes an aggregate-only comparison worse than no comparison at all.

Why it matters

Verification is the only evidence a migration decision has. If the evidence is structurally unable to see a class of error, the decision is being made on a narrower basis than anyone involved believes.

The classes it cannot see are also the classes migrations most often produce. Migrations are mostly mapping: source code to target code, old enumerations to new, one scale to another. Mapping errors are the canonical offsetting error, because value is moved rather than created or destroyed. So the blind spot and the likely defect are the same shape.

It persists because it is cheap and because it looks rigorous. "Matched to the penny" is a strong sentence in a steering report, and nobody asks which rows were being summed.

Implementation patterns

The fix is grain, and at volume the obstacle is cost. Compare at the grain the organisation is obliged to - the posting line, the policy, the order line - using a bucketed hash:

• Bucket by a stable key (account, date, tenant) so both sides can group independently without moving data.
• Canonicalise before hashing: fixed column order, explicit numeric scale, null versus empty string, timestamp precision and time zone, text collation. A mismatch caused by 1.50 against 1.5 burns a day and teaches nothing, so test the canonicaliser first.
• Hash each bucket on each side, compare hashes, descend only into buckets that differ. For 40 million rows a night this is two scans plus a few thousand comparisons, in minutes, where a full outer join would require moving one side's rows to the other.
• Size buckets at roughly 1,000 to 10,000 rows so that fetching a differing bucket is cheap.
• Publish coverage next to the result - rows and buckets compared - because a filter bug that silently covers 3% of rows reports zero differences.
• Require every difference to be explained, with a written agreed reason, or the night does not count as green.

Industry example

The pattern is most visible in regulated finance, where the obligation is at record level and the report is at aggregate level. Public post-incident accounts of large core-banking migrations repeatedly show the same sequence: aggregate controls passing throughout a long parallel run, and record-level divergence surfacing weeks after cutover through customer complaints rather than through monitoring. The 2018 UK core-banking migration that moved about 1.9 million customers in a single weekend is the best-documented instance of verification evidence being weaker than the decision it supported.

Failure scenarios

• Offsetting mapping error, as above: totals right, rows wrong, discovered by a customer.
• Silent coverage collapse. A join condition change means the comparison covers one partition. Zero differences reported, and the number of rows compared was never published.
• Tolerance creep. A £0.01 tolerance is added to suppress rounding noise, then raised to £1, then to £100 "for materiality". The control still reports green and now sees almost nothing.
• Explained-away differences. Differences are annotated as known and never revisited, so a new defect lands inside an existing exemption.
• Grain mismatch at the join. The comparison joins a one-row-per-order source to a one-row-per-order-line target, multiplying amounts, and the resulting differences are dismissed as a known artefact.

Trade-offs

Record-level comparison costs engineering effort, storage for the hashes, a canonicaliser nobody wanted to write, and a daily triage queue that must be staffed. Aggregate comparison costs almost nothing and runs in seconds.

The trade is therefore between a cheap control that cannot support a cutover decision and an expensive one that can. Where the decision is reversible and small, the cheap control is proportionate. Where the decision is a cutover, it is not. A reasonable middle path is aggregates nightly for trend and record-level hashes weekly plus every night in the four weeks before cutover.

When not to use it

Aggregate-only comparison is genuinely sufficient for a one-way, append-only feed with no mapping, no derivation and no sign changes, where a total and a row count together pin the content. It is also reasonable as an early smoke test in the first weeks of a parallel run, before the record-level machinery exists, provided the plan says when the stronger control arrives.

Do not build record-level comparison for a dataset whose records nobody is obliged to reproduce - clickstream, telemetry, derived analytics. The control should match the obligation, not the data volume.

Interview question

Q: A parallel run's nightly comparison has matched to the penny for five months and the team wants to cut over. What do you ask before agreeing, and what would make you refuse?

What a strong answer covers: at what grain the comparison runs and which error classes that grain cannot see · what proportion of rows was actually compared, and whether that number is published · the tolerance and its history · how differences are triaged and whether any standing exemptions exist · the canonicalisation rules · and the refusal condition stated plainly: no cutover on aggregate-only evidence for a record-level obligation, with a four-week record-level run as the minimum substitute.

Quick check

Quiz: Why can a parallel run match on totals every night for months while being wrong at record level? Because summation cancels errors of opposite sign inside the same total, which is the exact shape of a mapping error - the most common defect a migration produces.

Flashcard: How do you compare 40 million rows a night without a 40-million-row join? - Bucket by a stable key, hash the canonical form of each bucket on both sides, compare hashes, and fetch rows only for buckets that differ; publish the number of rows compared so a filter bug cannot report success.