A general ledger replacement runs in parallel for five months. The nightly comparison checks daily totals per account class and they match to the penny every single night. Three weeks after cutover, finance finds that about 900 customers have had interest posted to the wrong account class since month two, and the daily totals were always correct. What failed, and which design decision made it possible?

The trigger

A mapping change in month two sent one product's interest to class 4201 instead of 4202. Both classes are in the same comparison group, so every penny that left one bucket arrived in the other. The totals the comparison checked were sums over a set that contained both errors, and errors of opposite sign inside one sum cancel exactly.

Why the comparison passed for five months

The comparison was defined on the aggregate the business reports, not on the records the business owes. An aggregate is a lossy function: for any total there are infinitely many record sets that produce it. A reconciliation that compares only aggregates can prove the totals agree and cannot prove anything about the rows, and the errors it is structurally blind to are the ones that offset - which is precisely the shape of a mis-mapping, a sign flip, a rounding rule applied at the wrong level, or a double entry with a matching omission.

It is worse than useless, because five months of green nights built the confidence that authorised the cutover.

Why detection lagged

Nothing was wrong from the general ledger's point of view: it balanced. The error surfaced only when a human looked at an individual customer's statement. The system had no signal whose absence would have been noticed, so the discovery mechanism was a complaint.

The structural fix versus the tempting local fix

The tempting fix is to add account classes 4201 and 4202 as separate comparison lines. That closes this instance and leaves the class of defect open.

The structural fix is to compare at the grain at which the business is obliged - here, the posting line: account, customer, date, amount, sign. At volume this is a hash comparison, not a row-by-row export: bucket rows by a stable key, hash the canonical form of each bucket on both sides, compare bucket hashes, and only fetch rows for buckets that differ. For 40 million rows a day that is two scans and a few thousand hash comparisons rather than an 80 million-row join, and it runs in minutes.

Two further rules make it hold:

1. Every difference is explained or the run does not count as green. A difference with a written, agreed reason is acceptable; an unexplained one, however small, blocks the cutover decision.
2. Count the comparisons, not just the mismatches. A comparison job that silently covered 3% of rows because of a filter bug reports zero differences. Publish coverage as a number next to the result.

The general lesson

A control is only as strong as the grain it operates on, and aggregation is where evidence goes to die. When you design any verification - a parallel run, a migration check, a nightly audit - write down which classes of error it cannot see, and make sure the list does not contain the errors you most expect.

When this is the wrong control to build

An aggregate comparison is genuinely sufficient for a one-way append-only feed with no mapping, no derivation and no sign changes, where a total plus a row count pin the content. It is also a reasonable first smoke test in the opening weeks of a parallel run, as long as the plan states the date the record-level control arrives. Do not build record-level comparison for data nobody is obliged to reproduce - clickstream, telemetry, derived analytics. Match the control to the obligation, not to the data volume.