Star vs Snowflake Schemas

A fact table holds the numbers of events, such as one row for each order item. A dimension table holds the words that describe them, such as the product name or the country.

A schema is the set of tables in a database and how they link. This chapter compares two shapes for the same shop data: the star and the snowflake.

In a star, the fact table sits in the middle. Flat dimension tables surround it. Flat means that one table holds all the words about one thing.

In a snowflake, the dimensions are split into more tables. This is normalization from the last chapters, applied to the dimensions.

What you will learn

1. Build the shop as a star.
2. Split the same dimensions into a snowflake.
3. Write the same query on both. Count the joins.
4. Compare the good and bad sides, and know when a snowflake is worth it.
5. Share one dimension between two fact tables.

Here is the shop as a star. A fact table sits in the middle. We use the shop ids as keys, to keep the tables small.

fact_order_items has one row for each item on an order. It also holds the customer, copied from the order. Now one fact row reaches both dimensions.

fact_orders has one row for each order. Its measure, the number you add up, is amount. Order 105 has no amount yet, so it shows NULL, which means no value.

Now the dimensions. In a star they are flat: every word about a product or a customer sits in one row. We add a category for each product, and a country name for each customer.

Look at the repeats. The word Writing is stored twice, for Pen and Notebook. The word India is stored twice, for Asha and Omar.

A flat dimension repeats words. That is its cost.

The picture shows the star for fact_order_items. fact_orders works the same way.

PK means primary key: the column that names each row. A foreign key (FK) is a column that holds a key from another table. Each FK here points at one dimension.

Units for each category

A star answers this question with one join. Join the fact to dim_product, then group by category:

SELECT p.category, SUM(f.quantity) AS units
FROM fact_order_items AS f
JOIN dim_product      AS p ON p.product_id = f.product_id
GROUP BY p.category
ORDER BY units DESC;

The query counts every item, including the refunded order 103. All seven items add up to 6 + 3 + 1 = 10 units.

Paid revenue for each country

Revenue sits in fact_orders. It needs one join to dim_customer, and a filter on paid orders:

SELECT c.country_name, SUM(o.amount) AS revenue
FROM fact_orders  AS o
JOIN dim_customer AS c ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.country_name
ORDER BY revenue DESC;

Ben has no row, because his only order, 103, is refunded. Omar has no row, because he has no orders.

A snowflake splits a dimension into more tables. Each repeated word moves to its own small table, and the old table keeps a short key.

We add two small tables. dim_category holds each category once. dim_country holds each country once.

The product and customer tables now keep only the key of the small table. The fact tables do not change.

Compare the picture with the star in the last section. The fact table is the same. Each dimension now has a link to one more table.

The same query, one more join

Ask the same question as before: units sold for each category. The category name is now one table further away:

SELECT c.category_name, SUM(f.quantity) AS units
FROM fact_order_items AS f
JOIN dim_product      AS p ON p.product_id  = f.product_id
JOIN dim_category     AS c ON c.category_id = p.category_id
GROUP BY c.category_name
ORDER BY units DESC;

The data is the same, so the answer is the same. The only change is the extra JOIN line. Every dimension you snowflake adds one more.

Now the revenue question on the snowflake. The country name is two joins away from the fact: fact_orders, then dim_customer, then dim_country.

Dev has no country code. So the last join must be a LEFT JOIN, which keeps rows that find no match:

SELECT k.country_name, SUM(o.amount) AS revenue
FROM fact_orders  AS o
JOIN dim_customer AS c ON c.customer_id  = o.customer_id
LEFT JOIN dim_country AS k ON k.country_code = c.country_code
WHERE o.status = 'paid'
GROUP BY k.country_name
ORDER BY revenue DESC;

Now see what happens with a plain JOIN on the last line. The code is the same except for the word LEFT:

SELECT k.country_name, SUM(o.amount) AS revenue
FROM fact_orders  AS o
JOIN dim_customer AS c ON c.customer_id  = o.customer_id
JOIN dim_country  AS k ON k.country_code = c.country_code
WHERE o.status = 'paid'
GROUP BY k.country_name
ORDER BY revenue DESC;

The total is now 80.50 instead of 160.50. No error appears. In a star this cannot happen here, because the country sits in the same row as the customer.

Both shapes hold the same data and give the same answers. They differ in two things: the number of joins, and the number of places a word is stored.

BI tools are the tools that draw charts and dashboards. They work best when each dimension is one table.

Rename a category

The shop renames Writing to Stationery. In the star, you must change every product row that says Writing:

UPDATE dim_product
SET category = 'Stationery'
WHERE category = 'Writing';

In the snowflake, the name lives in one row:

UPDATE dim_category
SET category_name = 'Stationery'
WHERE category_id = 1;

Two rows is easy. A big product list has many rows for each category. If a change misses some, one category shows up as two groups in every report.

What about speed?

Modern warehouses run joins well, so one extra join is rarely a speed problem. The cost you feel is work.

There are more tables to learn and more joins to write. There are also more chances to lose rows, as Dev showed.

Dimensions are short next to fact tables, so repeated words use little space. That is why many teams accept the repeat.

Start with a star. Snowflake a part only when it passes these three tests:

1. Large: the group of categories has many rows, so repeating its words costs real work.
2. Shared: several dimensions use it. For example, dim_customer and a later dim_store both need country names.
3. Changing: it changes now and then, and you want one place to fix it. The next chapter covers such slowly changing values.

In our shop, a category has only three values. It is small and it rarely changes. A star is the better choice.

If you do snowflake, do it for one dimension only. Our demo splits two only to show the pattern.

A small table linked to one dimension is called an outrigger. dim_category is an outrigger of dim_product. The other dimensions stay flat.

Have both

You can keep the snowflake as the source and give people who write reports a flat view. A view is a saved query that looks like a table:

CREATE VIEW dim_product_flat AS
SELECT p.product_id, p.name, c.category_name AS category
FROM dim_product  AS p
JOIN dim_category AS c ON c.category_id = p.category_id;

SELECT * FROM dim_product_flat
ORDER BY product_id;

The modern default

Many teams build a star. Simplicity wins: fewer joins, fewer mistakes, and an easier model for new teammates.

The shop has two fact tables: fact_orders and fact_order_items. Both point at dim_customer. The picture shows the two stars sharing one dimension:

Several stars that share dimensions are called a fact constellation. A shared dimension is a conformed dimension: every fact uses the same keys and the same words.

Because of this, two reports can use the same country labels. Here are units for each country, from fact_order_items:

SELECT c.country_name, SUM(f.quantity) AS units
FROM fact_order_items AS f
JOIN dim_customer     AS c ON c.customer_id = f.customer_id
GROUP BY c.country_name
ORDER BY units DESC;

The revenue query in the star section used the same country names. So India reads as 4 units and 65.00 paid revenue, side by side.

Each exercise opens an ERD canvas. You draw the tables, and the review checks them against sample rows.

Start at the top. The last one is harder.