SCD Type 2: apply city-change CDC

SQL data engineering interview problem. Difficulty: advanced. Pattern: SCD Type 2. About 35 minutes. Part of the Pro drill bank.

A source system sends a daily file of changes (CDC, change data capture). Today's file tells you which customers changed city. You must update the type 2 customer history so that the old row is closed and a new current row is added. The old history must stay exactly as it is.

customers_hist is the current type 2 dimension. customer_cdc is the batch from 2026-08-15 with the new city for 4 customers. Produce the whole dimension after applying the changes. For a customer whose current row has a different city than the one in the batch, close the current row (set valid_to to the change date and is_current to false) and add a new row with the new city, valid_from set to the change date, valid_to set to 9999-12-31 and is_current true. Customers that are not in the batch, and all their old versions, stay exactly as they are. Return customer_id, full_name, city, valid_from, valid_to, is_current, ordered by customer_id, then valid_from. Do not rebuild the history from scratch. Each customer must have exactly one current row and no overlapping dates.

Requirements

  • Close the current row of each changed customer and set its valid_to to the change date.
  • Add a new current row with the new city and valid_to of 9999-12-31.
  • Keep every other row unchanged, including old versions.
  • Every customer ends with exactly one is_current = true row.
  • Order by customer_id, then valid_from.

Constraints

  • customer_cdc has only customers whose city really changed, four in this batch.
  • Customers that are not in customer_cdc keep their full history.
  • The new valid_from is the date of change_ts, and it must be the same date as the old row's valid_to, so the windows do not overlap.

Examples

Input: -- customers_hist (customer C001) customer_id | full_name | city | valid_from | valid_to | is_current C001 | Asha Rao | Hyderabad | 2025-01-01 | 2026-03-31 | false C001 | Asha Rao | Austin | 2026-04-01 | 9999-12-31 | true -- customer_cdc (customer C001) customer_id | full_name | city | change_ts C001 | Asha Rao | Hyderabad | 2026-08-15 Output: customer_id | full_name | city | valid_from | valid_to | is_current C001 | Asha Rao | Hyderabad | 2025-01-01 | 2026-03-31 | false C001 | Asha Rao | Austin | 2026-04-01 | 2026-08-15 | false C001 | Asha Rao | Hyderabad | 2026-08-15 | 9999-12-31 | true Why this passes: The current Austin row is closed at the CDC change_ts and a new current row opens with the CDC city. The earlier Hyderabad row (valid through 2026-03-31) is untouched history and is carried through as-is.

Topics: SCD2, MERGE, dimensions.

More SQL interview questions · All interview problems · Learn data engineering

advanced

SCD Type 2: apply city-change CDC

Production ticket: Close current customer versions and open new ones from customer_cdc without overlapping history.

`customers_hist` is the current type-2 dimension. `customer_cdc` is a 2026-08-15 batch of city changes (4 customers). Produce the **full** dimension after applying CDC: - customer_id, full_name, city, valid_from, valid_to, is_current - For a changed current row: set valid_to to the CDC change_ts and is_current = false, then insert a new row (new city, valid_from = change_ts, valid_to = 9999-12-31, is_current = true) - Unchanged customers keep every historical version as-is - No overlapping windows; exactly one is_current per customer_id - Order by customer_id, valid_from Do not rebuild history from scratch by dropping old versions.