12 KiB
Estimated reading time: 10–12 minutes
Definition
A Snowflake Schema is an analytical data model where a central Fact Table connects to Dimension Table, but some of those dimensions are further divided into additional related tables.
It is similar to a Star Schema, but the dimensions are more normalized, meaning descriptive information is separated into additional tables instead of being stored together in one larger dimension.
A simplified comparison:
STAR SCHEMA
dim_date
│
│
dim_content ───── fact_viewing ───── dim_user
│
│
dim_platform
Versus:
SNOWFLAKE SCHEMA
dim_genre
│
▼
dim_date ─── fact_viewing ─── dim_content ─── dim_series
│ │
│ ▼
│ dim_language
│
dim_platform
The branching dimensions create a shape resembling a snowflake.
Simple Mental Model
Think:
Star Schema = Keep dimensions together.
Snowflake Schema = Break dimensions into smaller related tables.
For example, in a Star Schema, dim_content might contain:
Content
├── Title
├── Series
├── Genre
└── Language
In a Snowflake Schema, it might become:
Content
├── Title
├── Series ID ──────→ dim_series
├── Genre ID ───────→ dim_genre
└── Language ID ────→ dim_language
The information is still available, but it is distributed across more tables.
How It Works
Let's use the media example from Star Schema.
In a Star Schema, we might have:
dim_content
| content_key | title | series | genre | language |
|---|---|---|---|---|
| 501 | Episode 1 | Show A | Drama | French |
| 502 | Episode 2 | Show A | Drama | French |
| 503 | Episode 1 | Show B | Documentary | English |
Notice the repetition:
Show A
Drama
French
appears multiple times.
A Snowflake Schema could separate this information.
dim_content
| content_key | title | series_key | genre_key | language_key |
|---|---|---|---|---|
| 501 | Episode 1 | 10 | 5 | 1 |
| 502 | Episode 2 | 10 | 5 | 1 |
| 503 | Episode 1 | 11 | 8 | 2 |
dim_series
| series_key | series_name |
|---|---|
| 10 | Show A |
| 11 | Show B |
dim_genre
| genre_key | genre_name |
|---|---|
| 5 | Drama |
| 8 | Documentary |
dim_language
| language_key | language_name |
|---|---|
| 1 | French |
| 2 | English |
Now the relationships look like:
fact_viewing
│
▼
dim_content
│
├────→ dim_series
│
├────→ dim_genre
│
└────→ dim_language
Instead of storing Drama repeatedly in dim_content, the model stores:
genre_key = 5
and dim_genre tells us:
5 = Drama
Example
Suppose the business wants to know:
"How many hours of French Drama content were watched last month?"
In a Star Schema, the query conceptually follows:
fact_viewing
│
▼
dim_content
│
├── genre = Drama
└── language = French
In a Snowflake Schema, it follows more relationships:
dim_genre
│
│ Drama
▼
fact_viewing ─── dim_content
▲
│ French
│
dim_language
Both architectures can answer the same business question.
The difference is how the descriptive data is organized.
Star Schema vs Snowflake Schema
The main difference is how dimensions are structured.
| Star Schema | Snowflake Schema | |
|---|---|---|
| Dimensions | Less normalized | More normalized |
| Number of tables | Fewer | More |
| Relationships | Simpler | More complex |
| Queries | Generally simpler | Can require more joins |
| Data duplication | More | Less |
| BI usability | Often easier | Potentially more complex |
| Maintenance | Simpler model | More structured separation |
Neither approach is automatically correct or incorrect.
The architecture should depend on the use case.
Why Use a Snowflake Schema?
There are situations where separating dimensions makes sense.
Imagine your company has:
50,000 pieces of content
but only:
20 genres
Instead of storing:
Drama
Drama
Drama
Drama
Drama
Drama
...
thousands of times, you can store:
genre_key = 5
and maintain the definition once:
dim_genre
5 = Drama
This reduces duplication.
It can also make certain data easier to maintain centrally.
For example, if the organization changes:
Children
to:
Kids & Family
the change could potentially be made in one place.
Why Not Snowflake Everything?
Because reducing duplication comes with a cost:
Complexity.
Compare:
fact_viewing
↓
dim_content
with:
fact_viewing
↓
dim_content
↓
dim_series
↓
dim_genre
More tables mean:
-
More relationships
-
More joins
-
More complexity for analysts
-
More opportunities for incorrect relationships
-
Potentially more complicated BI models
This is particularly relevant when the primary consumer is Power BI.
A model that is theoretically elegant from a database-design perspective may not necessarily be the easiest analytical model for business intelligence.
The goal isn't:
Create the most normalized architecture possible.
The goal is:
Create an architecture that reliably supports the analytical needs of the business.
Normalization
The concept behind Snowflake Schemas is closely related to Normalization.
Normalization means organizing data to reduce duplication and improve consistency.
Imagine:
Employee
Amadou | Technology | Toronto
Alice | Technology | Toronto
Bob | Technology | Toronto
The values:
Technology
Toronto
are repeated.
A more normalized structure could separate them:
Employee
↓
Department
↓
Location
Normalization is extremely common in operational databases.
However, analytical systems often deliberately accept some duplication to make queries simpler and faster.
This is one reason Star Schema is popular in analytics.
The Star Schema is typically more denormalized.
The Snowflake Schema is more normalized.
How It Fits Into the Bigger Picture
Our complete mental model is continuing to grow:
SOURCE SYSTEMS
GA4
JW Player
OTT
Apps
CRM
│
▼
[[ETL vs ELT]]
│
▼
[[Medallion Architecture]]
Bronze
Raw Data
↓
Silver
Clean Data
↓
Gold
Business-Ready Data
│
▼
Analytical Data Model
│
├──── [[Star Schema]]
│
└──── Snowflake Schema
│
▼
[[Fact Table]]
+
[[Dimension Table|Dimension Tables]]
│
▼
[[Semantic Layer]]
│
▼
Power BI
Remember that Medallion Architecture and dimensional modeling solve different problems.
Medallion Architecture answers:
How refined is the data?
Star and Snowflake Schemas answer:
How is the analytical data organized?
My Company / Real-World Context
Imagine your company has a content hierarchy:
Brand
↓
Series
↓
Season
↓
Episode
You could design a single dimension:
dim_content
content_key
episode
season
series
brand
genre
language
This would be closer to a Star Schema.
Or you could separate the hierarchy:
fact_viewing
│
▼
dim_content
│
▼
dim_season
│
▼
dim_series
│
▼
dim_brand
This is closer to a Snowflake Schema.
The second approach reduces duplication and separates the entities more explicitly.
But now imagine a Power BI analyst wants:
Viewing hours by brand.
The relationship path becomes:
fact_viewing
↓
dim_content
↓
dim_season
↓
dim_series
↓
dim_brand
instead of simply:
fact_viewing
↓
dim_content.brand
The correct architecture depends on your data, governance requirements, performance, and how the model is consumed.
CTO Perspective
As a CTO, the important question isn't:
"Should we always use Star or Snowflake?"
The better question is:
"Which model best supports our analytical requirements while remaining understandable, maintainable, and governed?"
A highly normalized model can be technically elegant but unnecessarily complex for BI users.
A highly denormalized model can be easy to consume but may introduce duplication and maintenance challenges.
Architecture is about trade-offs.
This is a recurring theme you'll encounter as a technology leader:
Simplicity
↕
Flexibility
↕
Performance
↕
Maintainability
↕
Cost
There is rarely a universally perfect architecture.
The goal is to choose the architecture that best supports the business requirements.
Questions to Ask
-
Why are we using a Snowflake Schema instead of a Star Schema?
-
What problem does the additional normalization solve?
-
Does the additional complexity provide meaningful business or technical value?
-
How many joins are required for common analytical queries?
-
Is the model easy for Power BI developers to understand?
-
Are dimensions being separated because they represent genuinely reusable business entities?
-
Could some dimensions be simplified without losing important functionality?
-
How does this affect query performance?
-
How does this affect maintainability?
-
Are shared definitions governed consistently?
-
Does the model support the questions the business actually needs to answer?
Meeting Scenario
Situation:
A data architect proposes redesigning the analytical model.
They want to separate:
dim_content
into:
dim_episode
dim_season
dim_series
dim_genre
dim_language
dim_brand
The reason given is:
"It's more normalized and therefore cleaner."
Possible response:
"I understand the normalization benefit, but before we redesign the model, I'd like to understand what problem the additional separation solves for us. Does it improve governance, maintainability, or performance enough to justify the added complexity for our analysts and Power BI models?"
If the answer is primarily:
"It's technically cleaner."
You could respond:
"Then I'd like us to compare the operational benefit against the additional complexity. If our primary use case is analytics, simplicity for the consumers of the model should also be part of the architectural decision."
This doesn't mean you're rejecting the proposal.
You're asking the team to justify an architectural decision based on business and operational outcomes, rather than technical elegance alone.
Key Takeaways
-
A Snowflake Schema is similar to a Star Schema, but dimensions are further divided into related tables.
-
Snowflake Schemas are more normalized.
-
Star Schemas are generally more denormalized.
-
Snowflake Schemas reduce data duplication but introduce additional relationships and complexity.
-
Star Schemas are often simpler for BI and analytical workloads.
-
Neither architecture is universally better.
-
The choice should depend on business requirements, maintainability, performance, governance, and usability.
-
Technical elegance alone is not sufficient justification for architectural complexity.
-
A CTO should understand the trade-off between normalization and simplicity.
Related Concepts
-
Star Schema
-
Fact Table
-
Dimension Table
-
Normalization
-
Grain
-
Measure
-
Conformed Dimension
-
Medallion Architecture
-
Data Warehouse
-
Semantic Layer
-
Data Governance
-
Data Lineage
The next lesson I recommend is Lesson 8: Semantic Layer (Semantic Layer.md). This one should be especially relevant to your actual job because it connects everything we've built so far to Power BI, governed KPIs, business definitions, and the problem of two analysts producing different answers to what appears to be the same question.