SCD Type 2 Merge

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

A type 2 slowly changing dimension keeps the full history of a record. When something changes, such as a customer moving to a new city, you do not overwrite the old row. You close it by giving it an end date, and add a new row that is current from that date on.

history has the versions of each customer: customer_id, name, city, valid_from, valid_to and is_current (1 for the current row). updates has new rows with customer_id, name, city and valid_from. For each customer in updates, close the current row in history: set its valid_to to the valid_from of the update and its is_current to 0. Keep the rows of customers that are not in updates as they are. Then add the rows from updates as the new current rows (valid_to empty, is_current 1). Return all the rows, sorted by customer_id and valid_from. The editor creates both tables and ends with result = history. Change the last line to build your answer.

Requirements

• Close the current row of each customer in updates (valid_to becomes the update's valid_from, is_current becomes 0).
• Keep the history rows of customers that are not in updates unchanged.
• Add the update rows as new current rows with valid_to empty and is_current 1.
• Return customer_id, name, city, valid_from, valid_to, is_current, sorted by customer_id, valid_from.

Constraints

• The three parts (closed, unchanged, new) must have the same columns in the same order before union.
• The new row starts on the same date that the old row ends, so there is no gap.
• Do not delete the old row. Closing it keeps the history.

Examples

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