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.
مواضيع تُناقش معًا غالبًا
في الحلقات التي تتناول Snowflake schema تُناقش غالبًا أيضًا: مخطط النجمة، DAX و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.
| Type | Concept |
|---|---|
| Wikipedia | Snowflake schema – Wikipedia en.wikipedia.org |
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
- Snowflake schema – Wikipedia en.wikipedia.org
Maintained by Mirko Peters (Microsoft MVP) · Last updated: