What Is a Star Schema?
A star schema is the structure Power BI is designed to work with. Imagine one central table – the fact table, with several dimension tables radiating out from it like the points
Each dimension connects directly to the fact table using a simple one-to-many relationship.
Each dimension connects directly to FactSales. Customer details, product categories, store information, and date table each sit in their own denormalised table, making the model fast, tidy, and easy to understand.
Your fact table contains measurable events such as SalesAmount, Quantity, and foreign keys to each dimension.
The dimension tables hold descriptive details such as CustomerName, City, ProductCategory, StoreRegion, or anything you want to filter and slice by.
Why Power BI loves star schemas
Fast queries – Fewer joins mean VertiPaq can scan data efficiently.
Better compression – Dimension tables contain repeated values, which compress extremely well.
Predictable DAX – Filter propagation and functions like CALCULATE or time intelligence work cleanly.
Easy for users – Even non-technical users can understand the layout in Model view.
A well-built star schema is as close as you get to “plug and play” modelling in Power BI.
Before moving on, here are two quick definitions:
Normalised data: information split into multiple related tables to remove duplication.
Denormalised data: related attributes stored together in one table to make querying faster.
This distinction becomes important when looking at schemas.
What Is a Snowflake Schema?
A snowflake schema appears when your dimension tables are normalised where tables are broken up into several smaller tables connected across multiple levels.
Dimensions branch into extra layers. Customers link to cities, cities to countries, products to categories, and so on; creating longer chains of joins for Power BI to navigate.
Instead of having all product attributes in one table, details like store, product, and customer are split out into different lookup tables.
This design is common in large data warehouses, where storage savings and strict data governance are priorities.
Where snowflakes cause problems in Power BI
Slower performance – VertiPaq must hop through several tables to apply a single filter.
Harder to understand – Users often struggle to follow multi-level lookups.
More fragile – Changes in one table require updates across several others.
Trickier DAX – Multi-hop relationships make filter behaviour less predictable.
Snowflakes aren’t wrong. They’re just not what Power BI performs best with.
Why Star Schemas Outperform Snowflakes (VertiPaq Basics)
VertiPaq is how Power BI stores it’s data, and controls how you access it!
It stores data in compressed columns and only loads the columns needed for a query. Star schemas line up perfectly with how VertiPaq is designed:
Dimension tables compress better because repeated values (e.g., ProductCategory) shrink well.
Fact tables remain dense and efficient when dimensions are flattened.
Fewer relationships reduce the work required for filter propagation.
Lower cardinality improves indexing and speeds up grouping and filtering.
Snowflake schemas do the opposite: they increase cardinality, add extra joins, and create more relationships for VertiPaq to manage.
Top comments (0)