Dimensional Modeling

What is dimensional modeling?

Facts join to dimensions via foreign keys, producing schemas that are easy to query for analytical questions ('how much revenue did we generate per customer segment per month?') even when the underlying data spans billions of rows.

• Fact tables: the events being measured — sales transactions, login events, page views, support tickets. Tend to be tall (many rows) and narrow.
• Dimension tables: the context for facts — who, what, where, when, why. Tend to be wide (many attributes) and shorter.

Why dimensional modeling exists

Transactional databases use 3NF (third normal form) modeling — each fact stored exactly once, integrity-enforced via foreign keys. This optimises for write performance and update consistency but makes analytical queries slow and complex (large multi-table joins for every aggregation).

Dimensional modeling makes the opposite trade-off: denormalise dimensions for query simplicity, accept storage duplication, optimise the resulting schema for the OLAP query patterns that BI generates.

Core principles

• Conformed dimensions: shared dimensions (customer, product, date) used consistently across multiple fact tables — the foundation of cross-fact analytical queries
• Surrogate keys: integer keys for fact-to-dim joins, decoupled from natural business keys, enabling slowly-changing dimension handling
• Slowly Changing Dimensions (SCDs): patterns for handling dimensional attributes that change over time (Type 1: overwrite; Type 2: history tracked; Type 3: limited history)
• Grain definition: every fact table has a single, defined grain — the smallest unit of measurement (one row per sale, one row per login, one row per minute of usage)
• Star schemas as default: denormalised dimensions in star arrangements, with snowflake selectively for hierarchical dimensions

Modern dimensional modeling (2025)

Dimensional modeling discipline survived the transition from on-premise warehouses to cloud warehouses, lakehouses, and the modern data stack. Today it is typically implemented through dbt models that materialise dimensional structures from raw source data — preserving Kimball's principles while modernising the tooling.

Some changes from the original 1990s approach:

• SCD Type 2 implementations are simpler in modern warehouses with cheap storage
• Pre-aggregation is less common — modern columnar engines handle aggregation queries faster than 1990s row-stores
• Semantic layers (Cube, dbt Semantic Layer) sit above the dimensional model to enforce metric consistency
• Lakehouse table formats (Iceberg, Delta) extend dimensional modeling to file-based storage

Common pitfalls

• 1. Treating analytical schemas like transactional schemas. Engineers trained on 3NF often resist dimensional denormalisation. The norms differ; analytical schemas should be dimensional.
• 2. Skipping conformed dimensions. When facts use different definitions of customer or date, cross-fact analysis breaks. Conformed dimensions are non-negotiable.
• 3. Mixing grains in fact tables. A fact table that mixes per-order rows with per-customer summary rows produces undefined aggregation behaviour. Pick one grain per fact and stick to it.

Related concepts

Star schema and snowflake schema are the structural patterns. Fact tables and dimension tables are the components. Data marts are subject-area dimensional models. Headless BI sits above dimensional models to expose metrics to consumers.

Frequently asked questions

Sources

1. The Data Warehouse Toolkit, Kimball (3rd ed)
2. dbt Labs analytics engineering documentation
3. Modern Data Stack reports (2024–25)