Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing

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.

customers_hist is the current type-2 dimension. customer_cdc is a 2026-08-15 batch of city changes (four 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.

Requirements

  • After applying CDC, every customer_id still has exactly one is_current = true row and no overlapping valid_from/valid_to windows.
  • Output is ordered by customer_id, valid_from.

Constraints

  • customer_cdc only contains customers whose city actually changed (four customers in this batch).
  • Customers not present in customer_cdc keep their full existing history unchanged, including prior (non-current) versions.
  • customer_cdc.city: New city as of change_ts.

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 (four 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.