Snowflake Schema — Normalized Dimensional Modeling

Snowflake schema extends star schema by normalizing dimension tables. Product category becomes separate table — saves storage but adds JOINs.

Normalized Dimensions¶

Dimension tables are normalized into multiple levels. Diagram resembles snowflake.

CREATE TABLE dim_product (
    product_key  INT PRIMARY KEY,
    product_name VARCHAR(200),
    category_key INT REFERENCES dim_category(category_key)
);

CREATE TABLE dim_category (
    category_key   INT PRIMARY KEY,
    category_name  VARCHAR(100),
    department_key INT REFERENCES dim_department(department_key)
);

CREATE TABLE dim_department (
    department_key  INT PRIMARY KEY,
    department_name VARCHAR(100)
);

Star vs Snowflake¶

• Star — faster queries, fewer JOINs
• Snowflake — less redundancy, large dimensions
• Modern warehouse — storage is cheap, star is usually better

Summary¶

Snowflake schema saves storage by normalizing dimensions. In modern cloud warehouses, star schema is usually preferred.