Fact Table Types and Measure Additivity

Level 4: Level 4: Data Engineering with SQLmedium20 minfact table graintransaction factperiodic snapshotaccumulating snapshotadditive measuressemi-additive measuresnon-additive measuresROW_NUMBER

Classify fact tables by grain and use measure additivity to decide which sums are valid: correctly roll up a semi-additive balance and recompute a non-additive ratio from additive parts.

Why "can I sum this column?" is a real question

Every warehouse metric is a SUM, AVG, or COUNT rolled up over some dimensions. The dangerous assumption is that any numeric column can be summed over any dimension. It cannot. Roll a bank balance up over time and you invent money that never existed. Additivity is the rule that tells you which aggregations are valid, and interviewers probe it because getting it wrong ships a dashboard that looks perfectly reasonable and is quietly wrong.

Three fact tables, told apart by grain

A fact table's grain is what one row means. Three shapes cover almost everything:

Transaction fact: one row per event (fact_sales, one row per line item). Its measures are fully additive: sum revenue across any dimension you like.
Periodic snapshot: one row per entity per period (fact_daily_balance, one row per account per day). You capture a level at a point in time, not an event.
Accumulating snapshot: one row per process instance, updated in place as it clears milestones. An order row carries order_date, ship_date, and deliver_date, and each column fills in as the order moves. You measure the lag between milestones.

Three kinds of measure

Additive: sum across all dimensions (revenue, quantity, order count).
Semi-additive: sum across some dimensions but not time (account balance, inventory on hand, headcount). To collapse time, take the period-end value or an average, never a sum.
Non-additive: never sum, not even across entities (ratios, percentages, unit prices). Recompute them from their additive parts.

Worked example: a daily balance snapshot

fact_daily_balance holds two accounts over three days. The input table, the query, and its live result render below.

Balance is semi-additive. Summing across accounts on the latest day gives today's total assets, 1200 + 900 = 2100, a real number. Summing the same column across all rows gives 5900, which means nothing: it folds Monday's and Tuesday's money into Wednesday's total. Same column, one legal roll-up and one meaningless one, decided entirely by whether you crossed the time dimension.

The trap interns hit

The classic warehouse bug is SUM(balance) over a date range on a periodic snapshot. It runs, returns a big confident number, and is wrong. The fix is to pick one row per entity first (each account's latest date via ROW_NUMBER), then sum those. For a non-additive measure the parallel trap is AVG of a per-row ratio: overall average order value is SUM(revenue) / SUM(order_count), never AVG(revenue / order_count), because averaging ratios weights a four-order day the same as a four-hundred-order day.

Interview nuance: when an interviewer hands you a daily_balance or inventory table and asks for a total, the answer they are listening for is "balances are semi-additive, so I take each entity's latest-period value and sum those, I never sum a balance across time."

Recap: transaction facts are fully additive; periodic snapshots are semi-additive (sum across entities, take period-end over time); accumulating snapshots track milestone lags; non-additive ratios get recomputed from additive parts. Additivity decides which aggregation is legal.

Sample data for this example

CREATE TABLE fact_daily_balance (
  account_key INTEGER,
  date_key    TEXT,
  balance     INTEGER
);
INSERT INTO fact_daily_balance VALUES
  (1, '2026-03-01', 1000),
  (1, '2026-03-02', 1500),
  (1, '2026-03-03', 1200),
  (2, '2026-03-01',  500),
  (2, '2026-03-02',  800),
  (2, '2026-03-03',  900);

Worked example (SQL)

SELECT
  'sum across accounts on the latest day (VALID)' AS calculation,
  SUM(balance)                                    AS result
FROM fact_daily_balance
WHERE date_key = (SELECT MAX(date_key) FROM fact_daily_balance)
UNION ALL
SELECT
  'sum across ALL days (MEANINGLESS)',
  SUM(balance)
FROM fact_daily_balance;

Apply

Your turn

The task this lesson builds to.

Write a script that fills the pre-created current_balance(account_key, balance) table with the CURRENT balance of every account: one row per account, holding that account's balance on its own LATEST date_key. balance is a semi-additive measure, so the current total is each account's most recent balance summed, not SUM(balance) over every row. Watch out: some accounts stop reporting before the newest date in the table, so you must use each account's own latest date, not the global maximum date. Expected result: three rows (accounts 1, 2, and 3) whose balances sum to a current total of 2800. The target is seeded empty; lead your load with DELETE FROM current_balance; so the script is safe to re-run.

4 hints and 6 automated checks are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Write a script that inserts exactly one row into the pre-created sales_report(metric, value) table: metric = 'overall_aov' and value = the overall average order value across all days in fact_daily_sales. Average order value is a NON-ADDITIVE measure, so recompute it from its additive parts: value = SUM(revenue) / SUM(order_count), not AVG(revenue / order_count). Force decimal division with * 1.0 so integer truncation does not round the answer down. Expected result: one row whose value is about 111.76. The target is seeded empty; lead your load with DELETE FROM sales_report; so the script is safe to re-run.

4 hints and 4 automated checks are waiting in the workspace.