Apply a change feed with deletes

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

Apply inserts, updates and deletes from a change feed to a customer table. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

target is the current customers table: customer_id, name, tier. changes is a change feed with customer_id, op, name, tier and seq. op is I (insert), U (update) or D (delete). seq orders the changes: a higher seq happened later. Delete rows carry no name or tier. Return what target looks like after every change is applied in seq order: a customer whose last change is I or U has the name and tier from that change a customer whose last change is D is gone a customer with no change is unchanged Columns: customer_id, name, tier. Sort by customer_id. Assign the DataFrame to result.

Requirements

• Return the columns customer_id, name, tier.
• Sort by customer_id.

Constraints

• seq is unique across the feed.
• A D row has no name and no tier.
• A customer can have several changes in the feed.

Examples

Input: target customer_id | name | tier 1 | Ann | gold 2 | Bo | silver 3 | Cy | bronze 4 | Di | gold changes customer_id | op | name | tier | seq 2 | U | Bo | gold | 1 3 | D | NULL | NULL | 2 5 | I | Ed | silver | 3 2 | U | Bo | platinum | 4 6 | I | Fay | bronze | 5 6 | D | NULL | NULL | 6 4 | D | NULL | NULL | 7 4 | I | Di | silver | 8 Output: customer_id | name | tier 1 | Ann | gold 2 | Bo | platinum 4 | Di | silver 5 | Ed | silver Customer 2 ends as platinum (the later update wins), 3 is deleted, 5 is new, 6 was inserted and deleted so it never appears, and 4 was deleted and re-inserted as silver.

Topics: lakebench, pyspark, CDC, delete, merge, left_anti.

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