❄️ Warehouse & Modeling · Chapter 3

Star vs snowflake schema

Same facts, same dimensions — the only question is whether a dimension is one flat table or a chain of little ones. The answer decides how many joins every dashboard pays for.

Study this lesson free in the app →

The basics

A star schema is a fact table joined directly to flat, denormalised dimension tables — draw it and it looks like a star. A snowflake schema normalises those dimensions further, so a dimension points to sub-dimensions (driver → vehicle → vehicle model; zone → city → state), and the diagram branches like a snowflake.

Why it matters: both store the same information. The choice trades storage and update simplicity (snowflake: each fact stored once) against query simplicity and speed (star: one join per dimension). Every BI tool, every analyst, every dashboard query lives with that choice daily.

The mental model: star = one hop from the fact to any attribute. Snowflake = several hops, each hop another join.

Do not confuse the snowflake schema (a modelling shape from the 1990s) with Snowflake the product. You can build either shape in either database.

Explained

Meghalaya Cabs' analyst wants revenue by state.

In the star schema: fact_rides → dim_zone (state is a column). 1 join.

In the snowflake schema: fact_rides → dim_zone → dim_city → dim_district → dim_state. 4 joins, and one of them is an inner join on a city that was never mapped to a district — so ₹3.2 lakh of Tura revenue silently vanishes from the report.

The wedding seating chart

A wedding planner keeps a guest list.

Star version: one card per guest — name, table number, side (bride/groom), family, home town. The caterer reads one card and knows everything.

Snowflake version: the guest card says family #17. Family #17 is on another sheet, which says home town #4. Home town #4 is on a third sheet. Writing "Shillong" once instead of forty times saves ink.

But the caterer, at 9 pm with 300 guests, has to flip three sheets per plate. And if sheet three goes missing, a whole family has no seat.

That is the trade: write once vs read in one look.

The two shapes side by side

STAR                                SNOWFLAKE

 dim_date   dim_driver               dim_date   dim_driver ── dim_vehicle ── dim_model
      \       /                           \       /
      fact_rides                          fact_rides
      /       \                           /       \
 dim_rider   dim_zone                 dim_rider   dim_zone ── dim_city ── dim_state
             zone, city,
             state, region

revenue by state:                   revenue by state:
  fact → dim_zone      (1 join)       fact → zone → city → state  (3 joins)

The facts are identical. Only the dimensions changed shape.

What actually differs

                     STAR                        SNOWFLAKE
joins per attribute  1                           1 per hierarchy level
dim storage          repeated text (compressed)  each value once
rename a state       update every zone row       update one row
analyst SQL          short, hard to get wrong    longer, easy to drop rows
BI semantic layer    maps 1:1                    extra relationships
optimiser            1 small hash join per dim   more joins, more estimate error

In a columnar store, state = 'Meghalaya' repeated across 4,000 zone rows is dictionary/run-length encoded down to almost nothing. The storage win of snowflaking is close to zero — and dimensions are tiny next to facts anyway.

The same question, both ways

-- STAR: state lives on dim_zone
SELECT z.state, SUM(f.fare_inr) AS revenue_inr
FROM   fact_rides f
JOIN   dim_zone z ON z.zone_key = f.pickup_zone_key
GROUP  BY z.state;

-- SNOWFLAKE: walk the hierarchy
SELECT s.state_name, SUM(f.fare_inr) AS revenue_inr
FROM   fact_rides f
JOIN   dim_zone  z ON z.zone_key  = f.pickup_zone_key
JOIN   dim_city  c ON c.city_key  = z.city_key
JOIN   dim_state s ON s.state_key = c.state_key
GROUP  BY s.state_name;

-- Building the star dim FROM the normalised staging tables
CREATE OR REPLACE TABLE dim_zone AS
SELECT z.zone_key, z.zone_name, c.city_name AS city,
       s.state_name AS state, s.region
FROM   stg_zone z
LEFT JOIN stg_city  c ON c.city_id  = z.city_id
LEFT JOIN stg_state s ON s.state_id = c.state_id;

The LEFT JOINs are deliberate: flatten once, carefully, in the pipeline — so nobody writes a lossy inner join at query time.

Where each shape belongs

Normalisation is not wrong — it is just in the wrong place when it reaches the analyst.

When snowflaking earns its keep

Default to star. Snowflake a piece only when one of these is true:

• A huge dimension with a heavy, rarely used sub-entity. 40M riders, and a 60-column corporate-account profile that applies to 2% of them. Splitting it out keeps the rider dim lean
• One attribute set shared by several dimensions. dim_geography referenced by rider home zone, driver base zone and pickup zone — an outrigger keeps three copies consistent
• Very frequently changing sub-attributes. Vehicle insurance status changes weekly; putting it on the driver row with history (SCD2) would explode the driver dim

At Meghalaya Cabs' scale (80k drivers, 4k zones) none of these apply. A star is faster to query, simpler to explain and cheaper to maintain.

Gotchas

• Inner joins down a hierarchy — any unmapped city drops its rides from every report. Flatten with LEFT JOIN and an 'Unknown' member
• Snowflaking to 'save space' in a columnar warehouse — saves almost nothing, costs every query
• Confusing the schema with the product — "we're on Snowflake so we use a snowflake schema" is a red flag in an interview
• Flattening without history thinking — if a zone moves to a new city boundary, decide whether old rides should show the old city (SCD2) before you flatten
• Exposing staging tables to BI — analysts will happily build their own (wrong) snowflake on raw normalised tables

Words to know

In two minutes

• Star: fact table plus flat dimensions, one join per dimension; snowflake: dimensions normalised into chains of sub-tables.
• Same information either way — the difference is how many joins a query needs and how many places an attribute lives.
• Columnar warehouses compress repeated text extremely well, which removed the storage argument that once favoured snowflaking.
• Star schemas are what BI tools and analysts expect: simpler SQL, fewer wrong joins, better optimiser estimates.
• Snowflake a dimension only for a real reason: a huge dimension with a big, rarely used sub-entity, an attribute set shared by several dimensions, or a source-aligned staging layer.
• Default: star schema in the presentation/mart layer; normalised shapes are fine upstream in staging.
• Snowflake schema has nothing to do with the Snowflake database product.

Test yourself

There are 8 questions, 3 puzzles and 5 recall cards for this lesson in the app — the recall cards come back on later days, just before you would forget.