Data Warehousing
Intermediate 16 min read Data Warehousing

Dimensional Modeling

Facts, dimensions, and one disciplined grain

Dimensional modeling splits data into facts (the measurements) and dimensions (the context). Choosing the grain first, then conforming dimensions, makes the schema stable even as reports multiply.

star schemafactdimensiongrainsurrogate keyconformed dimension
Prerequisites: Warehouse Architecture Joins In Depth
Before we start

What you will learn

  • Separate what you measure (facts) from how you describe it (dimensions)
  • Define a fact's grain before writing any SQL
  • Design a star schema with conformed dimensions
Introduction

What is this?

Dimensional modeling organizes data into fact tables — numeric measurements at a defined grain (one row = one order line) — and dimension tables that describe who/what/where/when. Analysts slice facts by dimension attributes; a well-chosen grain and conformed dimensions keep reports consistent.

Motivation

Why do you need it?

  • REQUIRED slug: de-foundations links here as the canonical modeling reference for the track.
  • The grain decision is the single highest-leverage modeling choice; everything downstream inherits it.
  • Conformed dimensions make 'sales' mean the same thing across channels and teams.
Think about it

Mental model

Imagine

A receipt: quantities and prices + the shop, till, and date

Think of

Fact + dimensions

The receipt's numbers are the facts; the shop name, clerk, and timestamp are dimension attributes. You can aggregate the amounts by any label — exactly what a report does.

Mechanics

How it works

  • Fact tables hold measures and foreign keys to dimensions; numeric, additive measures aggregate cleanly.
  • Dimensions are descriptive, often slowly-changing; each holds a surrogate key plus natural source attributes.
  • Grain: state precisely what one row means ('one row per order line item') before modeling.
  • Star schema: fact at the center, dimension tables joined around — simple, fast, analyst-friendly.
  • Conformed dimensions are shared and reused across facts so every metric slices consistently.

A star schema with a central sales fact.

dim_customer
dim_product
fact_sales
dim_date
Reports
Reference

Key commands

SELECT d.category, sum(f.gross_amount) FROM fact_sales f JOIN dim_product d USING (product_key) GROUP BY 1;

The analyst's canonical slice: facts aggregated by a dimension attribute.

SELECT count(*) FROM fact_sales GROUP BY customer_key, product_key, date_key HAVING count(*) > 1;

A grain audit: duplicates would mean the fact grain is violated.

Hands-on

Examples

Statement of grain, then the schema

Statement of grain, then the schema
-- GRAIN: one row per order line item (product in an order header).
-- FACT:  amount, quantity, discount (additive measures)
-- KEYS:  customer_key, product_key, date_key (surrogate ints)
 
CREATE TABLE fact_sales (
  customer_key BIGINT, product_key BIGINT, date_key BIGINT,
  qty INTEGER, gross_amount NUMERIC(12,2), discount NUMERIC(12,2)
);
CREATE TABLE dim_product (
  product_key BIGINT PRIMARY KEY, sku TEXT, name TEXT, category TEXT UNIQUE
); -- SKU shared across sources = one conformed product dimension

Additive measures aggregate; surrogate keys decouple facts from source-system IDs.

Real world

In production

  • Finance runs P&L off one fact_sales at grain order-line; a duplicate-detection gate rejects re-loads.
  • Marketing slices the same fact by a conformed region dimension while sales slices by segment — definitions match.
  • A subscription fact (at grain subscription-month) coexists with order facts sharing the same dim_customer.
Guidelines

Best practices

  • State the grain first in the model's documentation; every report inherits it.
  • Use surrogate keys for dimensions so source renames never break joins.
  • Put additive measures in facts; put rates/percentages as derived columns only when unavoidable.
  • Conform dimensions (shared SKU, shared calendar) across facts before adding more facts.
Watch out

Common mistakes

  • Skipping grain definition and letting 'sales' mean different things in every report.
  • Using natural keys from sources as dimension keys, which break when the source cleans or merges IDs.
  • Stuffing descriptive attributes into fact tables, bloating them and defeating the model's purpose.
When things break

Debugging tips

  • A dashboard total that won't reconcile usually traces to a different grain — ask 'what is one row?'.
  • The duplicate audit (GROUP BY keys HAVING count>1) catches reloaded/broken grain quickly.
  • When BI shows NULLs in joins, look for orphan fact keys not covered by dims.
Interviews

Interview checkpoint

Practice

Hands-on lab

Build a Star Schema and Audit Its Grain
40 min · intermediate
In progress
Objectives
  • Create fact and dimension tables in DuckDB
  • Load realistic orders and audit for grain violations
  • Run analyst-style slices against the model

Build a Star Schema and Audit Its Grain

40 min · intermediate

0/6 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

Where do additive numeric measures like quantity and revenue belong?

2

Using source-system IDs as dimension primary keys is ideal because they cannot change.

Go deeper

Official documentation

Kimball dimensional modeling techniques
docs
dbt star schema modeling guide
docs