Star Schema vs Snowflake Schema: Data Modeling for Analytics
Basics4 minInnovialabs editorial team
In short
Both schemas put measurable events in a central fact table surrounded by dimensions; a star keeps each dimension in one wide table for simple, fast queries, while a snowflake splits dimensions into normalized sub-tables, and most BI tools work best with a star.
The short answer
A star schema has one central fact table (what happened, with numbers) joined directly to flat dimension tables (who, what, where, when). A snowflake schema is the same idea, but dimensions are normalized into several linked tables, for example product → category → department.
For analytics and BI, the star is the usual default: fewer joins, simpler queries, easier for business users. Snowflaking is a targeted choice for very large or shared dimensions, not a goal in itself.
Building blocks on a retail example
Imagine a chain of stores selling goods. The questions are: how much did we sell, of what, where, when and to whom?
Fact table
fact_sales stores events and measures — numbers you sum or average:
| Column | Meaning |
|---|---|
| date_key | link to dim_date |
| store_key | link to dim_store |
| product_key | link to dim_product |
| customer_key | link to dim_customer |
| quantity | units sold |
| net_amount | revenue after discounts |
Dimension tables
Dimensions hold descriptive attributes you filter and group by: dim_product (name, brand, category, department), dim_store (city, region, format), dim_date (day, week, month, quarter, holiday flag), dim_customer (segment, loyalty tier).
Grain
The grain is what one fact row means. Here: "one product line on one receipt". Decide the grain first and write it down. Mixing grains — for example, some rows per receipt line and some per day — produces double counting that is hard to spot.
Surrogate keys
Dimensions use surrogate keys: simple integers generated in the warehouse (product_key = 1042), not the source system's codes. They protect the model from source changes, let you merge several sources, and make history tracking possible.
Slowly changing dimensions (SCD)
Attributes change: a product moves to another category, a store changes its region. Common options:
- Type 1 — overwrite the old value. Simple, but history is rewritten: past sales now appear under the new category.
- Type 2 — add a new row with a new surrogate key and validity dates (
valid_from,valid_to,is_current). Old sales stay linked to the old version. This is the most common choice when history matters. - Type 3 — keep a "previous value" column. Useful only for one-step comparisons.
Star vs snowflake
In a star, dim_product contains brand, category and department as plain columns. In a snowflake, dim_product points to dim_category, which points to dim_department.
| Criterion | Star | Snowflake |
|---|---|---|
| Joins per query | Fewer | More |
| Query simplicity | High, easy for analysts | Lower, more paths to know |
| Query speed | Usually faster | Can be slower due to extra joins |
| Storage | Some repeated text in dimensions | Less duplication |
| Maintenance of shared hierarchies | Update many rows | Update one row |
| Fit for BI tools | Natural fit | Works, but often flattened later |
Storage savings from snowflaking are usually small, because dimensions are tiny compared with fact tables. Columnar warehouses also compress repeated values well.
Which one BI tools prefer
Power BI, Tableau, Looker and similar tools are designed around the star: one fact table, filters flowing from dimensions to facts. Power BI's documentation explicitly recommends star schemas for its models. With snowflaked dimensions, these tools still work, but relationships become longer, filtering behaves less predictably and analysts get confused by many small tables. Teams often build a snowflake in the warehouse and expose a flattened star view to BI.
How to choose
- Start with a star. It covers most sales, finance and marketing reporting.
- Snowflake a dimension when it is very large, shared by many fact tables with a common sub-hierarchy, or updated frequently in one place.
- Keep the grain and SCD rules documented next to the model.
- Build a proper
dim_datetable instead of computing months and quarters in every query.
Common mistakes
- Putting text attributes into the fact table "for convenience".
- Using source IDs as keys, then breaking history when the source reuses them.
- Using Type 1 everywhere, so last year's reports change every time a category is renamed.
- Normalizing dimensions out of habit from transactional database design.