14 KiB
Definition
A Star Schema is a way of organizing analytical data where a central Fact Table connects directly to multiple Dimension Table.
It is called a Star Schema because when visualized, the structure often looks like a star:
dim_date
│
│
▼
dim_content ────── fact_viewing ────── dim_user
▲
│
│
dim_platform
The Fact Table sits at the center.
The Dimension Table surround it and provide context.
Star Schemas are commonly used in Data Warehouse and analytical systems because they make data easier to understand, query, and analyze.
Simple Mental Model
Think:
Fact Table = What happened?
Dimension Tables = Who? What? Where? When? How?
Star Schema = How we organize them together.
For example:
WHEN?
[[dim_date]]
│
│
WHAT? ─────── WHAT HAPPENED? ─────── WHO?
dim_content fact_viewing dim_user
│
│
HOW?
dim_platform
The Fact Table contains the measurable business event.
The Dimension Tables describe the event.
How It Works
Imagine a media company has a Fact Table:
fact_viewing
| content_key | user_key | platform_key | date_key | watch_seconds |
|---|---|---|---|---|
| 501 | 7821 | 3 | 20260719 | 125 |
| 502 | 9921 | 1 | 20260719 | 1800 |
| 501 | 8812 | 2 | 20260719 | 340 |
Remember from Fact Table that the Grain might be:
One row represents one viewing session for one piece of content by one user.
The Fact Table contains the event and its Measure.
Around it are dimensions.
dim_content
| content_key | title | series | genre | language |
|---|---|---|---|---|
| 501 | Episode 1 | Show A | Drama | French |
| 502 | Episode 5 | Show B | Documentary | English |
dim_platform
| platform_key | platform | device_type |
|---|---|---|
| 1 | Web | Desktop |
| 2 | iOS | Mobile |
| 3 | Roku | Connected TV |
dim_date
| date_key | date | month | quarter | year |
|---|---|---|---|---|
| 20260719 | 2026-07-19 | July | Q3 | 2026 |
The relationships allow us to combine the measurable event with descriptive context.
Conceptually:
fact_viewing.watch_seconds
+
dim_content.genre
+
dim_platform.platform
+
dim_date.month
Now we can answer:
How many hours of Drama were watched on Roku in July?
The Fact Table provides:
watch_seconds
The dimensions provide:
Drama
Roku
July
Why Is It Called a Star?
Because dimensions connect directly to the Fact Table.
dim_date
│
│
dim_content ──── fact_viewing ──── dim_user
│
│
dim_platform
Notice that:
dim_content
does not need to connect through another table before reaching:
fact_viewing
The relationships radiate outward from the center.
This creates the star-like shape.
Example
Suppose the CEO asks:
"What are our top five genres by total watch hours on Connected TV this year?"
The model could use:
[[Fact Table]]
fact_viewing
↓
SUM(watch_seconds)
[[Dimension Table]]
dim_content
↓
genre
[[Dimension Table]]
dim_platform
↓
device_type = Connected TV
[[Dimension Table]]
dim_date
↓
year = 2026
The query conceptually becomes:
Sum watch seconds
Group by genre
Filter device type to Connected TV
Filter year to 2026
The Star Schema makes these relationships predictable and easy for analytical tools to navigate.
How It Fits Into the Bigger Picture
We can now connect nearly everything we've learned so far.
SOURCE SYSTEMS
GA4
JW Player
OTT
Apps
CRM
│
▼
[[ETL vs ELT]]
│
▼
[[Medallion Architecture]]
Bronze
Raw data
↓
Silver
Clean and standardized data
↓
Gold
Business-ready analytical data
│
▼
[[Star Schema]]
│
┌────┴────┐
│ │
[[Fact Table]]
+
[[Dimension Table|Dimension Tables]]
│
▼
[[Semantic Layer]]
│
▼
Power BI
│
▼
Business Users
A Star Schema can therefore be one way of organizing business-ready analytical data.
It is important to remember:
Gold Layer does not automatically mean Star Schema.
A Gold Layer can contain many types of business-ready datasets.
However, Star Schemas are commonly used for analytical workloads because they organize Facts and Dimensions in a way that works well for BI.
Star Schema vs One Giant Table
You might wonder:
Why not just put everything into one table?
For example:
| user | content | genre | platform | device | date | watch_seconds |
|---|---|---|---|---|---|---|
| A | Show A | Drama | Roku | TV | July 19 | 125 |
| B | Show A | Drama | Roku | TV | July 19 | 300 |
| C | Show A | Drama | Roku | TV | July 19 | 500 |
This can work for small datasets.
But imagine hundreds of millions of viewing events.
The values:
Show A
Drama
Roku
Connected TV
could be repeated millions of times.
With a Star Schema, the Fact Table stores keys:
content_key = 501
platform_key = 3
The descriptive information exists in the dimensions.
This also provides centralized definitions.
If Platform 3 is classified as:
Connected TV
that classification can be maintained in dim_platform instead of being independently recreated across many datasets.
Star Schema vs Snowflake Schema
You will often hear these two terms together.
A Star Schema might look like:
dim_date
│
│
dim_content ──── fact_viewing ──── dim_user
│
│
dim_platform
The dimensions connect directly to the Fact Table.
A Snowflake Schema further normalizes some dimensions.
For example, instead of:
fact_viewing
│
▼
dim_content
title
series
genre
language
you might have:
fact_viewing
│
▼
dim_content
│
├──── dim_genre
│
├──── dim_series
│
└──── dim_language
The structure begins branching outward.
Visually:
dim_genre
│
▼
dim_date ───── fact_viewing ───── dim_content ─── dim_series
│ │
│ ▼
│ dim_language
│
dim_platform
This resembles a snowflake rather than a simple star.
We'll cover Snowflake Schema separately.
For now, remember:
Star Schema = Dimensions connect directly to Facts.
Snowflake Schema = Dimensions may be broken into additional related tables.
Star Schema vs Medallion Architecture
These two concepts are easy to confuse because both describe data architecture.
But they answer completely different questions.
Medallion Architecture asks:
How refined is the data?
Bronze
↓
Silver
↓
Gold
Star Schema asks:
How is analytical data modeled?
Dimensions
↓
Fact Table
↑
Dimensions
You could therefore have:
Gold Layer
│
├── Star Schema A
│
├── Star Schema B
│
└── Other business-ready datasets
One describes data maturity.
The other describes data modeling.
Multiple Fact Tables
A data environment will usually have more than one Fact Table.
For your company, you could potentially have:
fact_viewing
fact_web_sessions
fact_ad_impressions
fact_subscriptions
These could share some dimensions.
For example:
dim_date
/ \
/ \
▼ ▼
fact_viewing fact_ad_impressions
│ │
└──── dim_content ───┘
The same dim_content could potentially describe content across multiple business processes.
A shared dimension used consistently across multiple Fact Tables is sometimes called a conformed dimension.
This becomes powerful because the organization develops common definitions.
Instead of every system having its own interpretation of:
Content
you establish a shared business representation.
This connects directly to Data Governance.
My Company / Real-World Context
A media company's viewing model could potentially look like:
dim_date
│
│
▼
dim_content ──────── fact_viewing ──────── dim_user
▲
│
┌───────────┴───────────┐
│ │
dim_platform dim_device
The Fact Table could contain measures such as:
-
Watch seconds
-
Viewing sessions
-
Starts
-
Completions
The dimensions could allow analysis by:
-
Content
-
Series
-
Genre
-
Platform
-
Device
-
Date
-
Audience attributes
Power BI could then answer:
What content generated the most watch hours?
How is viewing distributed across platforms?
Which genres perform best on Connected TV?
How has viewing changed month over month?
Are mobile users consuming different content from OTT users?
The Star Schema provides the underlying analytical structure that makes these questions easier to answer consistently.
CTO Perspective
As a CTO, you do not need to personally design every Star Schema.
You should understand whether your analytical architecture is creating consistent, reusable models or whether every report is independently rebuilding business logic.
A warning sign would be:
Power BI Report A
↓
Custom SQL
↓
Custom definition of Platform
Power BI Report B
↓
Different SQL
↓
Different definition of Platform
Power BI Report C
↓
Another transformation
↓
Another definition of Platform
A better governed architecture might be:
Governed Data Model
[[Star Schema]]
│
▼
[[Semantic Layer]]
/ | \
/ | \
▼ ▼ ▼
Report A Report B Report C
This allows multiple reports to consume consistent business definitions.
The CTO-level question isn't:
"Did you use a Star Schema?"
It is:
"Are we building reusable, governed analytical models, or is each report independently defining the business?"
That's the larger architectural issue.
Questions to Ask
-
What business process does this Star Schema represent?
-
What is the central Fact Table?
-
What is its Grain?
-
What are the Measure?
-
Which Dimension Table surround it?
-
Are dimensions shared across multiple Fact Tables?
-
Are business definitions consistent across dimensions?
-
Where is business logic defined?
-
Are Power BI reports consuming governed models?
-
Can metrics be traced through Data Lineage to their source?
-
Is the model optimized for the questions the business actually needs to answer?
Meeting Scenario
Situation:
Your two data analysts have created separate Power BI reports.
Both reports analyze video performance.
One analyst calculates viewing by joining directly to JW Player data and creates their own platform classifications.
The other uses a different dataset and maintains a separate mapping.
The reports now disagree on viewing by platform.
The discussion starts focusing on:
"Which Power BI report is correct?"
Possible response:
"Before we troubleshoot the individual reports, I think we should look at the underlying data model. Are both reports consuming the same governed Fact Table and platform dimension, or are we defining those relationships independently in each report?"
If they're independent:
"Then the issue may be architectural rather than a Power BI issue. We should establish the governed definition once in the analytical model and have both reports consume the same structure."
You have now reframed the problem:
Wrong question:
Which Power BI report is right?
↓
Better question:
Why can two reports define
the same business concept differently?
That's the connection between Star Schema, Semantic Layer, and Data Governance.
Key Takeaways
-
A Star Schema organizes analytical data around a central Fact Table and surrounding Dimension Table.
-
The Fact Table represents measurable business events.
-
Dimensions provide descriptive context.
-
Grain defines what one row in the Fact Table represents.
-
Star Schemas make analytical data easier to understand and query.
-
Medallion Architecture describes data refinement; Star Schema describes data modeling.
-
A Gold Layer can contain Star Schemas, but Gold does not automatically mean Star Schema.
-
Multiple Fact Tables can share dimensions.
-
Shared, governed dimensions help maintain consistent business definitions.
-
Star Schemas can support reusable analytical models instead of rebuilding logic independently in every Power BI report.
Related Concepts
-
Fact Table
-
Dimension Table
-
Grain
-
Measure
-
Snowflake Schema
-
Medallion Architecture
-
Data Warehouse
-
Semantic Layer
-
Data Governance
-
Data Lineage
-
Conformed Dimension
For the next lesson, I recommend Lesson 7: Snowflake Schema. It's the natural comparison while Star Schema is fresh in your mind. After that, I would move to Semantic Layer, because that will connect the entire architecture you've learned so far directly to Power BI and to your responsibility as the manager of the data team.