DWH Interview Question 18 - What is the difference between a star schema and a snowflake schema?

Data Warehouse Interview Q&A · Question 18

In this post we are going to cover the difference between a star schema and a snowflake schema.

Both are ways to arrange a fact table and its dimensions. In a star schema, every dimension is one wide, denormalized table joined directly to the fact table. In a snowflake schema, the dimensions are normalized into sub-tables, for example product, then subcategory, then category, so the model branches out like the arms of a snowflake.

We will build the same product dimension both ways, ask both models the same question, and compare the joins it takes to get the answer.

Question 18 of the Real DWH Question and Answers series on TechBrothersIT.

On this page
  1. The interview answer
  2. Let's create some sample data
  3. Step 1: Star schema - sales by category
  4. Step 2: Snowflake schema - the same question
  5. Star vs snowflake
  6. The galaxy schema
  7. When does a snowflake make sense?
  8. Follow-up questions interviewers ask
  9. The full script

The interview answer

Say this first

A star schema has one fact table joined directly to denormalized dimension tables: one join per dimension. A snowflake schema normalizes the dimensions into sub-tables (product → subcategory → category), so queries need more joins but store less duplicate data. Most warehouses prefer the star for simplicity and query speed.

Let's create some sample data

We build the product dimension twice. For the star, dim_product holds name, subcategory and category on one row. For the snowflake, the same data is split into sf_product, sf_subcategory and sf_category. The fact_sales table is the same for both designs.

Sample data — run this first
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS sf_product;
DROP TABLE IF EXISTS sf_subcategory;
DROP TABLE IF EXISTS sf_category;

-- STAR: one denormalized product dimension
CREATE TABLE dim_product (
  product_key  INT PRIMARY KEY,
  product_name VARCHAR(50),
  subcategory  VARCHAR(50),
  category     VARCHAR(50)
);
INSERT INTO dim_product VALUES
  (1, 'Laptop Stand',   'Stands',   'Accessories'),
  (2, 'USB-C Cable',    'Cables',   'Accessories'),
  (3, 'Wireless Mouse', 'Mice',     'Electronics'),
  (4, 'Desk Lamp',      'Lighting', 'Home');

-- SNOWFLAKE: the same dimension normalized into three tables
CREATE TABLE sf_category (category_key INT PRIMARY KEY, category VARCHAR(50));
CREATE TABLE sf_subcategory (subcategory_key INT PRIMARY KEY, subcategory VARCHAR(50),
                             category_key INT REFERENCES sf_category(category_key));
CREATE TABLE sf_product (product_key INT PRIMARY KEY, product_name VARCHAR(50),
                         subcategory_key INT REFERENCES sf_subcategory(subcategory_key));
INSERT INTO sf_category VALUES (1, 'Accessories'), (2, 'Electronics'), (3, 'Home');
INSERT INTO sf_subcategory VALUES (10, 'Stands', 1), (20, 'Cables', 1), (30, 'Mice', 2), (40, 'Lighting', 3);
INSERT INTO sf_product VALUES (1, 'Laptop Stand', 10), (2, 'USB-C Cable', 20), (3, 'Wireless Mouse', 30), (4, 'Desk Lamp', 40);

-- the fact table is the same for both designs
CREATE TABLE fact_sales (sale_id INT PRIMARY KEY, product_key INT, amount DECIMAL(10,2));
INSERT INTO fact_sales VALUES (1, 1, 35.00), (2, 2, 18.00), (3, 3, 25.00), (4, 4, 30.00), (5, 1, 70.00), (6, 2, 27.00);

Let's look at the star version of the dimension first.

Query
SELECT * FROM dim_product ORDER BY 1;
Result4 rows · 4 columns
product_keyproduct_namesubcategorycategory
1Laptop StandStandsAccessories
2USB-C CableCablesAccessories
3Wireless MouseMiceElectronics
4Desk LampLightingHome

One table, four products. Category and subcategory sit right on the product row, and "Accessories" repeats on two rows.

Query
SELECT * FROM sf_product ORDER BY 1;
Result4 rows · 3 columns
product_keyproduct_namesubcategory_key
1Laptop Stand10
2USB-C Cable20
3Wireless Mouse30
4Desk Lamp40

In the snowflake, the product row only stores a subcategory_key.

Query
SELECT * FROM sf_subcategory ORDER BY 1;
Result4 rows · 3 columns
subcategory_keysubcategorycategory_key
10Stands1
20Cables1
30Mice2
40Lighting3

Each subcategory points to its category through category_key.

Query
SELECT * FROM sf_category ORDER BY 1;
Result3 rows · 2 columns
category_keycategory
1Accessories
2Electronics
3Home

Each category name is now stored once. Less repetition, but the category is two hops away from the product.

Query
SELECT * FROM fact_sales ORDER BY 1;
Result6 rows · 3 columns
sale_idproduct_keyamount
1135.00
2218.00
3325.00
4430.00
5170.00
6227.00

Six sales, each pointing to a product_key. Both designs use this same fact table.

Step 1: Star schema - sales by category

Let's ask for sales by category in the star. Category is on the product dimension, so we need one join.

Query
SELECT p.category, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY p.category
ORDER BY sales DESC;
Result3 rows · 2 columns
categorysales
Accessories150.00
Home30.00
Electronics25.00

Accessories sold 150.00, Home 30.00 and Electronics 25.00. One join, group by, done. That is simple for analysts and simple for BI tools.

Step 2: Snowflake schema - the same question

Now the same question in the snowflake. We go from the fact to product, then to subcategory, then to category.

Query
SELECT c.category, SUM(f.amount) AS sales
FROM fact_sales f
JOIN sf_product p     ON p.product_key = f.product_key
JOIN sf_subcategory s ON s.subcategory_key = p.subcategory_key
JOIN sf_category c    ON c.category_key = s.category_key
GROUP BY c.category
ORDER BY sales DESC;
Result3 rows · 2 columns
categorysales
Accessories150.00
Home30.00
Electronics25.00

Exactly the same answer: 150.00, 30.00 and 25.00. The difference is the work: three joins instead of one, for every query, every dashboard and every user.

Star vs snowflake

StarSnowflake
JOINSOne per dimensionOne per hierarchy level
STORAGESome repetition in dimensionsLess repetition (small saving)
EASE OF USESimple for analysts and BIMore tables to understand
LOADINGFewer tables and keysMore tables and keys to maintain

The snowflake does repeat less data. But dimensions are small compared to facts, and columnar warehouses compress repeated values well, so the saving is usually tiny.

The galaxy schema

Real warehouses have more than one fact table. For example, sales (one row per order line) and inventory (one row per product per day) are two business processes with two different grains. When they share the same date and product dimensions, you can compare sales and stock for the same product on the same day.

That shape is called a galaxy schema, or fact constellation. It is not a different technique, just several stars sharing their dimensions. Those shared dimensions are called conformed dimensions.

When does a snowflake make sense?

  • Large, shared sub-dimensions - for example one geography table reused by customers, stores and suppliers. Kimball calls that an outrigger.
  • Hierarchy already normalized upstream - and flattening it brings no real benefit.

The default interview answer: start with a star, and snowflake only with a clear reason.

Watch out

The snowflake schema has nothing to do with Snowflake the cloud warehouse. Plenty of teams on Snowflake build star schemas.

Follow-up questions interviewers ask

Which is faster?

Usually the star: fewer joins, and modern columnar engines make dimension redundancy cheap.

What is an outrigger?

A secondary dimension referenced by another dimension, such as a shared geography table.

What is a galaxy (fact constellation) schema?

Several fact tables sharing the same conformed dimensions.

The full script

Here is everything from this post in one script. Copy it and run it top to bottom.

Full script — copy and run top to bottom
-- ========================================================================
-- Data Warehouse Interview Q&A #18 - Star schema vs snowflake schema
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example:  CREATE SCHEMA dwh_q18;  SET search_path TO dwh_q18;
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS sf_product;
DROP TABLE IF EXISTS sf_subcategory;
DROP TABLE IF EXISTS sf_category;

-- STAR: one denormalized product dimension
CREATE TABLE dim_product (
  product_key  INT PRIMARY KEY,
  product_name VARCHAR(50),
  subcategory  VARCHAR(50),
  category     VARCHAR(50)
);
INSERT INTO dim_product VALUES
  (1, 'Laptop Stand',   'Stands',   'Accessories'),
  (2, 'USB-C Cable',    'Cables',   'Accessories'),
  (3, 'Wireless Mouse', 'Mice',     'Electronics'),
  (4, 'Desk Lamp',      'Lighting', 'Home');

-- SNOWFLAKE: the same dimension normalized into three tables
CREATE TABLE sf_category (category_key INT PRIMARY KEY, category VARCHAR(50));
CREATE TABLE sf_subcategory (subcategory_key INT PRIMARY KEY, subcategory VARCHAR(50),
                             category_key INT REFERENCES sf_category(category_key));
CREATE TABLE sf_product (product_key INT PRIMARY KEY, product_name VARCHAR(50),
                         subcategory_key INT REFERENCES sf_subcategory(subcategory_key));
INSERT INTO sf_category VALUES (1, 'Accessories'), (2, 'Electronics'), (3, 'Home');
INSERT INTO sf_subcategory VALUES (10, 'Stands', 1), (20, 'Cables', 1), (30, 'Mice', 2), (40, 'Lighting', 3);
INSERT INTO sf_product VALUES (1, 'Laptop Stand', 10), (2, 'USB-C Cable', 20), (3, 'Wireless Mouse', 30), (4, 'Desk Lamp', 40);

-- the fact table is the same for both designs
CREATE TABLE fact_sales (sale_id INT PRIMARY KEY, product_key INT, amount DECIMAL(10,2));
INSERT INTO fact_sales VALUES (1, 1, 35.00), (2, 2, 18.00), (3, 3, 25.00), (4, 4, 30.00), (5, 1, 70.00), (6, 2, 27.00);


-- Step 1: Star schema: sales by category (1 join)
SELECT p.category, SUM(f.amount) AS sales
FROM fact_sales f
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY p.category
ORDER BY sales DESC;

-- Step 2: Snowflake schema: the same question (3 joins)
SELECT c.category, SUM(f.amount) AS sales
FROM fact_sales f
JOIN sf_product p     ON p.product_key = f.product_key
JOIN sf_subcategory s ON s.subcategory_key = p.subcategory_key
JOIN sf_category c    ON c.category_key = s.category_key
GROUP BY c.category
ORDER BY sales DESC;

Key points

  • Star: dimensions denormalized, one join each
  • Snowflake: dimensions normalized into sub-tables, more joins
  • Same answers; the star is simpler and usually faster
  • Snowflake saving is small: dimensions are tiny next to facts
  • Default to a star; galaxy = facts sharing dimensions
Next in this series

Question 19 — What is a surrogate key? What is a surrogate key, and why do warehouses use them?

Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)