Modeling for analytics

Pro lesson Intermediate

Star schemas

Build the model most data warehouses are organized around.

10 min9 hands-on steps

In this lesson you will

  • Recognize a star schema and why every dimension is one join away
  • Give dimensions surrogate keys, and keep sales with no known customer
  • Build a date dimension and answer questions across the star

Words you'll meet

Star schema
A fact table in the middle, joined directly to each of its dimension tables around it.
Natural key
The id a source system gives a thing, like customer 1 in the tills.
Surrogate key
A meaningless number the warehouse gives each dimension row. Facts point at it.
Unknown member
A dimension row with key -1, standing in for "we don't know", so facts never lose their match.
Date dimension
One row per calendar day, with handy columns like day name, month and is_weekend.

The steps

Each step teaches one idea, then has you try it hands-on in your browser, and checks your work.

  1. 1Fact in the middle
  2. 2Two customer 1s
  3. 3Surrogate keys
  4. 4Who vanished?
  5. 5The unknown member
  6. 6Load the fact
  7. 7Query the star
  8. 8A date dimension
  9. 9Challenge

This lesson is part of LearnDE Pro

$12 a month or $100 a year: every lesson, spaced-repetition flashcards, and Learn by teaching. Refund on request within 14 days.