Star vs Snowflake Schemas

Chapter 8 built a star with deliberately flat dimensions. A snowflake is the same data with those dimensions normalized. Turn the dial below and watch what normalizing actually costs — and what it never changes.

The chapter runs on one warehouse throughout: Lumen Cinemas, 62 sites, one row per ticket sold — 144 M fact rows over eight years, 4.6 M of them in the last quarter, against a 41,000-row dim_film. By the end you should be able to look at a dimension attribute and say, with evidence, whether it stays flat or earns its own table.

star schema

Fact surrounded by flat, denormalized dimensions; one join per dimension.

snowflake schema

A star whose dimensions are normalized into linked sub-tables; multiple join hops.

outrigger

A single normalized sub-table hung off one dimension, rather than snowflaking the whole model.

fact constellation

Several fact tables sharing conformed dimensions (a "galaxy" of stars).

select f.studio_group, count(*) as admissions
from fact_ticket_sale t
join dim_film f on f.film_key = t.film_key
group by f.studio_group;