Model a product dimension that preserves price history
Extend a starter fact table with a dimension that supports accurate historical price analysis.
Concept
slowly-changing-dimensions
The primary modeling idea this problem reinforces.
Objectives
2
Concrete things this problem is meant to teach.
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.
This is the first modeling step where students have to move from pattern recognition into an actual schema design. The challenge is not just naming a Type 2 dimension, but making the fact-to-dimension relationship support correct historical analysis.
Use the sandbox to add the missing product dimension, model the validity window, and declare the fact-table foreign key to the surrogate primary key.
- Add a dimension for products with a surrogate primary key.
- Track when each product-version row becomes effective and when it expires.
- Declare an explicit foreign-key relationship from the fact table into the dimension surrogate key.
- The model includes a dedicated product dimension rather than keeping product history on the fact.
- The dimension makes row-version validity explicit with effective-from and effective-to metadata.
- The fact joins through an explicit foreign key to the dimension surrogate primary key so historical reporting remains reliable.
- The fact table already exists; focus on the product side of the model.
- Keep the design queryable for historical revenue by product version.
- A natural business identifier can exist without being the primary join key.
- Think about which columns let analysts tell when a product version starts and stops being active.
- The fact-side product key should reference the dimension surrogate primary key, not just visually point at the table.
- Using the natural product identifier as the only primary join key for versioned history.
- Adding changing product attributes to the fact table instead of modeling a reusable dimension.
- Tracking current-versus-historical state without a complete effective date range.
- Drawing a relationship line without declaring the foreign-key constraint that enforces the intended join.
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.
Preview mode is read-only. Create an account when you are ready to edit and submit work.
Sign up to editProduct dimension with surrogate key and effective dates
Following Kimball Type 2 slowly changing dimension guidance, separate stable business identity from versioned product attributes and validity windows.
Optimizes for
- • Historical reproducibility
- • Reusable dimension
Trade-offs
- • Extra ETL complexity
- • Needs current-row logic
Layer 1
Structural
Generic ERD correctness such as keys and relationships.
Layer 2
5 checks
Authored checks for this exact modeling scenario.
Layer 3
On demand
AI critique after deterministic checks are satisfied.
Product Dimension Exists
Create a dimension-style entity for product history instead of keeping all product attributes on the fact.
Product Effective From
Add an effective-from temporal column so each product version has a validity start.
Product Effective To
Add an effective-to temporal column so analysts can tell when each product version expires.
Order Items Product Relationship
Connect the order-items fact to the product dimension with an N:1 relationship.
Fact Fk Targets Product Surrogate
Declare a fact-table foreign key that references the product dimension surrogate primary key rather than a natural identifier.