Star schema vs snowflake schema: the same report built both ways

Two data engineers draw different pictures for the same sales warehouse. One has a fact table with four neat tables around it. The other has the same four, plus extra tables hanging off them. Both pictures are correct, and an interviewer will ask you which you would build and why. The answer is a trade-off you can see for yourself, because the same report can be built on both.

This guide builds both on a small dataset, runs one report on each, and then looks at what the extra tables cost and what they protect. The queries ran in DuckDB, and the output under each query is what it printed. The SQL is standard and works in PostgreSQL, Snowflake and BigQuery.

The tables used in every example

A fact table says what happened: 1 row per sale line, with numbers to add up. Dimension tables say who, what and when. Here there are 3 dimensions (product, customer, date) and eight sales. The fact table is the same in both designs. Only the dimensions differ.

CREATE TABLE fact_sales (
  sale_id INTEGER, date_sk INTEGER, customer_sk INTEGER, product_sk INTEGER,
  quantity INTEGER, amount INTEGER
);
INSERT INTO fact_sales VALUES
  (1, 1, 1, 1,  2,  200),
  (2, 1, 2, 3,  1, 2500),
  (3, 2, 3, 2,  5,  250),
  (4, 2, 1, 4,  1,  800),
  (5, 2, 4, 3,  1, 2500),
  (6, 3, 3, 1,  3,  300),
  (7, 3, 2, 4,  2, 1600),
  (8, 3, 4, 2, 10,  500);

CREATE TABLE dim_date (date_sk INTEGER, full_date DATE, month_name VARCHAR);
INSERT INTO dim_date VALUES
  (1, '2026-03-01', 'March'), (2, '2026-03-02', 'March'), (3, '2026-04-01', 'April');

SELECT * FROM fact_sales ORDER BY sale_id;

sale_id | date_sk | customer_sk | product_sk | quantity | amount
--------+---------+-------------+------------+----------+-------
1       | 1       | 1           | 1          | 2        | 200
2       | 1       | 2           | 3          | 1        | 2500
3       | 2       | 3           | 2          | 5        | 250
4       | 2       | 1           | 4          | 1        | 800
5       | 2       | 4           | 3          | 1        | 2500
6       | 3       | 3           | 1          | 3        | 300
7       | 3       | 2           | 4          | 2        | 1600
8       | 3       | 4           | 2          | 10       | 500
(8 rows)

The first question to ask of any fact table is "what is 1 row?". Here it is one product on one sale to one customer on 1 day. That is the grain, and every number in the table (quantity, amount) is true at that grain. Everything else in the design follows from it.

1How does a star schema answer a question?

In a star, each dimension is one flat table that holds every attribute you might want to group by. The product dimension carries its category and brand as plain columns, and the customer dimension carries city and region.

CREATE TABLE dim_product (product_sk INTEGER, product_name VARCHAR, category VARCHAR, department VARCHAR);
INSERT INTO dim_product VALUES
  (1, 'Notebook',   'Stationery',  'Office'),
  (2, 'Pen',        'Stationery',  'Office'),
  (3, 'Headphones', 'Electronics', 'Gadgets'),
  (4, 'Charger',    'Electronics', 'Gadgets');

CREATE TABLE dim_customer (customer_sk INTEGER, name VARCHAR, city VARCHAR, region VARCHAR);
INSERT INTO dim_customer VALUES
  (1, 'Asha',  'Pune',       'West'),
  (2, 'Ravi',  'Mumbai',     'West'),
  (3, 'Meena', 'Chennai',    'South'),
  (4, 'Kiran', 'Coimbatore', 'South');

The report: revenue by product category and customer region. One join per dimension you use, straight from the fact table:

SELECT p.category, c.region, SUM(f.amount) AS revenue
FROM fact_sales f
JOIN dim_product  p ON p.product_sk  = f.product_sk
JOIN dim_customer c ON c.customer_sk = f.customer_sk
GROUP BY p.category, c.region
ORDER BY p.category, c.region;

category    | region | revenue
------------+--------+--------
Electronics | South  | 2500
Electronics | West   | 4900
Stationery  | South  | 1050
Stationery  | West   | 200
(4 rows)

2 joins, and every join starts at the fact table, which is what gives the shape its name: the fact in the middle, the dimensions around it like points of a star. Notice that category and region are plain columns of the tables you joined. There is nothing to look up in a further table.

2What changes in a snowflake schema?

A snowflake normalises the dimensions. A repeated attribute moves into its own table, and the dimension keeps only a key that points to it. Here the category moves out of the product dimension, and the region moves out of the customer dimension (the city and its region form their own table).

CREATE TABLE sf_category (category_sk INTEGER, category VARCHAR, department VARCHAR);
INSERT INTO sf_category VALUES (1, 'Stationery', 'Office'), (2, 'Electronics', 'Gadgets');

CREATE TABLE sf_product (product_sk INTEGER, product_name VARCHAR, category_sk INTEGER);
INSERT INTO sf_product VALUES (1, 'Notebook', 1), (2, 'Pen', 1), (3, 'Headphones', 2), (4, 'Charger', 2);

CREATE TABLE sf_city (city_sk INTEGER, city VARCHAR, region VARCHAR);
INSERT INTO sf_city VALUES (1, 'Pune', 'West'), (2, 'Mumbai', 'West'), (3, 'Chennai', 'South'), (4, 'Coimbatore', 'South');

CREATE TABLE sf_customer (customer_sk INTEGER, name VARCHAR, city_sk INTEGER);
INSERT INTO sf_customer VALUES (1, 'Asha', 1), (2, 'Ravi', 2), (3, 'Meena', 3), (4, 'Kiran', 4);

The same report now has to walk one extra step on each branch:

SELECT cat.category, ci.region, SUM(f.amount) AS revenue
FROM fact_sales f
JOIN sf_product  p   ON p.product_sk   = f.product_sk
JOIN sf_category cat ON cat.category_sk = p.category_sk
JOIN sf_customer c   ON c.customer_sk  = f.customer_sk
JOIN sf_city     ci  ON ci.city_sk     = c.city_sk
GROUP BY cat.category, ci.region
ORDER BY cat.category, ci.region;

category    | region | revenue
------------+--------+--------
Electronics | South  | 2500
Electronics | West   | 4900
Stationery  | South  | 1050
Stationery  | West   | 200
(4 rows)

4 joins instead of two, and the same four numbers. To check that they are the same and not just similar, subtract one result from the other in both directions. Both should come back empty:

WITH star AS (
  SELECT p.category, c.region, SUM(f.amount) AS revenue
  FROM fact_sales f
  JOIN dim_product p ON p.product_sk = f.product_sk
  JOIN dim_customer c ON c.customer_sk = f.customer_sk
  GROUP BY p.category, c.region
),
snow AS (
  SELECT cat.category, ci.region, SUM(f.amount) AS revenue
  FROM fact_sales f
  JOIN sf_product p ON p.product_sk = f.product_sk
  JOIN sf_category cat ON cat.category_sk = p.category_sk
  JOIN sf_customer c ON c.customer_sk = f.customer_sk
  JOIN sf_city ci ON ci.city_sk = c.city_sk
  GROUP BY cat.category, ci.region
)
SELECT * FROM (SELECT * FROM star EXCEPT SELECT * FROM snow)
UNION ALL
SELECT * FROM (SELECT * FROM snow EXCEPT SELECT * FROM star);

category | region | revenue
---------+--------+--------
(0 rows)

No rows differ, so the designs answer the question identically. The choice is not about correctness. It is about what each design costs to query and to maintain.

3What does the star's repetition cost you?

Look back at dim_product: the word Stationery appears twice and Electronics twice. In the snowflake each appears once, in sf_category. That repetition is the price of the star, and it hurts when a value changes. Suppose the business renames Electronics to Consumer electronics. On a copy of the star dimension, a rushed update that fixes only the row it was told about looks like this:

CREATE TABLE dim_product_copy AS SELECT * FROM dim_product;

UPDATE dim_product_copy SET category = 'Consumer electronics' WHERE product_name = 'Headphones';

Now group the report by category and look at what the finance team would see:

SELECT p.category, SUM(f.amount) AS revenue
FROM fact_sales f
JOIN dim_product_copy p ON p.product_sk = f.product_sk
GROUP BY p.category
ORDER BY p.category;

category             | revenue
---------------------+--------
Consumer electronics | 5000
Electronics          | 2400
Stationery           | 1250
(3 rows)

One category has become two, because Charger still says Electronics. Nothing failed. The totals add up, and the dashboard shows two electronics lines. This is called an update anomaly, and it is the reason normalisation exists. In the snowflake the same rename is a single row in sf_category, so it cannot be half done:

UPDATE sf_category SET category = 'Consumer electronics' WHERE category = 'Electronics';

SELECT cat.category, SUM(f.amount) AS revenue
FROM fact_sales f
JOIN sf_product p ON p.product_sk = f.product_sk
JOIN sf_category cat ON cat.category_sk = p.category_sk
GROUP BY cat.category
ORDER BY cat.category;

category             | revenue
---------------------+--------
Consumer electronics | 7400
Stationery           | 1250
(2 rows)

One update, every product follows. That safety is the real argument for a snowflake. In practice, the star's rename is a controlled load step that rewrites the whole dimension at once, which is why this risk is small in a well-built pipeline and large in a hand-edited table.

4Which one should you choose?

On storage, be careful with the claim in interviews. Columnar warehouses compress repeated text very well, so the space a snowflake saves is usually small today. Query simplicity and maintenance are better reasons to talk about.

A sound default is a star schema, because reports are written far more often than dimensions are changed, and every join you remove is one an analyst cannot get wrong. Snowflake a dimension when you can name the reason: a sub-dimension shared by several dimensions (a geography table used by both customers and stores), a very large dimension where repeated text matters, or a hierarchy that must be edited in one place and in no other.

5What do interviewers ask after this?

Try the grain mistake yourself in Fix a join that doubles revenue, and read the data modeling questions for the theory. For a full path from joins to warehouse design, start with the SQL & Analytical Warehousing track.