Star Schema

One long, skinny table of things that happened, surrounded by short, wide tables describing the nouns involved.

DE Foundations · Beginner · ~6 min

In plain English

A receipt spike on a shop counter, plus a folder of customer cards and a folder of product cards. The spike grows forever; the folders stay thin and describe who and what.

Why it's worth your time

It's the model every BI tool, every optimiser and every analyst already expects — fighting it means fighting all three.

If you remember three things

Grain first: write down what one row means before loading anything
Facts hold keys and additive measures; dimensions hold everything you filter by
Denormalised on purpose — the join you avoid costs more than the bytes you save

Overview

A star schema splits the warehouse into facts and dimensions. A fact table holds events — one row per order line, per click, per payment — with foreign keys and numeric measures, and it is the table that grows forever. Dimension tables hold the descriptive nouns — customer, product, store, date — and stay small enough to sit in memory. Every analytical question then has the same shape: filter and group by dimension attributes, aggregate fact measures. The engine broadcasts the small dimensions to every worker and scans the fact table once. That single, predictable shape is why the star schema has outlived every framework built on top of it.

In an interview

A star schema has one fact table of events — thin rows, foreign keys and numeric measures, billions of rows — surrounded by small dimension tables that describe the entities. Queries filter and group on dimension attributes and aggregate fact measures. It is denormalised on purpose: dimensions are small enough that repeating a country name is cheaper than the join you avoid.

Production defaults

Grain: one sentence in the table description, before the first load
Surrogate keys: every dimension, so history can be versioned
Date dimension: ship one on day one — fiscal weeks and holidays are business logic
Unknown member: key -1 in every dimension, so late facts land somewhere

What breaks

Revenue is exactly double — Two loaders wrote at two grains, or a one-to-many join fanned the fact table. Check row counts against the declared grain.
Percentages sum to nonsense — A rate was stored as a measure. Store numerator and denominator and divide at query time.

Watch it explained

What is STAR schema | Star vs Snowflake Schema | Fact vs Dimension Table — codebasics, 6:59