SCD2 Under the Clock: Apply the Change Batch

The review screen (you already learned this)

Level 4's "SCD Type 2" lesson and its loader capstone built the mechanism, and Level 5's "Point-in-Time (As-Of) Joins Against an SCD2 Dimension" graded the join predicate. Neither is retaught here. The compressed version:

• A Type 2 dimension keeps history by adding a version row instead of overwriting. One driver, several rows, each with valid_from, valid_to, and is_current.
• The surrogate driver_key identifies a version, not a driver. The natural driver_id identifies the driver.
• Applying a change is two statements: close the open row by setting its valid_to to the effective date and is_current = 0, then insert the new open row starting at that same date.
• The as-of join predicate is fact_date >= valid_from AND fact_date < valid_to. Half-open on purpose: the closed row's valid_to equals the new row's valid_from, so a fact landing exactly on the change date belongs to exactly one version.

Table

Driver 42's two version rows. The windows touch but never overlap, so every trip attributes to exactly one city.

driver_key	driver_id	home_city	valid_from	valid_to	is_current	trip landing in this window
1	42	Detroit	2025-09-01	2026-03-01	0	T-2002 on 2026-02-20 ($31.00)
2	42	Ann Arbor	2026-03-01	9999-12-31	1	T-2003 on 2026-04-05 ($18.75)

What this lesson adds: the batch, under the clock

Interviewers do not hand you one change. They hand you a batch, and roughly 25 minutes, and they watch you narrate. The batch is where candidates who know the concept still lose the round, for three reasons:

1. No change detection. The batch contains a row for a driver whose city did not actually change. Apply it blindly and you create a version row that says the driver moved from Detroit to Detroit. History is now a lie.
2. Rows that should not be touched. Drivers with no row in the batch must come out byte-identical. A WHERE is_current = 1 with no batch predicate closes everybody.
3. Not safe to run twice. Pipelines retry. If your script doubles the versions when it runs again, it is not a loader, it is a one-shot.

The shape that survives all three

-- 1. Close the open version, but ONLY where the incoming city actually differs.
UPDATE dim_driver_scd
SET valid_to = (SELECT u.effective_date FROM driver_updates u
                WHERE u.driver_id = dim_driver_scd.driver_id),
    is_current = 0
WHERE is_current = 1
  AND EXISTS (SELECT 1 FROM driver_updates u
              WHERE u.driver_id = dim_driver_scd.driver_id
                AND u.new_city <> dim_driver_scd.home_city);

-- 2. Open the new version, skipping any driver that already has it.
INSERT INTO dim_driver_scd (driver_id, driver_name, home_city, valid_from, valid_to, is_current)
SELECT u.driver_id, d.driver_name, u.new_city, u.effective_date, '9999-12-31', 1
FROM driver_updates u
JOIN dim_driver_scd d
  ON d.driver_id = u.driver_id AND d.valid_to = u.effective_date
WHERE NOT EXISTS (
  SELECT 1 FROM dim_driver_scd c
  WHERE c.driver_id = u.driver_id
    AND c.home_city = u.new_city
    AND c.valid_from = u.effective_date
);

The EXISTS ... AND u.new_city <> home_city in step 1 is the change-detection guard: a no-op row never closes anything. The NOT EXISTS in step 2 is the idempotency guard: on a second run the version already exists, so nothing is inserted. Step 2 joins on d.valid_to = u.effective_date, which is exactly the row step 1 just closed, so a driver whose row never closed never gets a new version either.

This shape assumes at most one change per driver in the batch. When a batch carries two changes for the same driver, that scalar subquery in step 1 has no defined answer, and the close date has to be the earliest effective date while the new versions chain between the rest. That is the practice exercise.

Common mistake: closing the old row by deleting it. That is a Type 1 overwrite wearing Type 2 columns. Every historical fact that joined to that version now joins to nothing, and last quarter's revenue by city silently changes. The old row stays forever; only its valid_to and is_current move.

Interview nuance: most candidates answer "which SCD type?" with the number and stop. The complete answer is the number plus the join predicate: "Type 2, so each trip joins on driver_id with completed_at >= valid_from AND completed_at < valid_to, which attributes revenue to the city the driver lived in that day." Say the predicate. It is the sentence that proves you have built one.

Forward pointer: where does the change batch come from on a real platform? A CDC stream off the source database's write-ahead log. Level 9 builds that producer; this lesson is its consumer.

On a real platform this differs. A warehouse writes both statements as a single MERGE INTO dim_driver_scd USING driver_updates ON ... WHEN MATCHED AND target.home_city <> source.new_city THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ..., which is atomic and is what Snowflake, BigQuery, Databricks, and Redshift all expect to see. SQLite has no MERGE, so you write the same semantics as an explicit close-then-insert (or INSERT ... ON CONFLICT(key) DO UPDATE SET ... for a Type 1 upsert). The guards you are writing here are exactly the WHEN MATCHED AND ... conditions of the real statement.