Reconcile at the Same Grain

Two totals can disagree because of missing records, different filters, different units or different revision boundaries. They can also agree while both were multiplied by the same erroneous join. Reconciliation therefore begins before subtraction: define the comparable population and construct each measure at the same grain.

For an order-line example, the comparison key might be source, line identifier and currency at a declared reporting boundary. One side may contain raw revisions while the other contains current state. Reducing both sides to comparable accepted state is part of the reconciliation method, not a cosmetic preparation step.

Write the comparison contract

State the sources, eligible record states, reporting interval, revision policy and unit. Use the same cancellation treatment on both sides, or document an intentional mapping. A total containing cancelled lines cannot be compared directly with a total that excludes them.

Distinguish business time from observation time. A report reconstructed using corrections received today may differ from a report frozen yesterday, even for the same business date. Neither is necessarily incorrect; they answer different questions about what was known and accepted.

Record the query and input revisions. Without those identities, a later reader cannot tell whether a difference disappeared because the data was repaired or because the comparison itself changed.

Separate coverage from value

A missing key is different from an existing key with a zero amount. Preserve that distinction before applying any display defaults. Coalescing every missing amount to zero can make an absent record indistinguishable from an explicit zero-valued record.

This self-contained PostgreSQL query uses non-null fixture keys and reports both coverage and value:

WITH left_set(line_id, amount) AS (
  VALUES ('A', 10.00::numeric), ('B', 20.00::numeric)
), right_set(line_id, amount) AS (
  VALUES ('A', 10.00::numeric), ('C', 20.00::numeric)
)
SELECT coalesce(l.line_id, r.line_id) AS line_id,
       CASE
         WHEN l.line_id IS NULL THEN 'right_only'
         WHEN r.line_id IS NULL THEN 'left_only'
         ELSE 'both'
       END AS coverage,
       l.amount AS left_amount,
       r.amount AS right_amount,
       CASE WHEN l.line_id IS NOT NULL
                  AND r.line_id IS NOT NULL
            THEN l.amount - r.amount END AS delta
FROM left_set AS l
FULL OUTER JOIN right_set AS r USING (line_id)
ORDER BY line_id;

Both input totals are 30.00. Nevertheless, B is present only on the left and C only on the right. A totals-only comparison would miss the population disagreement. A has both coverage and an equal value.

Enforce uniqueness before comparing

Each reduced input must be unique by the comparison key. If one side contains two revisions for a line, the full join may create multiple comparisons for that line. The resulting mismatch table then inherits the same grain problem the reconciliation was intended to diagnose.

Reject missing identity fields before the comparison or use a separately documented matching rule. The example uses null in the joined key columns to detect an unmatched side because the original keys are guaranteed non-null. That interpretation is unsafe if the input permits null keys without a separate presence marker.

For composite keys, include every dimension required by the contract. Joining line identifiers without source or currency can make unrelated records appear matched. A shorter key is easier to type but does not change the meaning of the data.

Define tolerances after semantics

If amounts have a permitted tolerance, state the unit, threshold and calculation rule. A tolerance should account for an understood transformation, such as a documented rounding boundary. It should not absorb differences caused by comparing different populations.

Retain the unrounded delta in the evidence. Rounding both sides before comparison can hide a systematic difference that matters in aggregate. Conversely, a tiny numeric representation difference may be irrelevant when the contract explicitly permits it. The decision belongs to the reporting rule.

Inspect signed differences in both directions and total absolute differences. Positive and negative errors can cancel in the net total. A report showing only a net delta of zero may conceal many mismatched records.

Explain a mismatch in layers

Start with coverage counts, then compare values among matched keys. If a line amount is derived from quantity and unit price, compare those components separately. An equal final amount can result from compensating quantity and price errors, just as an unequal amount can result from an intended pricing revision.

Group findings by a stable, useful dimension such as producer revision or import batch. Avoid copying entire sensitive records into the reconciliation output. Keep enough identity to locate the source under controlled access.

Preserve the result boundary

Publish the comparison identity, population counts, unmatched counts, value mismatches and any incomplete input condition. A reconciliation that ran before one source completed its batch should remain labelled partial.

Use fixtures with equal totals but different keys, mismatched values that cancel, and duplicated revisions. These expose false agreement as well as obvious disagreement. Reconciliation becomes credible when the populations and meanings agree before the values are compared, and when the report retains the distinctions needed to explain its conclusion.