Dimensional modeling

Dimensional modeling is a data warehouse design methodology, developed by Ralph Kimball, that organizes data into fact tables (measures) and dimension tables (descriptive context) optimized for business-user querying.

Overview

Dimensional modeling is the design philosophy behind star and snowflake schemas. Rather than modeling data the way a source application stores it (highly normalized, optimized for transactional writes), dimensional modeling organizes data the way analysts think about it: measurable business events (facts) described by descriptive business context (dimensions). The approach was developed and popularized by Ralph Kimball, whose four-step design process is the standard reference:

Select the business process to model (e.g., retail sales, order shipments).
Declare the grain — exactly what one fact table row represents.
Identify the dimensions that give context to each fact (customer, product, date, store).
Identify the facts — the numeric measures to include (quantity, revenue, discount).

Kimball's related concept of a bus architecture ties multiple fact tables and data marts together with shared conformed dimensions (the same dim_date or dim_customer used everywhere), so metrics from different business processes can be compared consistently across the warehouse.

Example

Copy code
-- Step 2-4 realized as tables
CREATE TABLE dim_date (date_key INTEGER PRIMARY KEY, full_date DATE, quarter VARCHAR, is_holiday BOOLEAN);
CREATE TABLE dim_store (store_key INTEGER PRIMARY KEY, store_name VARCHAR, region VARCHAR);

CREATE TABLE fact_sales (             -- grain: one row per order line
    order_id   BIGINT,
    line_number INTEGER,
    date_key   INTEGER REFERENCES dim_date(date_key),
    store_key  INTEGER REFERENCES dim_store(store_key),
    quantity   INTEGER,
    revenue    DECIMAL(12,2),
    PRIMARY KEY (order_id, line_number)
);

Kimball vs. Inmon

Dimensional modeling (Kimball) is often contrasted with Bill Inmon's approach, which builds a normalized enterprise data warehouse first and derives dimensional data marts from it downstream. Data Vault is a third methodology, designed to sit between raw source data and a Kimball-style dimensional layer, optimized for auditability and change tolerance.

DuckDB angle

DuckDB's speed at ad hoc joins and aggregations makes it practical to prototype a dimensional model locally — building fact and dimension tables from Parquet or CSV with CREATE TABLE ... AS SELECT, testing the grain and dimensions, and iterating quickly — before scaling the same schema up in MotherDuck.

Related terms

Star schema →

A star schema is a dimensional data warehouse design where a central fact table of numeric measures connects directly to denormalized dimension tables, forming a shape like a star.

Fact table →

A fact table is the central table in a dimensional model that stores numeric measures (like revenue or quantity) alongside foreign keys linking to descriptive dimension tables.

Fact constellation schema →

A fact constellation schema (also called a galaxy schema) is a dimensional model with multiple fact tables that share one or more common dimension tables.

Conformed dimension →

A conformed dimension is a dimension table — like a shared date or customer dimension — that is defined identically and reused across multiple fact tables or data marts, so metrics stay comparable across the warehouse.

Dimension table →

A dimension table stores the descriptive, mostly-textual attributes — like customer name, product category, or region — that you use to filter, group, and label the measures in a fact table.

Grain →

Grain is the level of detail that a single row in a fact table represents — for example, one row per order line item versus one row per order per day.

FAQS

Kimball's process: select the business process, declare the grain, identify the dimensions, and identify the facts. Grain is declared before any columns are added, because it determines what every dimension and measure means.

A conformed dimension is a dimension table (like dim_date or dim_customer) that's shared and defined identically across multiple fact tables and data marts, so metrics from different business processes can be compared consistently.