Defining the Grain
Concepts covered: Grain Definition
The grain of a fact table states what 1 row means, and it is decided before any column is added. Ridgeline's fact_sales grain reads: 1 row per order line item, unique on (order_id, line_seq). Naming the columns that enforce the unit is what lets another engineer write a test against it.
| Grain statement | Precision | What goes wrong |
|---|---|---|
| 1 row per sale | vague | a sale could be an order, a line item or a payment |
| 1 row per order | a unit, no key | 3 products per order and 1 product_sk column |
| 1 row per order line item, unique on (order_id, line_seq) | testable | nothing a query cannot check |
Two grains, the same 2 orders
Order 50218 holds 3 line items: a tent at 299.99, a sleeping pad at 109.99 and a stove at 79.99. Order 50219 holds 1 headlamp at 44.99.
At the order grain, product_sk for order 50218 has no honest value: 3 products, 1 cell. Whatever the load writes there, a units-by-product report attributes the whole order to 1 product or drops it. SUM(line_amount) is 489.97 at both grains, so revenue agrees with finance and nobody looks further, until merchandising asks for revenue by category and the stove's 79.99 has been reported under tents for months. A wrong grain produces no error message, only numbers that look right.
Mixed grain, the quieter failure
The second grain error puts 2 grains in 1 table. The 12.00 shipping fee on order 50218 is charged once per order, and the first place a developer reaches for is a shipping_fee column on each line row. Now 3 rows carry 12.00 and SUM(shipping_fee) returns 36.00: the query and the column are both correct, and the number is 3 times too large. The fix is to allocate the fee across lines in proportion to line_amount, or to store it in a separate fact at the order grain.
SELECT order_id, line_seq, COUNT(*) AS n
FROM fact_sales
GROUP BY order_id, line_seq
HAVING COUNT(*) > 1;A count above 1 means duplicate source rows or a grain statement that does not match the data, so the test runs after every load and fails the pipeline when it returns rows.
The story below makes the grain choice for fact_sales under the 2 requests that landed in its first quarter.
About this lesson
This section is part of the Star Schemas lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.