Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Delta MERGE for upserts and SCD2

Snowflake, BigQuery & Databricks · Databricks

Delta MERGE for upserts and SCD2

Hardwarehouses-48
databricksdelta-lakemergescd2change-detection

Question

How do you implement SCD Type 2 with Delta MERGE?

Solution

To build an SCD Type 2 dimension with Delta, you need to do two things for each changed key: close the current row by setting its end date, and insert a new row as the current version. A single MERGE can do both, with a small trick for the insert.

Setup

The target dim_customer has customer_id, tracked attributes (city, plan), valid_from, valid_to, is_current and a row_hash of the tracked attributes. The source has the latest version of each changed customer, deduplicated.

The trick: duplicate the changed rows

A MERGE row can only match once, so a changed customer would either update or insert, not both. To get both, build the source with two copies of each changed row. One copy has the real key, which matches the current row so it can be closed. The other copy has a NULL merge key, so it matches nothing and is inserted as the new version.

MERGE INTO dim_customer t
USING (
  SELECT s.customer_id AS merge_key, s.* FROM updates s
  UNION ALL
  SELECT NULL AS merge_key, s.*
  FROM updates s
  JOIN dim_customer d
    ON d.customer_id = s.customer_id AND d.is_current AND d.row_hash <> s.row_hash
) m
ON t.customer_id = m.merge_key AND t.is_current
WHEN MATCHED AND t.row_hash <> m.row_hash THEN
  UPDATE SET t.is_current = false, t.valid_to = m.effective_date
WHEN NOT MATCHED THEN
  INSERT (customer_id, city, plan, valid_from, valid_to, is_current, row_hash)
  VALUES (m.customer_id, m.city, m.plan, m.effective_date, NULL, true, m.row_hash);

Brand-new customers appear once, with their real key, match nothing, and are inserted. Changed customers appear twice: the first closes the old row, the second inserts the new one.

Details

  • The hash comparison finds real changes, so rows that did not change are untouched and not rewritten.
  • Dedupe the source by key and take the latest version. If several changes for one key arrive in a batch, you must order them, or apply them one by one.
  • Late-arriving data, which has an effective date before the current row, needs extra care, since it may need to split history.

The easier way

Lakeflow Declarative Pipelines' AUTO CDC ... STORED AS SCD TYPE 2 handles ordering by a sequence column, out-of-order events and history columns for you, in a few lines. Use it when you are already on pipelines. Say that you know the manual MERGE so that you understand what the framework does.

PreviousNext