QuickDevGuide

Home › ETL - Talend & SSIS

Slowly Changing Dimensions (SCD) — ETL - Talend & SSIS

Dimensions change over time (a customer moves city). SCD types define how history is kept: Type Behaviour Use when 0 never change fixed attributes (birth date) 1 overwrite corrections, no history needed 2 new row per change with validfrom / validto / iscurrent reports must reflect history ("sales by city at the time") 3 keep previous value in an extra column only one level of history SCD Type 2 load steps Find incoming rows whose tracked attributes differ from the current row. Expire the current row (validto = loaddate, iscurrent = 0). Insert a new current row with a new surrogate key. Insert brand-new business keys as current rows. Facts reference the surrogate key, so old sales stay attached to the old city.

Key Takeaways