Chapter 30: Star vs Snowflake Schema

Before a single query runs against a data warehouse, someone has to decide how the data is organized. That decision, the schema design, determines how fast analysts can query it, how much storage it consumes, and how hard it is to maintain as the business changes. Two patterns dominate warehouse design: the star schema and the snowflake schema. Understanding both, and knowing when to use which, is one of the most practical skills a data engineer brings to the table.

But before schema design, there is a more fundamental distinction to understand: the difference between the systems that generate data and the systems built to analyze it.

---

30.1 OLTP vs OLAP

Every data system in an organization falls into one of two categories. OLTP (Online Transaction Processing) systems handle day-to-day operations: recording a sale, processing a payment, updating a customer's address. OLAP (Online Analytical Processing) systems exist to analyze what those operations produced: total revenue this quarter, which product category is declining, which customers are at risk of churning.

The two systems have fundamentally different goals, and those goals drive completely different design decisions.

OLTP: Built for Writing

An OLTP system is an operational database. It is designed for fast, concurrent writes on individual rows. When a customer places an order, the database must insert a row in orders, insert rows in order_items, update inventory, and confirm the transaction, all in milliseconds, while thousands of other customers are doing the same thing simultaneously.

To support this, OLTP databases are highly normalized: data is split across many tables to eliminate redundancy and ensure that a change to any piece of information only has to be made in one place. This keeps writes fast and consistent.

OLAP: Built for Reading

An OLAP system is a data warehouse. It is designed for analytical queries that read millions of rows at once: aggregating, filtering, grouping, comparing. When an analyst asks "what were our top 10 products by revenue in Q3?", the query might scan 50 million rows, join five tables, and return a result the analyst then explores further.

Normalization works against this use case, because more tables mean more joins, and joins are expensive at scale. Warehouses denormalize deliberately, trading storage for query speed.

The Difference Table

Dimension	OLTP	OLAP
Purpose	Run the business (operations)	Analyze the business (analytics)
Query type	Short, fast reads and writes on few rows	Complex reads across millions of rows
Operations	INSERT, UPDATE, DELETE	SELECT, GROUP BY, aggregate
Data volume	Gigabytes	Terabytes to Petabytes
Design	Highly normalized (3NF)	Denormalized (star / snowflake schema)
Schema changes	Rare, stability is critical	More flexible, evolves with reporting needs
Concurrency	Thousands of concurrent small transactions	Fewer concurrent heavy queries
Response time	Milliseconds	Seconds to minutes
Data currency	Real-time	Historical + near real-time
Users	Applications, services	Analysts, data scientists, executives
Examples	PostgreSQL, MySQL, SQL Server	Snowflake, BigQuery, Redshift, Synapse

Interview Angle "What is the difference between OLTP and OLAP?" is one of the most common opening questions in a data engineering interview. The expected answer goes beyond "one is for transactions, one is for analytics". It covers normalization vs denormalization, the nature of the queries each handles, data volume, and why you would never run heavy analytical queries directly against a production OLTP database.

Why You Cannot Analyze Directly Against OLTP

Running analytical queries against an operational database causes two problems. First, heavy analytical reads slow down the application, because every analyst running a report competes with every customer placing an order. Second, OLTP normalization means analysts need to join 8 to 12 tables to answer a single business question, which is both slow and fragile when the schema changes.

Data warehouses exist precisely to solve this. Data is extracted from OLTP systems, transformed into an analytical-friendly schema, and loaded into a warehouse where analysts can run any query without affecting the operational system. That process is ETL, or ELT when transformation happens inside the warehouse itself.

OLTP Database                             Data Warehouse
(operational, normalized)                 (analytical, denormalized)
         │                                        │
  ┌──────┴──────┐      ETL / ELT          ┌───────┴───────┐
  │  orders     │ ──────────────────────▶ │  fact_orders  │
  │  customers  │                         │  dim_customer │
  │  products   │                         │  dim_product  │
  │  inventory  │                         │  dim_date     │
  └─────────────┘                         └───────────────┘
  Fast writes, many tables,               Fast reads, fewer joins,
  real-time accuracy                      historical depth

---

30.2 Dimensional Modeling: The Foundation

Data warehouses are not structured like transactional databases. A transactional database (an OLTP system) is optimized for writing: fast inserts, updates, and deletes, with data split across many normalized tables to minimize redundancy. A warehouse is optimized for reading: analytical queries, aggregations, and reports across millions of rows.

Dimensional modeling is the design methodology built for that analytical use case. It was formalized by Ralph Kimball in the 1990s and is still the dominant approach for warehouse design today. It organizes data into two types of tables: fact tables and dimension tables.

Fact Tables

A fact table stores measurable events: the things a business wants to track and quantify. Each row is one event: a sale, a page view, a transaction, a shipment. Fact tables tend to be wide (many columns) and very tall (millions or billions of rows).

The columns in a fact table are:

• Measure columns: the numbers being tracked, such as quantity_sold, revenue, discount_amount
• Foreign key columns: references to dimension tables, such as customer_id, product_id, date_id

Dimension Tables

A dimension table stores descriptive context about the events in the fact table. Where did the sale happen? Who bought it? What product was it? When? Dimension tables provide the labels, categories, and attributes that make facts meaningful.

Fact table row:  customer_id=42, product_id=7, date_id=20240315, quantity=2, revenue=59.98

Dimension tables:  customer 42 = "Alice Johnson, New York, Premium tier"
                   product 7  = "Wireless Headphones, Electronics, Brand X"
                   date 20240315 = "March 15 2024, Q1, Friday"

The fact table records the what happened and how much. The dimension tables answer who, what, where, when, and why.

---

30.3 Star Schema

The star schema is the simpler and more common of the two patterns. A single fact table sits at the center, and dimension tables connect to it directly, one join per dimension. When drawn on paper, it looks like a star.

Structure

                    ┌──────────────────┐
                    │  dim_date        │
                    │  date_id (PK)    │
                    │  date            │
                    │  month           │
                    │  quarter         │
                    │  year            │
                    └────────┬─────────┘
                             │
┌──────────────┐             │             ┌────────────────┐
│ dim_customer │             │             │  dim_product   │
│ customer_id  │             │             │  product_id    │
│ first_name   │             │             │  product_name  │
│ last_name    │             │             │  category      │
│ city         ├─────────────┼─────────────┤  brand         │
│ state        │             │             │  unit_price    │
│ segment      │             │             └────────────────┘
└──────────────┘    ┌────────▼──────────┐
                    │  fact_sales       │
                    │  sale_id (PK)     │
                    │  date_id (FK)     │
                    │  customer_id (FK) │
                    │  product_id (FK)  │
                    │  store_id (FK)    │
                    │  quantity         │
                    │  revenue          │
                    │  discount         │
                    └────────┬──────────┘
                             │
                    ┌────────▼──────────┐
                    │  dim_store        │
                    │  store_id (PK)    │
                    │  store_name       │
                    │  city             │
                    │  state            │
                    │  region           │
                    └───────────────────┘

Each dimension connects directly to the fact table. dim_customer holds everything you want to know about a customer in one flat table: first name, last name, city, state, segment. No further joins needed.

A Star Schema Query

SELECT
    d.year,
    d.quarter,
    p.category,
    SUM(f.revenue) AS total_revenue,
    COUNT(DISTINCT f.sale_id) AS total_orders
FROM fact_sales AS f
JOIN dim_date     AS d ON f.date_id     = d.date_id
JOIN dim_product  AS p ON f.product_id  = p.product_id
JOIN dim_customer AS c ON f.customer_id = c.customer_id
WHERE c.state = 'CA'
  AND d.year = 2024
GROUP BY d.year, d.quarter, p.category
ORDER BY d.quarter, total_revenue DESC;

Four tables, four joins. That is the maximum depth in a star schema: one hop from the fact table to any dimension.

Strengths

• Fast queries. Fewer joins means simpler execution plans. Analytical queries run 40 to 60% faster compared to normalized alternatives, particularly for dashboards and real-time reporting.
• Simple to understand. Business analysts and BI tools navigate star schemas easily without needing to know the internals of the data model.
• Lower maintenance. An attribute change in dim_customer touches one table. No cascading updates.
• Optimized for columnar storage. Modern cloud warehouses (Snowflake, BigQuery, Redshift) use columnar compression that largely eliminates the storage penalty of denormalization.

Weaknesses

• Data redundancy. The same category name, "Electronics", is stored on every product row in dim_product. If you have 50,000 electronic products, that string is stored 50,000 times.
• Update anomalies. If a product's category is renamed, every row in dim_product needs updating.
• No hierarchy enforcement. dim_product stores category and subcategory as flat columns, so the parent-child relationship exists only by naming convention, not by database structure.

---

30.4 Snowflake Schema

The snowflake schema normalizes the dimension tables. Instead of one flat dim_product that holds category, brand, and subcategory together, each of those concepts becomes its own table, connected by foreign keys. Drawn on paper, the branching hierarchies look like a snowflake.

Structure

                    ┌──────────────┐
                    │  dim_brand   │
                    │  brand_id PK │
                    │  brand_name  │
                    └──────┬───────┘
                           │
              ┌────────────┘
              │
┌─────────────▼──────────┐     ┌────────────────┐
│  dim_product           │     │  dim_category  │
│  product_id  (PK)      ├─────▶ category_id PK │
│  product_name          │     │ category_name  │
│  brand_id    (FK)      │     │ department_id  │──▶ dim_department
│  category_id (FK)      │     └────────────────┘
│  unit_price            │
└────────────────────────┘
          │
          │ (FK)
┌─────────▼──────────┐
│  fact_sales        │
│  sale_id (PK)      │
│  product_id (FK)   │
│  customer_id (FK)  │
│  ...               │
└────────────────────┘

dim_product no longer holds category or brand names directly; it holds foreign keys to dim_category and dim_brand. Each of those can themselves point to higher-level tables. A geographic hierarchy might go: dim_city → dim_state → dim_country, three separate tables, three levels of normalization.

A Snowflake Schema Query

The same revenue-by-category question now requires more joins:

SELECT
    dc.category_name,
    dd.department_name,
    SUM(f.revenue) AS total_revenue
FROM fact_sales AS f
JOIN dim_product  AS p  ON f.product_id   = p.product_id
JOIN dim_category AS dc ON p.category_id  = dc.category_id
JOIN dim_department AS dd ON dc.department_id = dd.department_id
JOIN dim_date     AS d  ON f.date_id      = d.date_id
WHERE d.year = 2024
GROUP BY dc.category_name, dd.department_name
ORDER BY total_revenue DESC;

To answer what category a product belongs to, the query now joins fact_sales → dim_product → dim_category → dim_department. Each level of the hierarchy adds a join.

Strengths

• No data redundancy. "Electronics" is stored exactly once in dim_category. Every product that belongs to it points to that one row.
• Better data integrity. Foreign-key constraints enforce the hierarchy. An invalid category_id cannot be inserted into dim_product.
• 25 to 40% storage reduction compared to denormalized equivalents, which matters most in large-scale warehouses with many-level hierarchies.
• Compliance-friendly. Granular change tracking and audit trails are easier when data lives in one place rather than duplicated across millions of rows.

Weaknesses

• More complex queries. Every level of normalization adds a join. A five-level geographic hierarchy means five additional joins just to get a country name.
• Harder for analysts. Business users writing ad-hoc SQL need to understand the normalized relationships, and the model is harder to navigate than a flat star.
• Higher ETL complexity. Loading data into a snowflake schema requires maintaining referential integrity: parent tables must be loaded before child tables, in order.

---

30.5 Key Differences at a Glance

Feature	Star Schema	Snowflake Schema
Structure	Central fact + denormalized dimensions	Central fact + normalized dimension hierarchies
Complexity	Simple, fewer tables	Complex, many tables and foreign keys
Query performance	Faster, fewer joins (40 to 60% advantage)	Slower, traversing hierarchies adds joins
Storage	More, redundant attribute data	Less, each value stored once (25 to 40% saving)
Data integrity	Lower, redundant data can drift	Higher, FK constraints prevent anomalies
Maintenance	Easier, attribute change touches one table	Harder, changes can cascade across hierarchy
ETL complexity	Simpler, load dimensions, then facts	Complex, hierarchical load order required
Best for	Dashboards, real-time analytics, BI tools	Complex hierarchies, regulated industries, large warehouses

---

30.6 Slowly Changing Dimensions

Both schema types must handle Slowly Changing Dimensions (SCDs), the fact that dimension attributes change over time. A customer moves city. A product changes category. How should the warehouse record that?

Three common approaches:

Type	Approach	Trade-off
SCD Type 1	Overwrite the old value	Simple, but history is lost
SCD Type 2	Add a new row with an effective date range	Full history, but dimension table grows
SCD Type 3	Add a "previous value" column	Limited history, quick to query

Type 2 is the most common in production warehouses. A customer row that changes gets an end_date set on the old row and a new row inserted with start_date = today. The fact table continues to point to the correct historical version of the customer via the surrogate key. dbt's snapshot feature automates exactly this pattern, which we cover properly in Chapter 34.

---

30.7 Choosing Between Them

The decision comes down to five questions:

1. How complex are your hierarchies? If dimensions have more than two levels (geography spanning city, state, country; products spanning subcategory, category, department), snowflake schema's normalized structure handles this more cleanly.

2. Do you prioritize speed or storage? Dashboards and high-concurrency reporting favor star schema. Large-scale warehouses where storage cost is a real concern favor snowflake schema.

3. What is your team's SQL experience level? Star schema queries are simpler to write and easier for business analysts to navigate without help. Snowflake schema queries require understanding normalized relationships.

4. Are you in a regulated industry? Healthcare and financial services often require granular audit trails and strict data integrity, and snowflake schema's normalized structure and FK constraints are better suited to that.

5. Which cloud platform are you on? Modern platforms like Snowflake, BigQuery, and Redshift have join optimization and materialized views that narrow the performance gap significantly. On well-tuned platforms, snowflake schema can reach 90% of star schema query speed.

---

30.8 The Hybrid: "Starflake"

In practice, most production warehouses use neither pattern in its pure form. Hybrid designs, sometimes called starflake schemas, selectively normalize dimensions that are large, hierarchical, or frequently updated, while keeping smaller, stable dimensions denormalized.

fact_sales
├── dim_date          (flat, rarely changes)
├── dim_store         (flat, small table)
├── dim_customer      (flat, simple attributes)
└── dim_product       (normalized)
    ├── dim_category  (normalized, 3-level hierarchy)
    └── dim_brand     (normalized, updated frequently)

This approach optimizes where it matters. A date dimension with 365 rows per year never needs normalization. A product category hierarchy with five levels and millions of products benefits from it.

Interview Angle "When would you choose a star schema over a snowflake schema?" The expected answer covers the trade-off specifically: star schema for analytical speed and simplicity, snowflake schema for storage efficiency and data integrity on complex hierarchies. Mentioning that modern cloud warehouses narrow the performance gap, and that hybrid designs exist, shows real production experience rather than textbook knowledge.

---

30.9 Putting It Together: BikeStores as a Warehouse

The BikeStores transactional database from Part 1 can be modeled as a star schema warehouse:

-- Dimension: Products with category and brand (denormalized)
CREATE TABLE dim_product (
    product_key    INT IDENTITY PRIMARY KEY,
    product_id     INT,            -- source system key
    product_name   VARCHAR(255),
    brand_name     VARCHAR(100),
    category_name  VARCHAR(100),
    list_price     DECIMAL(10,2),
    effective_date DATE,
    expiry_date    DATE,
    is_current     BIT
);

-- Fact: Order line items
CREATE TABLE fact_order_items (
    order_item_key  INT IDENTITY PRIMARY KEY,
    order_id        INT,
    date_key        INT,
    product_key     INT,    -- FK to dim_product
    customer_key    INT,    -- FK to dim_customer
    store_key       INT,    -- FK to dim_store
    quantity        INT,
    list_price      DECIMAL(10,2),
    discount        DECIMAL(4,2),
    revenue         AS (list_price * quantity * (1 - discount))  -- computed
);

dim_product is denormalized: brand name and category name both live in the product row rather than in separate dim_brand and dim_category tables. This is a deliberate star schema choice, because the BikeStores data has simple two-level product classification that doesn't justify the complexity of normalized hierarchies.

---

Summary

Dimensional modeling organizes warehouse data into fact tables (measurable events) and dimension tables (descriptive context). The star schema keeps dimension tables flat and denormalized: fewer joins, faster queries, simpler SQL, but some data redundancy. The snowflake schema normalizes dimensions into hierarchies: better storage efficiency and data integrity, but more joins and more complex ETL. Modern cloud warehouses narrow the traditional performance gap through columnar storage and join optimization. Most production warehouses use hybrid designs, normalizing complex hierarchies while keeping simple dimensions flat. SCDs handle dimension attributes that change over time; Type 2 (new row per change with effective dates) is the most common pattern. The right choice depends on hierarchy complexity, query patterns, team expertise, and regulatory requirements.

---

Exercises

30.1 Draw (in ASCII or describe) a star schema for an e-commerce business with at least one fact table and four dimension tables. Label the fact measures and dimension attributes.

30.2 Take your star schema from 30.1 and convert dim_product into a snowflake schema with a separate dim_category and dim_brand table. Write the SQL CREATE TABLE statements for all three tables including foreign keys.

30.3 Write a query against the star schema you designed in 30.1 that returns total revenue by product category and customer state for the year 2024. Then rewrite it for the snowflake schema version. How many additional joins did normalization add?

30.4 A customer changes their city from "Sacramento" to "San Francisco" mid-year. You have historical orders from when they lived in Sacramento and new orders from San Francisco. Describe what SCD Type 1, Type 2, and Type 3 would each do with this change, and which would let you accurately report orders by customer location for any historical period.

30.5 The following fact table design has a problem. Identify it and explain why it violates dimensional modeling principles:

CREATE TABLE fact_sales (
    sale_id       INT PRIMARY KEY,
    customer_name VARCHAR(200),   -- stored directly
    product_name  VARCHAR(200),   -- stored directly
    category      VARCHAR(100),   -- stored directly
    sale_date     DATE,
    quantity      INT,
    revenue       DECIMAL(10,2)
);

30.6 Think About It: A colleague argues that since Snowflake (the cloud warehouse platform) has powerful join optimization, you should always use a snowflake schema, since the performance gap is gone. What is missing from this reasoning, and what other factors should influence the schema design decision beyond raw query performance?