Star Schema vs Snowflake Schema in Power BI
A star schema keeps attributes on the dimension: City and State live on Store, one hop from Sales. A snowflake splits those attributes into more tables: Store points at City, City points at State. The sales numbers can match. The model is heavier to filter and to explain.
Friends! The source database arrived as Store, then City, then State. Why not leave that snowflake? Because every extra hop is another relationship for DAX to trust. How? We put City and State back on the FreshBasket Store table, keep one line to Sales, and still get Mumbai 150 and Maharashtra 190. Flatten first. Snowflake later, if a real reason appears.
मित्रांनो! स्रोत database Store, मग City, मग State अशी आली. तो snowflake तसाच का ठेवू नये? कारण प्रत्येक extra hop म्हणजे DAX साठी आणखी एक relationship. कसे? City आणि State पुन्हा FreshBasket Store टेबलवर ठेवायचे, Sales कडे एक रेषा, आणि Mumbai 150 तसेच Maharashtra 190. आधी flatten. Snowflake नंतर, खरा कारण असेल तर.
मित्रों! स्रोत database Store, फिर City, फिर State ऐसी आई. उस snowflake को वैसे ही क्यों न छोड़ें? क्योंकि हर extra hop DAX के लिए एक और relationship है. कैसे? City और State फिर FreshBasket Store टेबल पर रखें, Sales तक एक लाइन, और Mumbai 150 तथा Maharashtra 190. पहले flatten. Snowflake बाद में, जब सच में ज़रूरत हो.
Quick answer
Vertical path:
- Star: Sales in the centre. Store holds Store ID, City, and State.
- One relationship. A State slicer filters Store, then Sales.
- Snowflake: Store → City → State. Same rupees, two hops.
- Maharashtra still totals 190 if both cities sit in that state. Mumbai is still 150.
- Learners should flatten City and State onto Store in Power Query.
- Keep a snowflake only when a shared table must serve many dimensions and you can test every slicer.
Star: State column on Store → Sales
Snowflake: State → City → Store → Sales
Same shop, two shapes
Sales (the fact, either shape):
| Order | Store ID | Amount |
|---|---|---|
| A | S1 | 100 |
| B | S1 | 50 |
| C | S2 | 40 |
Star Store (attributes live here):
| Store ID | City | State |
|---|---|---|
| S1 | Mumbai | Maharashtra |
| S2 | Pune | Maharashtra |
Snowflake pieces:
| Store ID | City ID |
|---|---|
| S1 | C1 |
| S2 | C2 |
| City ID | City | State ID |
|---|---|---|
| C1 | Mumbai | MH |
| C2 | Pune | MH |
| State ID | State |
|---|---|
| MH | Maharashtra |
What a reader sees:
- Star, City = Mumbai: one hop, rows A+B, 150.
- Star, State = Maharashtra: both stores, 190.
- Snowflake, State = Maharashtra: State filters City, City filters Store, Store filters Sales. Still 190.
- The number did not get better. The path got longer.
- A missing City ID blanks the store. Two joins, two places to break.
The star schema guide shows how to build the centre and the dimensions. This page is only the choice between one hop and many.
What do I need before this guide?
- Fact and dimension tables.
- Relationships so each hop is a real line.
- Course: Star schema vs snowflake.
Before and after (look at the tables first)
Before - Snowflake hops. Same 150, three hops
आधी (Before) — Snowflake hops. तेच 150, तीन hops.
पहले (Before) — Snowflake hops. वही 150, तीन hops.
After - Star, one hop. Mumbai 150. Maharashtra 190.
नंतर (After) — Star, एक hop. Mumbai 150. Maharashtra 190.
बाद में (After) — Star, एक hop. Mumbai 150. Maharashtra 190.
How to flatten a snowflake
A snowflake sends State through City and Store before it reaches Sales. Same rupees, more hops.
Snowflake State ला City आणि Store मधून Sales पर्यंत पाठवतो. तेच रुपये, जास्त hops.
Snowflake State को City और Store से होकर Sales तक भेजता है. वही रुपये, ज़्यादा hops.
- In Power Query, start from Store (Store ID, City ID).
- Merge City onto Store using City ID. Expand City and State ID.
- Merge State onto that result using State ID. Expand State name.
- Remove the helper ID columns you do not need for the relationship.
- Right-click the City and State queries and clear Enable load if nothing else needs them in the model.
- Close and Apply. Relate Sales[Store ID] to Store[Store ID] only.
- Test Mumbai 150 and Maharashtra 190.
A star keeps City and State on Store. One relationship. Maharashtra is still 190 and Mumbai is still 150.
Star City आणि State Store वर ठेवतो. एक relationship. Maharashtra अजून 190 आणि Mumbai अजून 150.
Star City और State को Store पर रखता है. एक relationship. Maharashtra अब भी 190 और Mumbai अब भी 150.
You did not delete the source files. You stopped the extra tables from becoming extra relationships. Reference versus Duplicate is the Power Query choice when several queries must share that cleaning. The star is the modelling choice.
When a snowflake is not a mistake
- A Date table should stay its own dimension. Do not paste a calendar onto every fact. That is not the snowflake we are avoiding.
- A Product table used by Sales and by Targets can stay one dimension. That is still a star (two facts, one product).
- A true snowflake is a dimension pointing at another dimension for a label you could have kept on the first table.
- If the warehouse team owns a snowflake and you must not reshape it, document the hops and test State, City, and Store slicers. Do not pretend it is a star.
Mistakes and calm fixes
| Symptom | Likely cause | Fix |
|---|---|---|
| State slicer does nothing | The State hop was never related | Relate it, or flatten State onto Store |
| Three relationships for one label | Snowflake left as delivered | Merge in Power Query |
| City name edited in two tables | Label copied onto fact and dimension | One home for the label |
| “I need snowflake for the interview” | Saying the word without a path | Draw both shapes and the 150 / 190 test |
Ravindra Bagale's Tip
Interview line: “I prefer a star. Snowflake splits dimensions into more hops. I flatten those hops in Power Query unless a shared table truly needs to stay separate.” Got it?
Ravindra Bagale's Tip – मराठी
Interview line: “मला star आवडतो. Snowflake dimensions ला जास्त hops मध्ये तोडतो. सामायिक टेबल खरोखर वेगळी ठेवायची नसेल तर मी ते hops Power Query मध्ये flatten करतो.” समजलं का?
Ravindra Bagale's Tip – हिंदी
Interview line: “मुझे star पसंद है. Snowflake dimensions को ज़्यादा hops में बाँटता है. मैं उन hops को Power Query में flatten करता हूँ, जब तक कोई साझा टेबल सच में अलग न रहना ज़रूरी हो.” समझ में आया?
Practice task
- Sketch the snowflake on paper: Store, City, State.
- Merge City and State onto Store.
- Disable load on the helpers.
- Prove Mumbai 150 and Maharashtra 190 with one relationship.
- Say out loud how many hops the State slicer used before and after.
Got it? Star is one hop. Snowflake is extra hops for the same 150 and 190. Flatten while you learn. Next: a composite model, when the sales model is already published and you only need to add a local target. Let us go ahead.
समजलं का? Star म्हणजे एक hop. Snowflake म्हणजे त्याच 150 आणि 190 साठी extra hops. शिकताना flatten करा. पुढे: composite model, जेव्हा sales model आधीच published आहे आणि फक्त local target जोडायचा आहे. आता पुढे जाऊया.
समझ में आया? Star यानी एक hop. Snowflake यानी उसी 150 और 190 के लिए extra hops. सीखते समय flatten करें. आगे: composite model, जब sales model पहले से published हो और सिर्फ local target जोड़ना हो. अब आगे बढ़ते हैं.
Frequently asked questions
What is a star schema?
One fact in the centre and dimension tables around it, with attributes on those dimensions.
What is a snowflake?
A dimension split into more tables, such as Store, City, and State, each joined onward.
Do the totals change?
Not if the joins are correct. Maharashtra is still 190. The path is just longer.
Should beginners use snowflake?
No. Flatten into a star unless a shared table must stay separate.
Is a Date table a snowflake?
No. A separate Date dimension is normal in a star.
Course lesson?
Star schema vs snowflake, in the data modelling chapter.