Warehouse Grain
What one row in your table actually means
Grain is the level of detail a table or query represents — per order, per day, per user. Getting grain wrong is the single most common cause of wrong BI numbers.
What you will learn
- State and verify the grain of any fact table
- Trace how grain changes between model layers
- Avoid double-counting from grain mismatches
What is this?
Grain names the business event one row represents — 'one row per order', 'one row per order line', 'one row per customer per day'. Every aggregation, join and sum presupposes a grain. When the grain a report assumes and the grain a table holds disagree, the numbers silently wrong-multiply or under-count.
Why do you need it?
- Most 'the dashboard is wrong' bugs are grain bugs, not math bugs.
- Grain is the contract between the model builder and the report reader.
- Grain-aware joins prevent fan traps (covered in SQL).
- Matching grain to the decision determines how fine and how expensive the model is.
Mental model
Counting bricks, walls or houses
Grain
The same construction site yields wildly different counts depending on what a 'row' is. Reports must declare whether they sum bricks or houses — otherwise two people 'counting the site' will never agree.
How it works
- Atomic grain is the finest event (one order, one event); aggregates roll it up to day/user/region.
- The grain key is the set of columns whose combination should be unique.
- Model layers store grain explicitly; tests assert uniqueness on the grain key.
- Joins succeed only when the many side's grain is pre-aggregated to the target's level.
- Sums are only composable within the same grain; changing grain breaks the semantics of a total.
Same events at three grains.
In production
- A fact table declares 'one row per order line'; summing line amounts gives order value, summing orders' value must use SUM of aggregated orders — not re-joining lines.
- A daily active users table is grain (day, user); a report rolling it to weekly must not sum the daily unique counts as if rows were additive.
- A weekly marketing snapshot at grain (campaign, week) will not match a per-click table unless both are joined at the click→campaign-week rollup.
Best practices
- Write the grain at the top of every model's definition and every report's SQL.
- Add a uniqueness test on the grain key in CI.
- Aggregate to the target grain before joining, not the other way round.
- Document grain changes between layers (atomic → daily) in the model docs.
Common mistakes
- Summing a table at row grain and calling it the customer-level total.
- Joining a daily table to a monthly table without rolling to the shared grain.
- Redefining grain in the report so totals depend on report filters.
- Mixing additive (amount) and non-additive (distinct users) measures in one sum.
Common issues
Revenue is 4x finance's numberJoined order-level facts at line level, summing repeatedly
Pre-aggregate to the target grain and confirm the key is unique.
Orders integer, but sums look fractionalA per-click table's grain mixed into an order rollup
Declare the row-grain of every input and roll up each to the report grain.
Debugging tips
- Wrong totals? Start by asking 'what does one row mean?' of each source table.
- SUM vs COUNT sanity: if revenue/count ≈ 0.0001, suspect a grain join.
- Verify uniqueness: SELECT key, COUNT(*) HAVING COUNT(*)>1 isolates a broken grain.
Interview checkpoint
Hands-on lab
- State the grain of three fact-like tables
- Find a grain mismatch that corrupts totals
- Roll a table up to a shared grain and reconcile
Detect and Fix Grain Mismatches
35 min · intermediate
Quick quiz
A table's grain is best described as:
You can safely SUM a table regardless of its grain as long as you use the same chart.