Data Warehouse Design — Star Schema & Snowflake Schema

In this tutorial, you'll learn about Data Warehouse Design. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

Data warehouse design is the process of structuring data for analytical querying using dimensional modeling techniques like star schemas and snowflake schemas, optimized for reporting and business intelligence.

What You'll Learn

By the end of this tutorial, you'll understand star and snowflake schemas, fact vs dimension tables, slowly changing dimensions (SCD), OLAP vs OLTP architecture, and how to design a sales warehouse using SQL and Python.

Why It Matters

Business decisions depend on data from multiple systems — sales, marketing, inventory, customer support. A well-designed data warehouse brings this data together in one place, structured for fast analytical queries. A bad design leads to slow dashboards, confusing data models, and fragile reports. DodaTech uses a star-schema warehouse to analyze Doda Browser usage patterns across millions of users.

Real-World Use

Amazon's warehouse processes petabytes of sales data using star schemas for inventory analytics. Spotify models listening history with fact tables containing billions of rows joined to artist and track dimensions. Airbnb's data warehouse uses dimensional modeling across 10,000+ datasets.

flowchart LR
  A[Etl Pipelines] --> B[Data Warehouse Design]
  B --> C{You Are Here}
  C --> D[Star Schema]
  C --> E[Snowflake Schema]
  D --> F[Fact Tables]
  D --> G[Dimension Tables]
  E --> H[Normalized Dimens]

What Is a Data Warehouse?

Think of a data warehouse like a library's reference section. Transactional databases (OLTP) are like a busy checkout counter — fast for single lookups, but terrible for research. A data warehouse (OLAP) is the reading room — organized, indexed, and designed for complex queries across many books at once.

Fact vs Dimension Tables

Fact Tables

Fact tables store measurements — numeric values you aggregate. Every row is an event.

• Sales fact: order_id, product_id, customer_id, date_id, quantity, amount
• Web fact: page_view_id, user_id, page_id, timestamp, duration_seconds
• Inventory fact: product_id, warehouse_id, date_id, units_on_hand

Dimension Tables

Dimension tables store descriptive attributes — the "who, what, where, when" context.

• Customer dimension: customer_id, name, city, segment, first_purchase_date
• Product dimension: product_id, name, category, price, supplier
• Date dimension: date_id, date, year, month, quarter, day_of_week, is_holiday

The Relationship

Fact tables have foreign keys pointing to dimension tables. The fact contains the numbers; dimensions provide the context.

-- Typical analytical query joining fact and dimensions
SELECT
  d.year,
  d.quarter,
  p.category,
  SUM(s.quantity * s.unit_price) AS total_revenue
FROM sales_fact s
JOIN date_dim d ON s.date_id = d.date_id
JOIN product_dim p ON s.product_id = p.product_id
WHERE d.year = 2026
GROUP BY d.year, d.quarter, p.category
ORDER BY d.quarter, total_revenue DESC;

Star Schema vs Snowflake Schema

flowchart LR
  subgraph "Star Schema"
    direction TB
    F1[Sales Fact] --- D1[Date Dim]
    F1 --- D2[Product Dim]
    F1 --- D3[Customer Dim]
    F1 --- D4[Store Dim]
  end
  subgraph "Snowflake Schema"
    direction TB
    F2[Sales Fact] --- D5[Date Dim]
    F2 --- D6[Product Dim]
    F2 --- D7[Customer Dim]
    F2 --- D8[Store Dim]
    D6 --- S1[Category Dim]
    D7 --- S2[City Dim]
    D8 --- S3[Region Dim]
  end

Designing a Sales Warehouse Schema

Let's design a star schema for an e-commerce company.

-- sales_warehouse.sql
-- Star schema for e-commerce sales analytics
CREATE TABLE dim_date (
    date_id INTEGER PRIMARY KEY,
    full_date DATE NOT NULL,
    year INTEGER NOT NULL,
    quarter INTEGER NOT NULL,
    month INTEGER NOT NULL,
    month_name TEXT NOT NULL,
    day_of_week INTEGER NOT NULL,
    is_weekend BOOLEAN DEFAULT FALSE,
    is_holiday BOOLEAN DEFAULT FALSE
);

CREATE TABLE dim_customer (
    customer_id INTEGER PRIMARY KEY,
    full_name TEXT NOT NULL,
    email TEXT,
    city TEXT,
    state TEXT,
    country TEXT,
    segment TEXT,
    first_purchase_date DATE,
    customer_tenure_days INTEGER
);

CREATE TABLE dim_product (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT,
    subcategory TEXT,
    unit_price DECIMAL(10,2),
    cost DECIMAL(10,2),
    supplier TEXT,
    active BOOLEAN DEFAULT TRUE
);

CREATE TABLE dim_store (
    store_id INTEGER PRIMARY KEY,
    store_name TEXT NOT NULL,
    location TEXT,
    region TEXT,
    manager_name TEXT,
    open_date DATE
);

CREATE TABLE fact_sales (
    sales_id INTEGER PRIMARY KEY,
    order_id TEXT NOT NULL,
    date_id INTEGER REFERENCES dim_date(date_id),
    customer_id INTEGER REFERENCES dim_customer(customer_id),
    product_id INTEGER REFERENCES dim_product(product_id),
    store_id INTEGER REFERENCES dim_store(store_id),
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2),
    discount DECIMAL(5,2) DEFAULT 0,
    total_amount DECIMAL(12,2),
    order_status TEXT
);

CREATE INDEX idx_sales_date ON fact_sales(date_id);
CREATE INDEX idx_sales_customer ON fact_sales(customer_id);
CREATE INDEX idx_sales_product ON fact_sales(product_id);

Simulating Warehouse Queries

# warehouse_demo.py
from datetime import date, timedelta
import random
from collections import defaultdict

customers = [
    {"id": 1, "name": "Alice", "segment": "Consumer"},
    {"id": 2, "name": "Bob", "segment": "Corporate"},
    {"id": 3, "name": "Charlie", "segment": "Home Office"},
    {"id": 4, "name": "Diana", "segment": "Consumer"},
]

products = [
    {"id": 1, "name": "Laptop", "category": "Electronics", "price": 999.99},
    {"id": 2, "name": "Mouse", "category": "Electronics", "price": 29.99},
    {"id": 3, "name": "Desk Chair", "category": "Furniture", "price": 249.99},
    {"id": 4, "name": "Notebook", "category": "Office Supplies", "price": 4.99},
]

def generate_sales_fact(rows=100):
    start_date = date(2026, 1, 1)
    sales = []
    for i in range(rows):
        day_offset = random.randint(0, 180)
        customer = random.choice(customers)
        product = random.choice(products)
        quantity = random.randint(1, 5)
        sales.append({
            "sales_id": i + 1,
            "date_id": (start_date + timedelta(days=day_offset)).strftime("%Y%m%d"),
            "customer_id": customer["id"],
            "product_id": product["id"],
            "quantity": quantity,
            "unit_price": product["price"],
            "total_amount": round(quantity * product["price"], 2),
            "customer_segment": customer["segment"],
            "product_category": product["category"],
        })
    return sales

sales = generate_sales_fact(20)

def revenue_by_segment(sales):
    segment_rev = defaultdict(float)
    for s in sales:
        segment_rev[s["customer_segment"]] += s["total_amount"]
    return dict(segment_rev)

def revenue_by_category(sales):
    cat_rev = defaultdict(float)
    count = defaultdict(int)
    for s in sales:
        cat_rev[s["product_category"]] += s["total_amount"]
        count[s["product_category"]] += s["quantity"]
    return {k: {"revenue": round(v, 2), "units_sold": count[k]} for k, v in cat_rev.items()}

print("=== Revenue by Customer Segment ===")
for seg, rev in sorted(revenue_by_segment(sales).items()):
    print(f"  {seg:<15} ${rev:>8.2f}")

print("\n=== Revenue by Product Category ===")
for cat, stats in sorted(revenue_by_category(sales).items()):
    print(f"  {cat:<20} ${stats['revenue']:>8.2f} ({stats['units_sold']} units)")

Expected output:

=== Revenue by Customer Segment ===
  Consumer        $ 3533.58
  Corporate       $ 4099.58
  Home Office     $ 2539.86

=== Revenue by Product Category ===
  Electronics            $ 7529.60 (28 units)
  Furniture              $ 2749.89 (11 units)
  Office Supplies        $  24.95 (5 units)

Slowly Changing Dimensions (SCD)

Dimension attributes change over time. A customer moves, a product category changes. SCD strategies handle these changes:

class SCDType2:
    """Slowly Changing Dimension Type 2 implementation."""
    def __init__(self):
        self.records = {}
        self.version = {}

    def insert(self, natural_key, attributes, effective_date):
        if natural_key not in self.records:
            self.version[natural_key] = 0
        self.version[natural_key] += 1
        v = self.version[natural_key]
        record = {
            "natural_key": natural_key,
            "version": v,
            "attributes": attributes,
            "effective_date": effective_date,
            "end_date": None,
            "is_current": True,
        }
        # Close previous version
        if natural_key in self.records:
            prev = self.records[natural_key][-1]
            prev["end_date"] = effective_date
            prev["is_current"] = False
        self.records.setdefault(natural_key, []).append(record)
        return record

scd = SCDType2()
scd.insert("C001", {"city": "NYC", "tier": "gold"}, "2026-01-01")
scd.insert("C001", {"city": "SF", "tier": "platinum"}, "2026-06-01")
print("SCD Type 2 history for C001:")
for v in scd.records["C001"]:
    print(f"  v{v['version']}: {v['attributes']} [{v['effective_date']} - {v['end_date'] or 'now'}] current={v['is_current']}")

Expected output:

SCD Type 2 history for C001:
  v1: {'city': 'NYC', 'tier': 'gold'} [2026-01-01 - 2026-06-01] current=False
  v2: {'city': 'SF', 'tier': 'platinum'} [2026-06-01 - now] current=True

Cloud Warehouse Design Patterns

Snowflake Design

Snowflake separates storage and compute. Design tables with clustering keys on frequently filtered columns:

-- Snowflake: clustering on date for partition pruning
CREATE OR REPLACE TABLE fact_sales (
    sale_id INTEGER AUTOINCREMENT,
    date_id DATE,
    product_id INTEGER,
    customer_id INTEGER,
    amount DECIMAL(12,2)
)
CLUSTER BY (date_id, product_id);

BigQuery Design

BigQuery is columnar and Serverless. Design for slot usage with Partitioning and clustering:

-- BigQuery: partition by date, cluster by category
CREATE OR REPLACE TABLE `project.sales.fact_sales`
PARTITION BY DATE(date_id)
CLUSTER BY product_category
AS
SELECT * FROM staging_sales;

Common Mistakes

1. Treating a Warehouse Like a Transactional Database

Don't run frequent single-row updates on a warehouse. They're designed for bulk loads and analytical scans. Use OLTP databases for operational queries.

2. Ignoring Date Dimensions

A proper date dimension with year, quarter, month, week, day-of-week enables time-based analysis without complex date functions. Pre-compute it.

3. Over-Normalizing Dimensions

Snowflake schemas reduce storage but increase join complexity. For most warehouses, star schemas are faster and simpler. Normalize only when dimension hierarchies are complex.

4. Not Partitioning Large Tables

Tables with billions of rows need Partitioning by date or region. Without it, every query scans the entire table, wasting time and money (especially in BigQuery).

5. Loading Unnecessary Data

Every column you load costs storage and query time. Load only columns you actually analyze. Archive raw data in a data lake if needed later.

6. Forgetting About Data Types

Using TEXT for dates or prices leads to broken queries and bad results. Enforce proper types (DATE, DECIMAL, INTEGER) during the load process.

Practice Questions

1. What is the difference between a fact table and a dimension table? Fact tables store measurable, numeric data about business events (sales, clicks). Dimension tables store descriptive attributes (customer name, product category) that provide context for the facts.

2. What is a star schema? A schema design with one central fact table surrounded by dimension tables. It's denormalized, simple to understand, and optimized for analytical queries with fewer joins.

3. What is SCD Type 2 and when would you use it? SCD Type 2 tracks changes by creating a new row for each change with version numbers and effective dates. Use it when you need full historical tracking of dimension attributes.

4. When would you choose a snowflake schema over a star schema? When dimension hierarchies are deep and complex (e.g., product → subcategory → category → department), storage costs are a concern, or you want to avoid data redundancy in highly normalized environments.

5. Challenge: Design a fact and dimension model for a blog analytics system tracking page views, time on page, and visitor countries. Fact table: page_view_fact (view_id, date_id, page_id, <a href="/design-patterns/visitor/">Visitor</a>_id, time_on_page_seconds, country_id). Dimensions: dim_date, dim_page (url, title, author), dim_<a href="/design-patterns/visitor/">Visitor</a> (cookie_id, browser, device), dim_country.

Mini Project: Date Dimension Generator

# date_dimension.py
from datetime import date, timedelta

def generate_date_dim(start=date(2020, 1, 1), end=date(2027, 12, 31)):
    dates = []
    current = start
    while current <= end:
        dates.append({
            "date_id": int(current.strftime("%Y%m%d")),
            "full_date": current.isoformat(),
            "year": current.year,
            "quarter": (current.month - 1) // 3 + 1,
            "month": current.month,
            "month_name": current.strftime("%B"),
            "day_of_month": current.day,
            "day_of_week": current.weekday(),
            "day_name": current.strftime("%A"),
            "is_weekend": current.weekday() >= 5,
            "week_of_year": current.isocalendar()[1],
        })
        current += timedelta(days=1)
    return dates

dates = generate_date_dim()
print("=== DATE DIMENSION SAMPLE ===")
print(f"{'date_id':<10} {'full_date':<14} {'year':<6} {'month':<8} {'day_name':<12}")
print("-" * 50)
for d in dates[:10]:
    print(f"{d['date_id']:<10} {d['full_date']:<14} {d['year']:<6} {d['month_name']:<8} {d['day_name']:<12}")
print(f"... ({len(dates)} total dates generated)")

Expected output:

=== DATE DIMENSION SAMPLE ===
date_id    full_date      year   month    day_name
--------------------------------------------------
20200101   2020-01-01     2020   January  Wednesday
20200102   2020-01-02     2020   January  Thursday
... (2922 total dates generated)

FAQ

Related Concepts

What's Next

You now understand data warehouse design fundamentals! Next, explore Data Lake vs Warehouse for understanding when to use each, then learn about Apache Airflow and dbt for orchestrating and transforming warehouse data.

• Practice daily — Design a star schema for a domain you know (fitness tracking, music library, etc.)
• Build a project — Generate a date dimension and load it into a SQLite database
• Explore related topics — Check out Snowflake vs BigQuery comparisons

Remember: every expert was once a beginner. Keep coding!