A data modeling technique optimized for data warehousing and BI, organizing data into fact tables (measurements) and dimension tables (context) for fast analytical queries.
Dimensional modeling is Ralph Kimball's methodology for building data warehouses that analysts actually enjoy using. The core insight is deceptively simple: separate what happened (facts) from the context it happened in (dimensions). Thirty years later, dimensional modeling is still the default mental model for analytics marts, and the Kimball four-step process (pick a business process, declare the grain, identify dimensions, identify facts) is the most reliable design algorithm in the field.
Newer methodologies keep appearing — Data Vault 2.0, Anchor Modeling, One Big Table, metric-layer-centric approaches — and each has a place. But dimensional modeling's combination of business-user intuition (facts = measurements, dimensions = context), BI-tool friendliness (every major BI tool has first-class support for dim/fact joins), and platform portability (Snowflake, BigQuery, Databricks, Redshift all optimize well for star schemas) keeps it as the mart-layer default. Even teams that use Data Vault for the integration layer usually materialize dimensional marts on top.
A healthtech analytics team I worked with was struggling with a 300-model dbt project that had no consistent modeling pattern. Models were named after their source systems (epic_orders, cerner_orders), grains were undocumented, dimensions were scattered across "whatever table needed them." We reorganized the consumption layer around dimensional modeling — 18 fact tables (one per business process: admissions, discharges, lab orders, prescriptions, billing), shared conformed dimensions (dim_patient, dim_provider, dim_encounter, dim_date). The exercise of applying Kimball's four-step process per fact table forced every grain decision to be explicit. Analysts stopped asking "which table should I use?" — the star structure made it obvious. Dashboard development time dropped roughly 3x. Lesson: dimensional modeling's real value isn't the schema shape; it's the design discipline it imposes.
Dimensional Modeling is a data modeling technique created by Ralph Kimball specifically for data warehouses and business intelligence. It organizes data into fact tables (quantitative measurements) and dimension tables (descriptive context), making data intuitive for business users and optimized for analytical queries.
-- Example: sales_fact
order_id | date_key | product_key | customer_key | quantity | revenue | discount-- Example: dim_product
product_key | product_name | category | subcategory | brand | color | size | price| Type | Description | Example |
|---|---|---|
| Transaction | One row per event | Individual sales |
| Periodic Snapshot | One row per time period | Daily account balance |
| Accumulating Snapshot | One row per lifecycle | Order fulfillment stages |
| Factless | No measures, just events | Student attendance |
Dimensional modeling pitfalls that catch teams repeatedly:
dim_date with pre-computed attributes (year, quarter, fiscal_period, is_weekend, is_holiday). Repeatedly doing EXTRACT(QUARTER FROM date) in queries is slower and error-prone.dim_customer with 400 attributes is unwieldy. Split into mini-dimensions (high-churn attributes separated out) or outriggers (related but optional attributes) when dimensions get unmanageable.dim_customer tables in different marts with the same name but different surrogate keys aren't conformed — they're parallel. True conformed dimensions share the surrogate key space and definitions. Enforce via governance or a shared-staging pattern.Dimensional modeling is a data warehouse design methodology created by Ralph Kimball that organizes data into fact tables (quantitative measurements — revenue, quantities, counts) and dimension tables (descriptive context — customer, product, date, store). Facts are typically long and narrow with foreign keys plus measures; dimensions are shorter and wider with descriptive attributes. The design optimizes for analytical queries, BI tool friendliness, and business-user intuition ("facts = what happened, dimensions = context"). It's the most widely adopted analytics data modeling approach and underlies virtually every major BI tool.
3NF normalized modeling minimizes redundancy — data is split into many tables connected by foreign keys, ideal for transactional OLTP systems where writes are frequent and updates must be consistent. Dimensional modeling denormalizes dimensions into flat tables and groups facts by business process, ideal for analytical OLAP workloads where reads dominate and query performance matters. The two aren't competitors — most modern warehouses use normalized staging/integration layers feeding dimensional mart layers. Use 3NF for source-of-truth integration; use dimensional for the consumption layer analysts actually query.
(1) Select the business process you're modeling — orders, clicks, payments, admissions, support tickets. Each business process becomes one fact table. (2) Declare the grain — one sentence describing what a single row in the fact means ("one row per order line per day"). (3) Identify the dimensions — who, what, when, where, why is this event happening. (4) Identify the facts (measures) — the numeric things you'll SUM/AVG/COUNT. Following this order prevents the most common design mistakes. The grain-declaration step in particular is where most teams get into trouble by skipping it.
Star schema: fact table in the center, dimensions denormalized (flat, one row per entity with all attributes). Snowflake schema (no relation to the Snowflake warehouse): dimensions normalized into multiple sub-tables. Star is faster to query, simpler to understand, uses slightly more storage. Snowflake saves storage and better handles attributes that change at different rates. On modern cloud columnar warehouses, storage is cheap and compute is the cost driver, so denormalized star schema almost always wins. Default to star; go snowflake only for specific reasons.
SCDs are patterns for handling dimensions whose attributes change over time. Type 1: overwrite — lose history, simplest. Type 2: new row with effective-date range — preserve history, most common for analytics. Type 3: add a previous-value column — preserve one change back only, rarely used. Type 4: split volatile attributes into a mini-dimension. Type 6: hybrid combining Types 1+2+3. The choice matters for historical reporting: did the $10M Q1 revenue come from customers classified as SMB at the time, or by their current classification? Type 2 answers "at the time"; Type 1 gives you only "currently."
For building dimensional models: dbt is the dominant modern tool for implementing fact and dimension tables as code on Snowflake, BigQuery, Redshift, Databricks. `dbt_utils.generate_surrogate_key`, `dbt-snapshot` for Type 2 SCDs, `dbt_utils.date_spine` for dim_date generation. For BI consumption: Looker (LookML explicitly models joins between facts and dimensions), Tableau, Power BI, Mode, Metabase — all have first-class support for star-schema navigation. Semantic layers (dbt semantic layer, Cube.dev, MetricFlow) sit on top of dimensional models to centralize metric definitions.
Yes — dimensional modeling remains the default for analytics mart layers across nearly every modern data stack. The Kimball patterns (facts, dimensions, SCDs, conformed dimensions, four-step design process) are foundational knowledge. What has changed is implementation — dbt replaced hand-written ETL, cloud columnar warehouses handle wide denormalized tables efficiently, and OBT competes in specific niches (ML feature tables, high-concurrency single-dashboard use cases). But the design *thinking* of dimensional modeling — declaring grain, separating facts from context, handling SCD, sharing conformed dimensions — is as relevant as it was in 1996.