Five Answers, One Question

Monday. You want a single number: what did we sell in Q2? Second-hand marketplace, so every item is unique and quantity is always one. You send the question to four people who all have access to the same production replica.

By lunchtime you have four answers.

Nobody made an arithmetic mistake. The dangerous answer is not the €28m — that gets noticed. It is C’s €9.6m, which is close enough to look right.

Three of these are variations on one failure and the fourth is its mirror image. Both come from the same root: the tables were designed for writing, and the analysts were reading them.

Order 26: Netherlands, 1 April, three items (COS €162.29, an unmapped brand €199.42, Prada €454.55), order_total €816.26. The Prada sits in the Hamburg authentication hub and the other two in a regional hub, so the order ships as two parcels. The customer paid with a voucher plus a card, so the order has three payment rows.

Join orders to payments and order 26 appears on three rows, each carrying the full €816.26. SUM(order_total) reports €2,448.78 for an €816.26 order. Join to shipments instead and it reports €1,632.52.

Across the quarter: 12,000 orders, 23,887 payment rows — the money is counted 1.99× on average, giving €28.4m. Shipments: 13,016 parcels, 1.08× per order, €11.8m. Same bug, quieter.

Fan-out (join explosion): joining a table to something with many rows per key duplicates the first table’s rows. Every measure in the query is then inflated by exactly that duplication factor.

The ten-second test: run SELECT count(*) before a join and after it. If the count went up, every measure is now wrong by that much. A and D would both have been caught in seconds (12,000 → 23,887; 12,000 → 13,016).

B would not have been caught. She joined order_items to orders — many-to-one, no fan-out at all. 16,062 rows in, 16,062 rows out. Then she summed order_total.

Every row in her result is one item, but order_total is a property of the order. Order 26 appears on three rows and each carries €816.26. Her €16.6m is the truth plus every multi-item order counted an extra time per extra item. The row count never moved, so the ten-second test says nothing.

The grain of a table is the answer to “what does one row mean?”, in one sentence, with no “and” in it. Every measure in that table must be a property of exactly that thing.

order_total at item grain is a category error — like putting a person’s age in a row that means “one household”. It compiles, it runs, it stays wrong forever, because nothing in the schema announces the grain. So write it down: the grain belongs in a comment on line one of the fact table.

A transactional database is normalised so a write touches one row and can’t contradict itself. That is right for taking an order and wrong for asking a question, because the answer now lives across six tables and the analyst has to reconstruct the relationships from memory — including which ones are one-to-many.

Dimensional modelling builds one table for reading:

• A fact table — long, narrow, one declared grain, holding the numbers you add up (measures) and nothing else but keys.
• Dimension tables around it — short, wide, one row per real-world thing, holding the words you slice by.

Every join runs from many facts to one dimension row. In a star, a join that fans out is not discouraged — it is structurally impossible.

-- GRAIN: one row per item sold. gmv_eur is additive over every dimension.
SELECT b.brand_name, sum(f.gmv_eur) AS gmv
FROM   fct_sale_line f
JOIN   dim_brand     b ON b.brand_key = f.brand_key
WHERE  f.date_key BETWEEN DATE '2026-04-01' AND DATE '2026-06-30'
GROUP BY 1;
--   16,062 rows in, 16,062 rows out, €10,218,789.89. Every time.

What the star does with the missing data

Analyst C’s undercount is the failure that survives code review: an ordinary INNER JOIN from items to brands, no fan-out, row count went down from 16,062 to 15,118. In the marketplace, 944 items were listed under a brand not yet in the brand table — new arrivals waiting on a data-entry queue. The join silently dropped every one and €606,483.40 with them.

The star’s answer is a rule, not a better join: a fact row always gets a dimension key, so the dimension carries a row for “unknown” (key −1).

The fifth-largest “brand” is a backlog in an operations queue. In the normalised query it was invisible; in the star it is a line item that adds up to the company total, so somebody will ask about it.

If a bad join multiplied everything by a constant you could shrug. It doesn’t. High-value orders get split across a voucher and a card; cheap ones are paid in one go. The number of payment rows correlates with basket value, so the fan-out lands hardest exactly where the money already is.

The contemporary and mass half of the catalogue goes from 8.6% of GMV to 5.2% — it loses two-fifths of its apparent size. A merchandising decision made on the second column is a decision to under-invest in the part of the business the join happened to under-count.

And look at the two dead heats. COS and Acne are separated by €1,140 in the true numbers — a tie. After the fan-out they are 0.26 points apart and Acne is comfortably ahead. Levi’s and Zara are separated by €59; after the fan-out Zara leads by a fifth. The join manufactured a ranking that does not exist, and rankings are what people act on.

Same question over a 2-million-row fact table in DuckDB:

• Normalised, five tables joined: 94–139 ms across runs.
• Star, two tables joined: 24–26 ms, stable.

Three to five times faster for a byte-identical answer. At warehouse scale, where you are billed per byte scanned, the same effect shows up on the invoice. Correctness is still the reason to do it.

1. Declare the grain first, in one sentence. A table with two grains in it is already broken.
2. Measures are additive, semi-additive, or non-additive. GMV adds over everything. A stock level adds over warehouses but not over time. A conversion rate adds over nothing — store numerator and denominator, divide at the end. Getting this wrong is how averages of averages reach a board deck.
3. Never join two fact tables directly. Both are many-sided, so joining them fans out both. Aggregate each to a common dimension separately, then join the summaries. The technique is drill-across.
4. Conform your dimensions. One dim_date, one dim_brand, used by every fact table. The moment two teams keep their own brand list, “revenue by brand” has two answers again.
5. Dimensions are allowed to be redundant. Brand, tier, category, country live flat on the dimension row. Splitting them back into sub-tables gives you a snowflake — tidier, and a step back toward the problem.

1. “What is the grain of this table?” One sentence, no “and”, delivered immediately. If it takes a paragraph, some measure is already being double-counted.
2. “Which joins in this query can fan out?” A good answer names them and says why they can’t here. “None — I checked the row count” is better. No answer means the dashboard number is a coin flip.
3. “Where do rows go when the dimension has no match?” The answer should be a key, not a shrug: “Unknown brand, key −1, with an alert when it exceeds 2% of GMV.” Anything else means the warehouse is quietly losing rows and totals will never tie back to finance.

pip install duckdb. Fully deterministic — your output matches the figures above to the cent.

-- STEP 1 · a quarter of a second-hand marketplace. Every item unique, qty 1.
CREATE OR REPLACE TABLE brands(brand_id INT, brand_name TEXT, tier TEXT);
INSERT INTO brands VALUES
 (1,'Chanel','ultra'),(2,'Louis Vuitton','ultra'),(3,'Gucci','high'),(4,'Prada','high'),
 (5,'Acne Studios','contemporary'),(6,'COS','contemporary'),(7,'Levi''s','mass'),(8,'Zara','mass');

CREATE OR REPLACE TABLE ob AS
SELECT o AS order_id,
       DATE '2026-04-01' + CAST((o*7) % 91 AS INT) AS order_date,
       ['DE','DE','DE','AT','CH','NL','FR'][1 + (o % 7)] AS country,
       CASE WHEN o % 13 = 0 THEN 3 WHEN o % 5 = 0 THEN 2 ELSE 1 END AS n_lines
FROM range(1,12001) t(o);

CREATE OR REPLACE TABLE lines_raw AS
SELECT row_number() OVER (ORDER BY order_id, k) AS order_item_id, order_id, k,
       1 + ((order_id*3 + k*7) % 8) AS brand_id
FROM ob, LATERAL (SELECT unnest(generate_series(1, ob.n_lines)) AS k) s;

CREATE OR REPLACE TABLE order_items AS
SELECT l.order_item_id, l.order_id, l.order_item_id AS item_id,
       round(CASE b.tier WHEN 'ultra' THEN 380 + ((l.order_item_id*37) % 2521)
                         WHEN 'high'  THEN 180 + ((l.order_item_id*37) % 1021)
                         WHEN 'contemporary' THEN 45 + ((l.order_item_id*37) % 276)
                         ELSE 9 + ((l.order_item_id*37) % 52) END
           + ((l.order_item_id*13) % 100)/100.0, 2) AS price_eur
FROM lines_raw l JOIN brands b USING(brand_id);

-- 6% of items are listed under a brand not yet in the brand table
CREATE OR REPLACE TABLE items AS
SELECT l.order_item_id AS item_id,
       CASE WHEN l.order_item_id % 17 = 0 THEN NULL ELSE l.brand_id END AS brand_id,
       CASE WHEN b.tier IN ('ultra','high') THEN 1 ELSE 2 + (l.order_id % 2) END AS hub_id
FROM lines_raw l JOIN brands b USING(brand_id);

CREATE OR REPLACE TABLE orders AS
SELECT ob.order_id, ob.order_date, ob.country, round(sum(oi.price_eur),2) AS order_total
FROM ob JOIN order_items oi USING(order_id) GROUP BY 1,2,3;

-- expensive orders get split across a voucher and a card
CREATE OR REPLACE TABLE payments AS
SELECT row_number() OVER () AS payment_id, order_id FROM (
  SELECT order_id, unnest(generate_series(1,
    CASE WHEN order_total >= 800 THEN 3 WHEN order_total >= 250 THEN 2 ELSE 1 END)) FROM orders);

-- one parcel per warehouse the order draws from
CREATE OR REPLACE TABLE shipments AS
SELECT row_number() OVER () AS shipment_id, order_id, hub_id
FROM (SELECT DISTINCT oi.order_id, i.hub_id FROM order_items oi JOIN items i USING(item_id));

Now be each analyst. Watch the row counts as much as the money:

-- TRUTH ................ 16,062 rows · €10,218,789.89
SELECT count(*), round(sum(price_eur),2) FROM order_items;

-- A · fan-out .......... 23,887 rows · €28,436,802.61   (+178.3%)
SELECT count(*), round(sum(o.order_total),2) FROM orders o JOIN payments p USING(order_id);

-- B · grain mismatch ... 16,062 rows · €16,565,917.51   (+62.1%)  ← row count unchanged!
SELECT count(*), round(sum(o.order_total),2) FROM order_items oi JOIN orders o USING(order_id);

-- C · silent drop ...... 15,118 rows · €9,612,306.49    (−5.9%)
SELECT count(*), round(sum(oi.price_eur),2)
FROM order_items oi JOIN items i USING(item_id) JOIN brands b USING(brand_id);

-- D · fan-out ........... 13,016 rows · €11,807,687.88  (+15.5%)
SELECT count(*), round(sum(o.order_total),2) FROM orders o JOIN shipments s USING(order_id);

Then build the star and prove it can’t be got wrong:

CREATE OR REPLACE TABLE dim_brand AS
  SELECT brand_id AS brand_key, brand_name, tier FROM brands
  UNION ALL SELECT -1, 'Unmapped brand', 'unknown';        -- ← the whole lesson

CREATE OR REPLACE TABLE fct_sale_line AS      -- GRAIN: one row per item sold
  SELECT oi.order_item_id AS sale_line_key, o.order_date AS date_key,
         coalesce(i.brand_id, -1) AS brand_key, o.country AS country_key,
         oi.price_eur AS gmv_eur
  FROM order_items oi JOIN orders o USING(order_id) JOIN items i USING(item_id);

-- 9 rows, totalling €10,218,789.89 — with "Unmapped brand" fifth at €606,483.40
SELECT b.brand_name, round(sum(f.gmv_eur),2) AS gmv
FROM fct_sale_line f JOIN dim_brand b ON b.brand_key = f.brand_key
GROUP BY 1 ORDER BY gmv DESC;

-- DRILL-ACROSS: revenue and parcels together, without joining two facts.
WITH rev AS (SELECT country_key c, sum(gmv_eur) gmv FROM fct_sale_line GROUP BY 1),
     par AS (SELECT o.country c, count(*) parcels
             FROM shipments s JOIN orders o USING(order_id) GROUP BY 1)
SELECT rev.c, round(rev.gmv,2), par.parcels, round(rev.gmv/par.parcels,2) AS gmv_per_parcel
FROM rev JOIN par USING(c) ORDER BY 2 DESC;
-- the gmv column still sums to €10,218,789.89. Join the facts directly and it won't.

The exercise that teaches the most: delete the UNION ALL SELECT -1 line, rebuild, and run the brand report again. Total drops to €9,612,306.49, the report still looks complete and sorted, and nothing anywhere tells you that €606,483 left the building. That is what Analyst C shipped.

Analysts do not get different numbers because they are careless. They get different numbers because a normalised schema makes every question a reconstruction, and reconstructions differ. A star schema is not a performance trick — it is the act of writing the reconstruction down once, correctly, so that the same question can only have one answer.

Vocabulary

Next: Lesson 06 — the dashboard that was silently three days stale, and the four tests that would have caught it.