Notesstorage🌱 seedling
Star Schema
Also: Dimensional Modeling · Dimensional Model · Fact Table · Dimension Table · Snowflake Schema · Kimball Model
A warehouse schema with one huge fact table of events at a fixed grain in the centre, holding foreign keys and numeric measures, surrounded by small, descriptive dimension tables. Normalising the dimensions further gives a snowflake schema.
Why it exists
Analysts slice measures (revenue, quantity, duration) by attributes (product category, store region, quarter). A layout where every attribute is one join from the facts makes those queries simple to write and fast to run.
How it works
- Grain first: decide exactly what one fact row represents, e.g. one item on one receipt.
- Fact table: billions of rows of foreign keys plus measures. Measures accumulate, so wide tables with hundreds of columns are common.
- Dimension tables: who, what, where, when. Small, wide and descriptive (product name, brand, category).
- Queries filter on dimension attributes, join to the facts, and aggregate the measures.
- Snowflake schema: dimensions normalised into sub-dimensions (product → category), trading duplication for extra joins.
Trade-offs
| Star | Snowflake | |
|---|---|---|
| Joins per query | one per dimension | more, one per normalised level |
| Duplication | attribute values repeat in dimensions | less |
| Ease for analysts | high | lower |
In practice
- Dimensional modelling's main advocate, Ralph Kimball, advised avoiding snowflakes: a flattened dimension holds the same information, and dimensions are tiny next to the facts.
- With Column-Oriented Storage, very wide denormalised tables ("one big table") are also common, since unused columns cost nothing to scan.
Pitfalls
- A mixed or undeclared grain: some rows per order, some per item, so sums double-count.
- Normalising dimensions out of OLTP habit (Normalization) and paying for extra joins on every query.
Connections
- Part of
- Storage
- Learn first
- Relational Model
- Contrasts with
- Normalization
Recall
These cards are also in your review deck, scheduled with spaced repetition.