Broken incremental: watermark on created_at misses updates

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Late Data. About 25 minutes. Part of the Pro drill bank.

Ticket from the finance team: 'Orders that changed status after they were created never reach silver. Totals do not match the source.' Friday's deploy filtered the incremental load on created_at. An order created on Monday that is changed on Thursday has an old created_at, so it is never picked up again.

The orders_incremental job should load every order that changed after the stored watermark, which is TIMESTAMP '2026-08-10 12:00:00'. The shipped query filters on created_at, so changes to older orders (a status change or a corrected total) are missed. Fix the filter so it uses updated_at and keeps only rows strictly after the watermark. Do not change the watermark value. Return order_id, user_id, order_status, order_total, updated_at, ordered by updated_at, then order_id.

Requirements

  • Filter on updated_at, not on created_at.
  • Keep only rows with updated_at strictly greater than TIMESTAMP '2026-08-10 12:00:00'.
  • Keep the watermark as a literal in the query.
  • Return the 5 columns, ordered by updated_at, then order_id.

Constraints

  • The watermark is TIMESTAMP '2026-08-10 12:00:00' and stays in the query as it is.
  • Use > so rows exactly at the watermark are not loaded a second time.
  • Return order_id, user_id, order_status, order_total, updated_at.

Examples

Input: order_id | created_at | updated_at | order_status ORD-0041 | 2026-08-10 10:05:00 | 2026-08-10 14:20:00 | paid ORD-0099 | 2026-08-11 09:00:00 | 2026-08-11 09:30:00 | pending Output: order_id | user_id | order_status | order_total | updated_at ORD-0041 | U014 | paid | 412.50 | 2026-08-10 14:20:00 ORD-0099 | U022 | pending | 88.00 | 2026-08-11 09:30:00 Why this passes: ORD-0041 was created before the watermark but updated after it. A created_at filter drops that row even though the business change happened in the incremental window.

Topics: watermarks, incremental, debugging.

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

intermediate

Broken incremental: watermark on created_at misses updates

Production ticket: Incremental orders load advanced the watermark on created_at and skipped status updates on older orders.

The `orders_incremental` job is supposed to pick up every order whose **updated_at** is after the stored watermark: `TIMESTAMP '2026-08-10 12:00:00'` Friday's deploy filtered on **created_at** instead. Status flips and total edits on older orders never landed in silver. Fix the shipped SQL so: - columns: order_id, user_id, order_status, order_total, updated_at - include every order with updated_at **strictly after** the watermark above - ordered by updated_at, order_id Do not change the watermark constant. Fix which timestamp the predicate uses.