Data Warehouse Modeling with Kimball

Design dimensional data warehouses. Covers star schema, snowflake schema, fact and dimension tables, slowly changing dimensions, conformed dimensions, and ETL design patterns.

This is a reference for implementing dimensional modeling principles from Ralph Kimball's approach in data warehouse design. This technique applies when building analytical data stores that support business intelligence workloads, particularly where fact tables and dimension tables are structured to support efficient querying and reporting.

Understanding the Core Components

Fact Tables

Fact tables store quantitative measurements related to business processes. Each fact table has a composite primary key made up of foreign keys to dimension tables.

CREATE TABLE sales_fact (
    product_key INTEGER NOT NULL,
    customer_key INTEGER NOT NULL,
    date_key INTEGER NOT NULL,
    sales_amount DECIMAL(10,2) NOT NULL,
    quantity_sold INTEGER NOT NULL,
    PRIMARY KEY (product_key, customer_key, date_key)
);

Fact tables should always include surrogate keys that reference dimension tables. These foreign key relationships must be explicitly defined and maintained.

Dimension Tables

Dimension tables store descriptive attributes about the entities in your fact tables. They should contain slowly changing dimensions (SCD Type 2) to track historical changes.

CREATE TABLE product_dimension (
    product_key INTEGER PRIMARY KEY,
    product_id VARCHAR(50) NOT NULL UNIQUE,
    product_name VARCHAR(100),
    category_name VARCHAR(50),
    brand_name VARCHAR(50),
    effective_date DATE,
    expiry_date DATE,
    is_current BOOLEAN DEFAULT TRUE
);

Dimension tables must maintain a natural key (business key) alongside the surrogate key. The natural key should be unique and immutable.

Implementing Slowly Changing Dimensions

Type 2 SCD Implementation

Use an effective date and expiry date to track historical changes. When a dimension record changes, create a new row with updated values and set the previous row's expiry date.

-- Example update for product_dimension
UPDATE product_dimension 
SET expiry_date = CURRENT_DATE,
    is_current = FALSE
WHERE product_key = 1234;

INSERT INTO product_dimension (
    product_key, product_id, product_name, category_name, brand_name,
    effective_date, expiry_date, is_current
) VALUES (
    1235, 'P001', 'Premium Widget', 'Electronics', 'TechCorp',
    CURRENT_DATE, '9999-12-31', TRUE
);

The key failure mode occurs when a fact table references a dimension row that has been expired but not replaced. This leads to data quality issues where historical facts point to non-existent current values.

Handling Dimension Row Expiry

When building queries, always filter for is_current = TRUE when joining with dimensions that support SCD Type 2:

SELECT f.sales_amount, d.product_name
FROM sales_fact f
JOIN product_dimension d ON f.product_key = d.product_key
WHERE d.is_current = TRUE;

If you forget to include the is_current = TRUE filter, queries will return incorrect results when historical data is present in the dimension table.

Designing Star Schema Patterns

Fact-Dimension Relationships

Each fact table should reference exactly one set of dimension tables. The relationship between fact and dimension tables must be explicit.

-- Example star schema with proper foreign key constraints
CREATE TABLE sales_fact (
    product_key INTEGER NOT NULL,
    customer_key INTEGER NOT NULL,
    date_key INTEGER NOT NULL,
    sales_amount DECIMAL(10,2) NOT NULL,
    quantity_sold INTEGER NOT NULL,
    PRIMARY KEY (product_key, customer_key, date_key),
    FOREIGN KEY (product_key) REFERENCES product_dimension(product_key),
    FOREIGN KEY (customer_key) REFERENCES customer_dimension(customer_key),
    FOREIGN KEY (date_key) REFERENCES date_dimension(date_key)
);

Each foreign key must reference a primary key in the dimension table. If you define a foreign key that references a non-primary key column, it will silently fail to enforce referential integrity.

Naming Conventions

Use consistent naming for all keys:

Implementing Date Dimensions

Date dimensions are critical for time-based analysis. They should include multiple levels of granularity:

CREATE TABLE date_dimension (
    date_key INTEGER PRIMARY KEY,
    full_date DATE NOT NULL UNIQUE,
    day_of_week INTEGER,
    day_name VARCHAR(10),
    week_of_year INTEGER,
    month_number INTEGER,
    month_name VARCHAR(10),
    quarter_number INTEGER,
    year_number INTEGER,
    is_weekend BOOLEAN
);

The date dimension should be populated with a complete range of dates, typically spanning several years into the past and future.

Handling Missing Data

When fact tables have missing dimension values, they should explicitly indicate this through NULL values or special keys:

-- Special key for unknown/missing dimension values
CREATE TABLE product_dimension (
    product_key INTEGER PRIMARY KEY DEFAULT -1,
    product_id VARCHAR(50),
    product_name VARCHAR(100),
    is_unknown BOOLEAN DEFAULT TRUE
);

-- Fact table referencing missing dimension
SELECT f.sales_amount, d.product_name
FROM sales_fact f
LEFT JOIN product_dimension d ON f.product_key = d.product_key
WHERE d.product_key IS NOT NULL OR d.is_unknown = TRUE;

Common Implementation Failures

Inconsistent Key Usage

Using different key names in fact and dimension tables causes silent failures. The following creates an implicit mismatch:

-- Fact table with different naming
CREATE TABLE sales_fact (
    product_id INTEGER NOT NULL,  -- Should be product_key
    customer_key INTEGER NOT NULL,
    date_key INTEGER NOT NULL
);

This mismatch prevents proper joins and can silently fail during ETL processing.

Missing Primary Key Definitions

Fact tables without primary key constraints cannot properly support the star schema pattern:

-- Incorrect: No primary key
CREATE TABLE sales_fact (
    product_key INTEGER NOT NULL,
    customer_key INTEGER NOT NULL,
    date_key INTEGER NOT NULL,
    sales_amount DECIMAL(10,2) NOT NULL
);

-- Correct: With primary key
CREATE TABLE sales_fact (
    product_key INTEGER NOT NULL,
    customer_key INTEGER NOT NULL,
    date_key INTEGER NOT NULL,
    sales_amount DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (product_key, customer_key, date_key)
);

Improper SCD Handling

Failing to properly implement the SCD Type 2 pattern leads to incorrect historical analysis. The key issue is not maintaining the effective date and expiry date fields correctly.

Query Design Considerations

When querying dimensional data, always consider the time dimension:

-- Proper query with time filtering
SELECT 
    d.month_name,
    SUM(f.sales_amount) as total_sales
FROM sales_fact f
JOIN date_dimension d ON f.date_key = d.date_key
WHERE d.is_current = TRUE
GROUP BY d.month_name
ORDER BY d.month_number;

Query Performance Optimization

Use proper indexing on fact table foreign keys and date dimensions:

CREATE INDEX idx_sales_fact_product_key ON sales_fact(product_key);
CREATE INDEX idx_sales_fact_customer_key ON sales_fact(customer_key);
CREATE INDEX idx_sales_fact_date_key ON sales_fact(date_key);

Data Quality Validation

Validate that all fact table records have valid dimension references:

-- Check for orphaned fact records
SELECT COUNT(*) 
FROM sales_fact f
LEFT JOIN product_dimension d ON f.product_key = d.product_key
WHERE d.product_key IS NULL;

The most common production failure occurs when ETL processes silently insert fact records with invalid dimension keys, leading to reports showing incorrect aggregations.

Dimension Table Maintenance

Regularly check for duplicate natural keys in dimension tables:

-- Check for duplicates in product dimension
SELECT product_id, COUNT(*) 
FROM product_dimension 
WHERE is_current = TRUE
GROUP BY product_id
HAVING COUNT(*) > 1;

This can occur when SCD Type 2 logic fails to properly expire old records.

Handling Special Cases

Null Values in Fact Tables

Fact tables should define how to handle NULL values:

-- Example of handling missing quantities
SELECT 
    SUM(COALESCE(quantity_sold, 0)) as total_quantity
FROM sales_fact f
LEFT JOIN product_dimension d ON f.product_key = d.product_key;

Time Zone Considerations

When dealing with date dimensions, ensure consistent time zone handling across all tables:

-- Example of date dimension with time zone info
CREATE TABLE date_dimension (
    date_key INTEGER PRIMARY KEY,
    full_date DATE NOT NULL UNIQUE,
    utc_offset INTEGER,  -- Hours from UTC
    is_current BOOLEAN DEFAULT TRUE
);

Testing Implementation

Verify that all foreign key relationships work correctly:

-- Test dimension key references
SELECT COUNT(*) 
FROM sales_fact f
JOIN product_dimension d ON f.product_key = d.product_key
WHERE d.is_current = TRUE;

The most frequent production issue arises when new fact tables are added without proper foreign key constraints, causing downstream queries to silently return incorrect data.

This page was rewritten on 15 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.