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.
- 1Fact in the middle
- 2Two customer 1s
- 3Surrogate keys
- 4Who vanished?
- 5The unknown member
- 6Load the fact
- 7Query the star
- 8A date dimension
- 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.