When building a data warehouse, one of the most important decisions is how to organize the data for analytics.
You will often encounter three dimensional modeling patterns:
- Star Schema
- Snowflake Schema
- Galaxy Schema (Fact Constellation)
At first, they can seem like three completely different architectures. They are not.
They are better understood as different ways of organizing fact tables and dimension tables around analytical requirements.
The real question isn't:
"Which schema is the best?"
A better question is:
"Which schema best fits the business processes, query patterns, data relationships, and analytical requirements I have?"
This article breaks down all three approaches and provides a practical framework for choosing between them.
First: What Is a Data Warehouse Schema?
A data warehouse schema defines how analytical data is organized into tables and relationships.
Unlike an operational database, where the primary concern may be efficient transaction processing, a data warehouse is designed primarily for analysis, reporting, aggregation, and decision-making.
A dimensional warehouse generally revolves around two important table types:
Fact tables
Fact tables contain measurable business events.
Examples:
Sales
Orders
Payments
Shipments
Inventory movements
Website clicks
Transactions
A sales fact table might look like:
fact_sales
sale_id
date_id
customer_id
product_id
store_id
quantity
sales_amount
discount_amount
Dimension tables
Dimensions provide descriptive context around the facts.
For example:
dim_customer
dim_product
dim_date
dim_store
dim_employee
A product dimension might contain:
product_id
product_name
category
subcategory
brand
supplier
This allows an analyst to ask questions such as:
How much revenue did we generate from electronics in Nairobi during Q2?
The fact table provides the measurement.
The dimension tables provide the context.
2. The Most Important Concept: Grain
Before choosing a schema, define the grain of your fact table.
The grain answers:
"What does one row in this fact table represent?"
For example:
One row = one product on one customer order
or:
One row = one customer transaction
or:
One row = daily inventory balance for one product at one warehouse
This decision is extremely important.
Example
Suppose we have:
fact_sales
and define its grain as:
One row represents one product sold in one transaction.
We can then safely store:
order_id
product_id
customer_id
date_id
quantity
sales_amount
But if another developer assumes:
One row = one complete order
the same table could easily be misinterpreted.
That can lead to incorrect:
- revenue calculations
- order counts
- averages
- inventory metrics
- dashboards
Strong pointer
Never design the schema before defining the grain.
A well-defined grain is one of the foundations of reliable dimensional modeling.
. Star Schema ⭐
The star schema is one of the most common dimensional modeling patterns.
It consists of:
dim_customer
|
|
dim_product — fact_sales — dim_date
|
|
dim_store
The fact table sits at the center.
Dimension tables surround it.
This creates a shape that resembles a star.
Example Star Schema
Imagine an online store.
Fact table
CREATE TABLE fact_sales (
sale_id INT,
date_id INT,
customer_id INT,
product_id INT,
store_id INT,
quantity INT,
sales_amount DECIMAL(12,2)
);
Product dimension
CREATE TABLE dim_product (
product_id INT,
product_name VARCHAR(100),
category VARCHAR(100),
subcategory VARCHAR(100),
brand VARCHAR(100)
);
Customer dimension
CREATE TABLE dim_customer (
customer_id INT,
customer_name VARCHAR(100),
city VARCHAR(100),
country VARCHAR(100),
customer_segment VARCHAR(50)
);
Date dimension
CREATE TABLE dim_date (
date_id INT,
full_date DATE,
month INT,
month_name VARCHAR(20),
quarter INT,
year INT
);
The dimensions are relatively denormalized.
For example, the product dimension can contain:
product
↓
subcategory
↓
category
as columns in the same table:
product_id | product_name | subcategory | category
rather than creating separate tables for every level.
4. Why Use a Star Schema?
The major advantage is simplicity.
Suppose an analyst wants revenue by product category.
The query can be straightforward:
SELECT
p.category,
SUM(f.sales_amount) AS revenue
FROM fact_sales f
JOIN dim_product p
ON f.product_id = p.product_id
GROUP BY p.category;
There is only one dimension join.
This makes the model easier for:
- Data analysts
- BI developers
- Data scientists
- Reporting teams
- Business users
Microsoft's dimensional-modeling guidance describes star schema as a design optimized for analytical workloads involving filtering, grouping, sorting, and summarization.
. Advantages of Star Schema
Simple queries
Fewer joins generally make analytical SQL easier to write and understand.
Easy for BI tools
Business intelligence tools can navigate a relatively simple model more easily.
Easy for analysts
An analyst doesn't need to understand a complicated hierarchy of dimension tables just to answer a simple business question.
Good analytical performance
The relatively direct fact-to-dimension relationships can make analytical queries efficient, although actual performance depends heavily on the warehouse engine, data volume, partitioning, clustering, indexing, and query design.
Easier maintenance
Adding an attribute can often be as simple as adding a column to a dimension.
6. Disadvantages of Star Schema
Star schemas intentionally introduce some redundancy.
For example:
product_id | product | category
-----------|---------|----------
101 | Laptop | Electronics
102 | Phone | Electronics
103 | Tablet | Electronics
104 | Camera | Electronics
The word:
Electronics
is repeated.
With very large dimensions, this can increase storage and create additional maintenance considerations.
However, an important modern-data-warehouse lesson is:
Don't automatically normalize everything simply because normalization reduces duplication.
Modern analytical warehouses often make storage relatively inexpensive compared with the human and query complexity introduced by excessive normalization.
The actual trade-off depends on the platform and workload.
. Snowflake Schema ❄️
A snowflake schema starts with the same basic idea as a star schema.
The difference is that dimensions are normalized into additional related tables.
Instead of:
dim_product
product_id
product_name
subcategory
category
you might have:
dim_product
|
↓
dim_subcategory
|
↓
dim_category
Conceptually:
dim_category
|
|
dim_subcategory
|
|
fact_sales → dim_product
The dimension has been broken into multiple related tables.
Microsoft describes this as a "snowflake dimension": a normalized set of tables representing a single business entity.
. Snowflake Example
Instead of this:
dim_product
product_id
product_name
subcategory
category
we create:
Product
CREATE TABLE dim_product (
product_id INT,
product_name VARCHAR(100),
subcategory_id INT
);
Subcategory
CREATE TABLE dim_subcategory (
subcategory_id INT,
subcategory_name VARCHAR(100),
category_id INT
);
Category
CREATE TABLE dim_category (
category_id INT,
category_name VARCHAR(100)
);
Now the query requires additional joins.
SELECT
c.category_name,
SUM(f.sales_amount) AS revenue
FROM fact_sales f
JOIN dim_product p
ON f.product_id = p.product_id
JOIN dim_subcategory s
ON p.subcategory_id = s.subcategory_id
JOIN dim_category c
ON s.category_id = c.category_id
GROUP BY c.category_name;
Compare that with the star-schema query.
The snowflake model is more normalized, but the query is more complex.
. Why Use a Snowflake Schema?
Snowflaking can make sense when a dimension contains a genuine hierarchy or reusable structure that benefits from normalization.
For example:
Country
↓
State
↓
City
↓
Store
If these relationships are complex, independently maintained, or reused in meaningful ways, splitting them into related tables may be useful.
Snowflaking can also reduce repeated values in dimensions.
But don't use it simply because:
"Normalization is always better."
That is an important misconception.
A data warehouse has a different purpose from an OLTP system.
. Advantages of Snowflake Schema
Less redundancy
Repeated dimensional attributes can be stored once.
Explicit hierarchies
Relationships such as:
Category → Subcategory → Product
can be represented explicitly.
Easier centralized maintenance in some cases
If a shared hierarchy changes frequently, maintaining it in one normalized structure can sometimes be advantageous.
Useful for complex dimensions
Snowflaking can be appropriate when dimensions contain complex, reusable relationships.
. Disadvantages of Snowflake Schema
More joins
A simple analytical question may require several tables.
More complex SQL
Analysts need to understand the relationships between multiple dimension tables.
More complicated BI models
Longer relationship chains can make semantic models harder to understand and can affect filter propagation and usability. Microsoft specifically notes these considerations when snowflake designs are modeled in Power BI.
Greater modeling complexity
More tables mean more relationships, testing, documentation, and governance.
. Galaxy Schema
Now imagine that your organization doesn't have only one business process.
You have:
Sales
Inventory
Shipping
Returns
Payments
Each process may require its own fact table.
Instead of building one giant fact table, we can create multiple fact tables that share common dimensions.
This is known as a:
- Galaxy Schema
- Fact Constellation
- Fact Constellation Schema
Conceptually:
dim_date
|
|
┌──────── fact_sales ────────┐
| | |
| | |
dim_customer dim_product dim_store
| | |
| | |
└────── fact_returns ────────┘
|
|
fact_inventory
The key idea is:
Multiple fact tables share common dimensions.
This is why it is called a constellation: several stars connected through shared dimensions.
The fact-constellation concept is documented as a model where multiple fact tables share dimension tables.
. Example: An E-Commerce Galaxy
Imagine an e-commerce company.
We have three business processes:
Sales
fact_sales
Measures:
quantity
sales_amount
discount
Returns
fact_returns
Measures:
return_quantity
refund_amount
Inventory
fact_inventory
Measures:
stock_quantity
inventory_value
All three can share:
dim_product
dim_date
dim_store
So:
dim_date
|
┌────────────┼────────────┐
| | |
↓ ↓ ↓
fact_sales fact_returns fact_inventory
↑ ↑ ↑
| | |
└──────── dim_product ────┘
This gives analysts the ability to analyze several business processes using common dimensions.
. The Critical Concept: Conformed Dimensions
Galaxy schemas introduce an extremely important data-warehousing concept:
Conformed dimensions
A conformed dimension is a dimension that is consistently defined and can be shared across multiple fact tables.
For example:
dim_date
should mean the same thing whether you're analyzing:
Sales
Inventory
Returns
Shipping
Likewise:
dim_product
should have consistent definitions across the business processes that use it.
This allows meaningful cross-process analysis.
For example:
Sales revenue
+
Inventory levels
+
Product returns
can be analyzed using the same:
Product
Date
Store
dimensions.
. Star vs Snowflake vs Galaxy
Here's the big picture:
| Feature | ⭐ Star | ❄️ Snowflake | 🌌 Galaxy |
|---|---|---|---|
| Main idea | One fact + dimensions | Normalized dimensions | Multiple fact tables |
| Dimensions | Mostly denormalized | More normalized | Shared/conformed |
| Fact tables | Usually one or several stars | Usually one or several | Multiple |
| Query complexity | Low | Medium | Medium–High |
| Number of joins | Fewer | More | Depends |
| Redundancy | Higher | Lower | Depends |
| Ease of use | High | Moderate | Moderate–Low |
| Best for | Straightforward analytics | Complex dimensions | Multiple business processes |
| Governance needs | Moderate | Higher | High |
| Typical use | BI/reporting | Complex hierarchies | Enterprise analytics |
These are not absolute performance rules. Actual performance depends on the warehouse engine, data size, physical design, workload, and query patterns.
. So Which One Should You Choose?
This is where data modeling becomes interesting.
Don't start with:
"I need a star schema."
Start with the business requirements.
Ask these questions.
: What business process am I modeling?
If you're modeling:
Sales
a star schema may be sufficient.
If you're modeling:
Sales
Inventory
Returns
Shipping
you may eventually need a galaxy/fact constellation.
Question 2: How complex are my dimensions?
Suppose you have:
Product
└── Subcategory
└── Category
Ask yourself:
Does this hierarchy actually need to be normalized?
If not, keeping it inside:
dim_product
may be simpler.
If the hierarchy is independently managed, reused, or genuinely complex, snowflaking may be justified.
: Who will query the data?
This is often overlooked.
If your users are:
Business analysts
BI developers
Data scientists
Managers
simplicity matters.
A model like:
fact_sales
dim_product
dim_customer
dim_date
dim_store
is easier to understand than:
fact_sales
dim_product
dim_subcategory
dim_category
dim_brand
dim_supplier
dim_region
dim_country
...
The schema isn't just a database structure.
It is also a user interface for your data.
. Question 4: How often do dimensions change?
Consider:
Product Category
If category information changes frequently and is maintained centrally, normalization may offer advantages.
But if the dimension is relatively stable, introducing several additional tables may create complexity without enough benefit.
. Question 5: Do Multiple Business Processes Share Dimensions?
Suppose:
Sales
Inventory
Returns
all need:
Product
Date
Store
That's a strong signal that you should think about a fact constellation / galaxy architecture.
Instead of duplicating dimensions, use shared conformed dimensions.
. A Practical Decision Tree
You can simplify the decision process like this:
START
|
↓
What are you modeling?
|
├── One main business process
| |
| ↓
| Start with STAR
|
└── Multiple business processes
|
↓
Shared dimensions?
|
┌───┴───┐
| |
YES NO
| |
↓ ↓
GALAXY Separate
dimensional
models
Then ask:
Are any dimensions genuinely complex or
strongly hierarchical?
|
┌───┴───┐
| |
YES NO
| |
↓ ↓
Consider Keep
SNOWFLAKE STAR
selectively
. A Better Real-World Approach: Don't Be Dogmatic
One of the biggest mistakes beginners make is thinking:
Project = one schema
In reality, you can combine approaches.
For example:
dim_date
|
|
fact_sales
/ | \
/ | \
dim_product dim_customer dim_store
|
|
dim_category
Here:
- The overall model is a star
-
dim_producthas been partially snowflaked - Multiple fact tables could turn the overall warehouse into a galaxy
This hybrid approach is often more practical than forcing an entire warehouse into one pure pattern.
Modern guidance also recognizes that production warehouses can combine star modeling with selective snowflaking rather than treating the choice as all-or-nothing.
. Strong Data Modeling Principles
Regardless of the schema you choose, remember these principles.
. Define the grain first
Write this sentence:
"One row in this fact table represents ______."
If you cannot complete that sentence clearly, stop modeling.
2. Identify the business process
Ask:
What event are we measuring?
Examples:
A sale
A shipment
A payment
A return
An inventory snapshot
. Separate facts from descriptive attributes
Facts:
quantity
revenue
cost
profit
discount
Dimensions:
customer
product
location
date
employee
4. Think about query patterns
Don't optimize only for theoretical normalization.
Ask:
What questions will analysts actually ask?
. Avoid unnecessary joins
Every additional relationship adds cognitive and technical complexity.
If an analyst needs five joins to answer:
"What were sales by product category?"
your model may deserve another look.
6. Use conformed dimensions
When multiple fact tables share:
Date
Customer
Product
Location
make sure those dimensions have consistent definitions.
. Design for the people using the warehouse
A technically elegant model that nobody understands is not necessarily a successful analytical model.
. The Interview Question You Should Be Ready For
You may hear:
"When would you choose a star schema over a snowflake schema?"
A strong answer is not:
"Star schema is faster."
That's too simplistic.
Instead:
"I would generally start with a star schema when the workload is primarily analytical and users benefit from simple fact-to-dimension relationships. I would consider snowflaking when a dimension contains a hierarchy or structure that has a clear reason to be normalized, such as independent maintenance or significant reuse. If multiple business processes need to share dimensions, I would consider a fact constellation or galaxy design. Ultimately, the choice depends on grain, query patterns, dimension complexity, governance, and the capabilities of the warehouse platform."
That's a much stronger data-engineering answer.
. The Golden Rule
If you remember only one thing from this article, remember this:
Start simple. Add complexity only when the business or data gives you a reason.
A practical starting point for many analytical workloads is:
dim_customer
|
|
dim_product — fact_sales — dim_date
|
|
dim_store
Start with a clean star.
Then ask:
Does this dimension genuinely need normalization?
↓
Snowflake it.
And:
Do I have multiple business processes
sharing common dimensions?
↓
Build a fact constellation.
The goal isn't to build the most complicated schema.
The goal is to build a schema that makes reliable analysis easier.
. Final Takeaway
The three schemas can be thought of as a progression in modeling complexity:
⭐ STAR
Simple dimensional analytics
↓
❄️ SNOWFLAKE
More normalized dimensions
↓
🌌 GALAXY
Multiple fact tables + shared dimensions
But don't interpret this as:
Star → Snowflake → Galaxy = better
It isn't a ranking.
Each solves a different modeling problem.
⭐ Star
Think:
"Keep analytics simple."
❄️ Snowflake
Think:
"This dimension has a good reason to be normalized."
🌌 Galaxy
Think:
"We have multiple business processes that need shared dimensions."
And above everything else:
Understand the business process, define the grain, understand the users and query patterns, then choose the schema.
That is the real skill behind data warehouse modeling.
Quick Cheat Sheet
STAR
├── Simple
├── Denormalized dimensions
├── Fewer joins
├── Analyst-friendly
└── Great starting point
SNOWFLAKE
├── Normalized dimensions
├── Explicit hierarchies
├── More joins
├── More complex
└── Use when normalization has a clear benefit
GALAXY
├── Multiple fact tables
├── Shared dimensions
├── Multiple business processes
├── Requires strong governance
└── Useful for broader enterprise analytics
The best data warehouse schema isn't the one with the most tables.
It's the one that allows your organization to answer important questions accurately, consistently, and efficiently.
Top comments (0)