Which totals do you reconcile against the input to judge a nightly aggregation job, and what does a match still miss?
answer
- cheap aggregates on both sides
- rows, sums, distinct keys, rejects
- the relation the transform implies
- reconcile per bucket, not once overall
- compensating errors survive a matching total
basics
~20 sCarry a small set of totals through the job: rows in against rows out with the relation the transform implies, the grand total of an additive measure, the distinct count of the grouping key, and the null and rejected counts. A match proves the job faithful to its input, not the number true.
solid answer
~50 sReconciliation compares cheap aggregates computed on both sides rather than the result itself. The usual set is rows read against rows written, with whatever relation the transform implies; the sum of an additive measure that the logic is supposed to preserve, such as an amount; the distinct count of the grouping key against the number of output groups; and the counts of nulls and of records the job rejected, so nothing disappears silently. These are cheap because each is one pass and one number. What they miss is everything that cancels: rows attributed to the wrong day or the wrong customer keep the grand total intact, non-additive measures like averages and distinct counts do not reconcile at all, and an equal number of dropped and duplicated rows nets to zero. A clean reconciliation says the job did not lose or invent value relative to its input; it never says the input was right.
go deeper
Recall the four cheap numbers — rows in, rows out, the sum of an additive measure, and the count of anything rejected — and that they are computed on both sides of the job.
Explain the relation each transform implies between input and output counts, why a join bounds neither side, and why a ratio must be reconciled through its underlying count and sum.
Demonstrate the limits out loud: compensating errors, wrong attribution and a faithful job over an incomplete input all pass a clean reconciliation, so per-bucket totals and invariants have to carry the rest.
Set the rule about what every published dataset must emit alongside itself, so reconciliation is a property of the platform rather than something each team re-invents, and decide who is paged when it breaks.
## What reconciliation is and why it beats reading the output A published aggregate is far too large for anyone to read, and there is usually no independent copy of the right answer to compare it to. **Reconciliation** sidesteps both problems: instead of checking the result, it computes a handful of cheap aggregates on the **input** and the same aggregates on the **output**, and asserts the relation between them that the transform implies. Each is one pass and one number, so the check costs a fraction of the job and can run every night. ## The totals worth carrying 1. **Row counts, in and out**, per input source and per output dataset. 2. **The grand total of an additive measure** the logic is supposed to preserve — an amount, a quantity, a duration. This is the strongest single number, because it is sensitive to both lost rows and altered values. 3. **The distinct count of the grouping key** in the input, against the number of rows in the output of a grouping step. 4. **Null counts and rejected counts.** Every record the job filtered, failed to parse or routed away is counted, so the gap between in and out is fully accounted for rather than assumed. 5. **Boundary counts** — rows per source period, so a missing input day shows up as a zero rather than as a slightly smaller grand total. ## The relation the transform implies The common mistake is to demand equal counts everywhere. What must hold is the relation the step actually implies: | step | expected relation between input and output rows | |---|---| | per-record mapping | equal | | filtering | output plus rejected equals input | | flattening a nested field | output equals the summed element count of the input rows | | grouping | output equals the distinct count of the grouping key | | joining two inputs | neither bound holds — output may be larger or smaller, so reconcile a measure and the per-key multiplicity instead of a count | The join row is the one people skip, and it is where accidental fan-out lives: a duplicated row on the lookup side multiplies matching rows and inflates every additive measure downstream. ## What a clean match still misses - **Compensating errors.** Two rows swapped between groups leave the grand total untouched. So does a sign error on two rows of equal magnitude. - **Wrong attribution.** The total is right and every unit sits under the wrong day, region or customer. Reconciling the same total *per bucket* rather than once overall is what narrows this, and it is the cheapest upgrade available. - **Non-additive measures.** Averages, ratios, medians and distinct counts do not reconcile by summation; the underlying count and sum do, so carry those and derive the ratio. - **Dropped and duplicated in equal measure.** A net of zero looks like a match. Distinct-key counts on both sides catch what a row count does not. - **A faithful job over a wrong input.** Reconciliation compares the output with the input; if the input was already incomplete, both sides agree and both are wrong. Demonstrating where a published number came from, end to end, is a governance subject rather than this one. ## Bounded and unbounded input reconcile differently - A **finite run** has an end, so the counts settle and one comparison closes the run. - A runtime that executes continuous work as **a rapid succession of small finite runs** gives you a start and an end per small run, so in and out counts exist per run and the daily figure is their summation. - A **record-at-a-time** runtime has no run boundary at all. Reconcile per bucket of the **event moment** — when the thing happened in the world — and treat the bucket as provisional until its **completeness claim**, the running assertion that no record older than a stated moment will still arrive, has passed it. Comparing a bucket before that point reports a shortfall that is not a defect. ## Where the totals should live The numbers themselves are worth keeping per run, not just asserting and discarding: the trailing series of row counts and grand totals is what turns tonight's single figure into a judgement about whether tonight is unusual. Rules declared outside the job and checked against what it published are a separate mechanism with its own owner; reconciliation here is the job proving its own arithmetic against the input it read.
- Which single total would you keep if you could only keep one?The sum of an additive measure, computed per bucket rather than once overall. It is sensitive to lost rows, invented rows and altered values at the same time, and bucketing it catches the misattribution that a grand total hides. A bare row count is cheaper but blind to every change in value.
- The output total exceeds the input total after a join. What do you suspect first?Fan-out: the lookup side is not unique on the join key, so each matching row multiplies. Check the distinct-key count against the row count on the lookup side, and the per-key multiplicity of the result. A genuine one-to-many relationship needs the expected relation restated, not the check disabled.
- How do you reconcile a job whose input is a continuous stream?Bucket both sides by the event moment and compare a bucket only once its completeness claim has passed, so records still in flight are not counted as losses. Under a model that runs continuous work as repeated small finite runs you additionally get per-run counts, which sum to the bucket.
It is a stocktake, not an audit. Counting what came into the warehouse, what went out and what is on the shelves will catch a lorry that never arrived and a pallet that vanished — and it will not notice that every box was shelved in the wrong aisle, because the totals still balance.
saying these in an interview costs you the question
- Demands equal row counts across a join or a filter
- Believes a matching grand total proves the output is correct
- Reconciles averages and distinct counts by summation
- Leaves rejected and unparsed records out of the accounting
- Compares an unbounded input's bucket before its completeness claim passed
- Reconciles only the grand total, never per day or per key