Grain: What Does One Row Represent?

• Grain is the unit of observation the table commits to. Every measure in the table must be a quantity of that unit, and every aggregate is only meaningful if the rows being aggregated are distinct observations of it.
• Aggregation is safe when the grain is known and violated when it is not, because SUM has no way to distinguish "two orders of 50" from "one order of 50 that appears twice". Both are two rows of 50 and both sum to 100. The database is correct in every step; the model lied about what a row was.
• A join changes grain whenever the join key is not unique on at least one side. One-to-one preserves grain. Many-to-one — a fact to a properly keyed dimension — preserves the fact's grain, which is exactly why star schemas are safe. Many-to-many multiplies, and that multiplication is arithmetic, not an error condition (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF).
• A GROUP BY also changes grain, deliberately: it produces a new table whose grain is the grouping key. The bug appears when the result of a GROUP BY is joined back to something at the original grain and re-aggregated, which double counts what was already counted (Aggregation: COUNT, SUM, AVG, GROUP BY, HAVING).
• Grain violations are conservative in structure and catastrophic in value. Types stay correct, keys stay valid, no null appears, no constraint fires. Only the magnitude is wrong, and magnitude is exactly the thing nobody can independently verify.
• The reason grain errors survive so long is that they usually inflate rather than deflate. A number that comes in low gets investigated within a day; a number that comes in high gets celebrated, and then gets built into a forecast (Trusting Data).

1-- WRONG: order-level measure summed across a line-level join
2SELECT p.category,
3       SUM(l.revenue)   AS revenue,     -- correct: line-grain measure
4       SUM(o.shipping)  AS shipping     -- WRONG: repeated once per line
5FROM   fct_orders      o
6JOIN   fct_order_lines l ON l.order_id = o.order_id
7JOIN   dim_product     p ON p.product_key = l.product_key
8GROUP  BY 1;
9 
10-- RIGHT: aggregate to a common grain first, then join one-to-one
11WITH lines_by_order AS (
12  SELECT order_id,
13         SUM(revenue) AS line_revenue,
14         COUNT(*)     AS line_count
15  FROM   fct_order_lines
16  GROUP  BY 1                              -- grain is now: one order
17)
18SELECT o.order_id,
19       o.shipping,                         -- counted once, because one row
20       l.line_revenue
21FROM   fct_orders   o
22JOIN   lines_by_order l USING (order_id);  -- one-to-one: grain preserved
23 
24-- RIGHT, the other direction: product mix stays at line grain and
25-- simply does not carry order-level measures at all.
26SELECT p.category, SUM(l.revenue) AS revenue
27FROM   fct_order_lines l
28JOIN   dim_product     p ON p.product_key = l.product_key
29GROUP  BY 1;

orders (order grain)                     order_lines (line grain)
+----------+---------+----------+       +----------+------+----------+---------+
| order_id | revenue | shipping |       | order_id | line | product  | revenue |
+----------+---------+----------+       +----------+------+----------+---------+
| O-1001   |  120.00 |     5.00 |       | O-1001   |    1 | Keyboard |   60.00 |
| O-1002   |   40.00 |     5.00 |       | O-1001   |    2 | Mouse    |   25.00 |
+----------+---------+----------+       | O-1001   |    3 | Cable    |   35.00 |
                                        | O-1002   |    1 | Monitor  |   40.00 |
                                        +----------+------+----------+---------+

SELECT SUM(o.shipping) FROM orders o JOIN order_lines l USING (order_id)

  after the join, one row per LINE, order columns repeated:
+----------+------+----------+----------+
| order_id | line | product  | shipping |
+----------+------+----------+----------+
| O-1001   |    1 | Keyboard |     5.00 |   <- the same 5.00
| O-1001   |    2 | Mouse    |     5.00 |   <- fee, three
| O-1001   |    3 | Cable    |     5.00 |   <- times
| O-1002   |    1 | Monitor  |     5.00 |
+----------+------+----------+----------+

  true total shipping     = 10.00   (2 orders x 5.00)
  SUM(o.shipping)         = 20.00   (4 lines  x 5.00)
  inflation factor        = average lines per order = 2.0

  No error. No null. No type change. No constraint violated.
  Row count went up, which looks like more data arriving.

• Write the grain sentence before writing the SQL, and put it in the model's description where the catalog and the BI tool will show it. "One row per order" and "one row per order line" are different tables and should never be the same table (Dataset Documentation).
• Name the business key that makes the grain unique, and test uniqueness on it every load. The grain declaration and the uniqueness test are the same statement written twice, once for humans and once for the machine (Data Tests).
• Model at the finest grain the source supports and derive coarser tables from it by aggregation. Coarse-to-fine is impossible; fine-to-coarse is a GROUP BY (Fact Tables).
• Never join two fact tables directly. Aggregate one to the other's grain first, in a CTE, and the fan-out cannot happen (Subqueries, CTEs, EXISTS, UNION, CASE).
• Keep order-level measures out of line-level tables. If a consumer insists on having them together, allocate the order-level amount across the lines so that the sum still equals the order total, and document the allocation rule where the column is defined.
• Assert the row count of a transformation against its input where the grain is supposed to be preserved. A fact-to-dimension join that changes the row count has a bug, and this is a one-line test (The Dimensions of Data Quality).

• A declared grain guarantees nothing on its own. It becomes a guarantee only when a uniqueness test on the business key runs on every load and blocks publication on failure (Contract Enforcement).
• A many-to-one join to a dimension with an enforced unique key guarantees the fact's row count is preserved. Analytical engines usually do not enforce that key, so the guarantee is inherited from your test, not from the schema (Database Constraints).
• No engine guarantees that a measure belongs at the table's grain. shipping_fee on a line-grain fact is valid SQL, valid data, and a permanent trap.
• Reconciliation against a source guarantees consistency of a total, not of a grain. A line-grain fact and an order-grain source can reconcile perfectly on summed revenue while disagreeing completely on row counts, which is the point of both checks existing.