PySpark data engineering interview problem. Difficulty: advanced. Pattern: SCD Type 2. About 20 minutes. Part of the Pro drill bank.
SCD2 via closing current rows and unioning new versions (no Delta MERGE). Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.
For customers in updates, close matching current history rows by setting valid_to to the update's valid_from and is_current=0. Keep untouched history rows. Append new current rows from updates. Order by customer_id, valid_from. Assign result.
Input: history + updates Output: customer_id | name | city | valid_from | valid_to | is_current 1 | Ada | NYC | 2024-01-01 | 2024-06-01 | 0 1 | Ada | SEA | 2024-06-01 | None | 1 2 | Alan | SFO | 2024-01-01 | None | 1 Ada gets a closed NYC version and a current SEA version.
Topics: lakebench, pyspark, scd2, union.
More PySpark interview questions · All interview problems · Learn data engineering
Interview-style drill: SCD2 via closing current rows and unioning new versions (no Delta MERGE).
For customers in `updates`, close matching current `history` rows by setting `valid_to` to the update's `valid_from` and `is_current=0`. Keep untouched history rows. Append new current rows from `updates`. Order by `customer_id`, `valid_from`. Assign `result`.