SQL data engineering interview problem. Difficulty: advanced. Pattern: Incremental. About 18 minutes. Part of the Pro drill bank.
customer_changes holds every version of every customer. load_state has one row for customer_changes with last_loaded_at, the high-water mark of the previous load. Return the rows an incremental load must pick up: versions with updated_at strictly after last_loaded_at, keeping only the newest version per customer_id. Read the watermark from load_state, do not type it in. Columns: customer_id, email, updated_at. Order by customer_id.
Input: customer_changes change_id | customer_id | email | updated_at 1 | C1 | c1@old.com | 2024-06-01 08:00:00 2 | C1 | c1@new.com | 2024-06-12 09:00:00 3 | C1 | c1@newest.com | 2024-06-14 10:00:00 4 | C2 | c2@x.com | 2024-06-05 10:00:00 5 | C3 | c3@x.com | 2024-06-10 00:00:00 6 | C4 | c4@x.com | 2024-06-11 07:00:00 7 | C5 | c5@a.com | 2024-06-09 23:59:59 8 | C5 | c5@b.com | 2024-06-20 12:00:00 load_state table_name | last_loaded_at customer_changes | 2024-06-10 00:00:00 Output: customer_id | email | updated_at C1 | c1@newest.com | 2024-06-14 10:00:00 C4 | c4@x.com | 2024-06-11 07:00:00 C5 | c5@b.com | 2024-06-20 12:00:00 Why this passes: Watermark is 2024-06-10 00:00:00. C1 has two newer versions, only the 14 June one is kept. C3 is exactly at the watermark and C2 is older, so both are skipped.
Input: customer_changes change_id | customer_id | email | updated_at 1 | C1 | a@x.com | 2024-06-11 10:00:00 load_state table_name | last_loaded_at customer_changes | 2024-06-10 00:00:00 Output: customer_id | email | updated_at C1 | a@x.com | 2024-06-11 10:00:00 Why this passes: One new version after the watermark.
Topics: lakebench, sql, watermark, incremental, latest version.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: Select only rows changed after the last load, keeping the newest version of each customer.
`customer_changes` holds every version of every customer. `load_state` has one row for `customer_changes` with `last_loaded_at`, the high-water mark of the previous load. Return the rows an incremental load must pick up: versions with `updated_at` **strictly after** `last_loaded_at`, keeping only the newest version per `customer_id`. Read the watermark from `load_state`, do not type it in. Columns: `customer_id`, `email`, `updated_at`. Order by `customer_id`.