SCD Type 1 vs Type 2 — how do you apply them in ETL?

SCD Type 1 vs Type 2 — how do you apply them in ETL?medium

Type: scenario
Topic: scd-type-1-vs-type-2
Frequency: common
Tags: dbms, scd, type, how, did

Answer

SCD Type 1 overwrites the old value (no history). SCD Type 2 adds a new row with effective/expiry dates (full history).

Explanation

SCD Type 1 overwrites the old value (no history). SCD Type 2 adds a new row with effective/expiry dates (full history). Dimension tables like product catalog used Type 2 to track price changes over time; operational tables used Type 1. Implemented via merge/upsert logic in PySpark processing CDC data from Amazon DMS.

Follow-upWhat tradeoffs did you consider in that implementation?