What is a Snowflake Schema?

How it works

In a star schema, dim_product carries category and department as plain columns, repeated for every product. In a snowflake schema those levels move into their own tables: the product row holds a category_key, and the category row holds a department_key. Drawn out, the dimensions branch outward like a snowflake.

That brings some benefits:

• Repeated text is stored once.
• Each level has a natural home for its own attributes, such as a category manager or a department budget code.
• A hierarchy that is maintained in a separate source system can be loaded as it is.

The costs are more joins in every query, a model analysts find harder to navigate, and BI tools that need more configuration. In columnar warehouses the storage saved by snowflaking is small, because repeated values in a column compress well. For that reason most teams keep dimensions denormalised (a star) and snowflake only where a level genuinely has many attributes of its own, or where a hierarchy is shared by several dimensions.

Note that “snowflake schema” is a modelling term and has nothing to do with the Snowflake data warehouse product.

Example

The same question, revenue by department, needs three joins on a snowflaked product dimension instead of one on a star.

CREATE TABLE dim_department (department_key int PRIMARY KEY, department text);
CREATE TABLE dim_category (category_key int PRIMARY KEY, category text, department_key int);
CREATE TABLE dim_product (product_key int PRIMARY KEY, product text, category_key int);
CREATE TABLE fact_sales (product_key int, amount numeric(10,2));

INSERT INTO dim_department VALUES (1, 'Footwear'), (2, 'Kitchen');
INSERT INTO dim_category VALUES (10, 'Running shoes', 1), (20, 'Cookware', 2);
INSERT INTO dim_product VALUES (100, 'Trail runner', 10), (200, 'Iron skillet', 20);
INSERT INTO fact_sales VALUES (100, 49.99), (100, 49.99), (200, 35.00);

SELECT d.department, sum(f.amount) 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
JOIN dim_department d ON d.department_key = c.department_key
GROUP BY d.department ORDER BY d.department;

 department | revenue
------------+---------
 Footwear   |   99.98
 Kitchen    |   35.00

Common mistakes

• Snowflaking every dimension out of habit from normalised OLTP design.
• Expecting large storage savings in a columnar warehouse, where compression already removes most repetition.
• Forgetting that a snowflaked level also needs an Unknown member, or products with no category vanish from inner joins.