RPDadvanced
Slowly Changing Dimensions in the RPD
Explain how Type 1 and Type 2 slowly changing dimension patterns are modeled and reported on within the RPD's logical layer.
Slowly Changing Dimensions (SCDs) are primarily an ETL/data-warehouse design concern, but the RPD has to correctly *expose* whichever SCD pattern the warehouse implements, and interviewers use this topic to test whether you understand the handoff between data modeling and semantic modeling.
It's like a passport with stamps for every address you've ever lived at (Type 2) versus a driver's license that just gets reprinted with your new address every time you move (Type 1) — one preserves your full history, the other only ever shows the present.
Key Concepts
1
For a Type 1 SCD (overwrite — history is not preserved, the dimension row is simply updated in place), the RPD modeling is straightforward: one dimension row per business key, current attributes only, no special handling needed beyond a normal logical dimension.
Type 1
2
For a Type 2 SCD (preserve full history — a new row is inserted for each change, with EFFECTIVE_DATE/EXPIRY_DATE or IS_CURRENT flag columns and a surrogate key that's unique per *version* of the entity, not per business entity), the RPD must be modeled so that fact-to-dimension joins use the surrogate key (which correctly ties each historical fact row to the dimension attributes *as they were at the time of the transaction*), while a separate business/natural key is exposed for "current value" style reporting or cross-version aggregation (e.g., "total revenue for this customer across all their history, regardless of which address version was active at the time"). This often requires two logical dimension table sources or careful column-level formulas: one path through the surrogate key for historically-accurate fact joins, and a IS_CURRENT = 'Y' filtered path for "current attribute" browsing/prompting.
Type 2EFFECTIVE_DATEEXPIRY_DATEIS_CURRENTIS_CURRENT = 'Y'
3
A classic interview trap is asking what happens if you join facts to dimensions using the *natural key* instead of the surrogate key on a Type 2 dimension — historical facts would incorrectly display the customer's *current* address/attributes instead of what was true at the time of the transaction, silently corrupting historical trend analysis. Recognizing and explaining this failure mode is a strong signal of real data-warehousing depth.