Star Schema Basics: Facts, Dimensions and Grain
Design analytics tables that answer business questions quickly and correctly — starting from the grain.
Data ModelingBeginner3 min readUpdated 11 Oct 2026
Start with the grain
The grain is one sentence: what does one row of this fact table represent? Everything else follows from it.
One row in
fact_sales= one order line.
Facts and dimensions
Entity relationship diagramSwipe to explore →Technical diagram
Rendering entity relationship diagram
| Fact table | Dimension table | |
|---|---|---|
| Holds | Measurements (quantity, revenue) | Descriptions (name, category, city) |
| Rows | Many — one per event at the grain | Fewer — one per entity (or version) |
| Keys | Foreign keys to dimensions | Surrogate primary key |
| Changes | Appended / corrected | Updated (Type 1) or versioned (Type 2) |
Design in four steps
- 1
Pick the business process
Orders, payments, shipments — one process per fact table. - 2
Declare the grain
One sentence. If two measures have different grains, they belong in different facts. - 3
Choose the dimensions
Who, what, where, when — anything you filter or group by. - 4
Add the facts
Numeric measures that are true at that grain. Prefer additive measures (revenue) over ratios (margin %).
Finished this note?