Upsert that is safe to run twice
PySpark data engineering interview problem. Difficulty: advanced. Pattern: Upsert. About 20 minutes. Part of the Pro drill bank.
Merge a batch into a table so that retries and stale rows change nothing. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.
target is the current accounts table: account_id, plan, updated_at (a date written as text like 2024-05-01, which sorts in time order). batch is a delivery of changes in the same columns. The delivery system retries, so the batch can contain the same row more than once, and it can also contain a stale version that is older than what target already holds. Return the table after the batch is applied. For each account the version with the latest updated_at wins, whether it comes from target or from batch. When target and batch hold versions with the same updated_at, they are identical, so applying the batch a second time must give the same table as applying it once. Columns: account_id, plan, updated_at. Sort by account_id. Assign the DataFrame to result.
Requirements
- Return the columns account_id, plan, updated_at.
- Exactly 1 row per account.
- Sort by account_id.
Constraints
- Rows with the same account_id and updated_at are identical.
- updated_at strings sort in time order.
Examples
Input: target account_id | plan | updated_at 1 | free | 2024-05-01 2 | pro | 2024-05-03 3 | free | 2024-05-02 batch account_id | plan | updated_at 2 | free | 2024-05-01 2 | pro | 2024-05-03 3 | pro | 2024-05-05 3 | pro | 2024-05-05 4 | free | 2024-05-04 4 | free | 2024-05-04 Output: account_id | plan | updated_at 1 | free | 2024-05-01 2 | pro | 2024-05-03 3 | pro | 2024-05-05 4 | free | 2024-05-04 Account 2 keeps its May 3 version (the stale May 1 row is ignored), account 3 moves to pro, account 4 is new, and the repeated rows count once.
Topics: lakebench, pyspark, idempotent, upsert, late data, retry.
More PySpark interview questions · All interview problems · Learn data engineering
Upsert that is safe to run twice
Interview-style drill: Merge a batch into a table so that retries and stale rows change nothing.
`target` is the current `accounts` table: `account_id`, `plan`, `updated_at` (a date written as text like `2024-05-01`, which sorts in time order). `batch` is a delivery of changes in the same columns. The delivery system retries, so the batch can contain the same row more than once, and it can also contain a stale version that is older than what `target` already holds. Return the table after the batch is applied. For each account the version with the latest `updated_at` wins, whether it comes from `target` or from `batch`. When `target` and `batch` hold versions with the same `updated_at`, they are identical, so applying the batch a second time must give the same table as applying it once. Columns: `account_id`, `plan`, `updated_at`. Sort by `account_id`. Assign the DataFrame to `result`.