Slowly Changing Dimensions: ETL Implementation Patterns

Explain how SCD Type 1, Type 2, and Type 3 patterns are actually implemented in an ETL load process, complementing the RPD-side SCD topic.

While the RPD-side SCD topic covers how OBIEE *exposes* slowly changing dimensions, this topic covers how the ETL process actually *implements* the change-tracking logic during a load — a distinction interviewers deliberately test to see whether a candidate understands both sides of the same problem, since a wrongly-loaded SCD dimension breaks reporting no matter how well the RPD is modeled on top of it.

Type 1 is like erasing and rewriting a whiteboard — the old value is simply gone. Type 2 is like keeping every previous whiteboard photo ever taken, dated and filed away, so you can look up exactly what it said on any past date. Type 3 is like a whiteboard with a small sticky note showing just what was written there before the current text, but nothing further back than that.

Key Concepts

Type 1 (overwrite) is the simplest ETL pattern: the load process compares incoming source rows against existing dimension rows by natural key, and if any tracked attribute changed, it simply UPDATEs the existing row in place — no history is kept, which is appropriate for corrections (fixing a typo in a customer name) rather than meaningful business changes.

Type 1UPDATE

Type 2 (add new row) is the most common pattern for genuinely historical attributes: the ETL process detects a change, expires the current row (setting its EXPIRY_DATE and IS_CURRENT = 'N'), and inserts a new row with a new surrogate key, the updated attribute values, a new EFFECTIVE_DATE, and IS_CURRENT = 'Y'. This requires the ETL to correctly perform surrogate key lookups during fact table loading — every fact row must resolve to the dimension surrogate key that was current *at the time of the transaction*, not simply the latest one, which is exactly why Type 2 dimension loads are more complex than Type 1.

Type 2expires the current rowinserts a new rowsurrogate key lookupsEXPIRY_DATE

Type 3 (add new column) is a less common hybrid: rather than a new row, the dimension table gets both a "current value" and a "previous value" column for a specific tracked attribute — useful when you only need to compare "before vs. after" for one most-recent change, not full unlimited history, and it avoids the row-multiplication of Type 2 at the cost of only remembering one prior state.

Type 3

A strong interview answer connects this back to the RPD topic explicitly: whichever ETL pattern is chosen determines what the RPD developer *can* expose — a Type 1-loaded dimension can never support "as transacted" historical reporting no matter how the RPD is modeled, because the history was never preserved in the first place.