Star vs Snowflake — When and Why · Data Engineering Quest

The two layouts

Star schema. Each dimension is a single denormalized table. dim_customers contains name, country, region, segment all in one row. The shape on a diagram is a fact table in the middle with dimensions radiating outward — hence "star."
Snowflake schema. Dimensions are normalized further. dim_customers has a region_key; dim_regions has a country_key; dim_countries sits beyond that. The shape is a fact table with dimensions branching into sub-dimensions — hence "snowflake."

The unsexy truth

For analytics work, star wins almost every time. The reasons:

Fewer joins per query → faster execution and clearer SQL.
BI tools generate cleaner queries against star schemas.
Denormalized dimensions are easier to mentally model.
Storage is cheap; the extra space the denormalization costs is negligible compared to the readability win.

Snowflake schemas have a place — when a sub-dimension is huge and changes independently, breaking it out can be the right call. But the default for a new analytics warehouse is a star schema, and you should snowflake only when the specific cost-of-keeping-it-flat justifies it.

Code

Same dimension, two layouts·sql

-- Star: one denormalized customer dimension
CREATE TABLE dim_customers (
    customer_key  INTEGER PRIMARY KEY,
    customer_id   TEXT NOT NULL,
    name          TEXT,
    region_code   TEXT,                         -- 'KR', 'US', 'JP'
    region_name   TEXT,                         -- 'Korea', 'United States', 'Japan'
    continent     TEXT                          -- 'Asia', 'North America'
);

-- Snowflake: customers links to regions, regions links to continents
CREATE TABLE dim_customers_snow (
    customer_key  INTEGER PRIMARY KEY,
    customer_id   TEXT NOT NULL,
    name          TEXT,
    region_key    INTEGER REFERENCES dim_regions(region_key)
);
CREATE TABLE dim_regions (
    region_key    INTEGER PRIMARY KEY,
    region_code   TEXT NOT NULL,
    region_name   TEXT,
    continent_key INTEGER REFERENCES dim_continents(continent_key)
);
CREATE TABLE dim_continents (
    continent_key INTEGER PRIMARY KEY,
    name          TEXT NOT NULL
);

-- Querying revenue by continent:
--   star:  fact JOIN dim_customers              (1 join)
--   snow:  fact JOIN dim_customers_snow JOIN dim_regions JOIN dim_continents (3 joins)

Star vs Snowflake — When and Why

The two layouts

The unsexy truth

Code

External links

Exercise

Progress

Comments 0