Dimensional Modeling
Intermediate 15 min read Dimensional Modeling

Dimension Tables

The vocabulary of every slicer and group-by

Dimensions describe the who, what, where and when. They are denormalized for reading, use surrogate keys, and carry the attributes that become every slicer and group-by in BI.

dimensionsattributessurrogate keysdenormalizedrole playingconformed
Prerequisites: Dimensional Modeling Overview
Before we start

What you will learn

  • Design dimension attributes that serve the known questions
  • Use surrogate keys and role-playing dimensions correctly
  • Handle degenerate and junk dimensions pragmatically
Introduction

What is this?

A dimension table is a denormalized description of one entity — customer, product, date, store, employee. Each row is one member with its attributes (name, region, category, tier). Reports group and filter by these attributes, so the attributes you choose ARE the questions the organization can ask.

Motivation

Why do you need it?

  • Analysts think and filter by attributes — dimensions are the interface to the data.
  • Denormalizing attributes makes star joins fast and readable, unlike normalized OLTP.
  • Consistent, well-keyed dimensions are reusable across many fact tables.
  • A complete date dimension alone solves calendars: holidays, fiscal quarters, ISO weeks.
Think about it

Mental model

Imagine

A contact card for each friend

Think of

A dimension row

The card holds name, city, phone, birthday — the attributes you sort your life by. Swap an attribute (city) and the whole 'who's nearby' question changes. Dimensions are those cards, shared by every address book (fact table) that references them.

Mechanics

How it works

  • Each dimension row carries a surrogate key (integer) plus business keys and attributes.
  • Attributes are denormalized — hierarchical categories flattened (category + subcategory + brand in one row).
  • Role-playing dimensions reuse one physical dim for several roles (order_date, ship_date, due_date → one dim_date).
  • Degenerate dimensions (order numbers) stay in the fact to avoid a pointless dim.
  • Junk/outrigger dims group rare low-cardinality flags to declutter wide facts.

Anatomy of a dimension and its roles.

Member
Surrogate key
Attributes
Roles
Slicers
Hands-on

Examples

Slicing with dimension attributes

Slicing with dimension attributes
SELECT
  d.fiscal_quarter,
  c.tier,
  SUM(f.amount) AS revenue
FROM fact_orders f
JOIN dim_customer c ON f.cust_key = c.cust_key
JOIN dim_date d   ON f.order_date_key = d.date_key
WHERE d.fiscal_year = 2026
GROUP BY d.fiscal_quarter, c.tier;

Every group-by here is a dimension attribute — fiscal quarter (date dim), tier (customer dim). Adding a new question usually means adding attributes, not new tables.

Real world

In production

  • A date dimension powers fiscal quarters, holidays and ISO weeks uniformly in every report.
  • A customer dim stores tier and region so churn reports slice by segment without locating raw tables.
  • One dim_date serves order, ship and due dates in a logistics star via role-playing aliases.
Guidelines

Best practices

  • Choose attributes by interviewing the questions, not by porting every source column.
  • Use surrogate keys; keep business keys as attributes for joins with source.
  • Keep dimensions slowly-changing-aware from day one (see SCD concept).
  • Flatten hierarchies: two attributes beat a recursive self-join in a report.
Watch out

Common mistakes

  • Huge 'kitchen sink' dimensions with 500 attributes that slow scans for one useful field.
  • Missing role-playing aliases, so analysts join the wrong date key.
  • Updating a customer's region in place and silently rewriting history.
  • No surrogate key — a 'code' that another team re-keys breaks the whole star.
Root cause analysis

Common issues

Error
Report can't filter on 'fiscal quarter'
Root cause

Date dim stores calendar only

Fix

Add fiscal attributes to the date dim and backfill all keys.

Error
Customer region changed for all history
Root cause

In-place update instead of a versioned dimension

Fix

Convert to SCD-2 (type 2) so history keeps its original region.

When things break

Debugging tips

  • A slicer missing options? Check the dimension's row set and refresh, not the fact.
  • If shipping_date filters are empty, check you aliased dim_date for the ship role.
  • For surprising counts, verify one dimension row per member (uniqueness test).
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

The main job of a dimension table is to:

2

An order number belongs in a dedicated dimension table.

Go deeper

Official documentation

Kimball — conformed and degenerate dimensions
docs