How do you reconcile a mart's row counts and measure sums against the source system?

## What reconciliation adds that other tests cannot Grain, not-null and referential tests all check the *shape* of a model. They pass perfectly on a table that is missing a third of its rows. Reconciliation asks the different question: does this mart still agree with the system the business considers authoritative? It is the check finance and audit care about most, and it is the one that catches whole classes of silent loss — a filter that excluded more than intended, an incremental window that skipped a day, a join that dropped unmatched rows, a source that stopped publishing one region. ## Control totals, not row-by-row comparison Comparing individual rows across systems is expensive and usually impossible — the mart has transformed them. What survives transformation is aggregates. For each business day compute, on both sides, the row count and the sum of each measure that matters, then compare: ```sql with src as ( select order_date as d, count(*) as n, sum(amount) as amt from stg_orders group by order_date ), tgt as ( select order_date as d, count(*) as n, sum(order_amount) as amt from fct_orders group by order_date ) select coalesce(src.d, tgt.d) as business_date, src.n as source_rows, tgt.n as mart_rows, coalesce(tgt.n, 0) - coalesce(src.n, 0) as row_diff, coalesce(tgt.amt, 0) - coalesce(src.amt, 0) as amount_diff from src full outer join tgt on src.d = tgt.d where coalesce(src.n, 0) <> coalesce(tgt.n, 0) or abs(coalesce(src.amt, 0) - coalesce(tgt.amt, 0)) > 0.01 ``` The **full outer join** is deliberate: an inner join would hide a business day that exists on only one side, which is the most important thing to catch. A count alone is not enough. Two rows can be swapped, or an amount can be scaled wrongly, without changing the count — the measure sums catch that. Conversely a sum alone can hide offsetting errors, so both together are the minimum useful pair. Some teams add a third: a distinct count of business keys, which catches duplication that a plain count would also show but a per-key comparison localizes. ## Group by business date, not load date This is the detail that separates a reconciliation that works from one that generates noise. If you aggregate by the timestamp at which rows arrived, late-arriving data appears as a surplus on today and a deficit on some past day, and the two never line up. Grouping by the business date the event belongs to means late data restates the day it belongs to, and the comparison stays interpretable. It also means yesterday's reconciliation result can legitimately change — which is information, not a bug, provided you store each run rather than overwriting. ## Model the legitimate differences explicitly A mart is rarely a copy. It filters test accounts, excludes cancelled records, deduplicates at-least-once deliveries, converts currency, allocates across dimensions. Every one of those makes the totals differ *correctly*. The discipline is to enumerate them, and where possible to reconcile against a source query that applies the same exclusions, so the expected difference is zero rather than "about two percent". A tolerance of "roughly right" degrades — it absorbs the next real defect. Where an exact match is genuinely impossible (floating-point currency conversion, for instance), define a numeric tolerance and write down why it is that number. ## Persist the results and trend them Write each reconciliation run into a results table: run timestamp, business date, source total, target total, difference. Two things follow. First, you can see a small gap opening gradually rather than only noticing when it crosses a threshold. Second, when someone asks in three months why last April moved, the history answers. ## Reconcile at each boundary, not only at the end Comparing only source to final mart tells you that something lost the rows. Comparing source to staging, staging to intermediate, and intermediate to mart tells you *which transformation* did. On a pipeline with several layers this is the difference between a ten-minute diagnosis and a day of bisecting. ## Cost and cadence Full-history reconciliation is a full scan on both sides and does not belong in every build. The usual shape is: reconcile a rolling recent window — say the last seven or thirty business days, wide enough to cover the late-arrival window — on every run, and reconcile all history on a schedule. Anything older than the late-arrival window should be frozen; if it moves, that itself is an alert, because history restating without a declared backfill is a strong signal that something reprocessed data it should not have. ## Severity A reconciliation break is usually a warning-plus-triage rather than a hard build failure, because the mart is not corrupt — it disagrees. The exception is regulated or financial reporting, where publishing a figure that does not tie to the ledger is worse than publishing nothing, and the check blocks.