Incremental load since a watermark

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

A data pipeline does not reload everything each night. It remembers how far it got last time (a high-water mark, also called a watermark) and only picks up what changed after that point. This is called an incremental load. customer_changes has every version of every customer, each with an updated_at time. load_state has 1 row for this table, and its last_loaded_at column is the watermark from the previous load. Return what the next load must pick up: versions with updated_at strictly after last_loaded_at, and for each customer only the newest of those versions. Read the watermark from load_state. Do not type the date into your query. Return customer_id, email and updated_at, ordered by customer_id.

Requirements

• Take the watermark from load_state, using the row for customer_changes.
• Keep only versions with updated_at strictly greater than the watermark.
• Keep only the newest version for each customer.
• Return customer_id, email, updated_at, ordered by customer_id.

Constraints

• A customer can have several versions after the watermark.
• Versions of the same customer never share a timestamp, so the newest one is clear.
• The watermark must come from the table, not from a typed-in value.

Examples

Input: 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 C4 | [email protected] | 2024-06-11 07:00:00 C5 | [email protected] | 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 | [email protected] | 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 | [email protected] | 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