When the totals agree and the records do not

Zero difference is a weak explanation

Both systems report three rows and £1,000. The source has A, B and C worth £900, £50 and £50; the destination has A, B and B at those same amounts. One event is missing and another appears twice, with no difference left for the summary to complain about. The arithmetic is correct. What failed is the inference that matching totals and counts establish that the same events arrived.

The records are invented, with one declared event type, GBP amounts and a one-event-per-identity agreement. They are not client accounts or instructions for making accounting adjustments. Keeping rounding, currency conversion and timing differences out of this case makes the problem easier to see. Equal aggregates discard the identity information that distinguishes the populations. We do not need a complicated financial explanation to demonstrate the gap; the two fifty-pound events already supply it.

Reconciliation needs to identify the facts expected to agree and compare them at that level. Totals remain useful, but contain no account of which identities or repeated occurrences produced them. In this example, reporting A matched, B repeated and C absent gives the operator specific records to investigate. It describes the observed disagreement without assuming whether ingestion, selection or another part of the integration caused it.

Agree the basis before matching the rows

The example compares the same hypothetical event population at the same declared cut-off. A real comparison needs an event definition, account scope, currency, amount basis and time window. Otherwise a missing row may be an expected consequence of asking the two systems different questions. An order, payment, balance movement and payout are related business objects, but their identifiers and amounts need not represent a one-to-one population. A shared-looking reference is not enough to establish the join contract.

Stripe's current balance-report documentation provides a concrete reason to inspect that contract. It distinguishes itemised balance transactions and their identifiers, gross, fee and net amounts, settlement currency and reporting dates.[1] Those are provider-specific definitions. Each field needs to retain that meaning when the downloaded data enters the reconciliation model, particularly when one payment produces several balance movements. They should be read before treating a downloaded report as if every amount described the same event at the same point in its lifecycle. The toy A/B/C ledger does not reproduce a Stripe report or claim that an invoice number uniquely identifies a balance transaction.

Amount definitions can explain a difference before anything has gone wrong. The separate illustrative transaction has £100 gross, a £3 fee and £97 net, with the arithmetic checked in the resource. Comparing gross with net would produce a three-pound discrepancy by design. Forcing the values to agree with an adjustment would conceal that explanation. First align the amount basis and preserve the fee relationship, so the comparison asks the same question of both records rather than treating every unequal number as an integration loss.

Time boundaries deserve the same treatment. Record which timestamp selects the population and how the cut-off is applied. A source creation date and a destination posting date can place the same event in different periods. Those timing differences may need an open classification, but that does not mean every unexplained absence should be excused as timing. Identify the event and expected transition first. Without a defined basis, a reconciliation result is a suggestion for investigation, not evidence that the integration fulfilled its contract.

Count each identity before joining

The executable resource stores amounts in integer minor units for its invented GBP records and groups each side by identity. It preserves the list of amounts within each group, then compares the union of identifiers. Grouping first makes the duplicate visible. A contributes one source row and one destination row, both £900. B contributes one source row but two destination rows. C contributes one source row and none at the destination. Both total amounts still equal one hundred thousand pence.

An ordinary join on B alone can produce two apparently matched pairs, each linking the same source row to a destination B. If later code marks every joined row matched, the duplicate can disappear inside a successful matching operation. The grouped comparison instead treats anything other than one expected row on each side as multiplicity review. It does not pick an arbitrary destination row and call the other redundant. That distinction matters when the underlying identity assumption itself might be wrong.

The executed comparison labels A matched, B multiplicity_review and C missing_destination. It uses the supplied one-to-one rule, with no fuzzy matching or downloaded provider records. Those classifications are precise about the disagreement and deliberately open about its cause. B could be repeated ingestion, a poor join key or a mapping defect; C could be unfetched, rejected or waiting on an allowed transition. The next investigation starts with identifiable facts that differ, rather than a diagnosis the arithmetic never supplied.

An amount mismatch is another category when there is exactly one row on each side but the expected amount differs. An unexpected destination identity is different from a missing destination identity. Preserve those distinctions in the report rather than reduce everything to a signed difference. A positive amount difference does not tell someone whether to remove a duplicate, retrieve a missing record, correct a mapping or wait for a defined posting event. The classification should help choose the next question without prescribing an unjustified repair.

Give each discrepancy somewhere to go

A useful report retains the original record references and the comparison basis, along with an owner and the evidence needed to close the discrepancy. For B, keep both destination rows distinguishable by their ingestion or source provenance even though their logical identity is the same. If the input already discarded that distinction into a single dictionary entry, reconciliation cannot recover which duplicate was received when. Preserve raw provenance long enough to investigate, with access and retention appropriate to the data.

C needs to remain attached to this cut-off and population while its next permitted check is made. Later evidence of delayed posting can resolve it under the agreed timing rule. A later dashboard that drops C from both sides because its window moved cannot. The missing row did not become explained merely because it stopped being counted. Keep that original finding open until the relevant evidence accounts for it, so a changing total cannot silently do the work of closing an investigation.

This comparison should stay read-only while the identity question is unresolved. A balancing entry would explain neither of the two differences whose amounts cancel, and even the apparent duplicate needs checking against the contract before choosing a valid occurrence. The tool can show the operator exactly where to investigate without moving data to satisfy the overview. In this case the overview was balanced already; changing it would risk adding another unexplained fact to the two we started with.

There is a legitimate simpler case. For a rough management trend, a high-level total may be all the consumer needs, provided it is labelled as a summary and nobody treats it as record completeness. Full event-level reconciliation costs storage, processing and attention. Use it where the operational promise depends on identified events arriving once with the expected values. The stronger claim deserves the stronger evidence; not every chart needs to become an audit ledger.

Keep the summary connected to its explanation

The report can still lead with £1,000 on each side, provided it also states that two record discrepancies remain. A matched, B has a multiplicity difference and C is missing. That presentation preserves a concise overview while retaining the unresolved findings that determine whether the integration met its one-event-per-identity contract. An aggregate total and a record-completeness result are both useful when their different scopes are visible.

Keep both B occurrences and the original finding about absent C available until their owners can explain them. A rerun over a changed time window may produce another balanced £1,000 overview while leaving those earlier questions untouched. The comparison should show which findings the new evidence resolves, rather than replacing the old report and calling the disappearing differences fixed. That is how a concise summary can continue to lead the page without becoming the mechanism by which unresolved record-level work is forgotten.