DimensionalSample
Star schema
One fact table of measurements in the middle, surrounded by denormalised dimension tables that describe them. Every question is one join away.
Try this
- 01Ask “Revenue by region and month” and count the joins: every dimension is exactly one hop from the fact.
- 02Click dim_product: it touches only the fact table. Dimensions never join to each other in a star.
- 03Compare the SQL of the three questions: they all share the same shape (fact, join dimensions, group by attributes).
Notation
- Fact table
- One row per business event at a declared grain (here: one product sold in one store on one day). Holds the numbers you add up.
- Dimension table
- The who, what, where and when. Wide, flat and descriptive, so filters and labels live in one place.
- Surrogate key
- A meaningless integer key (date_key, product_key) that the fact table points at. Protects the model from source system key changes.
- Business key (BK)
- The identifier the business uses (SKU, customer id), kept on the dimension for lookups.
- Crow’s foot
- Many fact rows point to one dimension row.
Use it when
- BI and reporting layers queried by analysts and dashboards
- Stable, well-understood business processes
- Columnar warehouses where wide dimensions are cheap
Avoid it when
- Operational systems that need normalised writes
- Many-to-many relationships everywhere (bridges add complexity)
- The grain of the process is still unclear