11.3 Star Schema vs Snowflake Schema in Power BI with Example
Quick answer: the short, self-contained version of this lesson is the guide Star vs Snowflake Schema in Power BI.
Start with a problem
Raja's model chains Store, then City, then Region as separate tables. The Region slicer works, but the model is harder to explain and DAX gets longer.
Explain simply
A snowflake splits a dimension into a chain of tables. A star flattens it into one table per dimension around the fact. Power BI prefers the star.
Show the data
Snowflake level 1: Store
| Store | City ID |
|---|---|
| Kothrud | C1 |
| Baner | C1 |
| Gangapur | C2 |
| College Road | C2 |
| Rajarampuri | C3 |
About this data: This table uses columns: Store, City ID. This table uses columns: City ID, City, Region. This table uses columns: Store, City, Region. This table uses columns: Model, Tables around Orders, Hops from Orders to Region. Values shown are the lesson examples already on this page (not a new dataset).
Snowflake level 2: City
| City ID | City | Region |
|---|---|---|
| C1 | Pune | West Maharashtra |
| C2 | Nashik | North Maharashtra |
| C3 | Kolhapur | South Maharashtra |
Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv, dark_stores_maharashtra.csv (source: src/practice/).
Demonstrate
-
Power Query › select Store › Home › Merge Queries › City on City ID › Join kind Left Outer › OK.
Your result should look like this:
Store rows 5 (unchanged) -
Expand the City column › tick City and Region › remove City ID.
Your result should look like this:
Store City Region Kothrud Pune West Maharashtra Baner Pune West Maharashtra Gangapur Nashik North Maharashtra College Road Nashik North Maharashtra Rajarampuri Kolhapur South Maharashtra -
Right-click the City query › untick Enable load.
- Model view: keep one relationship, Store[Store] 1, then * Orders[Store].
Show the result
Your result should look like this (fictional sample):
| Model | Tables around Orders | Hops from Orders to Region |
|---|---|---|
| Snowflake | Store, City | 2 |
| Star | Store (flattened) | 1 |
Explain what happened
The merge copied City and Region onto each store, so the chain became one table. The numbers do not change (West ₹850, North ₹800, South ₹450), but there are fewer relationships to manage.
Explore further
- Keep a snowflake only for a clear reason, for example a very large shared level used by several facts.
Detailed reference for this lesson:
Star schema vs snowflake schema in Power BI with example — in 30 seconds
What: A star schema has one fact table in the middle and one table per dimension around it.
Why: Fewer relationships. Simpler DAX. Faster reports.
Tiny example: Product, then Sub Category, then Category (3 tables) becomes one Product table.
Star schema vs snowflake schema in Power BI with example — 30 सेकंदात
काय: Star schema मध्ये मधोमध एक fact table आणि त्याच्या भोवती प्रत्येक dimension चा एक table असतो.
का: कमी relationships. सोपा DAX. Fast reports.
छोटं उदाहरण: Product, then Sub Category, then Category (3 tables) चा एकच Product table होतो.
Star schema vs snowflake schema in Power BI with example — 30 सेकंड में
क्या: Star schema में बीच में एक fact table और उसके चारों ओर हर dimension की एक table होती है.
क्यों: कम relationships. आसान DAX. Fast reports.
छोटा उदाहरण: Product, then Sub Category, then Category (3 tables) से एक ही Product table बनती है.
First look at the data (before, then after):
Same before / after tables as text
Before — Product
| Product ID | Product | Sub Cat ID |
|---|---|---|
| P-021 | Gokul Milk 500 ml | S1 |
| P-041 | Misal Masala | S2 |
Before — Sub Category, then Category
| Sub Cat ID | Sub Category | Category |
|---|---|---|
| S1 | Milk | Dairy & Breakfast |
| S2 | Masala | Snacks |
Formula / click path
Power Query: Merge Product with Sub Category (Left Outer)
Expand Sub Category + Category · disable load on helper
After — One flat Product dimension (star)
| Product ID | Product | Sub Category | Category |
|---|---|---|---|
| P-021 | Gokul Milk 500 ml | Milk | Dairy & Breakfast |
| P-041 | Misal Masala | Masala | Snacks |
Do it step by step:
- Select Product in Power Query.
- Home › Merge Queries with Sub Category on Sub Cat ID. Left Outer.
- Expand Sub Category and Category.
- Right-click the helper query › untick Enable load.
- In Model view you now see one Product table joined to Orders.
एक-एक step करून बघा:
- Power Query मध्ये Product निवडा.
- Sub Cat ID वर Sub Category सोबत Home › Merge Queries. Left Outer.
- Sub Category आणि Category expand करा.
- Helper query वर right-click करा › Enable load untick करा.
- Model view मध्ये आता Orders ला जोडलेला एकच Product table दिसतो.
एक-एक step करके देखिए:
- Power Query में Product चुनिए.
- Sub Cat ID पर Sub Category के साथ Home › Merge Queries. Left Outer.
- Sub Category और Category expand कीजिए.
- Helper query पर right-click कीजिए › Enable load untick कीजिए.
- Model view में अब Orders से जुड़ी एक ही Product table दिखती है.
Star schema: one central fact table directly connected to each dimension table. The diagram looks like a star (see Figure 1.2). This is the recommended design for Power BI.
Snowflake schema: dimensions are normalised into several related tables – for example Orders, then Product, then Sub Category, then Category. The chain of tables looks like a snowflake.
OrdersProduct ID Amount | ← | ProductProduct ID Sub Category ID | ← | Sub CategorySub Category ID Category ID | ← | CategoryCategory ID Category Name |
| Star schema | Snowflake schema | |
|---|---|---|
| Structure | Denormalised dimensions (one table per dimension) | Normalised dimensions (split into sub-tables) |
| Number of relationships | Fewer | More |
| DAX complexity | Simpler | Harder (filters travel through chains) |
| Performance in Power BI | Generally better | Generally slower |
| Model readability for users | Easy – all product fields in one table | Harder – fields spread across tables |
Flatten snowflakes in Power Query
Now look at this: if the source is snowflaked, use Merge Queries in Power Query to bring Category and Sub Category into the Product table, then disable load for the helper tables. Result: a clean star schema (तारा रचना).
Star schema ha Power BI cha "golden rule" aahe. Lakshat theva: fact madhyabhagi, dimensions bajula.
Ravindra Bagale's Tip
Friends, many students keep a snowflake (Product, then Sub Category, then Category) because that is how the source delivered it. In Power BI, flatten it into one Product table in Power Query. Fewer relationships mean simpler DAX, easier hierarchies and faster reports. This matters for both exams and interviews.
Ravindra Bagale's Tip – मराठी
मित्रांनो, source ने तसंच दिलं म्हणून बरेच students snowflake (Product, then Sub Category, then Category) तसाच ठेवतात. Power BI मध्ये Power Query वापरून तो एकाच Product table मध्ये flatten करा. कमी relationships म्हणजे सोपं DAX, सोप्या hierarchies आणि जलद reports. हे exam आणि interview दोन्हीसाठी important आहे.
Ravindra Bagale's Tip – हिंदी
दोस्तों, source ने ऐसा ही दिया इसलिए बहुत से students snowflake (Product, then Sub Category, then Category) वैसे ही रखते हैं. Power BI में Power Query से उसे एक ही Product table में flatten करो. कम relationships यानी आसान DAX, आसान hierarchies और तेज़ reports. यह exam और interview दोनों के लिए ज़रूरी है.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Region total drops after flattening | Inner join dropped a store without a City | Use Left Outer; check the row count stays 5 |
| Store rows multiply after the merge | City ID not unique in City | Remove duplicates in the lookup first |
| Two City tables in the model | Left load on after merging | Disable load on the City query |
Practise independently
Practice task
Flatten a product snowflake: SubCategory (Dairy, Snacks, Fruits), then Category group (Fresh = Dairy + Fruits, Packaged = Snacks). What are the totals by group in the practice orders?
Hints
- Dairy ₹1,050, Snacks ₹650, Fruits ₹400.
Solution
Fresh ₹1,450 (Dairy ₹1,050 + Fruits ₹400); Packaged ₹650.
Think like a professional
Default to a star. If you keep a snowflake, write down why. The next developer will thank you.
Revision card
Interview card
Comparison card