Production engineering guide for data warehouse modeling covering patterns, implementation strategies, and operational best practices.
Data warehouse modeling is the process of designing the logical structure of dimensional data models for analytical workloads. This approach organizes data into fact and dimension tables, using star or snowflake schemas, to support efficient querying and reporting. Modeling decisions affect query performance, storage efficiency, and the ability to maintain data consistency across business periods.
Fact tables store measurable business events with foreign keys linking to dimension tables. Each fact table must define its grain precisely: the level of detail at which measurements are recorded.
A typical fact table definition might look like:
CREATE TABLE sales_fact (
sale_id BIGINT PRIMARY KEY,
product_sk INT NOT NULL,
customer_sk INT NOT NULL,
date_sk INT NOT NULL,
store_sk INT NOT NULL,
quantity_sold INT,
sale_amount DECIMAL(12,2),
FOREIGN KEY (product_sk) REFERENCES product_dimension(sk),
FOREIGN KEY (customer_sk) REFERENCES customer_dimension(sk),
FOREIGN KEY (date_sk) REFERENCES date_dimension(sk),
FOREIGN KEY (store_sk) REFERENCES store_dimension(sk)
);Fact tables must have a composite primary key that defines the granularity of each record. The grain determines what queries can be answered and how many rows will be returned.
The grain of a fact table must be explicitly defined during modeling. If you define a sales fact table with daily sales by product, but store only monthly totals, you have a mismatch between the data model and business requirements. The system does not enforce this constraint automatically; it must be maintained through documentation and ETL validation.
Dimension tables contain descriptive attributes about entities in your business domain. They are typically normalized to reduce redundancy and improve maintainability.
Example dimension table:
CREATE TABLE product_dimension (
sk INT PRIMARY KEY,
product_id VARCHAR(20) NOT NULL UNIQUE,
product_name VARCHAR(100),
category VARCHAR(50),
brand VARCHAR(50),
unit_price DECIMAL(10,2),
effective_from DATE,
effective_to DATE,
is_current BOOLEAN
);Dimension tables should include surrogate keys to decouple from business keys that may change over time. The effective_from and effective_to columns support slowly changing dimension (SCD) type 2 logic.
Hierarchical relationships between dimensions must be explicitly modeled using parent-child relationships or explicit hierarchy columns. The system does not automatically infer these relationships from table schemas.
CREATE TABLE geography_dimension (
sk INT PRIMARY KEY,
city_id VARCHAR(20),
city_name VARCHAR(100),
state_id VARCHAR(20),
state_name VARCHAR(100),
country_id VARCHAR(20),
country_name VARCHAR(100),
parent_geography_sk INT,
FOREIGN KEY (parent_geography_sk) REFERENCES geography_dimension(sk)
);SCD Type 2 modeling requires maintaining historical versions of dimension records. The system does not automatically create new rows for changed attributes; this must be handled in ETL processes or through database triggers.
Example of a type 2 dimension update:
-- Mark current record as inactive
UPDATE product_dimension
SET effective_to = '2023-12-31',
is_current = FALSE
WHERE product_id = 'P001' AND is_current = TRUE;
-- Insert new version
INSERT INTO product_dimension (
sk, product_id, product_name, category, unit_price,
effective_from, effective_to, is_current
) VALUES (
1001, 'P001', 'New Product Name', 'Electronics', 299.99,
'2024-01-01', '9999-12-31', TRUE
);The star schema uses denormalized dimension tables connected directly to a central fact table.
The snowflake schema normalizes dimension tables into multiple related tables, reducing redundancy but increasing join complexity.
Both approaches require explicit design decisions. The choice affects query performance and complexity. Snowflake schemas may require more complex queries with additional joins.
Fact tables must include validation logic to ensure data integrity. Null values in foreign keys often silently fail during joins, producing unexpected results.
Example of a validation query:
SELECT COUNT(*)
FROM sales_fact sf
LEFT JOIN product_dimension pd ON sf.product_sk = pd.sk
WHERE pd.sk IS NULL;This query should return zero rows; if it returns a count greater than zero, the fact table contains references to non-existent dimension records.
Schema changes must preserve existing data and maintain query compatibility. Adding new columns to fact tables requires careful handling of ETL processes.
Example of adding a new column:
ALTER TABLE sales_fact ADD COLUMN discount_amount DECIMAL(10,2);This operation may lock the table during execution, affecting concurrent queries.
Indexing strategies for fact tables should be based on common query patterns. Most analytical queries filter by date or join with specific dimensions.
Example of a performance-optimized fact table:
CREATE TABLE sales_fact (
sale_id BIGINT PRIMARY KEY,
product_sk INT NOT NULL,
customer_sk INT NOT NULL,
date_sk INT NOT NULL,
store_sk INT NOT NULL,
quantity_sold INT,
sale_amount DECIMAL(12,2),
discount_amount DECIMAL(10,2),
INDEX idx_date (date_sk),
INDEX idx_product (product_sk),
INDEX idx_customer (customer_sk),
INDEX idx_store (store_sk)
);Time-series data requires careful handling of date dimensions. The date dimension should include multiple granularity levels: year, quarter, month, week, day.
Example of a minimal date dimension:
CREATE TABLE date_dimension (
sk INT PRIMARY KEY,
date_value DATE NOT NULL UNIQUE,
year INT,
quarter INT,
month INT,
week INT,
day_of_month INT,
day_of_week INT,
is_weekend BOOLEAN
);Fact tables may contain records with missing dimension references. These are typically handled through LEFT JOINs, but queries must account for potential NULL values.
Example of handling missing data:
SELECT
COALESCE(pd.product_name, 'Unknown Product') as product_name,
COUNT(sf.sale_id) as total_sales
FROM sales_fact sf
LEFT JOIN product_dimension pd ON sf.product_sk = pd.sk
GROUP BY pd.product_name;Data model integrity requires validation queries to check for data consistency. Common issues include:
Example validation query for fact table integrity:
SELECT COUNT(*) as missing_dimension_refs
FROM sales_fact sf
LEFT JOIN product_dimension pd ON sf.product_sk = pd.sk
WHERE pd.sk IS NULL AND sf.product_sk IS NOT NULL;Data warehouse models must support audit trails and lineage tracking. This requires storing metadata about data sources, ETL processes, and transformation logic.
The system does not automatically track these relationships; they must be implemented through schema design or external tools.
Defining fact table grain too coarse or too fine leads to inefficient queries or missing data.
Fact tables without proper indexes may experience poor query performance, especially for large datasets.
Fact tables referencing dimensions that don't contain all necessary attributes create data quality issues.
Failing to properly implement slowly changing dimensions results in historical data inconsistencies.
Model validation queries should be part of deployment processes. These include:
The system does not automatically validate these constraints; they must be implemented through scripts or automated testing processes.
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.
We use cookies for analytics (Google Analytics) and advertising (Google AdSense) to improve your experience and support free content. Privacy Policy