Most analytics performance problems aren't about compute — they're about how the data is organized. This lesson teaches you the star schema and snowflake schema from first principles, with real SQL examples, so you can design data warehouses that are fast, intuitive, and built for self-serve analytics.
Imagine you've just landed a role on a data team, and your first task is to help the business answer a deceptively simple question: "How much revenue did we generate by product category, by region, last quarter?" You have all the raw data — orders, products, customers, stores. But when you write the SQL to answer that question, it takes 15 seconds to run, joins eight tables, and your analyst colleague says they can't figure out how to write it themselves. Something is wrong — not with the data, but with how it's organized.
That's the problem data warehouse schemas solve. A schema isn't just a technical detail for engineers to worry about; it's the foundation that determines whether your analytics is fast, intuitive, and self-serve — or slow, confusing, and gatekeeper-dependent. The two schemas you'll encounter most often in analytics engineering are the star schema and the snowflake schema, and understanding the tradeoffs between them is one of the most practical skills you can build early in your data career.
By the end of this lesson, you'll understand how both schemas work, when to choose one over the other, and how to actually implement them in a modern data warehouse using SQL. You'll also see the common mistakes teams make when designing schemas and how to avoid them.
What you'll learn:
This lesson is designed for beginners, but you'll get the most out of it if you have:
Before diving into star vs. snowflake, let's establish what a schema actually is. In everyday usage, a "schema" can mean two things:
In this lesson, we're talking about the second definition: the design pattern for organizing your analytical data. When data engineers and analytics engineers talk about choosing a schema, they mean deciding what tables to create and how those tables should relate to each other to best serve analytical queries.
Raw transactional databases are typically designed for writing data quickly — inserting orders, updating inventory, creating user records. This is called OLTP (Online Transaction Processing) design. Data warehouses are designed for reading data quickly across large volumes — aggregating millions of rows, slicing by dozens of dimensions. This is called OLAP (Online Analytical Processing) design.
The star schema and snowflake schema are both OLAP design patterns. They share a common foundation: a distinction between fact tables and dimension tables.
Key insight: The fundamental goal of analytical schema design is to make it easy to answer business questions with fast, readable SQL. Every structural decision you make should be evaluated against that goal.
Before we compare the two schema types, you need to understand the two types of tables they're both built from.
A fact table stores the measurable events that your business cares about. Think of it as the log of things that happened: sales transactions, page views, support tickets, shipments. Each row in a fact table typically represents one event.
Fact tables have two types of columns:
Here's what a basic fact table for e-commerce orders might look like:
-- fact_orders table
order_id BIGINT PRIMARY KEY,
order_date_key INT, -- FK to dim_date
customer_key INT, -- FK to dim_customer
product_key INT, -- FK to dim_product
store_key INT, -- FK to dim_store
quantity INT,
unit_price DECIMAL(10,2),
discount_amount DECIMAL(10,2),
total_revenue DECIMAL(10,2)
Notice what's not here: the customer's name, the product category, the store's city. That information lives in dimension tables. The fact table stays narrow and numeric.
A dimension table stores the descriptive attributes that give your facts context. If the fact table answers "how much?", dimension tables answer "who, what, where, and when?"
-- dim_customer table
customer_key INT PRIMARY KEY,
customer_id VARCHAR(50), -- natural key from source system
first_name VARCHAR(100),
last_name VARCHAR(100),
email VARCHAR(200),
city VARCHAR(100),
state VARCHAR(50),
country VARCHAR(50),
customer_segment VARCHAR(50) -- e.g., 'VIP', 'Standard', 'Trial'
Note: The
customer_keyis a surrogate key — an artificial numeric identifier generated by your warehouse. Thecustomer_idis the natural key from your source system (like your CRM). Using surrogate keys gives you stability even if natural keys change in the source.
Now that you understand the building blocks, let's see how star and snowflake schemas arrange them differently.
The star schema gets its name from its visual structure: one or more fact tables at the center, surrounded by dimension tables radiating outward — like points on a star.
The defining characteristic of a star schema is that dimension tables are denormalized. That's a fancy way of saying all the attributes for a concept are stored in a single flat table, even if some of those attributes could theoretically be broken out into separate tables.
Here's our e-commerce star schema:
dim_date
|
dim_store ---- fact_orders ---- dim_customer
|
dim_product
Let's build out the dim_product table for a star schema:
-- Star schema: dim_product (denormalized)
CREATE TABLE dim_product (
product_key INT PRIMARY KEY,
product_id VARCHAR(50),
product_name VARCHAR(200),
sku VARCHAR(100),
-- Category attributes flattened into this table
category_id VARCHAR(50),
category_name VARCHAR(100),
-- Subcategory attributes also flattened
subcategory_id VARCHAR(50),
subcategory_name VARCHAR(100),
-- Brand attributes flattened
brand_id VARCHAR(50),
brand_name VARCHAR(100),
brand_country VARCHAR(50),
-- Product attributes
unit_cost DECIMAL(10,2),
is_active BOOLEAN
);
Notice that category, subcategory, and brand information are all stored directly in this table. If you have 10,000 products and 50 categories, the category name is repeated thousands of times across the rows. This is the "denormalization" — deliberately storing redundant data to avoid joins.
Now, the query to answer our original question — revenue by product category by region — is clean:
SELECT
p.category_name,
c.state,
SUM(o.total_revenue) AS revenue
FROM fact_orders o
JOIN dim_product p ON o.product_key = p.product_key
JOIN dim_customer c ON o.customer_key = c.customer_key
JOIN dim_date d ON o.order_date_key = d.date_key
WHERE d.year = 2024
AND d.quarter = 4
GROUP BY p.category_name, c.state
ORDER BY revenue DESC;
Four tables, clean joins, no nesting. This is the star schema's superpower.
Tip: Star schemas are almost always the right choice for BI tools like Looker, Tableau, or Power BI. These tools often build their own join logic automatically, and they work much better with flat, wide dimension tables than with deeply normalized structures.
The snowflake schema takes the star schema and adds another layer of structure: dimension tables are normalized, meaning related attributes are broken out into their own separate tables.
The name comes from the visual: the central fact table connects to dimension tables, which then connect to their own sub-dimension tables — creating a branching, snowflake-like shape.
Here's what the same product dimension looks like in a snowflake schema:
-- Snowflake schema: normalized product hierarchy
CREATE TABLE dim_brand (
brand_key INT PRIMARY KEY,
brand_id VARCHAR(50),
brand_name VARCHAR(100),
brand_country VARCHAR(50)
);
CREATE TABLE dim_subcategory (
subcategory_key INT PRIMARY KEY,
subcategory_id VARCHAR(50),
subcategory_name VARCHAR(100),
category_key INT -- FK to dim_category
);
CREATE TABLE dim_category (
category_key INT PRIMARY KEY,
category_id VARCHAR(50),
category_name VARCHAR(100)
);
CREATE TABLE dim_product (
product_key INT PRIMARY KEY,
product_id VARCHAR(50),
product_name VARCHAR(200),
sku VARCHAR(100),
subcategory_key INT, -- FK to dim_subcategory
brand_key INT, -- FK to dim_brand
unit_cost DECIMAL(10,2),
is_active BOOLEAN
);
Now the same revenue-by-category query requires more joins:
SELECT
cat.category_name,
cust.state,
SUM(o.total_revenue) AS revenue
FROM fact_orders o
JOIN dim_product p ON o.product_key = p.product_key
JOIN dim_subcategory sub ON p.subcategory_key = sub.subcategory_key
JOIN dim_category cat ON sub.category_key = cat.category_key
JOIN dim_customer cust ON o.customer_key = cust.customer_key
JOIN dim_date d ON o.order_date_key = d.date_key
WHERE d.year = 2024
AND d.quarter = 4
GROUP BY cat.category_name, cust.state
ORDER BY revenue DESC;
Six joins instead of three. The query is harder to write, harder to read, and — in many warehouse engines — slower to execute.
Warning: A common beginner mistake is assuming the snowflake schema is "more correct" because it looks like proper database normalization. Normalization principles were designed for OLTP systems to reduce update anomalies. In analytical workloads where you're reading far more than writing, denormalization is often the right call.
Now that you've seen both schemas in action, let's be honest about when each one makes sense. This isn't a simple "star schema wins" story — there are legitimate reasons to use snowflake patterns.
Modern columnar data warehouses like Snowflake, BigQuery, and Redshift are extremely good at scanning wide tables quickly. They don't read columns you don't reference, and their query optimizers handle joins well — but additional joins still have overhead, especially at scale.
For most analytical workloads, the star schema will be faster because:
For a deeper understanding of how these warehouses handle data physically, see How Data Warehouses Actually Store Data: Columnar Storage, Partitioning, and Clustering Explained.
Snowflake schemas use less storage because they don't repeat attribute values. In our example, "Electronics" as a category name isn't stored 5,000 times — it's stored once in dim_category. For small dimension tables (which is almost always the case), this difference is negligible in modern cloud warehouses where storage is cheap.
Key insight: Storage cost differences between star and snowflake schemas are almost never a deciding factor in modern cloud warehouses. Compute cost from complex queries is almost always the bigger concern. For a full picture of how to manage these costs, see Cost Management in Cloud Data Platforms.
Here's where snowflake schemas have a genuine advantage: when an attribute changes in one place, you update it once. If "Acme Corp" rebrands to "Apex Corp," you update one row in dim_brand and every product associated with that brand immediately reflects the new name. In a star schema dim_product, you'd update thousands of rows.
However, tools like dbt handle this beautifully at the transformation layer — you can use star schema output tables that are generated from normalized source models, giving you the best of both worlds. If you're working with dbt, the dbt Fundamentals: Transform Data with SQL in Your Warehouse lesson walks through how transformation layers work.
Star schemas win decisively here. When an analyst opens a BI tool or writes an ad-hoc query, they want to find the table that has what they need and join it to the fact table. A flat dim_product table with 20 columns is far more discoverable than navigating a four-level hierarchy of dim_product → dim_subcategory → dim_category. Self-serve analytics depends on schemas that don't require a map to navigate.
Here's a simplified way to think about it:
| Situation | Recommended Approach |
|---|---|
| BI tool usage (Looker, Tableau, Power BI) | Star schema |
| High-volume analytical queries | Star schema |
| Dimension attributes change frequently | Snowflake (or hybrid) |
| Very deep hierarchies (5+ levels) | Snowflake for the hierarchy |
| Self-serve analytics for non-engineers | Star schema |
| Storage is a serious constraint | Snowflake (rarely applies in cloud) |
In practice, most production data warehouses end up as a hybrid: core dimension tables are denormalized (star schema style) for BI performance, but some deep or complex hierarchies are partially normalized. This is sometimes called a galaxy schema or fact constellation when multiple fact tables share dimension tables.
This connects closely to the broader discussion of Data Modeling for Analytics: Dimensional Modeling vs One Big Table, which covers even more radical denormalization approaches and when they're appropriate.
Let's walk through the full e-commerce example, including the date dimension — a dimension that appears in nearly every analytical schema and has its own particular design conventions.
CREATE TABLE dim_date (
date_key INT PRIMARY KEY, -- e.g., 20241015 for Oct 15 2024
full_date DATE,
year INT,
quarter INT,
month INT,
month_name VARCHAR(20),
week INT,
day_of_week INT,
day_name VARCHAR(20),
is_weekend BOOLEAN,
is_holiday BOOLEAN
);
The date dimension is always pre-populated with every date in your expected range — typically loaded once as a static table. The date_key is an integer in YYYYMMDD format, which sorts naturally and is more compact than storing a full timestamp.
Tip: Always use a dedicated date dimension rather than extracting date parts in your queries with functions like
YEAR(order_date). The date dimension allows analysts to filter byis_holidayoris_weekendeasily, and it performs better in large aggregations because the filtering happens on integers rather than computed values.
Before you build a fact table, you must define its grain — the precise level of detail each row represents. For fact_orders, you might choose:
These are very different tables. Line-item grain is usually more useful because it lets you analyze individual products within orders:
CREATE TABLE fact_order_line_items (
line_item_key BIGINT PRIMARY KEY,
order_id VARCHAR(50), -- natural key for traceability
order_date_key INT,
customer_key INT,
product_key INT,
store_key INT,
quantity INT,
unit_price DECIMAL(10,2),
discount_amount DECIMAL(10,2),
line_revenue DECIMAL(10,2), -- quantity * unit_price - discount
is_returned BOOLEAN
);
Defining grain is one of the most consequential decisions in schema design. Mismatched grain between a fact table and a dimension you're trying to join is a leading cause of incorrect analytical results.
If your data updates over time and you need to track historical attribute changes — like a customer changing their loyalty tier or a product moving to a different category — that's handled by slowly changing dimensions, which deserve their own focused treatment in Slowly Changing Dimensions in Practice: Handling Historical Data Changes in Your Warehouse.
Set up this miniature star schema in your data warehouse and run the analytical queries below. If you don't have a warehouse yet, you can use a free Snowflake or BigQuery account.
Step 1: Create and populate the dimension tables
-- Create and populate dim_date (a small sample)
CREATE TABLE dim_date (
date_key INT, full_date DATE, year INT, quarter INT, month INT,
month_name VARCHAR(20), is_weekend BOOLEAN
);
INSERT INTO dim_date VALUES
(20240101, '2024-01-01', 2024, 1, 1, 'January', FALSE),
(20240601, '2024-06-01', 2024, 2, 6, 'June', FALSE),
(20241015, '2024-10-15', 2024, 4, 10, 'October', FALSE);
-- Create and populate dim_product (star schema - flat)
CREATE TABLE dim_product (
product_key INT, product_name VARCHAR(200),
category_name VARCHAR(100), brand_name VARCHAR(100)
);
INSERT INTO dim_product VALUES
(1, 'Wireless Headphones Pro', 'Electronics', 'SoundWave'),
(2, 'Running Shoes X500', 'Footwear', 'TrailBlaze'),
(3, 'HDMI Cable 6ft', 'Electronics', 'ConnectPro');
-- Create and populate dim_customer
CREATE TABLE dim_customer (
customer_key INT, first_name VARCHAR(100),
last_name VARCHAR(100), state VARCHAR(50), customer_segment VARCHAR(50)
);
INSERT INTO dim_customer VALUES
(1, 'Maria', 'Santos', 'California', 'VIP'),
(2, 'James', 'Okafor', 'Texas', 'Standard'),
(3, 'Priya', 'Nair', 'New York', 'VIP');
Step 2: Create and populate the fact table
CREATE TABLE fact_order_line_items (
line_item_key BIGINT, order_id VARCHAR(50), order_date_key INT,
customer_key INT, product_key INT,
quantity INT, unit_price DECIMAL(10,2), line_revenue DECIMAL(10,2)
);
INSERT INTO fact_order_line_items VALUES
(1, 'ORD-001', 20240101, 1, 1, 2, 89.99, 179.98),
(2, 'ORD-001', 20240101, 1, 3, 1, 12.99, 12.99),
(3, 'ORD-002', 20240601, 2, 2, 1, 129.99, 129.99),
(4, 'ORD-003', 20241015, 3, 1, 1, 89.99, 89.99),
(5, 'ORD-003', 20241015, 3, 2, 2, 129.99, 259.98);
Step 3: Answer these questions with SQL
Write the queries yourself first, then check your logic by verifying the numbers add up to the total you'd calculate manually from the inserts.
Mistake 1: Putting measures in dimension tables
Dimension tables hold attributes, not metrics. If you find yourself putting total_revenue or order_count in a dimension table, that's a sign those values belong in the fact table or in an aggregated fact table (a separate concept).
Mistake 2: Designing grain inconsistently If your fact table is at order-level grain but you need line-item analysis, you'll either get wrong aggregations or need to rebuild the table. Define grain before you write a single column.
Mistake 3: Over-normalizing into a snowflake schema for "correctness" This is the most common schema mistake beginners make. They've learned that normalization is "good" in database design courses, and they apply it eagerly to analytical schemas. Remember: analytics optimizes for read performance and usability, not write performance or update anomaly prevention.
Mistake 4: Forgetting a date dimension Many beginners store raw timestamps in the fact table and compute date parts in queries. This works but is slower and less flexible. Build a date dimension once; use it everywhere.
Mistake 5: Not tracking the source of truth for dimension attributes
When dim_customer.state disagrees with what's in your CRM, chaos follows. Your transformation layer — often built in dbt — should have clear rules about which source wins for each attribute. Tools that enforce data lineage, like those discussed in Multi-Hop Data Lineage Tracking Across the Modern Data Stack, help trace these conflicts.
You've covered a lot of ground. Here's what you now understand:
The next step in your dimensional modeling journey is putting this into practice using a real transformation tool. Introduction to dbt walks you through how modern analytics engineers build these schemas programmatically, with version control and automated testing — rather than hand-writing DDL and hoping nothing breaks.
From there, you'll want to explore how to structure a full dbt project around these schema patterns in Building a Multi-Layer dbt Project with Staging, Intermediate, and Mart Layers, where the mart layer is where your star schema tables live.
Good schema design is one of those foundational skills that pays dividends for years — every query runs faster, every analyst works more independently, and every dashboard is more trustworthy. Now you have the mental model to design analytically.