Snowflake schema

What is a snowflake schema?

A snowflake schema is a dimensional model in which one or more dimension tables are normalised into further linked tables, so a single business entity is spread across a parent table and its sub-dimensions. The fact table stays in the centre, but instead of each dimension being one wide table, its repeating hierarchy levels branch out into smaller tables. Drawn as a diagram, those branches make the model look like a snowflake, which is where the name comes from.

This is a schema shape, not a product. It has nothing to do with Snowflake, the cloud data warehouse; the two just share a word.

It helps to hold a star schema next to it. In a star schema, a product dimension keeps product, subcategory, category and brand together in one dimension table. Snowflake that same dimension and product moves to one table, subcategory to a second, category to a third, each joined by a key. It is still the Kimball dimensional modelling approach, applied with more normalisation inside the dimensions.

How a dimension gets snowflaked

Take a product dimension. In a flat design it is a single dimension table with a row per product and columns for subcategory, category and brand. Those columns repeat: every mountain bike carries the same "Bikes" category text, over and over down the rows.

Normalise it and the repeating levels move out. Microsoft's Adventure Works sample does exactly this. Products live in DimProduct, each product points to a row in DimProductSubcategory, and each subcategory points to a row in DimProductCategory. The category name is now stored once per category rather than once per product. A report that filters sales by category then travels fact table to product to subcategory to category, three joins where a star needs one.

That is the trade at the heart of the pattern. You remove repeated values and get stricter normalisation inside the dimension; you pay with more tables and longer join paths.

Snowflake schema versus star schema

Ralph Kimball, who formalised dimensional modelling, is direct about this: avoid snowflaking in most warehouses. The Kimball Group gives two reasons. Business users find the extra tables hard to read and navigate, and the added joins can slow queries down. A denormalised dimension holds exactly the same information in a form that people and query engines both handle more easily.

The gap is widest in an in-memory model. In Power BI, every dimension you snowflake lengthens the filter propagation chain a query has to walk, and the field list fills with thin one-column or two-column tables that mean little to a report author. Microsoft's guidance says the same thing: the benefits of a single, denormalised dimension table generally outweigh those of several normalised ones.

So the default for a star schema is denormalisation. Collapse each dimension into one wide table and keep the model a single join deep. A snowflake schema earns its place only when a specific reason overrides that default.

When snowflaking is defensible

A few situations make the extra tables worth it.

• Very large dimensions. When a dimension runs to millions of rows, repeating the category and hierarchy text on every row adds real size. Splitting the high-cardinality detail from the repeating rollups can shrink the model, and the saving grows with the dimension.

• Shared sub-dimensions. When several dimensions reference the same lookup, keeping it in one place keeps its meaning consistent. Kimball calls this narrow form an outrigger: a single sub-dimension hung off a larger one, for example a date table joined to the hire-date column of an employee dimension so you can analyse hires by fiscal year. Outriggers are fine in moderation, as the exception rather than the rule.

• Engines that join cheaply. A columnar relational warehouse such as Fabric Warehouse resolves joins with indexes and stays fast, so a normalised dimension costs less there than in an in-memory model. Microsoft still recommends a star for the fewer joins, but the penalty is smaller.

These cases share one thing: the model is maintained by data engineers, not dragged around directly by report builders. When analysts work straight on the tables, the star schema almost always wins.

What to watch out for with snowflake schemas

If you snowflake on purpose, or inherit a model that already is, a few habits keep it usable.

• Flatten for the reporting layer. A normalised source is fine, but shape it into wide dimensions in Power Query or your data warehouse before the semantic model sees it. The report layer wants a star.

• Watch the filter paths. Longer relationship chains can give filter results a report author does not expect. Check that a filter on an outer sub-dimension reaches the fact table the way you meant it to.

• Keep hierarchies documented. A hierarchy that spans several tables cannot be built as one object in Power BI, so category, subcategory and product have to keep the same meaning everywhere they appear.