Star Schema vs Snowflake Schema — Which to Use in Power BI?

Your Power BI report loads in 12 seconds. Your DAX measures return confusing results. Your model view looks like a plate of spaghetti. In most cases, the root cause is the same — a poorly designed data model.

The schema you choose — Star or Snowflake — is the single most impactful decision you make before writing a single DAX measure. This guide explains both schemas clearly, compares their trade-offs, and gives you a definitive answer on which to use in Power BI.

1. Why Data Modeling Matters in Power BI

Power BI's analytical engine — VertiPaq — is a columnar, in-memory engine that compresses and stores your data. It is extraordinarily fast when the data model is structured correctly. It becomes slow and error-prone when it is not.

A good data model:

• Makes DAX simpler — measures become 2 lines instead of 20
• Makes reports faster — VertiPaq works best with specific relationship patterns
• Makes maintenance easier — adding a new dimension doesn't break existing measures
• Makes slicers work correctly — filters propagate predictably across tables

The two dominant schema patterns in dimensional modelling are the Star Schema and the Snowflake Schema. Understanding both — and when to use each — is a core skill for any Power BI developer.

2. Key Concepts — Fact Tables and Dimension Tables

Before comparing schemas, it helps to be clear on the two building blocks:

A fact table typically has millions of rows and relatively few columns — mostly foreign keys and numeric measures. A dimension table has fewer rows but many descriptive columns. The relationship between them drives every filter and calculation in your report.

3. Star Schema

In a Star Schema, each dimension table connects directly to the central fact table. There is exactly one hop between any dimension and the fact — the model diagram looks like a star with the fact table at the centre.

-- FACT TABLE (centre of the star)
FactSales
├── SaleID          (PK)
├── DateKey         (FK → DimDate)
├── ProductKey      (FK → DimProduct)
├── CustomerKey     (FK → DimCustomer)
├── RegionKey       (FK → DimRegion)
├── SalesPersonKey  (FK → DimSalesPerson)
├── Quantity
├── UnitPrice
└── Revenue

-- DIMENSION TABLES (points of the star) — ALL denormalised
DimProduct
├── ProductKey (PK)
├── ProductName
├── Category          ← stored directly in DimProduct
├── SubCategory       ← stored directly in DimProduct
├── Brand             ← stored directly in DimProduct
└── UnitCost

DimDate
├── DateKey (PK)
├── Date, Year, Quarter, Month, Week, Weekday
└── IsHoliday, FiscalYear, FiscalQuarter

DimCustomer
├── CustomerKey (PK)
├── CustomerName, Email, Phone
├── City, State, Country  ← all geography in one table
└── Segment, Tier

Key characteristic: Dimension tables are denormalised — all attributes for a subject (product, customer, geography) are stored in a single flat table. This means some data is repeated (e.g., "Electronics" appears in thousands of rows), but that repetition is intentional and beneficial in VertiPaq because of its columnar compression.

4. Snowflake Schema

In a Snowflake Schema, dimension tables are normalised — broken into smaller sub-dimension tables. Instead of one DimProduct table containing Category and SubCategory, those become separate tables linked to DimProduct.

-- FACT TABLE (same as before)
FactSales
├── SaleID
├── DateKey         (FK → DimDate)
├── ProductKey      (FK → DimProduct)
├── CustomerKey     (FK → DimCustomer)
├── Quantity, UnitPrice, Revenue

-- NORMALISED DIMENSION CHAIN (snowflake "branches")
DimProduct
├── ProductKey (PK)
├── ProductName
├── SubCategoryKey  (FK → DimSubCategory)  ← linked, not stored
└── UnitCost

DimSubCategory                             ← separate table
├── SubCategoryKey (PK)
├── SubCategoryName
└── CategoryKey    (FK → DimCategory)      ← another level up

DimCategory                                ← another separate table
├── CategoryKey (PK)
└── CategoryName

-- Geography also split into multiple tables
DimCustomer
├── CustomerKey (PK)
├── CustomerName, Email
└── CityKey     (FK → DimCity)

DimCity → DimState → DimCountry            ← chain of 3 tables

Key characteristic: Data is normalised — no redundancy, smaller storage footprint. However, every filter on Category must now travel through multiple table relationships to reach the fact table.

5. Side-by-Side Comparison

6. Performance in Power BI — Why Star Schema Wins

Power BI's VertiPaq engine is optimised for the Star Schema pattern. Here is why:

Filter Propagation

When a user selects "Electronics" from a slicer:

• Star Schema: Filter hits DimProduct → travels one relationship → filters FactSales. One hop.
• Snowflake Schema: Filter hits DimCategory → travels to DimSubCategory → travels to DimProduct → travels to FactSales. Three hops.

Each additional relationship hop adds latency. On large datasets (10M+ rows), this difference is measurable in seconds.

DAX Measure Complexity

Consider a simple measure — Revenue for a selected Category:

-- Star Schema: Category is directly on DimProduct
-- No extra DAX needed — slicer on DimProduct[Category] just works

Revenue by Category =
[Total Revenue]
-- That's it. Filter context handles everything automatically.

-- Snowflake: Category is in a separate DimCategory table
-- Need CROSSFILTER or TREATAS to make the filter reach FactSales

Revenue by Category (Snowflake) =
CALCULATE(
    [Total Revenue],
    CROSSFILTER(DimSubCategory[CategoryKey], DimCategory[CategoryKey], BOTH)
)
-- More complex, harder to debug, easier to get wrong

VertiPaq Compression

VertiPaq compresses columns using dictionary encoding and run-length encoding. A denormalised DimProduct with "Electronics" repeated 500 times compresses extremely well — often better than you'd expect. The storage savings from normalisation (Snowflake) are largely negated by VertiPaq's compression, removing one of the few advantages Snowflake has in traditional databases.

7. When to Use Star Schema

Use Star Schema in Power BI in almost every situation. Specifically:

• You are building reports and dashboards for business users
• Performance is a priority (it almost always is)
• Your dimension hierarchies have 2–4 levels (Category → SubCategory → Product)
• You want simple, maintainable DAX measures
• Your dataset fits in Power BI's in-memory model (under ~1 billion rows)
• You are using Import Mode or DirectQuery with a well-designed warehouse

8. When Snowflake Schema Makes Sense

Snowflake Schema is appropriate in specific scenarios — mostly outside of Power BI's in-memory model:

• Data Warehouse layer: When designing tables in Snowflake, Redshift, BigQuery, or Azure Synapse for storage efficiency and ETL flexibility
• DirectQuery on very large tables: When the database engine handles the joins and VertiPaq is not involved
• Rapidly changing hierarchies: When category structures change frequently and you want to update one table instead of denormalising again
• Shared dimension layers: When multiple fact tables share the same sub-dimension (e.g., DimGeography shared across Sales, HR, and Logistics)

9. Real-World Example — Sales Data Model

Here is a production Star Schema for a manufacturing company's sales reporting in Power BI:

── FACT TABLE ────────────────────────────────────────────
FactSales (15 million rows)
├── SaleKey         BIGINT  (PK)
├── DateKey         INT     (FK → DimDate)
├── ProductKey      INT     (FK → DimProduct)
├── CustomerKey     INT     (FK → DimCustomer)
├── SalesPersonKey  INT     (FK → DimSalesPerson)
├── Quantity        DECIMAL
├── UnitPrice       DECIMAL
├── Discount        DECIMAL
└── Revenue         DECIMAL  (Quantity * UnitPrice * (1 - Discount))

── DIMENSION TABLES ──────────────────────────────────────
DimDate (3,650 rows — 10 years of dates)
├── DateKey, Date, DayName, DayOfWeek
├── Week, WeekStart, Month, MonthName, MonthShort
├── Quarter, Year, FiscalYear, FiscalQuarter
└── IsHoliday, IsWeekend, IsBusinessDay

DimProduct (500 rows — fully denormalised)
├── ProductKey, ProductCode, ProductName
├── Category, SubCategory, Brand          ← no separate tables needed
├── UnitCost, PackSize, IsActive
└── LaunchDate, DiscontinuedDate

DimCustomer (8,000 rows)
├── CustomerKey, CustomerCode, CustomerName
├── Email, Phone, ContactPerson
├── City, State, Region, Country          ← geography flattened in
├── Segment, Tier, AcquisitionDate
└── IsActive, CreditLimit

DimSalesPerson (45 rows)
├── SalesPersonKey, EmployeeCode, Name
├── Team, Manager, Region
└── JoinDate, TargetRevenue

── DISCONNECTED TABLES (slicers only — no relationships) ─
SlicerTopN    → {5, 10, 20, 50}
SlicerPeriod  → {"MTD", "QTD", "YTD", "Last 12 Months"}

Notice how geography (City, State, Region, Country) lives directly inside DimCustomer rather than in a separate DimGeography table. Category and SubCategory live directly in DimProduct. This is a Star Schema — flat, simple, and fast.

10. How to Flatten a Snowflake in Power Query

If your source database uses Snowflake Schema, here is how to flatten it into a Star Schema inside Power Query before the data reaches Power BI's model:

// Start with DimProduct (has SubCategoryKey but no Category name)
let
    Source         = Sql.Database("server", "database"),

    DimProduct     = Source{[Schema="dbo", Item="DimProduct"]}[Data],
    DimSubCategory = Source{[Schema="dbo", Item="DimSubCategory"]}[Data],
    DimCategory    = Source{[Schema="dbo", Item="DimCategory"]}[Data],

    // Step 1: Merge SubCategory into Product
    MergeSubCat = Table.NestedJoin(
        DimProduct, "SubCategoryKey",
        DimSubCategory, "SubCategoryKey",
        "SubCatData", JoinKind.LeftOuter
    ),
    ExpandSubCat = Table.ExpandTableColumn(
        MergeSubCat, "SubCatData",
        {"SubCategoryName", "CategoryKey"}
    ),

    // Step 2: Merge Category into the result
    MergeCat = Table.NestedJoin(
        ExpandSubCat, "CategoryKey",
        DimCategory, "CategoryKey",
        "CatData", JoinKind.LeftOuter
    ),
    ExpandCat = Table.ExpandTableColumn(
        MergeCat, "CatData",
        {"CategoryName"}
    ),

    // Step 3: Remove the FK columns — we only need the names
    FinalProduct = Table.RemoveColumns(
        ExpandCat, {"SubCategoryKey", "CategoryKey"}
    )

in
    FinalProduct

// Result: DimProduct now has ProductName, SubCategoryName,
// CategoryName all in one flat table — Star Schema ready.

11. Data Modeling Best Practices for Power BI

• Always use a dedicated Date table — create one with CALENDAR() or CALENDARAUTO() and mark it as a Date Table. Never use dates directly from the fact table.
• Use integer surrogate keys — integer relationships are faster than string relationships. Use INT or BIGINT keys, not GUIDs or natural keys.
• Keep fact tables narrow — only foreign keys and measurable numeric columns. Move descriptive text to dimension tables.
• Avoid bidirectional relationships — they cause ambiguous filter propagation and slow performance. Use CROSSFILTER() in DAX when you need it for specific measures instead.
• Hide foreign key columns — hide all FK columns (ProductKey, DateKey, etc.) from the report view. Users should filter on dimension attributes, not keys.
• Use disconnected tables for slicers — parameter slicers (Top N, Period selectors) should use unrelated tables with no relationships.
• Prefix your tables — use Fact/Dim/Slicer prefixes so the model view stays organised as it grows.

12. Decision Guide — Which Schema to Choose?