Chapter 1 · Practice 3 of 4
Prefer the most detailed grain the source data supports
Why it matters
Detailed facts can always be rolled up; summarised facts cannot be split back down. Microsoft's fact table guidance notes that while you can store facts at a higher granularity, it is not easy to split measure values to lower levels later, so you trade detailed analysis for size.
Start with the atomic grain, such as each order line, and build aggregate tables on top when queries need speed. Microsoft calls these aggregate fact tables: rollups of a base fact table that exist to accelerate common queries. The base table stays the source of truth.