The Great Dimension Quest. Hail, data explorer. The Kingdom of Retailia runs on commerce — customers in a hundred villages, products in every category, promotions sweeping the land, and stores from the mountains to the coast. The Royal Council has a transactional database that records every sale and every stock count, but it's a tangle of normalized tables nobody can analyze. Your quest: forge it into a clean star schema the kingdom's analysts can actually query.
You'll work through five chambers below — each a hands-on puzzle where you sort columns into the dimension, fact, or bridge tables where they belong. Together they build one coherent dimensional model.
The source you're modeling
- Customers — people across the realm, whose details change over time.
- Products — merchandise grouped into categories.
- Promotions — discounts that can apply to many orders at once (and an order can carry many promotions).
- Locations — a Region → City → Store hierarchy.
- Sales Orders — the transaction fact: who bought what, where, when, for how much.
- Inventory — a second fact: stock on hand per product per store.
The techniques you'll practice
- Surrogate keys — stable integer keys for your dimensions instead of fragile natural IDs.
- Slowly Changing Dimensions (SCD Type 2) — preserve a customer's history with effective-date columns.
- Bridge tables — model the many-to-many between orders and promotions.
- Hierarchical dimensions — fold Region → City → Store into one
dim_locations. - Conformed facts + data types — add a second fact table and assign the right type to every column.
How to work the quest
Each chamber below presents a set of columns and the tables they could belong to. Pick a column, click the table it belongs in, then Check answer. Reveal the worked solution at the end to compare your model against a reference star schema. The in-page tutor can talk through any modeling decision with you.