Microsoft Power BI Desktop for Business Intelligence
A hands-on Power BI Desktop course covering data prep, modeling, DAX, and dashboard design, taught by Maven Analytics' instructors.
A star schema organizes data into one central fact table (measurable events like sales) surrounded by dimension tables (descriptive context like product, store and date), each joined directly to the fact table. A snowflake schema is a star whose dimension tables are split further into sub-tables, for example product → subcategory → category. Stars are simpler and faster to query. Snowflakes store less repeated data but need more joins.
If you've built a Power BI report on one giant flat table and watched it slow down, or seen totals that didn't add up, the cause was probably the data model rather than the visuals. Dimensional modeling is the skill that fixes that. It's also a named skill in the PL-300 exam ("create fact tables and dimension tables").
Every dimensional model starts by sorting data into two kinds of tables.
Fact tables record events you can measure:
Dimension tables describe the who, what, where and when:
A simple test: if you'd sum or average it, it belongs in a fact table. If you'd filter or group by it, it belongs in a dimension.
A chain of stores wants to analyze sales. Here's the star:
dim_date
│
dim_product ── fact_sales ── dim_store
│
dim_customer
fact_sales: date_key, product_key, store_key, customer_key, quantity, sales_amount, cost_amount
dim_product: product_key, product_name, brand, subcategory, category, unit_price
dim_store: store_key, store_name, city, region, store_size
dim_date: date_key, date, day_of_week, month, quarter, year, is_holiday
dim_customer: customer_key, segment, age_band, loyalty_tier
Notice that dim_product holds subcategory and category in the same table. "Beverages" appears on every beverage product row. That repetition is intentional: it means any product question needs just one join from fact to dimension.
In a snowflake schema, the product dimension is normalized into a chain:
dim_category ── dim_subcategory ── dim_product ── fact_sales
dim_product: product_key, product_name, brand, subcategory_keydim_subcategory: subcategory_key, subcategory_name, category_keydim_category: category_key, category_nameNow "Beverages" is stored once. But answering "sales by category" needs three joins instead of one.
| Star schema | Snowflake schema | |
|---|---|---|
| Dimension design | Denormalized (flattened) | Normalized (split into sub-tables) |
| Joins per query | Fewer, one per dimension | More, a chain per dimension |
| Query speed | Usually faster | Usually slower |
| Storage | Some repeated text | Less duplication |
| Ease of use | Easy for analysts and BI tools | Harder to navigate |
| Updating an attribute | Change many rows | Change one row |
| Best for | BI, dashboards, self-service analytics | Very large or frequently changing hierarchies, some enterprise warehouses |
Microsoft's own Power BI guidance recommends star schemas, and the reasons are practical:
If your source system gives you snowflaked tables, the usual practice is to flatten them into star dimensions in Power Query (merge subcategory and category into the product table) before loading. You get the storage benefit in the source system and the usability benefit in the report.
Many first Power BI reports load one wide table where every sale row repeats the product name, store city, customer segment and so on. It works at first, then problems appear:
Splitting that table into facts and dimensions is usually the single biggest improvement you can make to an early report. Our Power BI dashboard tutorial builds the model this way from the start.
Before designing any fact table, write down its grain, meaning exactly what one row represents. For example: "one row per product per receipt". Everything follows from that.
Mixing grains is a classic error. Imagine putting monthly sales targets (one row per store per month) into the same table as daily sales lines. Totals double-count or come out blank depending on how you slice them. Different grains belong in separate fact tables that share dimensions.
product_key) generated by the warehouse, instead of reusing the source system's product code. It protects the model when source codes change.dim_date used by sales, targets and inventory), so reports can combine them.These ideas come from Ralph Kimball's dimensional modeling approach, which is still the standard reference for analytics data modeling.
In modern warehouses (Snowflake the product, BigQuery, Databricks), storage is cheap, so the storage argument for snowflaking has mostly faded. Many teams build star schemas as the final layer of their transformation pipeline, often with dbt, and point BI tools at those tables. The joins behind every query are the ones from SQL Joins Explained, so a clean star keeps them simple.
Data Warehouse Fundamentals (DataCamp) covers facts, dimensions and schema design without heavy prerequisites. For hands-on modeling in Power BI, Microsoft Power BI Desktop for Business Intelligence (Udemy) walks through building relationships and a proper date table. Browse all Data Warehousing & Modeling courses.
Is a star schema normalized or denormalized? The dimensions are denormalized: attributes like category are repeated on each row so that a query needs fewer joins. The fact table is typically normalized, holding only keys and measures.
Is snowflake schema related to the Snowflake database? No. The snowflake schema is a modeling pattern named after its branching shape, and it's decades older than the Snowflake cloud platform. You can build either schema in any database.
When should I use a snowflake schema? When a hierarchy is very large or changes often and must be kept consistent in one place, or when an existing enterprise warehouse already uses it. For BI reporting layers, flatten to a star where you can.
Do I need a date table in a star schema? Yes, in almost every case. A dedicated date dimension enables time intelligence and consistent fiscal calendars. Power BI's time intelligence functions expect one.
A star schema puts measurable events in a fact table and descriptive context in flat dimension tables, one join away. A snowflake splits those dimensions further to reduce repetition, at the cost of more joins and complexity. For analytics and Power BI, the star is the default: simpler, faster and easier for everyone who builds reports on top of it.