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.

The RPD exposes whichever SCD pattern the warehouse implements. This is how the load actually implements 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 overwrites. Compare the incoming row against the existing one by natural key; if a tracked attribute changed, update it in place.

    UPDATE d_customer SET region = :new_region
     WHERE cust_id = :cust_id;

History is lost, which is the point. Use it for corrections — a misspelled name — where the old value has no meaning.

Type 2 inserts a new version.

    1. find the current row by natural key, where CURRENT = 'Y'
    2. if a tracked attribute changed:
         UPDATE that row: EXP_DATE = today - 1, CURRENT = 'N'
         INSERT a new row: a new surrogate key,
                           EFF_DATE = today,
                           EXP_DATE = 9999-12-31,
                           CURRENT = 'Y'

    SK   CUST_ID  REGION  EFF_DATE    EXP_DATE    CURRENT
    501  C100     North   2020-01-01  2023-06-30  N
    847  C100     South   2023-07-01  9999-12-31  Y

Which attributes are tracked is a business decision, not a technical one. Tracking every column means a new row whenever a phone number changes, and the dimension grows for no analytical benefit.

Tracking only the attributes people slice by — region, segment, grade — keeps it useful.

Type 3 keeps the previous value in a column.

    CURRENT_REGION   PREVIOUS_REGION

One step of history, fixed. It answers "compare under the old structure and the new" and nothing more. Rare, and occasionally exactly right for a reorganisation.

What the fact load must then do. With Type 2, the fact's foreign key must be the surrogate key that was current when the transaction happened:

    lookup: WHERE cust_id = :id
        AND :txn_date BETWEEN eff_date AND exp_date

Not WHERE current = 'Y'. Getting this wrong is the standard defect: every historical fact points at today's version, so all history is restated under the current structure and the Type 2 dimension was built for nothing.

WHERE current = 'Y'

Late-arriving dimensions are the edge case worth naming: a fact arrives for a customer the dimension has not yet loaded. Either reject it, or insert a placeholder row to be updated later — and decide which, because the default is usually a silent failed lookup.

What the interviewer is probing.1. "Describe type 1, 2 and 3." Probing: the fundamentals. Stalls: "They track history." Moves up: type 1 overwrites, type 2 adds a new row with validity dates and a current flag, type 3 keeps a prior-value column.

2. "How does the fact join a type 2 dimension?" Probing: the critical detail. Stalls: "On the key." Moves up: on the surrogate key assigned at load time, so the fact points at the version current when the event happened.

3. "How do you detect a change efficiently?" Probing: the implementation. Stalls: "Compare every column." Moves up: a hash of the tracked attributes compared against the current row, which is one comparison rather than dozens.

4. "What is the trap with late-arriving facts?" Probing: the depth. Stalls: "None." *Moves up:* the dimension version current at the event date must be found, not the current one — which requires a date-range lookup, not a current-flag lookup.