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

1
2
3
4
5
🏆

📜 Prologue: Your Great Mission

Hail, brave data explorer! You have arrived in the Kingdom of Retailia, where lords and ladies of commerce gather in the grand halls. The Royal Council has summoned you, for they need your wisdom and skill to forge a powerful data model that can analyze sales throughout the realm, manage the kingdom's vast inventory, and track the magical promotions across many distant lands (stores).

Your quest has several chapters, each testing your mastery of the arcane arts of data modeling. Each step will ask you to gather specific mystical columns into dimension or fact "chambers." Master them all to complete your epic dimensional model and earn the title of Data Archmage!

Here is the ancient lore you must study to succeed on your journey:

  • 👤 Customers from villages, towns and cities across the kingdom.
  • 📦 Products sorted into magical categories of merchandise.
  • 🎁 Promotions that bestow savings and enchantments across the land.
  • 🏠 Locations (Region → City → Store) where merchants establish their commerce hubs.
  • 📊 Sales Orders (fact table) containing the sacred scrolls of transaction details.
  • 📋 Inventory (another fact) to track the mystical stock levels in each store.

Your path through this perilous quest:

  1. Forge powerful dimension tables with surrogate keys to bind your data.
  2. Imbue Slowly Changing Dimensions (SCD) to capture the ancient history of Customers.
  3. Construct a bridge table to channel the energy of multiple promotion spells.
  4. Master the hierarchical dimension for tracking the Locations across the realm.
  5. Summon a second fact table (Inventory) to balance with the first.
  6. Assign the proper data types to each column for the final blessing and enchantment.

🏆 Final Chapter: Triumph & Celebration

Certificate of Dimensional Mastery

Hear ye, hear ye! By royal decree of King Retailia, we hereby certify that the bearer of this document has been bestowed the honorable title of:

Data Archmage of the Realm

For successfully completing every trial of the Great Dimension Quest, forging stable surrogate keys, taming the wild Slowly Changing Dimensions, linking promotions with a mighty bridge, conquering the treacherous location hierarchies, and bestowing proper data types upon the kingdom's sacred columns.

Rejoice! You have completed every chapter of the Great Dimension Quest! The Royal Court erupts in celebration! Merchants cheer your name throughout the marketplace, the wizards of analytics sing ballads of your prowess, and the tables themselves glow with perfect relational harmony, their entities perfectly normalized.

Your model shall stand as a shining example in the Great Library of Data, inspiring all future data knights and dimensional wizards for generations to come!

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 kingdommaf_single_qoutes 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 storemaf_single_qoutes vault.

    Channel your inner data wizard to place each column in its correct dim_locations, fact_orders, or fact_inventory domain. The kingdommaf_single_qoutes 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 kingdommaf_single_qoutes 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