Dimensional Modeling
Advanced 16 min read Dimensional Modeling

Star Schemas

The shape that makes analytical SQL almost trivial

A star schema is one fact table surrounded by its dimension tables, joined on surrogate keys. It is the canonical BI shape because queries stay simple, joins stay cheap and metrics stay consistent.

star schemastarone-to-manydenormalizedquery pathfacts + dims
Prerequisites: Fact Tables Dimension Tables
Before we start

What you will learn

  • Design a clean star with correct cardinalities
  • Explain why star joins are cheap and consistent
  • Evaluate when to add or avoid extra linked facts/dims
Introduction

What is this?

A star schema arranges one fact table in the center and its dimension tables on the spokes. Every fact row joins each dimension exactly once via surrogate keys (many-to-one), so group-by queries use simple inner-joins and aggregate fast. Conforming the shared dimensions across stars is what gives the whole warehouse one vocabulary.

Motivation

Why do you need it?

  • Simple joins mean simple, fast SQL and fewer mistakes.
  • Smaller dimension tables behave like lookups — the optimizer loves them.
  • All metrics from one fact inherit the same dimensions, so slices agree.
  • Adding a new question usually means a new attribute or a new star, not a refactor.
Think about it

Mental model

Imagine

A restaurant menu organized by course

Think of

A star schema

Every table (star) groups appetizers, mains, desserts. Shared knowledge ('is there pork?') lives once per course card so every waiter gives the same answer — that's the conformed vocabulary across your stars.

Mechanics

How it works

  • Fact table holds measures and one FK per dimension spoke.
  • Dimensions are unique on the surrogate key (1 row per member).
  • Queries JOIN fact (many) → dim (one): never a fan trap as long as dims are unique.
  • Queries GROUP BY dim attributes and aggregate fact measures.
  • Multiple stars share conformed dims so 'region' means the same everywhere.

The classic star.

dim_date
dim_customer
fact_sales
dim_product
GROUP BY
Hands-on

Examples

Star join stays one-level deep

Star join stays one-level deep
SELECT c.region, p.category, d.fiscal_quarter, SUM(f.gmv)
FROM fact_sales f
JOIN dim_customer c ON f.cust_key   = c.cust_key
JOIN dim_product   p ON f.prod_key   = p.prod_key
JOIN dim_date      d ON f.date_key   = d.date_key
GROUP BY c.region, p.category, d.fiscal_quarter;

Every join is many-to-one into a unique, small dimension row. No nested subqueries, no fan traps — the star's entire reason for existing.

Real world

In production

  • An operational reporting star serves SLA dashboards by joining a fresh fact to shared store and date dims.
  • A finance star (fact_rev) and a sales star (fact_orders) share dim_customer and dim_date, so both reports slice the same way.
  • A lakehouse store serves parquet ice tables in star shape to Superset with pre-computed surrogate keys.
Guidelines

Best practices

  • Keep dims unique on surrogate key and use many-to-one joins from the fact.
  • Share conformed dims across stars instead of copying attributes per star.
  • Aggregate only-to-target-grain; never join two facts without reconciling grain.
  • Add only the attributes people filter on — a lean star beats a fat schema.
Watch out

Common mistakes

  • Joining two stars' facts directly, welding grain bugs into one report.
  • A dimension with duplicate surrogate keys, reintroducing fan traps.
  • Embedding date attributes in multiple dims, so 'month' means different things.
  • Growing one star with new facts until the grain is no longer declarable.
Root cause analysis

Common issues

Error
Two stars report different regional totals
Root cause

Each star has its own (non-conformed) region attribute

Fix

Share one dim_region; certify its definition across both stars.

Error
Distinct customers double-counted across product group-bys
Root cause

Fact joined to product at finer grain than the count

Fix

Count distinct at the fact-member grain, not the joined output grain.

When things break

Debugging tips

  • Totals double? Check dimension key uniqueness with a HAVING COUNT(*)>1 query.
  • Slices disagree between stars? Check whether they share conformed dims or copied attributes.
  • Slow star? Verify the fact is the only large table and it's partitioned/maintained.
Interviews

Interview checkpoint

Practice

Hands-on lab

Model, Break and Repair a Star
40 min · advanced
In progress
Objectives
  • Assemble a working star with unique dims
  • Inject the classic fan trap (duplicate dim key)
  • Repair and verify totals reconcile to source

Model, Break and Repair a Star

40 min · advanced

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

Quick quiz

0/2
1

In a correct star join, cardinality between fact and dimension is:

2

Stars should never share dimensions because coupling is bad.

Go deeper

Official documentation

Kimball — star schema overview
docs