Re-run an incremental load safely

SQL data engineering interview problem. Difficulty: advanced. Pattern: Incremental. About 22 minutes. Part of the Pro drill bank.

customers_current is the current state of a customer table. customer_changes holds every version of every customer. load_state has 1 row for customer_changes with last_loaded_at, the watermark: the time of the newest change that the previous load already took in. Do not change any of these tables. Work on a copy: 1. Create a table named customers_work with the same rows as customers_current. 2. Load into it the newest version of each customer among the changes with updated_at strictly after the watermark. A customer that does not exist yet is inserted. An existing customer is updated only when the change is strictly newer than the row already in customers_work; otherwise the row is left alone. 3. The scheduler retried the job, so run the same load a second time in your script. The table must be the same as after the first run. 4. Finish with a SELECT of customers_work. Read the watermark from load_state. Columns: customer_id, email, updated_at. Order by customer_id. The script must be safe to run twice.

Requirements

• Create customers_work from customers_current.
• Return the columns customer_id, email, updated_at.
• Order by customer_id.

Constraints

• customer_id is the key of both tables.
• Do not modify customers_current, customer_changes or load_state.
• The script runs twice in one session.
• Two changes for one customer never share the same updated_at.

Examples

Input: customers_current customer_id | email | updated_at C1 | [email protected] | 2024-06-12 09:00:00 C3 | [email protected] | 2024-06-10 00:00:00 C5 | [email protected] | 2024-06-25 08:00:00 C6 | [email protected] | 2024-05-01 12:00:00 customer_changes change_id | customer_id | email | updated_at 1 | C1 | [email protected] | 2024-06-01 08:00:00 2 | C1 | [email protected] | 2024-06-12 09:00:00 3 | C1 | [email protected] | 2024-06-14 10:00:00 4 | C2 | [email protected] | 2024-06-05 10:00:00 5 | C3 | [email protected] | 2024-06-10 00:00:00 6 | C4 | [email protected] | 2024-06-11 07:00:00 7 | C5 | [email protected] | 2024-06-09 23:59:59 8 | C5 | [email protected] | 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 | [email protected] | 2024-06-14 10:00:00 C3 | [email protected] | 2024-06-10 00:00:00 C4 | [email protected] | 2024-06-11 07:00:00 C5 | [email protected] | 2024-06-25 08:00:00 C6 | [email protected] | 2024-05-01 12:00:00 Why this passes: With the watermark at 10 June, C1 moves to its newest version (14 June), C4 is inserted, C2 and C3 are not loaded (before or exactly at the watermark), C5 keeps its 25 June row because the change from 20 June is older, and C6 is untouched. The second run changes nothing.

Input: customers_current customer_id | email | updated_at customer_changes change_id | customer_id | email | updated_at 1 | N1 | [email protected] | 2024-07-02 09:00:00 2 | N1 | [email protected] | 2024-07-03 09:00:00 3 | N2 | [email protected] | 2024-06-01 09:00:00 load_state table_name | last_loaded_at customer_changes | 2024-07-01 00:00:00 Output: customer_id | email | updated_at N1 | [email protected] | 2024-07-03 09:00:00 Why this passes: Into an empty table only N1 is loaded, with its newest version. N2 is before the watermark.

Topics: lakebench, sql, incremental, merge, watermark, idempotent.

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