M365Con 2027 – free online conference · 11–22 Jan 2027 · 120 sessions m365con.net

Snowflake schema

Concept

A snowflake schema is a dimensional data model in which dimension tables are normalized into further related tables (for example product category and subcategory) instead of connecting directly to the fact table as in a star schema.

✦ CONCEPTSnowflake schemaM365.FM
Title graphic: Snowflake schema (Concept)

Often discussed together

Episodes about Snowflake schema often also cover Star schema, DAX and Power Query.

Description

A snowflake schema is a dimensional data model in which dimension tables are normalized into further related tables. Instead of each dimension connecting directly to the fact table as a single flat table, as in a star schema, a dimension is split into several linked tables. A typical example is a product dimension that is separated into product, product category and subcategory tables, with the tables joined to one another in a chain. The name comes from the branching shape that results when these normalized dimensions are drawn around a central fact table.

The model is used in analytics and business intelligence, where data is organized into fact tables, which hold measurable events, and dimension tables, which describe the context of those events. The snowflake variant applies normalization to the dimension side. According to the M365.FM episodes, this normalization is what separates it from the star schema, in which the fact table links directly to each dimension table and the dimension tables are denormalized.

The main trade-off discussed in the episodes is between query simplicity and storage or governance benefits. The episode "Build a Power BI Star Schema for Faster Reports" compares the two designs and says that a star schema offers faster query performance because fewer joins are required, whereas a snowflake schema is slower because of the additional joins. The same comparison states that DAX calculations are simplified in a star schema and more complex in a snowflake schema, and that report responsiveness can be reduced by the performance overhead of the extra joins. In that comparison, the star schema is presented as the choice for intuitive and responsive Power BI dashboards.

The episode "Optimize Microsoft Fabric Lakehouse Performance" frames the choice in terms of speed, storage and tuning for lakehouse and warehouse workloads. It describes the star schema as a central fact table with denormalized dimension tables, optimized for speed and suited to BI dashboards, reporting queries and read-heavy workloads. The snowflake schema is described as having normalized dimension tables that lead to more complex queries because of the joins. The episode lists large datasets, storage optimization and data governance as the situations where a snowflake schema is the better fit, while noting that it can slow queries down.

The episode "From Raw Data to Real Business Impact: Mastering Power BI and Microsoft Fabric with Thummalacherla Krishnakanth" places the comparison inside a broader view of enterprise reporting. The conversation covers star schema versus snowflake schema alongside fact and dimension tables, Power Query transformations, data quality, relationship design, cross-filter direction and scalable semantic models. The episode says a well-designed data model improves performance, simplifies DAX development and keeps reports maintainable as organizations grow, which is the context in which the snowflake-versus-star decision is made.

In practice, the decision between the two designs comes down to priorities. When fast, simple queries and straightforward DAX measures matter most, as in read-heavy Power BI reporting, the episodes point toward a star schema. When reducing redundancy in large dimensions, saving storage or supporting governance matters more, a snowflake schema can be justified, accepting the extra joins and more complex queries that come with it. The facts available do not go beyond these comparisons, so specific tooling steps for building a snowflake schema are not covered here.

Overview compiled with AI from M365.FM episodes and the facts on this page. You can improve it with the edit button below.

TypeConcept
WikipediaSnowflake schema – Wikipedia en.wikipedia.org

Key points about Snowflake schema

Key points about Snowflake schema from the episodes, each with its source episode:

Episodes about Snowflake schema

Episodes in which Snowflake schema is discussed:

Timeline of episodes about Snowflake schema

Frequently asked questions

Questions marked with AI were written with AI from the facts on this page.

What is the difference between a snowflake schema and a star schema? (AI)

In a star schema the fact table links directly to denormalized dimension tables, while in a snowflake schema the dimension tables are normalized into further related tables, such as product category and subcategory.

Is a snowflake schema slower than a star schema in Power BI? (AI)

According to the M365.FM episode "Build a Power BI Star Schema for Faster Reports", a snowflake schema is slower because additional joins are needed, and it can make DAX calculations more complex and reduce report responsiveness.

When is a snowflake schema a reasonable choice? (AI)

The episode "Optimize Microsoft Fabric Lakehouse Performance" lists large datasets, storage optimization and data governance as the cases where a snowflake schema suits best, despite the added join complexity.

Glossary

Community wiki

Short info above, community knowledge below: add practical tips, examples or corrections. New contributions are reviewed first; trusted members publish directly.

Experts and companies for this topic

Nobody has listed this topic yet. Add it to your profile.

Sources

Maintained by Mirko Peters (Microsoft MVP) · Last updated: