Orientation
What You'll Master Here
The fact table holds the numbers and the vast majority of a warehouse’s rows, so designing it well is where dimensional modeling gets serious. This chapter is the craft of fact design: choosing measures that aggregate correctly, picking the right kind of fact table, and handling the special cases that trip people up.
You will learn measure additivity (the rule that decides what you are allowed to SUM), the three fact table types every warehouse uses, and two patterns with intimidating names but simple ideas: factless facts and degenerate dimensions.
Each concept is shown with real tables and the exact aggregation behavior, because fact-design mistakes are the silent, expensive kind: the query runs, returns a number, and the number is subtly wrong. Seeing what is and is not safe to sum is the whole point.
Why it matters
Facts are where measurement and almost all data volume live. A mis-designed fact, wrong grain, wrong additivity, wrong type, produces wrong KPIs at scale and is painful to fix once it has history.
Core mental model
A fact table is keys + measures at one grain. The art is choosing measures that sum correctly and the fact type that matches the process.
- measure
- A numeric value in a fact that you aggregate (amount, quantity, balance).
- additivity
- Across which dimensions a measure can be safely summed (additive / semi / non).
- fact type
- Transaction, periodic snapshot, or accumulating snapshot, the three shapes of fact tables.
- degenerate dimension
- A dimension key (like order number) stored on the fact with no dimension table.
Common mistake
Treating every numeric column as freely summable.
You sum balances over time or sum prices and report nonsense; additivity must be checked per measure.
Better habit
- Classify every measure’s additivity before exposing it.
- Match the fact type to how the process actually behaves.
- Use degenerate dimensions and factless facts where they fit.
Most fact-design bugs are additivity bugs: summing something that should not be summed. Get additivity right and most metric errors disappear.
For each measure you meet, ask "can I add this across time? across product?" That single question organizes the whole chapter.
Practice prompts
- Name a measure that is safe to sum and one that is not.
- State the three fact table types from memory.
Remember this
Fact design is choosing measures that aggregate correctly and the fact type that fits the process; additivity is the concept that prevents most silent metric bugs.