Data Modeling Patterns: Star Schema, Data Vault, and More

Data Modeling patterns are reusable architectural blueprints for structuring database schemas -- including star schema for analytics, data vault for enterprise data warehouses, and One Big Table for simplicity -- each optimized for specific query patterns, scalability requirements, and maintenance workflows.

What You'll Learn

You will understand five major Data Modeling patterns: star schema, snowflake schema, data vault, anchor modeling, and One Big Table. You will learn when to use each pattern and how to design schemas for analytics, Data Warehousing, and operational workloads.

Why Data Modeling Patterns Matter

Choosing the wrong pattern leads to slow queries, unmaintainable schemas, and brittle Data Pipelines. DodaZIP initially used a normalized schema for analytics queries, resulting in 12-second dashboard loads. Switching to a star schema reduced load times to 200ms.

Data Modeling Learning Path

flowchart LR
  A[Normalization] --> B[Denormalization]
  B --> C[Data Modeling Patterns]
  C:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px

Pattern Comparison

Star Schema

The star schema is the most common dimensional modeling pattern for data warehouses. It consists of one fact table (measures) surrounded by dimension tables (attributes).

flowchart LR
    D1[Date Dimension] --- F[Fact Sales]
    D2[Product Dimension] --- F
    D3[Customer Dimension] --- F
    D4[Store Dimension] --- F

Fact Table

CREATE TABLE fact_sales (
    sale_id BIGINT PRIMARY KEY,
    date_key INT NOT NULL,        -- FK to date_dim
    product_key INT NOT NULL,     -- FK to product_dim
    customer_key INT NOT NULL,    -- FK to customer_dim
    store_key INT NOT NULL,       -- FK to store_dim
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    discount DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP NOT NULL
);

-- Index for common queries
CREATE INDEX idx_fact_sales_date ON fact_sales (date_key);
CREATE INDEX idx_fact_sales_product ON fact_sales (product_key);

Dimension Table

CREATE TABLE dim_product (
    product_key INT PRIMARY KEY,
    product_id VARCHAR(20),       -- Business key
    product_name VARCHAR(200) NOT NULL,
    category VARCHAR(100),
    subcategory VARCHAR(100),
    brand VARCHAR(100),
    price DECIMAL(10,2),
    cost DECIMAL(10,2),
    effective_date DATE NOT NULL,
    end_date DATE,
    is_current BOOLEAN DEFAULT true
);

CREATE TABLE dim_date (
    date_key INT PRIMARY KEY,
    full_date DATE NOT NULL,
    year INT NOT NULL,
    quarter INT NOT NULL,
    month INT NOT NULL,
    month_name VARCHAR(20) NOT NULL,
    week INT NOT NULL,
    day_of_week INT NOT NULL,
    is_weekend BOOLEAN NOT NULL,
    is_holiday BOOLEAN DEFAULT false
);

Analytics Query

SELECT
    d.year,
    d.quarter,
    p.category,
    SUM(f.quantity) AS units_sold,
    SUM(f.total_amount) AS revenue
FROM fact_sales f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
WHERE d.year = 2026
GROUP BY d.year, d.quarter, p.category
ORDER BY d.year, d.quarter, revenue DESC;

Expected output:

 year | quarter | category    | units_sold | revenue
------+---------+-------------+------------+----------
 2026 |       1 | Electronics |      54321 | 1234567.89
 2026 |       1 | Clothing    |      98765 |  987654.32

Snowflake Schema

A snowflake schema normalizes dimension tables to remove redundancy. It uses more storage-efficient but requires more JOINs.

-- Normalized dimensions (snowflake)
CREATE TABLE dim_category (
    category_key INT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL
);

CREATE TABLE dim_subcategory (
    subcategory_key INT PRIMARY KEY,
    subcategory_name VARCHAR(100) NOT NULL,
    category_key INT NOT NULL REFERENCES dim_category(category_key)
);

CREATE TABLE dim_product_snowflake (
    product_key INT PRIMARY KEY,
    product_name VARCHAR(200),
    subcategory_key INT NOT NULL REFERENCES dim_subcategory(subcategory_key),
    brand VARCHAR(100)
);

Trade-off: Snowflake schemas use less storage but need more JOINs. Star schemas are faster for querying but use more storage.

Data Vault

Data Vault is designed for enterprise data warehouses requiring auditability, flexibility, and scalability. It has three entity types: hubs (business keys), links (relationships), and satellites (attributes).

-- Hub: Core business entities
CREATE TABLE hub_customer (
    customer_hash CHAR(32) PRIMARY KEY,  -- MD5 hash of business key
    customer_id VARCHAR(50) NOT NULL,    -- Business key
    load_date TIMESTAMP NOT NULL,
    record_source VARCHAR(100) NOT NULL
);

CREATE TABLE hub_product (
    product_hash CHAR(32) PRIMARY KEY,
    product_id VARCHAR(50) NOT NULL,
    load_date TIMESTAMP NOT NULL,
    record_source VARCHAR(100) NOT NULL
);

CREATE TABLE hub_order (
    order_hash CHAR(32) PRIMARY KEY,
    order_id VARCHAR(50) NOT NULL,
    load_date TIMESTAMP NOT NULL,
    record_source VARCHAR(100) NOT NULL
);

-- Link: Relationships between hubs
CREATE TABLE link_order_customer (
    link_hash CHAR(32) PRIMARY KEY,
    order_hash CHAR(32) NOT NULL REFERENCES hub_order(order_hash),
    customer_hash CHAR(32) NOT NULL REFERENCES hub_customer(customer_hash),
    load_date TIMESTAMP NOT NULL,
    record_source VARCHAR(100) NOT NULL
);

-- Satellite: Attributes (slowly changing)
CREATE TABLE sat_customer_details (
    customer_hash CHAR(32) NOT NULL REFERENCES hub_customer(customer_hash),
    load_date TIMESTAMP NOT NULL,
    customer_name VARCHAR(200),
    email VARCHAR(200),
    phone VARCHAR(20),
    address TEXT,
    record_source VARCHAR(100),
    PRIMARY KEY (customer_hash, load_date)
);

Data Vault advantages:

• Full audit trail (every change is recorded)
• Parallel loading (hubs and satellites can load independently)
• Resilient to source system changes
• Excellent for CDC (change data capture) pipelines

Anchor Modeling

Anchor modeling is the most normalized approach, splitting attributes into separate tables called anchors and knots.

-- Anchor: Core entity
CREATE TABLE anchor_customer (
    customer_id BIGINT PRIMARY KEY,
    created_at TIMESTAMP NOT NULL
);

-- Attribute: Time-varying property
CREATE TABLE attr_customer_name (
    customer_id BIGINT NOT NULL REFERENCES anchor_customer(customer_id),
    valid_from TIMESTAMP NOT NULL,
    valid_to TIMESTAMP,
    name VARCHAR(200) NOT NULL,
    PRIMARY KEY (customer_id, valid_from)
);

CREATE TABLE attr_customer_email (
    customer_id BIGINT NOT NULL REFERENCES anchor_customer(customer_id),
    valid_from TIMESTAMP NOT NULL,
    email VARCHAR(200) NOT NULL,
    PRIMARY KEY (customer_id, valid_from)
);

-- Tie: Relationship
CREATE TABLE tie_customer_order (
    customer_id BIGINT NOT NULL REFERENCES anchor_customer(customer_id),
    order_id BIGINT NOT NULL,
    valid_from TIMESTAMP NOT NULL,
    valid_to TIMESTAMP,
    PRIMARY KEY (customer_id, order_id, valid_from)
);

One Big Table (OBT)

OBT stores all data in a single wide table, often with repeated values. It is the ultimate denormalized pattern.

CREATE TABLE one_big_table (
    order_id INT,
    order_date DATE,
    order_status VARCHAR(20),
    customer_id INT,
    customer_name VARCHAR(100),
    customer_email VARCHAR(200),
    product_id INT,
    product_name VARCHAR(200),
    category VARCHAR(100),
    quantity INT,
    unit_price DECIMAL(10,2),
    total DECIMAL(12,2),
    store_id INT,
    store_name VARCHAR(100),
    store_region VARCHAR(50),
    PRIMARY KEY (order_id, product_id)
);

When to use OBT:

• Small to medium datasets (under 100GB)
• Simple analytical queries
• Rapid prototyping
• Export/ETL staging tables

Choosing the Right Pattern

Common Data Modeling Errors

1. Using Star Schema for OLTP

Star schemas are designed for analytics. Using them for transactional workloads causes write slowdowns due to wide tables and redundant data.

2. No Slowly Changing Dimension Strategy

Customer addresses and product categories change. Without SCD Type 1 (overwrite), Type 2 (add row), or Type 3 (add column), historical reports become inaccurate.

3. Over-Complicating with Data Vault Prematurely

Data Vault is for enterprise data warehouses with many source systems. For a simple application database, star schema or 3NF is more appropriate.

4. Skipping Surrogate Keys

Natural keys (email, product SKU) change over time. Surrogate keys (auto-increment INT) provide stable references for dimension tables.

5. Not Partitioning Large Fact Tables

Fact tables grow billions of rows quickly. Partition by date to enable partition pruning and easier maintenance.

6. Ignoring Index Strategy for Dimension Tables

Dimension tables need indexes on surrogate keys and frequently filtered columns (category, region) for fast JOINs.

7. One-Size-Fits-All Approach

Different parts of the system need different patterns. Use 3NF for OLTP, star schema for analytics, and OBT for exports.

Practice Questions

1. What is the difference between star schema and snowflake schema?

Star schema denormalizes dimensions (single table per dimension). Snowflake schema normalizes dimensions into multiple related tables.

2. What are the three core entity types in Data Vault?

Hubs (business keys), Links (relationships), and Satellites (attributes over time). Hubs and Links are immutable; Satellites store history.

3. When would you use One Big Table?

For small datasets, rapid prototyping, or simple export/ETL staging. Also when query simplicity matters more than storage efficiency or write performance.

4. What is a slowly changing dimension?

A dimension where attribute values change over time. SCD Type 1 overwrites, Type 2 keeps history with new rows, Type 3 adds columns for limited history.

5. Challenge: Design a data model for an e-commerce analytics platform.

Requirements: 50M orders/year, 500K products, 2M customers. Analytics queries need revenue by category, region, and month. Choose a pattern and design the schema. Answer: Star schema. Fact table fact_sales with surrogate keys to dim_date, dim_product, dim_customer, dim_store. Use SCD Type 2 for customer address changes and Type 1 for product category changes. Partition fact table by month. Add covering indexes for common filter columns.

FAQ

Try It Yourself

Build a star schema:

1. Create fact_sales and dimension tables (dim_date, dim_product, dim_customer)
2. Populate dim_date with 5 years of dates programmatically
3. Insert 10,000 sample sales records
4. Write analytics queries: revenue by month, top products, customer cohorts
5. Compare query performance against a normalized equivalent (3NF)
6. Add indexes on foreign keys in the fact table
7. Measure the performance improvement

What's Next

You have learned five Data Modeling patterns: star schema, snowflake schema, data vault, anchor modeling, and One Big Table. Choose the pattern that fits your workload -- star schema for analytics, data vault for enterprise warehousing, and normalized forms for OLTP.