Dimensional Modeling: Star & Snowflake

Your production database is normalised to within an inch of its life — and rightly so. Every fact lives in exactly one place, updates are cheap, and nothing can contradict itself.

Then an analyst asks for “revenue by region, by product category, by month.” Suddenly that beautiful schema is a twelve-join nightmare: orders, order lines, products, categories, stores, regions, a date lookup — slow to write, slow to run, easy to get wrong. Dimensional modeling is the craft of re-shaping data for questions instead of transactions, and almost every warehouse and dashboard you will meet is built on its two ideas — facts and dimensions.

Two kinds of table

Dimensional modeling — popularised by Ralph Kimball in the 1990s — says every analytical table is one of exactly two types. A fact table records measurements of events: one row per thing that happened (a sale, a click, a shipment). It is tall and thin — millions of rows but few columns: a handful of numeric measures (quantity, amount) plus foreign keys pointing at the dimensions. Facts are where the maths happens; you SUM and COUNT and AVG them. A dimension table holds the descriptive context — the who, what, where, and when. It is short and wide: relatively few rows, but many text columns you filter and group by (product_name, category, brand). Dimensions are where the labels live.

A quick test sorts almost anything: if you would SUM it, it is a measure in a fact; if you would GROUP BY it, it is an attribute in a dimension. Revenue is a fact; the product category you slice it by is a dimension.

Declare the grain first

Before you add a single column, answer one question: what does one row of the fact table mean? That is the grain, and Kimball’s first rule is to declare it before anything else. “One row per order line” is a fine grain; so is “one row per order” or “one row per product per store per day.” But you must pick one and state it, because the grain decides everything downstream — which dimensions can attach, what a COUNT(*) means, and whether your sums can be trusted.

Star versus snowflake

The default design is the star schema: the fact table in the middle, each dimension hanging directly off it by a foreign key — the shape literally looks like a star. Its defining move is denormalisation. A dim_product row carries everything about the product flat and wide — product_name, category, subcategory, brand, department, side by side — even though category and department repeat across thousands of products. That redundancy is deliberate, because it makes “revenue by department” a single join.

A snowflake schema instead normalises those wide dimensions into sub-tables: dim_product points to dim_category, which points to dim_department, branching outward like a snowflake. It is the trade normalisation always makes — less redundancy (each department name stored once) at the cost of more tables and more joins. Now “revenue by department” is no longer one hop but fact ⋈ product ⋈ category ⋈ department, three.

So which wins? Kimball’s guidance, and the modern default, is star — for three reasons. Storage is cheap and joins are expensive: the star’s redundant text costs a little disk, while the snowflake’s extra joins cost query time on every analytical query, the hot path. Columnar engines erase the downside anyway, compressing a repeated department string down to almost nothing. And a star is simply easier for humans and BI tools to get right. Snowflaking earns its keep only in narrow cases — a genuinely enormous dimension where the redundancy is real money. Otherwise, keep it flat.

Surrogate keys

One detail you will see everywhere: a dimension gets a surrogate key — a meaningless auto-incrementing integer (product_key = 42) — and the fact stores that, not the natural business key ("SKU-9981"). Three reasons. Integer joins are faster and smaller than joining on long text. Surrogates insulate the warehouse from the source system renumbering or reusing its ids. And — the big one — they are what let a dimension track history: when a product’s category changes, you keep the old version and the new one as two rows with two surrogate keys while the natural key stays the same. That technique is the whole next lesson.

Practice