Star Schemas and Grain

Scenario

Example company: an analyst needs revenue by product category. Order totals live in an order_totals table (one row per order), and categories come from order_items (one row per product in an order). The analyst joins them and sums the order total.

What you see in production

• Category revenue adds up to more than total company revenue.
• The error is worst in months with large multi-item orders.
• Two dashboards built by different people disagree, and both look plausible.

The naive code

SELECT c.name AS category, SUM(t.total) AS revenue          -- order-level number...
FROM order_totals t
JOIN order_items i ON i.order_id = t.order_id               -- ...joined to many lines per order
JOIN products p ON p.product_id = i.product_id
JOIN categories c ON c.category_id = p.category_id
GROUP BY c.name;

Reproduce it

Using the sample shop database: the order total is repeated once per order line after the join (fan-out), so summing it counts each order several times. The fix computes revenue at the line grain.

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total FROM order_items GROUP BY order_id
)
SELECT 'true company revenue' AS measure, ROUND(SUM(total), 2) AS value FROM order_totals
UNION ALL
SELECT 'naive: order total joined to lines', ROUND(SUM(t.total), 2)
FROM order_totals t JOIN order_items i ON i.order_id = t.order_id
UNION ALL
SELECT 'correct: line amounts', ROUND(SUM(i.quantity * i.unit_price), 2)
FROM order_items i;

measure | value
true company revenue | 1869.8
naive: order total joined to lines | 2985.14
correct: line amounts | 1869.8

Root cause

Every table has a grain: what one row represents. order_totals has one row per order; order_items has one row per order line. Joining a coarser table to a finer one repeats the coarse values (fan-out), and summing them double-counts.

The fix

Use dimensional modelling (Ralph Kimball's star schema):

• Fact tables hold measurable events at a declared grain: fct_order_lines (one row per order line) with quantity, amount, and keys to dimensions.
• Dimension tables describe the context: dim_product, dim_customer, dim_date, one row per entity.
• Facts join to dimensions many-to-one, so joins never multiply fact rows.
• Write the grain in the table's description and enforce it with a uniqueness test on the key.
• Don't sum a measure that lives at a coarser grain than the rows you are grouping. Either allocate it down to the finer grain (for example, shipping cost split across lines) or aggregate at its own grain.

Trade-offs

• Star schemas duplicate some data (denormalised dimensions), in exchange for simple, fast, correct queries.
• Wide denormalised "one big table" models are popular in columnar warehouses and are fine too, if their grain is explicit.
• Allocating order-level amounts to lines needs a business rule (by value, by quantity) that finance must agree on.

Detect it in production

• Uniqueness tests on every table's declared grain.
• Reconciliation tests: the sum of a measure across any breakdown must equal the total (category revenue = company revenue).

Key points

• Every table needs a declared grain (what one row means).
• Joining coarse to fine data fans out rows; summing then double-counts.
• Model facts at a fine grain and dimensions many-to-one; test grain and totals.