Analytics Engineering
Start free
← All projects
DATA MODELINGintermediate

Data Modeling: The Great Dimension Quest

Dimensional-modeling exercise: build a star schema from a relational source

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

  1. Surrogate keys — stable integer keys for your dimensions instead of fragile natural IDs.
  2. Slowly Changing Dimensions (SCD Type 2) — preserve a customer's history with effective-date columns.
  3. Bridge tables — model the many-to-many between orders and promotions.
  4. Hierarchical dimensions — fold Region → City → Store into one dim_locations.
  5. 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.

Project plan

5 steps to ship this

Draft each step inline below. Reveal the worked solution when you're ready to compare.

  1. Surrogate Key Forging 🔑

    The kingdom's ancient scribes decree that natural IDs are fragile as parchment in water. Instead, we must forge powerful surrogate keys in the fires of Mount Data to preserve stability in the dim_customers and dim_products scrolls.

    Use your mystic powers to drag the appropriate columns into each dimension. Remember the ancient wisdom:customer_key and product_key shall be the primary keys of power, while natural IDs remain as subordinate attributes.

    Pick an item, then click the section it belongs in. Click again to put it back.

    Pool · 7 remaining

    dim_customers

    Empty

    dim_products

    Empty

  2. Time-Shifted Customer Dimension ⏳

    The High Council demands we track the changing attributes of dim_customers throughout the ages. You must employ the mystical Type 2 Slowly Changing Dimension spell. Add the powerful time-keeping columns effective_start_date, effective_end_date, and current_flag to complete this temporal enchantment.

    Pick an item, then click the section it belongs in. Click again to put it back.

    Pool · 3 remaining

    dim_customers (SCD Chamber)

    Empty

  3. Bridge of Multi-Promotion 🌉

    The merchants speak of Orders blessed by multiple Promotion spells at once! You must build dim_promotions with its own powerful surrogate key and mystical attributes, then conjure a bridge_order_promotions table to create arcane links between many orders and many promotions.

    Pick an item, then click the section it belongs in. Click again to put it back.

    Pool · 7 remaining

    dim_promotions

    Empty

    bridge_order_promotions

    Empty

  4. The Location Labyrinth & Dual Facts 🗺️

    Before you stands the complex dim_locations dimension - a mighty hierarchy of the realm (region → city → store). Meanwhile, two powerful fact tables await your wisdom:

    • fact_orders - the chronicle of all sales transactions across the kingdom.
    • fact_inventory - the magical ledger that tracks product quantities in each store's vault.

    Channel your inner data wizard to place each column in its correct dim_locations, fact_orders, or fact_inventory domain. The kingdom's future depends on your wisdom!

    Pick an item, then click the section it belongs in. Click again to put it back.

    Pool · 14 remaining

    dim_locations

    Empty

    fact_orders

    Empty

    fact_inventory

    Empty

  5. The Final Blessing of Data Types ⚙️

    At last, brave one, you must bestow upon each column a fitting data type incantation (INT, VARCHAR, DATE, DECIMAL). Choose wisely from the ancient scrolls, for each selection is crucial to the kingdom's data integrity and the success of your quest!

    Pick an item, then click the section it belongs in. Click again to put it back.

    Pool · 8 remaining

    Data Types (Complete the Final Enchantment)

    Empty