Star Schema vs Snowflake Schema in Power BI

Source databases are usually normalised: a product table points to a subcategory table, which points to a category table, each storing its own attributes once. Brought into Power BI unchanged, that design forms a snowflake, with dimensions branching out in chains. A star flattens each dimension into one table. Microsoft's guidance is clear about which usually works better in Power BI, and the reasons are practical rather than theoretical (Microsoft Learn: star schema).

What changes between the two

A snowflake dimension is a set of normalised tables for one business entity. Modelling it that way in Power BI has costs that Microsoft lists:

• More tables to load, each with key columns just for relationships, which is less efficient for storage and performance.
• Longer filter propagation chains, which can be less efficient than filtering a single table.
• A cluttered Data pane, often with tables containing only one or two columns, which confuses report authors.
• No hierarchies across tables: a Category, Subcategory, Product hierarchy is only possible when the columns sit in one table.

Flattening into a single table removes all four problems. The trade-off is redundancy: a category name repeats on every product row, which can increase model size for very large dimensions. Microsoft's conclusion is that the benefits of a single model table generally outweigh those of multiple tables.

Side by side

Flattening a real snowflake

The Chinook sample database stores its product dimension exactly as a snowflake: tracks link to albums, albums to artists, and tracks to genres and media types (Chinook on GitHub):

import sqlite3
import urllib.request
import pandas as pd

url = "https://github.com/lerocha/chinook-database/releases/download/v1.4.5/Chinook_Sqlite.sqlite"
urllib.request.urlretrieve(url, "chinook.sqlite")
con = sqlite3.connect("chinook.sqlite")
snowflake = {name: pd.read_sql(f"SELECT * FROM {name}", con) for name in ["Track", "Album", "Artist", "Genre", "MediaType"]}
for name, table in snowflake.items():
    print(f"{name:<10}{len(table):>6} rows {table.shape[1]:>3} columns")

Track       3503 rows   9 columns
Album        347 rows   3 columns
Artist       275 rows   2 columns
Genre         25 rows   2 columns
MediaType      5 rows   2 columns

Joining them from the track outwards gives one Product dimension:

product = (snowflake["Track"][["TrackId", "Name", "AlbumId", "GenreId", "MediaTypeId"]].rename(columns={"Name": "Track"})
           .merge(snowflake["Album"].rename(columns={"Title": "Album"}), on="AlbumId", how="left")
           .merge(snowflake["Artist"].rename(columns={"Name": "Artist"}), on="ArtistId", how="left")
           .merge(snowflake["Genre"].rename(columns={"Name": "Genre"}), on="GenreId", how="left")
           .merge(snowflake["MediaType"].rename(columns={"Name": "Media type"}), on="MediaTypeId", how="left")
           [["TrackId", "Track", "Album", "Artist", "Genre", "Media type"]])
print(f"flattened Product dimension: {len(product)} rows, {product.shape[1]} columns, TrackId unique: {product['TrackId'].is_unique}")
print("missing values after the joins:", product.isna().sum().to_dict())
print(product[["Artist", "Album", "Genre"]].nunique().to_string())

flattened Product dimension: 3503 rows, 6 columns, TrackId unique: True
missing values after the joins: {'TrackId': 0, 'Track': 0, 'Album': 0, 'Artist': 0, 'Genre': 0, 'Media type': 0}
Artist    204
Album     347
Genre      25

Five tables become one with the same 3,503 rows, the key still unique and no gaps from failed joins. Readers now find Artist, Album and Genre in one place, and a Genre, Artist, Album, Track hierarchy becomes possible. The model also loses four relationships, and every filter on genre or artist now reaches the fact table in a single step.

What flattening quietly drops

The Artist table has 275 rows, but the flattened dimension contains only 204 artists. The other 71 have no albums with tracks, so they have no row at the product level. For a sales model that rarely matters, since those artists cannot have sales. But if a report must list every artist, including those without products, or if another fact table relates to artists directly, flattening loses information. Checks like the ones above, row counts before and after, key uniqueness and distinct counts per level, make such effects visible before they surprise anyone.

When a snowflake makes sense

Keeping some dimensions as separate tables is reasonable when:

• A fact table is stored at a higher grain, such as sales targets set per category. A separate category table can filter both the product-level sales and the category-level targets; Microsoft's many-to-many guidance covers relating such facts and controlling their totals with measures (Microsoft Learn: many-to-many guidance).
• The dimension is very large and the repeated attributes would add noticeably to the model's size.
• The upper level has attributes of its own that are needed independently, such as a category manager or category-level targets.

Even then, keep the chain short, hide the linking keys, and consider duplicating the few upper-level columns that reports use most into the main dimension.

Deciding for one dimension

Ask four questions about each normalised dimension:

1. Does any fact table relate to an upper level, such as category, rather than to the lowest level?
2. Do reports need members at an upper level that have no lower-level rows?
3. Is the dimension so large that repeating upper-level attributes would matter?
4. Does the upper level carry many attributes of its own?

If every answer is no, flatten it. If one is yes, keep that level as its own table, and still flatten everything else.

How to flatten in Power BI

In Power Query, start from the lowest-level table, merge in each parent table on its key with a left outer join, expand only the columns reports need, and remove the now-unneeded ID columns. Check the row count before and after each merge; if it grows, a parent table had duplicate keys. Better still, if a data warehouse or a view can present the flattened dimension, flatten it there once for every model. Star schema in Power BI walks through building the rest of the model, and relationships and cardinality covers relating the flattened dimension to the facts.

Star and snowflake schemas and data modelling best practices are part of the data modelling module of the Power BI course.

Sources

• Understand star schema and the importance for Power BI (Microsoft Learn) — Microsoft Learn
• Many-to-many relationship guidance (Microsoft Learn) — Microsoft Learn
• Chinook sample database (GitHub) — GitHub