Take a grocery chain with 20,000 products. Each product belongs to one of 300 subcategories (basmati rice, toor dal, sunflower oil), each subcategory belongs to one of 40 categories (grains, pulses, edible oils), and each category sits in one of 6 departments (staples, beverages, personal care and so on). The sales fact table needs to be analysed at any of these levels. The question is how to store that hierarchy, and the two classic answers give the star schema and the snowflake schema their names.
The star: one wide table per dimension
In a star schema, the product dimension is a single table with 20,000 rows. Each row carries the product name, brand, pack size, and also the subcategory name, category name and department name. The words “Staples” and “Pulses” are repeated on thousands of rows. Draw the fact table in the middle with product, store, date, customer and promotion dimensions around it, each connected by one join, and the picture looks like a star.
This is deliberately denormalised. To report sales by department you join the fact table to one dimension table and group by a column. No chain of joins, no lookups through intermediate tables.
The snowflake: normalise the dimension
In a snowflake schema, the product table holds only product-level attributes plus a key to a subcategory table. The subcategory table (300 rows) holds a key to the category table (40 rows), which holds a key to the department table (6 rows). Each department name is stored once. Drawn out, the dimensions branch into smaller tables, giving a snowflake shape.
To report sales by department you now join fact to product, product to subcategory, subcategory to category, category to department: four joins instead of one.
Side by side
| Star | Snowflake | |
|---|---|---|
| Dimension structure | One denormalised table | Several normalised tables |
| Joins per query | One per dimension | One per level of hierarchy |
| Redundant text | Higher | Lower |
| Ease for report authors | Simple, columns are where you expect | Must know which table holds which level |
| Updating a category name | Update many rows | Update one row |
| Typical recommendation | Default for analytics (Kimball) | Used selectively |
Why the storage saving rarely wins
The main argument for snowflaking is that it avoids repeating text. In our example, a star stores “Staples” perhaps 7,000 times. That sounds wasteful until you compare it with the fact table. Dimensions are usually tiny next to facts: 20,000 product rows against hundreds of millions of sales rows. Shaving a few megabytes off a dimension makes no practical difference to cost or speed. Columnar warehouses such as BigQuery, Snowflake (the product, unrelated to the schema name), Redshift and Synapse also compress repeated values in a column very efficiently, so the repetition costs even less than it appears.
Meanwhile, every extra join is something a report author has to understand and a query engine has to execute. Ralph Kimball’s guidance, which most analytics teams still follow, is to keep dimensions flat unless there is a specific reason not to.
Why Power BI is especially opinionated about this
Microsoft’s own modelling guidance for Power BI recommends star schemas, and the reasons are specific to how its engine and DAX work.
- Filter propagation. Filters flow along relationships from the “one” side to the “many” side. In a star, a slicer on department filters the product table, which filters sales: one hop. In a snowflake, the filter must travel through every intermediate table. It works, but each hop adds evaluation work and another place for a relationship to be misconfigured.
- Simpler DAX. Measures that remove or modify filters, such as “share of category sales”, are easier to write when category is a column in the same table as product. With snowflaking you often need to clear filters on several tables at once.
- Usability. In the field list, a business user sees one Product table with all product attributes. In a snowflake they see four tables and must guess where “Department” lives.
- Compression. The VertiPaq engine in Import mode compresses repeated values within a column, so the redundancy in a flat dimension costs little.
A common practical pattern is to keep the source tables normalised in the warehouse or in Power Query staging, then merge them into one flat dimension before loading into the model.
When snowflaking is reasonable
Snowflaking is not wrong, just usually unnecessary. There are cases where some normalisation helps:
- A shared sub-dimension. If both a customer dimension and a store dimension need detailed geography (pincode, district, state, zone), a single geography table referenced by both avoids maintaining the same hierarchy twice. Kimball calls this an outrigger and allows it sparingly.
- Facts at different grains. If sales targets are set at category level and actual sales are recorded at product level, you need a category table that both can relate to. A separate category dimension, connected to the product dimension or used directly by the targets fact, is a legitimate design.
- Very large, frequently changing hierarchies. If a hierarchy is huge and its upper levels are renamed often, normalising can reduce reload work. This is rare for typical business dimensions.
A note on the name confusion
Neither design is the same as a fully normalised operational database. A source ERP might spread a single sale across a dozen tables (header, lines, tax lines, price list, unit conversions). Even a snowflake schema is far simpler than that, because it is still organised around facts and dimensions. The star versus snowflake debate is about how much normalisation to keep inside the dimensions, not about whether to model dimensionally at all.
When in doubt, start with a star. If a specific requirement, such as targets at a coarser grain or a genuinely shared hierarchy, calls for splitting a dimension, split only that part and keep everything else flat.
Written by the RP Strativa Team. The grocery chain and its figures are illustrative.
Leave a Reply