---
title: "Dimensional modeling"
description: "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."
canonical: "https://motherduck.com/glossary/dimensional-modeling/"
related:
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "Context Management for Data Agents | MotherDuck"
    url: "https://motherduck.com/videos/context-management-data-agents/"
  - title: "Data Transformation | MotherDuck Docs"
    url: "https://motherduck.com/docs/integrations/transformation/"
---

# 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:

1. **Select the business process** to model (e.g., retail sales, order shipments).
2. **Declare the grain** — exactly what one fact table row represents.
3. **Identify the dimensions** that give context to each fact (customer, product, date, store).
4. **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

```sql
-- 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.