Product price and category history

A commerce merchandising team changes product price and category over time. Finance needs order revenue by the product price and category that were valid when each order item was purchased, while operations still needs an easy way to find the current product row.

A Type 2 dimension is only useful when it preserves both the version identity and the validity window. Without a surrogate row key, effective dates, and current-row signaling, historical joins become fragile.

• Add a product dimension with a surrogate primary key and natural product id attribute.
• Track price and category as versioned product attributes.
• Version with an effective-from and point order items at the product version surrogate key, so revenue is reportable as of the order date (the floor).
• Grade up by closing each version with an effective-to, then adding a current-row flag for the operations current-lookup need.
• Declare an N:1 relationship and foreign key from order items to the product surrogate key.