A star schema almost always performs better than a snowflake schema in Power BI. Filters reach the fact table through one relationship instead of a chain, the model holds fewer tables and key columns, and DAX stays simpler. Microsoft's own modeling guidance recommends combining snowflaked dimension tables into one table in most cases.

What is a star schema

A star schema has two kinds of table:

• Fact tables hold events and their numbers, such as sales lines with quantity and revenue.

• Dimension tables hold the descriptive columns you filter and group by, such as product, customer and date.

Example structure

A Sales fact table with these columns:

• DateKey

• ProductKey

• CustomerKey

• Quantity

• Revenue

Dimension tables around it:

• Date

• Product (with category and subcategory as columns)

• Customer (with region as a column)

Each dimension connects directly to the fact table with a one-to-many relationship.

Key characteristics

• Flat dimension tables.

• One relationship between each dimension and the fact.

• Filters travel one step.

What is a snowflake schema

A snowflake schema normalizes dimensions into several related tables. Instead of one Product table, you have three: Product, Product Subcategory and Product Category. Product relates to the fact table. Subcategory relates to Product, and Category relates to Subcategory.

Key characteristics

• Normalized dimensions, with each attribute stored once.

• Chains of relationships between dimension tables.

• More tables and more key columns.

Snowflakes are common in relational data warehouses, where they save storage and simplify updates. In a Power BI model the trade-offs are different.

How Power BI processes data models

Each visual sends a query to the semantic model. In an Import model, the VertiPaq engine stores each column compressed in memory. A filter on a dimension column is passed along relationships until it reaches the fact table, and the engine then aggregates the fact rows that remain.

Two things follow:

• Each relationship a filter crosses is extra work.

• Every table carries key columns that exist only to support relationships, and they take memory.

Performance comparison: star schema vs snowflake schema

Why star schema performs better in Power BI

1. Fewer relationships for each filter

In a star schema, a filter on Category travels once: Product to Sales. In a snowflake, it travels Category to Subcategory, Subcategory to Product, then Product to Sales. Microsoft notes that longer filter chains can be less efficient than a filter on a single table.

2. Fewer tables and key columns

Microsoft's guidance says a snowflaked design loads more tables, which is less efficient for storage and performance. Each extra table brings key columns that exist only for the relationship. Category names repeated on every product row compress well in VertiPaq, because the column has few distinct values.

3. Simpler filter context

In a star schema, every attribute of a product is in one table, so a measure that needs to clear or change a filter touches one table. In a snowflake, the same filter can arrive through several tables, and measures must account for all of them. For example, clearing all product filters in a star schema is one REMOVEFILTERS ( 'Product' ). In a snowflake you also need to clear Subcategory and Category.

4. Better usability

A flat Product table lets you build one Category, Subcategory, Product hierarchy. Report authors see one table instead of three, two of which may hold only a key and a name. Fewer confusing choices means fewer badly built visuals.

When a snowflake schema might be acceptable

A snowflake is not always wrong. It can be reasonable when:

• A dimension is very large and a parent level carries many wide columns, so repeating them on every row costs more than the extra relationship.

• Facts are stored at different grains. For example, targets set by product category can relate to a Category table while sales relate to Product.

• The model is small and speed is not a problem, and matching the warehouse structure makes maintenance easier.

Even then, test a flattened version. In most models it is the better choice.

Best practice: flatten dimensions for Power BI

Keep the normalized structure in the warehouse if it serves the warehouse. Flatten it on the way into Power BI.

Flatten in Power Query

If Product, ProductSubcategory and ProductCategory are separate queries, create the flat table with two merges. In Power Query Editor, select Home > New Source > Blank Query, name the query Product Flat, open the Advanced Editor, and paste:

let
    Source = Product,
    MergedSubcategory = Table.NestedJoin(Source, {"ProductSubcategoryKey"}, ProductSubcategory, {"ProductSubcategoryKey"}, "Subcategory", JoinKind.LeftOuter),
    ExpandedSubcategory = Table.ExpandTableColumn(MergedSubcategory, "Subcategory", {"SubcategoryName", "ProductCategoryKey"}),
    MergedCategory = Table.NestedJoin(ExpandedSubcategory, {"ProductCategoryKey"}, ProductCategory, {"ProductCategoryKey"}, "Category", JoinKind.LeftOuter),
    ExpandedCategory = Table.ExpandTableColumn(MergedCategory, "Category", {"CategoryName"}),
    RemovedKeys = Table.RemoveColumns(ExpandedCategory, {"ProductSubcategoryKey", "ProductCategoryKey"})
in
    RemovedKeys

The query references the other three queries by name. Right-click Product, ProductSubcategory and ProductCategory and clear Enable load on each, so they feed the merge but only Product Flat loads into the model. A left outer join keeps products that have no subcategory.

Flatten in the source

If the source is a SQL database, a view does the same job and folds cleanly:

CREATE VIEW dbo.vDimProduct AS
SELECT
    p.ProductKey,
    p.ProductName,
    s.SubcategoryName,
    c.CategoryName
FROM dbo.DimProduct AS p
LEFT JOIN dbo.DimProductSubcategory AS s
    ON s.ProductSubcategoryKey = p.ProductSubcategoryKey
LEFT JOIN dbo.DimProductCategory AS c
    ON c.ProductCategoryKey = s.ProductCategoryKey;

Import the view as the Product table.

Modeling best practices for Power BI performance

• Model each business process as a fact table with a consistent grain.

• Relate every dimension directly to the fact tables.

• Avoid many-to-many relationships where a proper dimension or bridge table works.

• Use single-direction filtering by default. See cross filter direction.

• Remove columns nobody reports on.

• Use a dedicated date table.

• Relate on integer surrogate keys.

For relationship types and their cost, see Power BI relationships and cardinality. For the wider set of modeling rules, see our data modeling best practices.

Star schema and DAX performance

A star schema makes DAX shorter and more predictable:

• Filters on product attributes live in one table, so CALCULATE modifiers touch one table.

• RELATED reaches a dimension column in one step from the fact table.

• Measures can filter a column rather than iterate a table, which the engine handles more cheaply.

A clean model often turns a measure that needed several filter modifiers into a plain SUM.

Which schema to use in Power BI

Use a star schema for Power BI models. Flatten snowflaked dimensions in Power Query or in the source, and keep a snowflake only for a specific reason from the list above, such as facts stored at a different grain. If your reports are slow for other reasons, our Power BI performance optimization guide covers the other layers.

FAQ: star schema vs snowflake schema in Power BI

Is star schema mandatory in Power BI?

No. Power BI accepts any set of tables and relationships. A star schema is Microsoft's recommended design because it gives the best mix of speed, model size and usability.

Does snowflake schema always cause slow reports?

No. A small snowflaked model can be fast enough. The cost grows with the number of tables in each chain and the size of the model, and a snowflake also blocks hierarchies that span tables, whatever the speed.

Can I convert snowflake to star schema in Power BI?

Yes. Merge the parent tables into the child table in Power Query, as in the example above, and disable load on the parent queries. If the source is a database, a view that joins the tables does the same thing before the data reaches Power BI.

Does star schema improve DAX performance?

Usually. Filters cross fewer relationships and measures need fewer filter modifiers. The biggest DAX gains still come from how each measure is written, so a star schema is the foundation, not the whole fix.

Sources

• Understand star schema and the importance for Power BI - Microsoft Learn

• Model relationships in Power BI Desktop - Microsoft Learn