Data modeling · Foundations

What is grain?

Mediumgrainmodelingfan-outmetrics

Interview question

What does table grain mean, and why does it matter?

How to answer

The short answer

Grain is the answer to: "What does 1 row represent?"

Examples:

  • 1 row per order
  • 1 row per order line
  • 1 row per customer per day
  • 1 row per page view event

If the grain is unclear, joins and aggregates silently double-count.

Output
fct_orders grain: 1 row per order
join to order_items (1 row per line) without aggregating first
  -> revenue duplicated across lines

How to protect grain

  • Write the grain in one sentence in the model docs
  • Enforce uniqueness tests on the grain key
  • Aggregate to the needed grain before joining facts to facts
  • Never mix two grains in one

Tiny wrong vs right

Wrong: "fct_sales has some header rows and some line rows." Right: "fct_order_headers is 1 row per order; fct_order_items is 1 row per line."

Interview tip

Start every modeling answer with grain. Senior interviewers listen for that habit.