Power BI star schema vs snowflake schema for a clean model
Power BI star schema vs snowflake schema is a modeling choice that shapes every report built afterward. A star keeps each business entity, such as product or customer, in one dimension table beside the fact table. A snowflake splits that entity across several related tables. Both designs can return correct numbers. However, Microsoft's guidance leans toward the star for most Power BI models, and the reasons are practical.
- One table per entity
- Simpler DAX and filters
- Exceptions explained
Last updated
Power BI star schema vs snowflake schema: what is the difference?
A star schema has two kinds of tables. Fact tables hold events such as sales lines, with keys and numbers. Dimension tables describe those events: who, what, where and when. Each dimension joins straight to the fact table, so the diagram looks like a star.
A snowflake schema normalizes the dimensions. Microsoft's example is a product dimension held in three related tables: category, subcategory and product. The tables then branch outward from the fact table, which gives the design its name.
In short, the facts are the same in both. What differs is whether one entity lives in one table or in a chain of tables.
How do a star and a snowflake model compare by area?
These rows reflect Microsoft's star schema guidance and common practice.
| Area | Star schema | Snowflake schema |
|---|---|---|
| Dimension layout | One table per entity | Several related tables per entity |
| Relationships | Fewer, one hop to the fact | More, with longer filter chains |
| Field list | Short and easy to scan | More tables for authors to search |
| Hierarchies | Built inside one table | Cannot span separate tables |
| DAX | Usually simpler | Often needs more care |
| Storage | Some repeated text values | Less repetition |
| Where it is common | Reporting models | Source databases and warehouses |
Why does Microsoft's guidance favor the star?
The guidance says the benefits of a single model table generally outweigh those of multiple tables. Three reasons stand out.
Shorter filter paths
In a snowflake, a filter on category must pass through subcategory and product before it reaches sales. Microsoft notes that longer relationship chains take more work to traverse. So one table means one hop.
A friendlier field list
Report authors pick fields from the Data pane. When a dimension splits into small tables, some with only one or two columns, the list grows harder to read. Therefore fewer, wider tables help self-service users.
Hierarchies that work
A hierarchy such as category, subcategory and product needs its levels in one table. Because a snowflake spreads those columns out, the author cannot build that drill path until the tables merge.
When is a snowflake dimension acceptable in a model?
The guidance is not absolute. Microsoft says the best decision can depend on data volumes and on usability needs. So a snowflake shape is sometimes a fair choice.
For example, a very large dimension may repeat long text values many times when it is flattened. Keeping a small lookup table beside it can then save memory. A team may also mirror the source design while a project is young, and merge the tables later.
Still, we treat each case as an exception that needs a reason. When nobody can name the reason, we merge the tables in Power Query. The source database can stay normalized, since the model does not have to copy its shape.
How do we turn a snowflake source into a star?
Most source systems are normalized, so this task comes up on almost every project. The usual tool is Power Query. We merge the category and subcategory queries into the product query, keep the columns that readers need and load one product table.
When the source is a SQL database, a view can do the same join upstream. That often refreshes faster, because the database does the work. Then we turn off loading for the helper queries so they stay out of the model.
After that, we check the relationships. Each dimension should join to the fact table on one key, with a filter that flows from dimension to fact. Finally, we compare totals with the source to confirm that the merge lost no rows. For a step-by-step build of the star itself, see the Power BI star schema guide, and for hands-on help with a whole model, see Power BI data modeling.
Power BI star schema vs snowflake schema: six model checks
Open the model view and work through these questions in order.
- Does each business entity sit in one table?
- Do any filters cross more than one relationship?
- Are there tables with only one or two columns?
- Can authors build the hierarchies they ask for?
- Do fact tables hold keys and numbers only?
- Does one date table serve every fact?
Power BI star schema vs snowflake schema: frequent questions
Is a snowflake schema wrong in Power BI?
No. A snowflake model can return correct results. However, Microsoft's guidance says a single table per dimension generally serves Power BI better. Filters travel a shorter path, the field list stays tidy and hierarchies become possible. So we choose the star by default and keep a snowflake only for a stated reason.
Does a star schema make a Power BI model larger?
Sometimes a little, since merged tables repeat some text values. Still, the engine compresses repeated values well, so the cost is usually small. In return, queries cross fewer relationships. We measure file size and page speed before and after a merge and do not rely on a rule of thumb.
Must the source database change to a star as well?
No. The source can keep its normalized design, which suits transaction systems. The reshaping happens on the way in, either in Power Query or in a database view. Therefore the application team changes nothing, and the semantic model still gets the shape that reporting needs.
What about one big flat table instead of either schema?
A single flat table looks simple at first, but it causes trouble as a model grows. Measures become harder to write, and a second fact table has nothing to share dimensions with. So we split a flat extract into facts and dimensions early. The model then scales to new subject areas.
Can Power BI Agency review our data model?
Yes. One of our consultants opens the model view with you in a free 30-minute assessment and points out the changes that would matter most. Power BI Agency is a division of Sataware Technologies and independent of Microsoft. We are a small team. Phone (480) 485-7373, and we answer within 2 hours.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.