What is the Grain of a Fact Table?

How it works

Kimball’s four-step design process puts the grain second: choose the business process, declare the grain, then choose the dimensions, then the facts. Every dimension and every measure must be true at the declared grain, so the grain decides what can go in the table.

Pick the most atomic grain the source provides. Atomic data answers questions nobody has asked yet, because you can always aggregate up but never down. For an online shop that is the order line, not the order and not the day.

Then test each candidate column against the grain. At “one row per order line”, the product and the line’s quantity are true; the order’s shipping fee is not, because it is charged once per order. Such a value goes in a separate order-grain fact table, or is allocated down to the lines so that the line values add back to the order total.

The grain is also a test. If the declared grain is “one row per order line”, then (order_id, line_number) must be unique, and a pipeline check that finds duplicates has found a bug. Many reporting errors come from joins that silently change the grain, for example joining order lines to payments when one order has two payments, which fans out rows and inflates sums.

Example

The mixed-grain bug: a per-order shipping fee copied onto each line is counted once per line. The true total is 99 + 49 = 148.

CREATE TABLE fact_order_line (order_id text, line_number int, net_amount numeric, shipping_fee numeric);
INSERT INTO fact_order_line VALUES
  ('O-1', 1, 4999, 99), ('O-1', 2, 798, 99),
  ('O-2', 1, 1299, 49);

SELECT sum(shipping_fee) AS wrong_total,
       (SELECT sum(fee) FROM (SELECT DISTINCT order_id, shipping_fee AS fee
                              FROM fact_order_line) o) AS right_total
FROM fact_order_line;

 wrong_total | right_total
-------------+-------------
         247 |         148

Common mistakes

• Choosing columns first and discovering the grain later, after the table holds facts at two levels.
• Choosing an aggregated grain (per day) when the source provides events, which blocks future questions.
• Joining to a table at a finer grain without aggregating it first, so rows fan out and totals inflate.
• Not testing the grain columns for uniqueness in the pipeline.