Skip to content
Back to challenges
Dimensional Modeling FoundationsConcept Challengeintermediate20 min

Price history with SCD2

Extend a starter fact table with a dimension that supports accurate historical price analysis.

How this preview works
Preview this problem before signing in. Sign in to save progress and submit work.

Concept

slowly-changing-dimensions

The primary modeling idea this problem reinforces.

Requirements

4

Business needs the model must satisfy.

Read the concept guide: Slowly changing dimensions: Type 2
Scenario

You inherit a fact table for order items. The business needs a product dimension that supports price changes over time without corrupting historical revenue analysis.

Why this matters

This is the first step where you move from recognizing a pattern to designing the schema. Naming a Type 2 dimension is the easy part. The work is making the fact-to-dimension relationship support correct historical analysis.

Requirements
  • Add a dimension for products with a surrogate primary key.
  • Floor: track when each product-version row becomes effective (effective-from).
  • Declare an explicit foreign-key relationship from the fact table into the dimension surrogate key.
  • Grade up: record when each version expires (effective-to) so the validity window is explicit.
Read-only starter workspace
Inspect the authored starting ERD, then open the workspace to edit and run the checks. Saving your progress needs a free account.

Template

Price history starter workspace

The model users start from in the full workspace.

Entities

1

Seeded tables visible in the public preview.

Dialect

postgresql

Generated SQL target for this starter model.

Mini Map

This preview is read-only. Open the workspace to edit and verify; saving needs a free account.

Open workspace

Try the question first.

The discussion has other people's approaches and solutions. Give it a real attempt before you read them.