Star vs Snowflake Schemas

Level 4: Level 4: Data Engineering with SQLmedium22 minstar schemasnowflake schemanormalizationdenormalizationdimension modelingsurrogate keysmulti-join aggregation

Denormalize a wide star dimension into a snowflake sub-dimension, then pay the extra join to query it.

Why warehouses argue about this on day one

You loaded a dim_product table last sprint, and it carries category_name and category_manager right on the product row. "Revenue by manager" is then a single join from fact_sales to dim_product. Now a teammate asks you to store each manager once instead of repeating it across 40,000 product rows. That one request is the entire star vs snowflake debate, and interviewers open with it because it reveals whether you understand the read-versus-normalize trade-off that shapes every warehouse.

The mental model

A star schema keeps each dimension wide and denormalized: one dim_product row holds every descriptive attribute, including category_name and category_manager, even though thousands of products repeat the same category. A snowflake schema normalizes that dimension into sub-dimensions: dim_product drops the repeated category columns and instead stores a category_key pointing at a separate dim_category(category_key, category_name, category_manager). The category attributes now live in exactly one place.

The trade is precise. Snowflaking removes redundancy and gives you one source of truth for the shared sub-attribute, so renaming a manager updates one row instead of thousands. The cost is that every query pays an extra join. Star is the BI default: fewer joins, simpler SQL, and columnar engines make the wide dimension cheap to scan, so the redundancy barely hurts. Snowflaking earns its keep only when a sub-hierarchy is large and shared across several dimensions or facts, so normalizing it once beats copying it everywhere.

A worked example

Given a snowflake (fact_sales to dim_product to dim_category), revenue by manager needs both joins:

SELECT c.category_manager, SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_product  p ON p.product_key  = f.product_key
JOIN dim_category c ON c.category_key = p.category_key
GROUP BY c.category_manager;

With Priya managing Audio and Marco managing Video, this returns Marco 600 and Priya 300. In a star, category_manager already sits on dim_product, so you drop the second join entirely and get the same answer with one fewer hop. The seeded tables, this query, and its live result render below.

A pitfall interns hit

Forgetting the second join returns wrong groupings. Group by category_key instead of category_manager and a manager who owns two categories splits into two rows that no longer reconcile to a single total. The opposite failure is over-snowflaking: a sub-dimension for every attribute turns routine reports into join marathons that are slower to write and slower to run.

Interview nuance: star vs snowflake is a read-simplicity and performance trade-off against normalization, and most warehouses ship star or "starflake" because BI tools slice a denormalized dimension with a single join.

Sample data for this example

CREATE TABLE dim_category (
  category_key     INTEGER PRIMARY KEY,
  category_name    TEXT,
  category_manager TEXT
);
INSERT INTO dim_category VALUES
  (1, 'Audio', 'Priya'),
  (2, 'Video', 'Marco');

CREATE TABLE dim_product (
  product_key  INTEGER PRIMARY KEY,
  sku          TEXT,
  product_name TEXT,
  category_key INTEGER
);
INSERT INTO dim_product VALUES
  (1, 'SKU-EAR', 'Earbuds',    1),
  (2, 'SKU-HDP', 'Headphones', 1),
  (3, 'SKU-MON', 'Monitor',    2),
  (4, 'SKU-CAM', 'Webcam',     2);

CREATE TABLE fact_sales (
  sale_id     INTEGER PRIMARY KEY,
  product_key INTEGER,
  revenue     INTEGER
);
INSERT INTO fact_sales VALUES
  (1, 1, 100),
  (2, 2, 150),
  (3, 1, 50),
  (4, 3, 400),
  (5, 4, 200);

Worked example (SQL)

SELECT
  c.category_manager,
  SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_product  p ON p.product_key  = f.product_key
JOIN dim_category c ON c.category_key = p.category_key
GROUP BY c.category_manager
ORDER BY revenue DESC;

Apply

Your turn

The task this lesson builds to.

Snowflake a denormalized product dimension. dim_product_wide(product_key, sku, product_name, category_name, category_manager) is seeded and repeats the category columns across products. Build dim_category(category_key, category_name, category_manager) from the DISTINCT categories (let category_key auto-assign), then rebuild dim_product(product_key, sku, product_name, category_key) so each product references its category by category_key instead of carrying the repeated columns. Preserve all six products and keep each product_key from the source. Expected: dim_category = 3 rows (one per distinct category), dim_product = 6 rows with zero orphan category_key values. Lead with DELETE FROM both targets (product before category) so the load re-runs cleanly.

4 hints and 5 automated checks are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Write a load that fills revenue_by_manager(category_manager, total_revenue) with total revenue per category_manager. The snowflake fact_sales to dim_product to dim_category is seeded, and category_manager lives only on dim_category, so you must join fact_sales to dim_product (on product_key) to dim_category (on category_key) and GROUP BY c.category_manager. Expected output: two rows, Priya = 720 (she manages two categories) and Marco = 680. Lead with DELETE FROM revenue_by_manager; so the load re-runs cleanly.

3 hints and 5 automated checks are waiting in the workspace.