Data modeling · Advanced Modeling

How to decide grain

Mediumgrainfact designatomicbusiness process

Interview question

How do you decide the grain of a fact table?

How to answer

The short answer

Start from the business process and the questions you must answer, then write one sentence: "1 row per ___."

Decision checklist

  1. 1What business process is this? (ordering, shipping, billing)
  2. 2What is the most atomic event we need? (header vs line)
  3. 3Which measures must be additive at that level?
  4. 4Which dimensions attach naturally at that level?
  5. 5Will anyone need lower detail later? Prefer atomic; aggregate in rollups
Output
Process: e-commerce checkout
Questions: revenue by product, by day, by customer
Atomic grain: 1 row per order line
  (header-only grain cannot answer product questions)

Separate process: end-of-day inventory
Grain: 1 row per product per warehouse per day

Anti-patterns

  • Mixing headers and lines in one fact
  • Choosing a coarse grain that blocks future product analytics
  • Choosing ultra-fine click grain inside a revenue mart without need

Protect it

Document the sentence, add a uniqueness test on the grain key, and refuse to "just add a couple header rows."

Interview tip

Say the grain sentence before listing columns. Interviewers treat that as seniority signal.