QualityGENERALSOURCE-SPECIFICSIMULATED

Reconciliation

Count the rows and sum the measure at the source for a closed period, and compare with the serving table. The only check that observes both ends at once.

What actually happens How to build it Can I trust it?

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

Every internal check passes. How do I find out whether the number at the end of the pipeline still agrees with the system that produced it?

Who needs this

A finance team that has to sign a figure, an executive comparing a dashboard with the payment provider's statement, and every engineer who will be asked "is this number right" and currently has no way to answer beyond "the pipeline ran".

What one row is

Reconciliation compares two aggregates that must be at the same declared grain over the same declared period. One order in the source must correspond to one order in the serving table; if the serving table is at order-line grain, the row counts will never agree and the discrepancy is a modelling artefact rather than a data loss (Grain: What Does One Row Represent?).

The obvious build

Compare row counts between adjacent stages. Raw had a million rows, staging had a million rows, the fact table has a million rows, therefore nothing was lost. Per-hop counts are genuinely useful — they localise a loss to an arrow — and they are the natural thing to build first.

Why it breaks

Every hop preserved its count and the transformation changed the arithmetic. Refunds stopped being subtracted; the row counts are identical at every stage and revenue is overstated (Two Dashboards, Two Numbers).

How it breaks with real data

Every hop preserved its count and the transformation changed the arithmetic. Refunds stopped being subtracted; the row counts are identical at every stage and revenue is overstated (Two Dashboards, Two Numbers).
A duplicate load added exactly as many rows as a filter dropped, so the count at the end matches the count at the start and two errors have hidden each other.
The extract itself never saw the rows. Per-hop reconciliation starts at the raw layer and therefore begins downstream of the loss, certifying the movement of data that was already incomplete (Incremental Extraction).
The comparison runs against an open period, so the source has rows the pipeline has not received yet. The check fires daily, is always "explained by late data", and is eventually switched off (Late-Arriving Data).
Source and serving define the measure differently — one includes tax, the other does not — so the check reports a stable, meaningless discrepancy that everyone learns to subtract mentally (Semantic Changes).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

Reconciliation is the only check in the portfolio with an external reference. Every other check reads the copy and asks whether the copy is internally plausible; a self-consistent error satisfies all of them. Comparing against the source is the only way to notice that the composition is wrong (Data Quality).
It observes the whole journey at once, which is its strength and its limitation. A discrepancy proves something between the two ends is wrong and says nothing about where — which is why per-hop counts remain valuable as the localisation step (The Fundamental Data Journey).
It requires a closed period: a boundary after which the source will not produce more records for that period. Without one, "missing" and "not yet arrived" are indistinguishable and every result is ambiguous.
Two aggregates are worth comparing and they fail differently. Row count catches losses and duplications. A summed measure catches value-level errors — a cast that nulled, arithmetic that changed, a currency conversion — that count alone cannot see. Running both is what makes reconciliation broad (The Dimensions of Data Quality).
The comparison is only as good as the agreement about what is being compared. net_revenue must mean the same thing on both sides, which is a conversation with the producing team before it is a query (Data Contracts).

Both ends, one comparison

The check is two aggregates and a subtraction. Count the orders and sum the net amount in the source for a day that is definitely closed; do the same in the serving table; compare. What makes it the most valuable check in the module is not sophistication — it is that the second operand comes from a system your pipeline did not produce.

Both a count and a sum are needed, and they fail for different reasons. If only the count matches, rows are all present and something changed their values. If only the sum matches, the values are right and the rows have been duplicated or dropped in offsetting ways. Together they narrow the cause before anyone opens a lineage graph.

The WHERE clause is doing as much work as the aggregates. It names one closed day, on both sides, using the same timestamp semantics — event time, not load time. Reconciling by load time compares "what we processed" with "what we processed", which is a tautology dressed as a check.

Source versus serving, for one closed period

1-- Run against the SOURCE system. One closed day, event time, aggregate only.
2SELECT
3    DATE(o.placed_at)                                   AS business_date,
4    COUNT(*)                                            AS source_orders,
5    SUM(o.amount_minor) - COALESCE(SUM(r.amount_minor), 0)
6                                                        AS source_net_minor
7FROM orders AS o
8LEFT JOIN refunds AS r ON r.order_id = o.id
9WHERE o.placed_at >= TIMESTAMP '2026-08-24 00:00:00'
10  AND o.placed_at <  TIMESTAMP '2026-08-25 00:00:00'
11GROUP BY 1;
12 
13-- Run against the SERVING table. Same grain, same period, same definition.
14SELECT
15    order_date                                          AS business_date,
16    COUNT(*)                                            AS served_orders,
17    SUM(net_amount_minor)                               AS served_net_minor
18FROM fct_orders
19WHERE order_date = DATE '2026-08-24'
20GROUP BY 1;
21 
22-- The check: both differences must be zero, and both sides must be non-empty.
23--   source_orders    - served_orders    = 0
24--   source_net_minor - served_net_minor = 0

Two details are load-bearing. The source query subtracts refunds because the serving table does — the definitions must match or the check reports a permanent, meaningless difference. And the last comment is not decoration: assert that both queries returned a row, or a source query that failed silently reconciles to zero against nothing.

Comparing the same thing at both ends

Most reconciliations that never work fail here rather than in the SQL. The two sides are counting different units, or covering different periods, or applying different definitions of the measure — and the difference is reported as a data quality failure when it is a specification failure.

Grain is the most common of the three. The source has orders and refunds as separate tables; the serving table may be one row per order, one row per order line, or one row per financial event. Only the first of those can be compared by count against the source's order count, and choosing to compare the others by count produces a discrepancy that is entirely correct and entirely uninformative.

The table below tracks what one row means on each side. The breaksIf column is where reconciliations go wrong in practice — read it as a checklist to run before writing the query rather than as a description of failures you will later debug.

What one row is on each side of the comparison

Stage	One row is	Breaks if
Source `orders`	One order, in its current state, with the amount as most recently updated.	The source mutates rows in place, so "the amount for a closed day" changes after the fact and the same reconciliation returns a different answer next week (Slowly Changing Dimensions).
Source `refunds`	One refund event against one order, possibly several per order, possibly on a later day.	Refunds are attributed to the refund date on one side and to the original order date on the other. Both are defensible; only one can be in the check.
Raw landing	One delivered change record, possibly delivered more than once.	It is used as the reconciliation baseline, which measures the pipeline against what it received rather than against what happened (The Raw Landing Zone).
`stg_orders`	One order, reconstructed as the latest change per order id.	The latest change is chosen by arrival order rather than commit order, so an out-of-order update wins and the amount differs from the source's (CDC Ordering and Transaction Boundaries).
`fct_orders`	One order at order grain, with refunds already netted into the measure.	The model is actually at order-line grain, in which case the count comparison is meaningless and only the sum comparison is valid (Fact Tables).
Revenue mart	One country-day with revenue pre-aggregated.	It is reconciled by count against the source's order count, comparing country-days with orders — a discrepancy of several orders of magnitude that is nobody's bug (Data Marts).

Reconcile at the grain where one row on each side means the same real-world thing. Where the grains genuinely differ, drop the count comparison and reconcile the summed measure only — and say on the dashboard that you did.

The bug that only this check finds

SIMULATEDThe five-pass-one-fail result described here is produced by src/de/sim/pipeline.ts under its transform-bug fault and pinned by scripts/de-sim.test.ts. It is a deterministic teaching model with a fixed seed, not a measurement, and its value is that the isolation is asserted rather than asserted-by-the-author.

The model in this repository installs a fault that is deliberately unspectacular: the revenue transformation stops subtracting refunds. Nothing crashes, nothing is missing, nothing is duplicated, nothing is late, nothing is mistyped, and the distribution of the day is unchanged because the same orders are present in the same countries.

Five of the six checks in that model pass. Reconciliation fails alone, because it is the only one holding a reference the pipeline did not produce. That result is asserted in the test suite rather than described in prose, which is the difference between a claim and a model.

The lineage below is the same story told as a walk. At each node, ask what could go wrong there and whether reconciliation would notice. The answer is yes almost everywhere — and the last column shows exactly where it stops being yes, which is the honest boundary of the strongest check available.

What reconciliation observes at each hop, and where it stops

Source `orders` and `refunds`
holds The authoritative record of what happened, and the reference operand of the comparison.
could corrupt An application bug writing wrong amounts. Reconciliation is blind to this by construction — it would agree perfectly with a wrong source (Source of Truth).
↑ reads from
Change capture
holds A position in the source log and every committed change emitted from it.
could corrupt A gap during a connector outage. Reconciliation sees the resulting shortfall in both count and sum, and localises nothing (CDC Failure Modes and the Retention Deadline).
↑ reads from
Event log
holds Durable, replayable change records, ordered within a partition.
could corrupt Redelivery inflating counts, or retention expiring before a consumer caught up. Reconciliation sees both as a count discrepancy (Retention and Replay).
↑ reads from
Raw landing
holds Every arriving record, preserved exactly as received.
could corrupt Nothing, if it is genuinely immutable — which is why it is the wrong place to reconcile from and the right place to reconcile *through* (Keeping Raw History: The Recovery Position and the Liability).
↑ reads from
Transformation
holds The deduplicated, windowed, modelled result and all of the business arithmetic.
could corrupt A fan-out join, an over-broad filter, a window closed too early, and the refund subtraction that quietly disappeared. Reconciliation sees every one of these in the sum (Two Dashboards, Two Numbers).
↑ reads from
Serving table
holds The modelled result consumers query, and the other operand of the comparison.
could corrupt A backfill appending instead of replacing. Reconciliation sees the doubled count immediately (What Backfills Break).
↑ reads from
Dashboard
holds One number, produced by filters and joins defined in the BI layer.
could corrupt A filter or join added downstream of the serving table. Reconciliation is blind here too — it stops at the table, and the BI layer is past its right-hand edge (The Metrics Layer).

Blind at both ends and sighted through the middle. Reconciliation cannot see a wrong source or a wrong dashboard filter, and it sees essentially everything the pipeline does in between — which is precisely the region no other check can reach.

Four reconciliations, four blind spots

There is more than one comparison worth running, and they differ in cost, in coverage and in how much cooperation they need from the producing team.

The progression below runs from what you can do alone to what requires an agreement. The first two need only source read access. The third needs a shared definition. The fourth needs the producer to emit something, which makes it a contract and moves the obligation to where it belongs (Who Owns Data Quality).

Read the blind spots together. Every one of these is defeated by a wrong value at the source, and none of them says anything about a period that has not closed. Those two gaps are permanent, and the right response is to state them rather than to keep adding checks that also cannot see them.

Reconciliation variants, in increasing order of what they require

Check	Expresses	Catches	Still misses
Row count, source versus serving, one closed period	Every record the source has for the period reached the serving table once and only once.	Missing rows from a capture gap or an early window close, duplicated rows from a non-idempotent re-run, and rows dropped by an over-broad filter (Missing Rows).	Offsetting errors — a loss and a duplication of equal size cancel exactly. And every value-level error, since a count cannot see amounts at all.
Summed measure, source versus serving, one closed period	The arithmetic that produced the measure is unchanged end to end.	A cast that nulled, a refund subtraction removed, a currency conversion applied twice, a sign error, a join that changed the multiplicity of a measure (Two Dashboards, Two Numbers).	Any measure it does not sum — quantity, discount, tax and status are all invisible to a revenue reconciliation. And a definitional difference reports as a permanent failure.
Per-group sums, source versus serving, one closed period	The measure agrees within every segment, not merely in total.	Two groups wrong in opposite directions, a single country or product line dropped by a join, and a dimension mapping that sends rows to the wrong group (Dimension Tables).	Groups that exist on one side only, which appear as a missing row rather than as a difference and must be tested for explicitly.
Producer-emitted control total for its own closed period	The producing system asserts what it produced, and the platform checks against that assertion.	Everything the direct comparison catches, without needing standing read access to the operational database — and it makes the producer a participant rather than a subject (Data Contracts).	Anything the producer computes wrongly, since you are now reconciling against its belief rather than its data. It also fails silently if the producer stops emitting, so the absence of a control total must itself be an alert.

Every row is blind to a wrong source and to open periods. Those are not gaps to close with more checks — they are the reason a human who knows the business still has to look at the number occasionally.

How to build it

Most important first.

Reconcile against the source system, not against the raw layer. Reconciling raw to serving proves your transformations preserved what you received and says nothing about what you failed to receive.
Compare a count and at least one summed measure. A count-only reconciliation passes cleanly through every arithmetic bug in this module (Distribution Tests).
Define the closed-period boundary explicitly and generously — a full period plus the known late-arrival window — and reconcile the period that is definitely closed rather than the most recent one (The High-Water Mark).
Agree the measure definition with the producing team in writing before the first run, and store it next to the check. Most long-lived reconciliation discrepancies are definitional, not technical (Who Owns Data Quality).
Reconcile per group as well as in total — per country, per product line, per source system. A total that matches while two groups are wrong in opposite directions is a real and unpleasant outcome.
Record every run's discrepancy as a number, not a boolean. The value of the series is that a discrepancy growing from zero is visible long before it crosses any threshold (Pipeline Metrics).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

A clean reconciliation guarantees that two independent systems agree on one measure for one closed period at one grain. That is the strongest routine correctness statement a data platform can make, and it is narrower than it sounds.
It guarantees nothing about any measure it does not sum. Reconciling revenue says nothing about quantity, discount, tax, status or timestamps (Data Tests).
It guarantees nothing about open periods, which is where late data lives and where operational dashboards read (Freshness Checks).
It cannot detect an error present identically at both ends. A bug in logic shared by the extract and the model — or a wrong value at the source — reconciles perfectly and is exactly as wrong at both ends (The Dimensions of Data Quality).
It says nothing about meaning. A perfectly reconciled revenue column that changed from gross to net reconciles beautifully and reports something other than what its consumers believe (Semantic Changes).

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

The check on this check is to deliberately drop a known set of rows from a staging copy and confirm the discrepancy equals what was dropped. A reconciliation that has never been shown to detect a known loss is unvalidated.
Also assert that both sides of the comparison actually returned data. A reconciliation where the source query silently returned zero rows will report a discrepancy of zero if written carelessly — the failure mode is a check that passes when it should be impossible to evaluate.
It misses a discrepancy that is correct: two systems that legitimately count different things will disagree forever, and the honest response is to change the definition rather than the threshold (Data Contracts).

Freshness

Reconciliation is structurally the slowest check in the portfolio, because it must wait for a period to close. That is not a defect to optimise away — it is the price of the guarantee.
The practical consequence is that reconciliation protects reported history and cannot protect a live dashboard. A platform needs fast, weak checks for the open period and this slow, strong one for the closed one (Data Quality).
Running it more often than the period boundary does not make it fresher; it makes it noisier. The right cadence is one run per closed period, plus a re-run after any backfill of that period (Validating a Backfill Before You Publish).

When the schema or meaning changes

Any change to the measure's definition on either side invalidates the reconciliation. Definitional changes must be announced and versioned like schema changes, because the check will otherwise report a technical failure for a business decision (Schema Evolution).
A new source system feeding the same serving table adds a term to the comparison. Reconciliations that were written against one source silently become partial when a second one appears (Source of Truth).
A grain change in the serving model — orders to order lines — breaks the count comparison entirely while leaving the sum comparison valid. That asymmetry is a useful diagnostic and a common surprise (Grain: What Does One Row Represent?).

How to re-run this safely

A discrepancy is a diagnosis task before it is a repair task. The sequence that works is: confirm the period is closed, confirm both definitions still match, then walk per-hop counts to localise the loss (Lineage Debugging).
Once localised, repair is a bounded backfill of the affected range with an idempotent merge, followed by a re-run of the reconciliation for that period specifically (Planning a Backfill).
Record the closed reconciliation result per period permanently. Being able to say "this period reconciled cleanly when it closed" is what stops every future incident from re-litigating all of history (Audit Logs for Privileged Actions).

What can go wrong

Failure modes

Reconciling raw against serving rather than source against serving, which certifies the movement of data that was already incomplete.
Comparing counts only, which passes through every arithmetic error.
Running against an open period, producing a permanent discrepancy that gets explained away.
Definitional drift between the two sides, producing a stable non-zero difference that everyone learns to ignore (Alert Fatigue: The Page Nobody Reads).
A source query that fails silently or returns nothing, making the comparison trivially satisfied — the failure of the mitigation itself.
Direct query access to the source being revoked for good security reasons, after which the check quietly stops running (Data Access Control).

Misreads

"The row counts match, so the data is correct." Counts matching is consistent with every value being wrong, which is exactly what a bad cast or an arithmetic change produces.
"Reconciliation proves the data is right." It proves two systems agree. If the source is wrong, they agree on a wrong number and the check is perfectly green (Source of Truth).
"A non-zero discrepancy means the pipeline lost data." It means the two aggregates differ. Definitional drift and open periods produce discrepancies with no data loss at all, and they are the more common cause.
"We reconcile, so we do not need the other checks." Reconciliation is slow, coarse and blind to everything outside the measures it sums. It completes the portfolio rather than replacing it (Data Quality).

Privacy, retention and access

A reconciliation query needs read access to an operational system, which is a standing grant to a production database for an automated job. Scope it to an aggregate-only view rather than to the tables (Data Access Control).
Reconciliation results for closed periods are the natural audit artefact for reported figures, and should be retained under the same retention rules as the figures themselves (Data Retention).

Operating it

How you see it in production

Absolute and relative discrepancy per period, per measure, as a recorded series rather than a pass/fail — drift from zero is the early signal (Pipeline Metrics).
Per-hop row counts on the same chart, so a discrepancy can be localised to an arrow in the same view where it was detected (Data Lineage).
A permanent per-period record of the reconciliation result at close, which becomes the platform's audit trail for reported figures (Audit Logs for Privileged Actions).
Time between period close and reconciliation completing, because a check that runs late is a check that has stopped protecting the publish (Depth Is Not an Emergency; Age Is).

What changes at 10x and 100x

At 10x volume nothing structural changes: these are aggregates, and their cost tracks the period rather than the history (Scan Cost).
At 100x, the source-side aggregate may become unacceptable for the operational database, and the reconciliation moves to a control total the producer emits for its own closed periods — which is a contract, not a query (Data Contracts).
At many sources, reconciliation becomes a per-source obligation and the coordination cost grows faster than the technical one. This is where it turns into an organisational commitment rather than a scheduled job (Who Owns Data Quality).

What drives cost here

The dominant cost is the query against the source, which is an operational system that was not built for analytical aggregates. Scope it tightly to one closed period and run it once (Workload Isolation).
On the serving side the cost is a partition-scoped aggregate, which is cheap when the table is partitioned by the period being reconciled and expensive when it is not (Partition Pruning).
Per-group reconciliation multiplies the result rows but not the scan, so it is nearly free once the aggregate is being computed — which makes it the best value addition available here.

What this approach costs

It is the slowest check and the strongest. Choosing it means accepting that your best correctness signal arrives after the period, and building weaker fast checks for everything before that.
It requires access to the source, which crosses a team boundary and often a security one. A producer-emitted control total is the alternative and it is weaker — it reconciles against the producer's belief rather than against the producer's data (Least Privilege).
It forces a definitional agreement that many organisations have avoided having. That conversation is expensive, uncomfortable, and the single most valuable side effect of building the check.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALComparing an aggregate at the source with the same aggregate at the serving table for a closed period is method rather than tooling, and works with two hand-written queries. What varies is whether source access is available and whether a period boundary genuinely exists.
SOURCE-SPECIFICA transactional database can be aggregated directly for a closed period; a SaaS API often exposes only a paginated list with no aggregate endpoint, so the control total must come from a report the vendor generates rather than from a query you write, and its own boundary rules then govern the comparison.
SIMULATEDThe result cited in the last section comes from the deterministic model in src/de/sim/pipeline.ts and its test file, not from a measured platform. It is a worked example chosen because it isolates the case, not evidence about how often this bug occurs in practice.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasetransactions-and-acid aggregation-and-grouping joins relationships-and-keys follow-the-query

Backendread-replicas database-constraints

API Designwhat-is-an-api-contract

Securityaudit-logs least-privilege

Observabilityqueue-age alert-fatigue correlation-vs-causation

Distributed Systemsreconciliation source-of-truth

Domains that do not exist yet

— Distributed Systems owns why two systems that both behaved correctly can still disagree at a moment in time — replication lag, partial failure and the absence of a global clock. Reconciliation sidesteps all of it by only ever comparing closed periods, which is a design choice worth understanding rather than a coincidence.