Star Schema vs Snowflake Schema

Star schema and snowflake schema are the two standard ways to model dimensions in a data warehouse, and the choice between them is really a choice about where you want the complexity to live: in the storage and maintenance of the dimension tables, or in the joins your queries have to run. Neither is "correct" — they're opposite ends of a denormalization trade-off, and most warehouses end up with a mix.

What a star schema actually looks like

A star schema has one fact table (the measurements — order amount, quantity, revenue) surrounded by denormalized dimension tables (customer, product, date). Denormalized means each dimension is flattened into a single wide table, even if some of its attributes are technically hierarchical or repeat across rows.

CREATE TABLE dim_product (
    product_key      INT PRIMARY KEY,
    product_name     VARCHAR(200),
    category_name    VARCHAR(100),   -- flattened in, not a separate table
    subcategory_name VARCHAR(100),   -- flattened in, not a separate table
    brand_name       VARCHAR(100),   -- flattened in, not a separate table
    supplier_name    VARCHAR(200)    -- flattened in, not a separate table
);

CREATE TABLE fact_sales (
    sale_id      BIGINT PRIMARY KEY,
    product_key  INT REFERENCES dim_product(product_key),
    customer_key INT REFERENCES dim_customer(customer_key),
    date_key     INT REFERENCES dim_date(date_key),
    quantity     INT,
    amount       NUMERIC(12,2)
);

-- One join per dimension, no matter how many attributes you pull
SELECT p.category_name, SUM(f.amount) AS total
FROM fact_sales f
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY p.category_name;

Category, subcategory, brand, and supplier all live as plain columns on dim_product, even though in a normalized world they'd be their own tables. That redundancy is the whole point: any query touching product attributes needs exactly one join.

What a snowflake schema normalizes out

A snowflake schema takes those same dimensions and normalizes them into a hierarchy of smaller tables — category and subcategory become their own tables, referenced by foreign key instead of duplicated as text on every product row.

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

CREATE TABLE dim_subcategory (
    subcategory_key INT PRIMARY KEY,
    subcategory_name VARCHAR(100),
    category_key INT REFERENCES dim_category(category_key)
);

CREATE TABLE dim_product (
    product_key      INT PRIMARY KEY,
    product_name     VARCHAR(200),
    subcategory_key  INT REFERENCES dim_subcategory(subcategory_key),
    supplier_key     INT REFERENCES dim_supplier(supplier_key)
);

-- Same rollup now needs three joins instead of one
SELECT c.category_name, SUM(f.amount) AS total
FROM fact_sales f
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_subcategory sc ON p.subcategory_key = sc.subcategory_key
JOIN dim_category c ON sc.category_key = c.category_key
GROUP BY c.category_name;

The actual trade-off: storage vs query cost

Normalizing a dimension removes redundant text (a category name repeated across ten thousand product rows becomes one row in a category table), which shrinks storage and makes updates to category names a single-row change instead of a bulk update across every product. That's the entire case for snowflaking.

The cost is that every query touching those attributes needs more joins, and columnar warehouses (BigQuery, Snowflake the product, Redshift) are specifically optimized for the star pattern — scanning a single wide dimension table is cheap, and join elimination or broadcast joins on small denormalized dimensions are fast. Chaining three or four joins to rebuild a hierarchy that could have been three flat columns adds query planning overhead and makes BI tools (which usually expect a flat star for their semantic layers) harder to configure.

When to actually choose each

Default to a star schema for anything BI tools or analysts query directly — dashboards, self-serve reporting, anything where join simplicity and query speed matter more than storage efficiency. Reach for snowflaking selectively, not wholesale, when a specific dimension has a genuine hierarchy that changes independently of the fact table — a product catalog with categories that get renamed or restructured by a merchandising team, where you want that update to happen in one place, not be reflected across every fact-adjacent product row via a slow bulk update or SCD (slowly changing dimension) process.

Wrapping up

Star vs snowflake isn't a stylistic preference — it's a direct trade between how much you denormalize (fewer joins, more redundant storage, simpler queries) and how much you normalize (more joins, less redundancy, easier maintenance of hierarchies that actually change). Start with a star schema by default because most analytical queries favor fewer joins, and snowflake only the specific dimensions where the normalized structure earns its keep.