How do you implement Slowly Changing Dimension (SCD) Type 2 in Databricks?

SCD Type 2 tracks historical changes by creating a new record for each change and keeping the old record. In Databricks you typically use Delta Lake to store the dimension table. The steps are:

1. Schema: Add columns effective_start, effective_end, and is_current to the dimension table.
2. Merge: When new source data arrives, perform a MERGE (upsert) against the Delta table. For each source row, compare the key and the business attributes. If the key exists and any attribute has changed, set is_current to false and effective_end to the current timestamp for the existing row, then insert a new row with the new attributes, effective_start as the current timestamp, effective_end as a sentinel (e.g., 9999-12-31), and is_current true.
3. Idempotency: Use the MERGE condition on the key and the business attributes to avoid duplicate inserts.
4. Querying: To get the current view, filter is_current = true. To get history, query the full table.

Example Delta Lake SQL:

MERGE INTO dim_customer tgt
USING source src
ON tgt.customer_id = src.customer_id
WHEN MATCHED AND (tgt.name <> src.name OR tgt.address <> src.address) THEN
  UPDATE SET tgt.is_current = false, tgt.effective_end = current_timestamp()
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, address, effective_start, effective_end, is_current)
  VALUES (src.customer_id, src.name, src.address, current_timestamp(), '9999-12-31', true);

This approach ensures that every change is captured with its own row, preserving history while keeping the current state easily queryable.