Fact table grain

The demo below runs the most ordinary query a fact table receives, a request for total revenue. The business took 150 here, since order 101 sold 60 of goods and order 102 sold 90. Before you run the sum, add up the amount column yourself and predict whether the query will report the money the business actually took.

The query did nothing wrong. It read every row at face value and summed them, exactly as written, and it returned 210 against real revenue of 150. Order 101 entered the total twice, once through its two line rows and once through the order-total row that arrived from the second feed.

A mixed grain fails quietly because each row is individually true. The lines are real and the total is real, so no constraint can object, and every aggregate that touches the table inherits the double-count.

You can reproduce the failure with the sum itself.

select sum(amount)::int as total
from sales;

The inflated number looks plausible, and a plausible wrong number survives review. No error exists anywhere in the pipeline, so the discrepancy is usually found by a person reconciling reports long after the figure has been repeated.

Look at which rows caused the damage. The two line rows and the total row each describe order 101 truthfully, and the failure appeared only when one table held both descriptions, because a sum cannot tell which rows share meaning. A fact table therefore needs a single declared answer to the question this guide opened with, before any measure is added to it.

Dimensional modeling calls that answer the grain declaration. Ralph Kimball, whose design method shaped most modern warehouses, made declaring the grain the first step of building any fact table. The declaration comes ahead of choosing dimensions or measures, because every later choice is checked against it, and a measure that is not true at the declared grain has no honest place in the table.

The repaired table gives the opening question a single answer, and the sum from the failure section returns 150 on it. Grain repairs are cheap at design time and expensive afterward, because every downstream report inherits whatever the table declares.

Declaring a grain forces a choice about how fine the rows should be. One row per order line is finer than one row per order, which is finer in turn than one row per day. From line-grain rows you can still compute order totals and daily totals, while a table stored at daily grain can never answer which item sold, because aggregation destroys the detail it rolls up.

The cost of fine grain is volume, since one row per order line can be many times the rows of one row per order. Kimball’s advice remains the default for exactly this trade: store the finest grain the source supports and aggregate on the way out, because storage is usually cheaper than a question you can no longer ask.

Some questions genuinely live at a coarser grain, such as an account balance measured at the end of each day. Those are served by dedicated tables built for that purpose, and they arrive later in this track as snapshot fact types.

• A plausible total hides the damage. A double-counted sum is rarely absurd, and 210 against a true 150 survives a glance. Reconcile a fact table against its source of truth at load time, rather than trusting review to notice.
• Order-level values on line rows multiply. A shipping fee copied onto every line of its order is summed once per line. Allocate such values across the lines or keep them in an order-grain table.
• Counting orders from line grain needs distinct. At one row per order line, count(*) counts lines. Orders are counted with count(distinct order_id), and the difference stays invisible until an order has two lines.
• Averages change meaning with the grain. The average of line amounts differs from the average order value. Decide which average the question asks for, and compute order totals first when it asks for the order one.