Semi-Additive Rehearsal: The Wrong Sum and the Right Sum, Side by Side

The review screen (you already learned this)

Level 4's lesson "Fact Table Types and Measure Additivity" owns the first teaching of this, so here is the compressed version:

• A transaction fact stores events. One row per thing that happened, and its measures are additive: they sum across every dimension including time. A payout of $500 plus a payout of $300 is $800, on any day, in any city.
• A periodic snapshot fact stores state on a schedule. One row per entity per date, and its measures are semi-additive: they sum across entities on a single date, and never across dates. Six drivers' wallet balances on June 30 add up to a real number. One driver's balances on June 10, June 20, and June 30 add up to nothing at all.
• Ratios and percentages are non-additive. You never sum them and you never average an average. Recompute from the parts: SUM(numerator) / SUM(denominator).

That is the ladder. Naming it is table stakes.

What this lesson adds

Naming the trap is a knowledge answer. Interviewers get that answer from most candidates. What they rarely get is the demonstration: one result set holding the wrong number and the right number side by side, so the size of the error is visible instead of asserted.

Table

Two Detroit drivers, three June snapshots each. The naive SUM adds all six rows. The correct month-end total adds only the two rows in the highlighted column.

driver_id	as_of_date	balance_usd	counted by the naive SUM	counted by the as-of SUM
42	2026-06-10	980	yes	no
42	2026-06-20	1105.75	yes	no
42	2026-06-30	1240.5	yes	yes
74	2026-06-10	640.6	yes	no
74	2026-06-20	715.4	yes	no
74	2026-06-30	860.25	yes	yes

The correct total for Detroit is $2,100.75, the two June 30 balances. The naive total counts every snapshot and lands on $5,542.50, about two and a half times the real figure, which is not a wallet balance, not a revenue figure, and not any quantity that exists in the business. The multiple is close to the three snapshots per month but not equal to it, because the balances grow across those snapshots rather than repeating.

Getting to the as-of total

The pattern is the one you used for grain repair, pointed at time instead of duplicates: number each driver's snapshots newest first, keep number one, then sum across drivers.

WITH ranked AS (
  SELECT driver_id, balance_usd,
         ROW_NUMBER() OVER (PARTITION BY driver_id ORDER BY as_of_date DESC) AS rn
  FROM fact_wallet_snapshot
  WHERE as_of_date BETWEEN '2026-06-01' AND '2026-06-30'
)
SELECT ROUND(SUM(CASE WHEN rn = 1 THEN balance_usd ELSE 0 END), 2) AS correct_total
FROM ranked;

Putting both numbers in one query is what makes it a proof. SUM(balance_usd) over the same CTE gives the naive figure; SUM(CASE WHEN rn = 1 THEN balance_usd ELSE 0 END) gives the correct one. Same rows, same scan, two answers, and the interviewer can see which one the dashboard has been showing.

Common mistake: filtering the snapshot to a single hard-coded date (WHERE as_of_date = '2026-06-30') and calling it solved. That works until a driver has no row on the month-end date, and then they silently vanish from the total. Ranking per driver and keeping the latest row in the window survives a missing snapshot; an equality filter does not.

Interview nuance: "which measures on your fact table are semi-additive" is the standard follow-up to any star-schema answer. The strong version of the answer is three sentences: name the measure, name the dimension it will not sum across (almost always time), and say how you aggregate it instead (latest in the period, or an average-of-day-balances if finance asks for that). Then show the side-by-side if you have a keyboard.

On a real platform this differs. Bank and wallet warehouses run exactly this daily-balance snapshot, and it is usually the biggest table they own. Warehouse dialects add conveniences here: Snowflake and BigQuery let you reach for LAST_VALUE(...) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), and a semantic layer lets you declare the rule once instead of re-deriving it per query: dbt's Semantic Layer (MetricFlow) declares the measure with non_additive_dimension: { name: metric_time, window_choice: max }, which is exactly "do not sum this across time, take the latest", so no analyst can sum it by accident. ROW_NUMBER is the portable version that works everywhere, including here.