DAX and data models

Star Schema and Snowflake Schema: What It Means

Star schema and snowflake schema, explained straight for practitioners. Real numbers, the trade-offs, and what each does to your Power BI dashboard.

Austin Levine

·

Updated

Star schema and snowflake schema are two shapes for the tables behind a report. A star schema keeps each dimension one hop from the fact table. A snowflake schema splits those dimensions into more linked tables. In Power BI, a star schema runs faster and makes measures simpler, so most models should end up as a star.

Star schema and snowflake schema are two ways to arrange the tables behind your dashboard. A star schema keeps one fact table in the middle with dimension tables one step away. A snowflake schema splits those dimensions into smaller linked tables, so the same data spreads across more joins. The shape you pick changes how fast reports run, how easy your measures are to write, and whether people trust the number on the screen. Gartner puts the cost of poor data quality at $12.9 million per year for the average organization, and a confusing model is one common source of that cost.

This article is written for the person who builds the model, not just the person who reads the report. By the end you'll know what each shape does to performance, what it does to your DAX, and how to move from one to the other in Power Query.

Key takeaways

  • A star schema keeps each dimension one hop from the fact table; a snowflake splits dimensions into more linked tables.

  • In Power BI a star schema runs faster and makes measures simpler, so most models should end as a star.

  • Power BI's VertiPaq engine does more work for every relationship it crosses at query time.

  • A snowflake earns its place in a few real cases, and Power Query can fold one back into a star.

What's the difference between a star schema and snowflake schema?

A star schema has one fact table and several dimension tables that connect straight to it. The fact table stores the events you measure, like one row per sales order. Each dimension stores the descriptions you slice by: a Date table, a Product table, a Customer table, a Store table. Every dimension sits one relationship away from the fact. The Product table carries the product name, its category, and its department, all in the same table.

A snowflake schema takes those wide dimensions and splits them into normalized pieces. Instead of one Product table, you get Product, then Category, then Department, chained together. Date might split into Date, Month, and Quarter. You end up with more tables and more relationships for the same information.

Here's the same model in both shapes.

What you compare

Star schema

Snowflake schema

Dimension tables

Few, wide, denormalized

Many, narrow, normalized

Hops from dimension to fact

One

Two or three

Relationships to maintain

Fewer

More

Ease of writing measures

Simpler

Harder to trace

Query speed in Power BI

Faster

Slower on large models

Duplicate values in a column

More

Fewer

Both shapes store the same numbers. The difference is how many tables the engine walks through to answer a question.

Why does Power BI run faster on a star schema?

Power BI stores data in a columnar engine called VertiPaq. It compresses each column and answers questions by scanning columns and following relationships. Every relationship the engine crosses at query time adds a little work.

In a star schema, a filter on a slicer reaches the fact table in one hop. A user clicks Department, and the filter goes straight from the dimension to the fact. In a snowflake schema, that same filter travels Department, then Category, then Product, then Sales. Three relationships instead of one, for every click.

On a 5,000-row demo model, nobody notices the extra hops. On a 40-million-row fact table feeding an executive KPI dashboard that refreshes through the day, the extra joins turn into slow visuals and a report that lags when three people open it at once. Microsoft's own guidance for Power BI is to model as a star for this reason. The engine is tuned for it.

How does the CALCULATE function change with each schema?

DAX filter context flows from the one-side of a relationship to the many-side. That's the CALCULATE function Power BI hands you, and it moves filters along active relationships to change what a measure sees. The more tables you put between a dimension and the fact, the more relationships that filter has to cross, and the more chances there are for one of them to be inactive or ambiguous.

Say you write a year-over-year measure or any KPI in BI reports, like margin or churn. In a star schema, the filter path is short and easy to reason about. In a snowflake schema, you trace the same filter across two or three relationships, and you're more likely to reach for USERELATIONSHIP or to debug why a filter didn't propagate the way you expected. The measure logic doesn't get more powerful. It gets harder to read and slower to fix.

Simpler models also make Copilot and other AI features return one correct number instead of guessing across a chain of tables. A clean star is the model that both a person and a machine can read the same way.

When is a snowflake schema the right call?

A snowflake schema earns its place in a few real cases:

  • A very large shared dimension. If one dimension has millions of rows and gets reused by many fact tables, normalizing part of it can save memory and avoid duplicating a wide sub-table everywhere.

  • A source that's already normalized. If the data lands in OneLake or a warehouse in third-normal form, keeping some of that structure can be less work than flattening every table by hand.

  • Lineage or governance needs. Sometimes a regulated attribute has to live in its own table with its own owner and audit trail.

Even in these cases, most Power BI models still flatten to a star before they reach the report layer. You keep the normalized source in the warehouse, then shape a star on the way into the model. The report runs on the star, and the warehouse keeps whatever structure it needs.

How do you turn a snowflake into a star in Power Query?

You do this once in Power BI Desktop, then publish. The idea is to fold the chained tables back into one dimension.

  1. Find the chain. Say Product links to Category, and Category links to Department. Those three should become one Product dimension.

  2. Merge the tables. In Power Query, use Merge Queries to join Category onto Product on the category key, then expand the columns you need (category name, department name). Repeat until every attribute sits on Product.

  3. Load one dimension. Disable load on the leftover Category and Department queries so only the flat Product table reaches the model.

  4. Rebuild the relationship. Connect the single Product table to the fact table on the product key. Delete the old multi-hop relationships.

  5. Test what people see. Use View As in Power BI Desktop to check row-level security still returns the right rows now that the tables changed shape.

Your fact table settings don't change. Incremental refresh still runs the same way: you keep the RangeStart and RangeEnd parameters on the fact table, and the star or snowflake choice sits on the dimension side. When you promote the model from development to production with deployment pipelines, the flattened star travels with it, so nobody rebuilds relationships by hand in production and gets one slightly wrong.

Frequently asked questions

Is a star schema always better in Power BI? For most models, yes. A star schema matches how the VertiPaq engine works, so it runs faster and keeps measures simple. Reach for a snowflake schema only when a shared dimension is very large or the source is already normalized and flattening it costs more than it saves.

Does a snowflake schema save memory? Sometimes, on very large dimensions where normalizing removes a lot of repeated values. On typical business dimensions the saving is small, and columnar compression already handles repeated values well, so the query cost usually outweighs the memory gain.

Can I mix star and snowflake in one model? Yes. Many real models are mostly star with one or two snowflaked dimensions where it makes sense. Keep the snowflaking to the tables that need it and flatten the rest, so the common report paths stay one hop from the fact.

Does the schema affect row-level security? It can. RLS filters flow along relationships, so a snowflake adds hops that a security rule has to cross. Fewer tables mean simpler, faster RLS. After any change, test with View As before you publish.

Which shape is better for a KPI dashboard? A star schema. A KPI dashboard reads many measures at once and refreshes often, so short filter paths and simple DAX matter more here than anywhere else. Flatten your dimensions and the whole dashboard gets faster and easier to maintain.

Read next: KPI and data modeling and KPI dashboard.

If you want to check your own model before it goes to production, run our free Power BI Model Review: thirteen questions, a score out of 100, and your top three risks with the fix for each. And if your numbers still won't reconcile across reports, book a semantic-model and AI-readiness review with us and we'll walk your model with you.

Is your Power BI set up the right way?

1

Book 45 minutes and tell us what you want looked at.

2

We walk through your reports and setup with you, live.

3

Within two working days, you get our plan in writing.

Is your Power BI set up the right way?

1

Book 45 minutes and tell us what you want looked at.

2

We walk through your reports and setup with you, live.

3

Within two working days, you get our plan in writing.

Is your Power BI set up the right way?

1

Book 45 minutes and tell us what you want looked at.

2

We walk through your reports and setup with you, live.

3

Within two working days, you get our plan in writing.

CaseWhen is a Berlin BI consultancy that builds reporting that leaders can trust, on the Microsoft stack: Power BI, Fabric and Azure.

Berlin, Germany

© CaseWhen Consulting GmbH

English

CaseWhen is a Berlin BI consultancy that builds reporting that leaders can trust, on the Microsoft stack: Power BI, Fabric and Azure.

Berlin, Germany

© CaseWhen Consulting GmbH

English

CaseWhen is a Berlin BI consultancy that builds reporting that leaders can trust, on the Microsoft stack: Power BI, Fabric and Azure.

Berlin, Germany

© CaseWhen Consulting GmbH

English