Star vs Snowflake Schema

## The vocabulary first A dimensional model has two kinds of table. A **fact table** stores measurements at a declared grain — one row per order line, per day-and-account balance, per shipment. A **dimension table** stores the descriptive context you filter, group and label by — the product, the customer, the store, the date. Every fact row carries a foreign key to each dimension it is described by. Star versus snowflake is a question about **the shape of the dimensions only**. It says nothing about the fact table. ## The star shape In a star schema each dimension is exactly one table, deliberately denormalized so that every descriptive attribute a user might want lives as a column on the same row: ```sql CREATE TABLE dim_product ( product_key BIGINT PRIMARY KEY, sku VARCHAR(40), product_name VARCHAR(200), brand_name VARCHAR(100), category_name VARCHAR(100), department_name VARCHAR(100) ); ``` The brand, category and department labels repeat across thousands of product rows, and that repetition is intentional. A query for revenue by department joins one table: ```sql SELECT d.department_name, SUM(f.net_amount) FROM fact_sales f JOIN dim_product d ON d.product_key = f.product_key GROUP BY d.department_name; ``` Drawn on a whiteboard, the fact table sits in the middle with a ring of single dimension tables around it — hence "star". ## The snowflake shape A snowflake normalizes the hierarchy out of the dimension into one table per level, each pointing at its parent: ```sql dim_product(product_key, sku, product_name, brand_key) dim_brand(brand_key, brand_name, category_key) dim_category(category_key, category_name, department_key) dim_department(department_key, department_name) ``` The same department query now traverses the chain: ```sql SELECT dep.department_name, SUM(f.net_amount) FROM fact_sales f JOIN dim_product p ON p.product_key = f.product_key JOIN dim_brand b ON b.brand_key = p.brand_key JOIN dim_category c ON c.category_key = b.category_key JOIN dim_department dep ON dep.department_key = c.department_key GROUP BY dep.department_name; ``` Each label is now stored once. The branching arms off the central fact table are what the "snowflake" name describes. ## What does not change This is the part candidates most often get wrong. Snowflaking a dimension does **not** change the fact table's grain, its row count, its measures, or the set of foreign keys it carries. `fact_sales` still holds `product_key` in both designs; the fact still joins to `dim_product` and only to `dim_product`. All that changes is the path from a fact row to a high-level label — one join in a star, several in a snowflake. It is also not a physical storage decision: both shapes are ordinary tables, and the choice is independent of how the engine stores or compresses them. ## The trade-off in one paragraph The star buys query simplicity and a single obvious join path with duplicated label text; the snowflake buys single-place storage of each label with more tables, more join paths and more ways for a hand-written query to go wrong. Because dimensions are typically orders of magnitude smaller than facts — a few hundred thousand product rows against billions of sales rows — the storage the snowflake saves is usually negligible, which is why the star is the industry default for consumer-facing marts. ## Why the redundancy is acceptable here In a transactional schema, repeating a label on every row invites contradictory data because many concurrent writers can update one copy and not another. A warehouse dimension is different: it is rebuilt or merged by a controlled batch load, users only read it, and the load process is the single writer that keeps every copy consistent. The reason to normalize an operational table therefore does not transfer automatically to a mart — the analytics model is optimized for reads, and it pays for that with duplication the loader manages. ## What an interviewer is listening for A crisp definition, the fact-table-unchanged point, and the naming of the axes being traded — join count and analyst usability against storage and label maintenance. Answers that simply assert "snowflake is more normalized, so it is better designed" miss that normalization is a means, not a goal, in an analytics model.