Your monthly revenue rollup no longer matches the atomic fact table for closed months — what causes do you check?

## Start by characterizing the drift Before theorizing, run the comparison and look at the shape of the difference: ```sql SELECT d.month_key, SUM(f.revenue_amount) AS atomic_revenue, MAX(a.revenue) AS agg_revenue FROM fact_sales f JOIN dim_date d ON d.date_key = f.date_key LEFT JOIN (SELECT month_key, SUM(revenue_amount) AS revenue FROM agg_sales_month_product_store GROUP BY month_key) a ON a.month_key = d.month_key GROUP BY d.month_key HAVING SUM(f.revenue_amount) <> MAX(a.revenue); ``` The shape is diagnostic: - **One or two old months off** — late-arriving or restated facts. - **Everything from a date forward** — a definition or logic change shipped then. - **Totals match but a breakdown by category does not** — a dimension change re-parented rows. - **Drift growing every run** — the incremental rebuild is missing or duplicating rows each cycle. ## Cause 1: late-arriving and restated facts Most aggregates are rebuilt incrementally over a trailing window — the current month, maybe the last seven days. Anything landing outside that window updates the atomic table and never reaches the summary. Sources restate for very ordinary reasons: a settlement file corrects last quarter, a returns feed arrives late, a bug fix reprocesses a month. The defence is a **rebuild window matched to observed lateness**, not to convenience. Measure how far back rows actually change — the spread between event date and load date in the atomic table — and rebuild at least that far. Where restatements can reach arbitrarily far back, drive the rebuild off the fact table's own audit column (`loaded_at`, `updated_at`): find the distinct periods touched since the last aggregate run and rebuild exactly those. ## Cause 2: dimension changes re-parenting history This one surprises people because the fact rows never moved. If a product is reassigned to a new category, or a dimension attribute changes and history is versioned, then a rollup grouped by category legitimately changes for past periods. Whether the aggregate should follow depends on the policy the atomic model already implements — and the only defect is when the two disagree. The practical rule: **any aggregate that groups by a dimension attribute is invalidated when that attribute changes.** So dimension loads must publish which keys changed, and the aggregate rebuild must cover the periods in which those keys appear. Rebuilding on the fact side alone will never catch it, which is exactly why the drift shows up as "totals tie, breakdown does not". ## Cause 3: definition drift The aggregate and the star are usually maintained by different transformations, and over months they diverge: a `WHERE store_type <> 'TEST'` added to one, a currency conversion updated on the other, a new revenue component included in the star but not summed into the rollup. The structural fix is to stop maintaining two definitions. The aggregate should be a `GROUP BY` over the star with **no additional predicates**. Any filter that belongs in the business logic belongs upstream in the atomic model where every consumer inherits it. When you find drift, diff the two transformations first — it is faster than any data forensics. ## Cause 4: unknown members and nulls A fact row whose dimension lookup fails is typically assigned an unknown-member key rather than dropped. If the atomic load does that and the aggregate load filters those rows out, or joins in a way that drops them (an inner join to a dimension that lacks the member), the summary is short by exactly the unmatched rows. This drift is usually small, persistent, and maddening — always check row counts, not only measure sums. ## Cause 5: a broken grain If the aggregate's declared grain is not enforced, an incremental load that inserts instead of merging leaves duplicate rows and the summary reads *high*. A grain-uniqueness check distinguishes this from every other cause in one query, so run it early. ## Fix it, then make it impossible to miss Two actions. **Repair**: rebuild the affected periods from the atomic table — deleting and reloading whole periods is safer than surgical patching because it converges regardless of the original cause. **Prevent**: make the reconciliation query a pipeline test that runs after every aggregate load, comparing measure sums *and* row counts by period, and failing the run on any mismatch beyond an explicitly agreed tolerance. Include a breakdown by one or two grouping attributes so re-parenting is caught, not just totals. Agree the tolerance deliberately. Exact equality is the right default for currency and counts; anything looser must be justified, because "close enough" is how a small permanent gap becomes normal. ## What interviewers listen for A method rather than a hunch: characterize the drift, use its shape to narrow the cause, fix by rebuilding whole periods, and leave behind a test. The candidate who jumps straight to "probably late data" without checking whether the breakdown or only the total is wrong has not operated one of these.