Star Schema vs Snowflake Schema (With SQL Examples)

What a star schema looks like

A fact table sits in the center, surrounded by dimension tables — each one flat, denormalized, and joined to the fact table exactly once. Picture a literal star: one hub, several single-hop spokes.

CREATE TABLE dim_product (
  product_key    INT PRIMARY KEY,
  product_name   VARCHAR(100),
  category_name  VARCHAR(50),   -- denormalized directly onto the product row
  subcategory_name VARCHAR(50),
  brand_name     VARCHAR(50)
);

CREATE TABLE fact_sales (
  sale_id        BIGINT PRIMARY KEY,
  product_key    INT REFERENCES dim_product(product_key),
  date_key       INT,
  quantity       INT,
  revenue        DECIMAL(12,2)
);

-- One join reaches every product attribute
SELECT p.category_name, SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY p.category_name;

Category and brand names live directly on every product row. If "Electronics" gets renamed to "Consumer Electronics," every row referencing that category needs updating — that repetition is exactly what the snowflake schema exists to avoid.

What a snowflake schema looks like

The same dimension gets split into normalized sub-tables, each one holding a single level of the hierarchy:

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

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

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

-- Reaching category_name now needs two extra joins through the hierarchy
SELECT c.category_name, SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_product p       ON f.product_key = p.product_key
JOIN dim_subcategory s  ON p.subcategory_key = s.subcategory_key
JOIN dim_category c     ON s.category_key = c.category_key
GROUP BY c.category_name;

Renaming "Electronics" now means updating exactly one row, in dim_category — but every query that needs the category name has to traverse three joined tables instead of one to get it.

Side-by-side comparison

Query performance implications

Every additional join is another opportunity for the query optimizer to choose a suboptimal join order or algorithm, and another pass the engine has to make over data. On a fact table with hundreds of millions of rows, the difference between one dimension join and three chained ones is rarely negligible — this is the core reason star schema remains the default recommendation for BI-facing dimensional models, even though snowflaking is not "wrong."

When to use each

• Use star schema for the large majority of dimensions in a BI-facing warehouse — it's simpler for analysts to query, simpler for BI tools to auto-join, and faster on almost every engine.
• Use snowflake schema selectively, for a dimension with a genuinely deep hierarchy that changes independently at each level and needs strict referential integrity — a multi-level product taxonomy maintained by a separate merchandising team is a common real case.
• Mixed/hybrid schemas — mostly star, with one or two dimensions snowflaked — are the norm in real-world warehouses, not an edge case.

Common mistakes

• Snowflaking every dimension by default out of a normalization habit carried over from OLTP schema design, adding unnecessary joins to nearly every analytical query.
• Leaving a genuinely volatile shared hierarchy fully denormalized in a star schema, creating a bulk-update problem every time a high-level category is renamed or restructured.
• Assuming storage savings from snowflaking matter on a modern columnar cloud warehouse, where compression already minimizes the cost of repeated dimension values.
• Forgetting that BI tools generally assume a star schema shape when auto-generating joins, making a heavily snowflaked model harder to self-serve against.