How to Implement Data Warehouse Schemas Using Star and Snowflake Models

Use a star schema for fast, simple analytical queries with denormalized dimensions, or choose a snowflake schema when storage efficiency and hierarchical data integrity matter more than join performance.

Data warehouse schemas form the foundation of effective analytical querying, directly impacting both query speed and storage costs. The DataExpert-io/data-engineer-handbook repository provides hands-on guidance for building these schemas in PostgreSQL, with dedicated modules covering dimensional and fact data modeling. This guide walks through the complete implementation of star and snowflake schemas based on the handbook's structured curriculum.

Understanding Star Schema vs. Snowflake Schema

Both patterns organize data around a central fact table, but they differ fundamentally in how they handle dimension tables.

Star Schema Characteristics

The star schema places a fact table at the center, surrounded by fully denormalized dimension tables. Each dimension contains all descriptive attributes in a single flat structure, minimizing the number of joins required for typical queries.

Key advantages include:

• Faster query performance due to fewer joins
• Simpler SQL for business analysts
• Intuitive visual model that mirrors business concepts

The trade-off is increased storage from redundant data and potential update anomalies when dimension attributes change.

Snowflake Schema Characteristics

The snowflake schema normalizes dimensions into hierarchies of related tables. Complex dimensions split into sub-dimensions linked by foreign keys, creating a branching structure resembling a snowflake.

Key advantages include:

• Reduced storage through eliminated redundancy
• Stronger data integrity via normalized structures
• Easier maintenance of hierarchical attributes (e.g., product → category → brand)

The cost is additional join complexity and potentially slower query performance.

Step-by-Step Implementation in PostgreSQL

The Data Engineer Handbook structures implementation across two weeks: dimensional design followed by fact modeling. The recommended workflow follows four concrete steps.

Step 1: Define the Grain

Grain determines the level of detail each fact row represents. Common examples include individual sales transactions, daily inventory snapshots, or monthly account balances. This decision locks in what each row measures and cannot be changed without rebuilding the fact table.

Step 2: Create the Fact Table

The fact table contains:

• Foreign keys to each dimension
• Degenerate dimensions (transaction identifiers)
• Numeric metrics for aggregation

Step 3: Design Dimension Tables

Choose between star and snowflake structures based on your priorities.

Step 4: Populate via ETL

Extract source data, transform to match your grain, and load into the dimensional model.

Star Schema Implementation

This implementation follows the handbook's approach with denormalized dimensions for minimal join complexity.

Product Dimension (Denormalized)

CREATE TABLE dim_product (
    product_id   INT PRIMARY KEY,
    product_name TEXT,
    category     TEXT,
    brand        TEXT,
    price        NUMERIC
);

Customer Dimension (Denormalized)

CREATE TABLE dim_customer (
    customer_id   INT PRIMARY KEY,
    customer_name TEXT,
    city          TEXT,
    state         TEXT,
    country       TEXT
);

Sales Fact Table

CREATE TABLE fact_sales (
    sales_id      SERIAL PRIMARY KEY,
    product_id    INT REFERENCES dim_product(product_id),
    customer_id   INT REFERENCES dim_customer(customer_id),
    sales_date    DATE,
    quantity      INT,
    revenue       NUMERIC
);

Querying this schema requires only single joins to each dimension:

-- Star schema query with minimal joins
SELECT 
    p.product_name, 
    c.city,
    SUM(f.revenue) AS total_rev
FROM fact_sales f
JOIN dim_product p USING (product_id)
JOIN dim_customer c USING (customer_id)
WHERE f.sales_date >= '2024-01-01'
GROUP BY p.product_name, c.city
ORDER BY total_rev DESC;

Snowflake Schema Implementation

Converting to a snowflake structure normalizes the product hierarchy into separate tables.

Normalized Dimension Hierarchy

-- Top of hierarchy: brand
CREATE TABLE dim_brand (
    brand_id   INT PRIMARY KEY,
    brand_name TEXT
);

-- Middle layer: category references brand
CREATE TABLE dim_category (
    category_id   INT PRIMARY KEY,
    category_name TEXT,
    brand_id      INT REFERENCES dim_brand(brand_id)
);

-- Bottom layer: product references category
CREATE TABLE dim_product (
    product_id   INT PRIMARY KEY,
    product_name TEXT,
    category_id  INT REFERENCES dim_category(category_id),
    price        NUMERIC
);

The fact table remains unchanged—it still references dim_product via product_id. The difference emerges at query time:

-- Snowflake query with multiple hierarchical joins
SELECT 
    p.product_name, 
    b.brand_name,
    c.category_name,
    SUM(f.revenue) AS total_rev
FROM fact_sales f
JOIN dim_product p USING (product_id)
JOIN dim_category c USING (category_id)
JOIN dim_brand b USING (brand_id)
WHERE f.sales_date >= '2024-01-01'
GROUP BY p.product_name, b.brand_name, c.category_name
ORDER BY total_rev DESC;

same analytical output now requires traversing three dimension tables instead of one.

Performance and Design Comparison

Factor	Star Schema	Snowflake Schema
Query complexity	Simple—fewer joins	Complex—more joins
Query speed	Faster	Slower (mitigated by indexing)
Storage efficiency	Higher redundancy	Lower redundancy
Maintenance	Simpler updates	Easier hierarchical changes
ETL complexity	Straightforward	More transformation logic

Hands-On Practice with the Data Engineer Handbook

The repository provides reproducible environments for experimentation. Key resources include:

• intermediate-bootcamp/materials/1-dimensional-data-modeling/README.md — Covers dimension design fundamentals, slowly changing dimensions, and schema setup
• intermediate-bootcamp/materials/2-fact-data-modeling/README.md — Explores fact table types (transaction, periodic snapshot, accumulating snapshot) and dimension linking
• docker-compose.yml at repository root — Spins up PostgreSQL with preconfigured databases for lab work

To start experimenting:

# Clone the repository

git clone https://github.com/DataExpert-io/data-engineer-handbook.git
cd data-engineer-handbook

# Start the PostgreSQL environment

docker-compose up -d

# Connect and run schema scripts

psql -h localhost -U postgres -d data_engineering

The week 1 and week 2 modules include progressive labs where you build both schema types, load sample data, and compare EXPLAIN ANALYZE output to understand optimizer behavior.

Summary

• Star schemas optimize for query speed with denormalized, flat dimensions surrounding a central fact table
• Snowflake schemas trade join performance for storage efficiency and data integrity through normalized dimension hierarchies
• Both patterns share the same fact table structure—differing only in how dimensions are organized
• The Data Engineer Handbook provides structured, hands-on PostgreSQL labs for building and comparing both approaches
• Grain definition is the foundational decision that precedes all schema design

Frequently Asked Questions

When should I choose a star schema over a snowflake schema?

Choose a star schema when query performance is critical and your dimensions have relatively flat hierarchies. Star schemas excel in environments where business analysts write ad-hoc queries, since simpler SQL reduces errors and speeds development. They also perform better when dimension tables are small enough that storage redundancy isn't costly.

Does the snowflake schema always save storage space?

Not always. Normalization reduces redundancy, but dimension tables are typically small compared to fact tables in most data warehouses. The storage savings from normalizing a 10,000-row product dimension are negligible compared to a billion-row sales fact table. Measure actual byte differences before accepting the query complexity cost.

How do I handle slowly changing dimensions in these schemas?

Both schemas support slowly changing dimension (SCD) techniques equally. Type 1 (overwrite), Type 2 (add row with effective dates), and Type 3 (add column) strategies apply regardless of normalization level. The handbook's dimensional modeling module specifically covers implementing Type 2 SCDs with surrogate keys and date ranges in PostgreSQL.

Can I mix star and snowflake patterns in the same warehouse?

Yes—this is common in practice. Many implementations use star schemas for frequently queried dimensions (like date and customer) while snowflaking complex, hierarchical dimensions (like product or organizational structures). The Data Engineer Handbook's labs demonstrate this hybrid approach as an advanced optimization technique.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}