Drill 2 of 12 in Data engineering questions
0

Interview Question Bank · Watermark · COALESCE(updated_at, created_at) · strict >

2. Rows changed since the watermark

EasyData engineeringQuestion #242

Concept: Watermark · COALESCE(updated_at, created_at) · strict >show

An incremental load copies only the rows that changed after the last successful run, which is recorded as a watermark. Compare each row's last-change time with the watermark using a strict >, because a row stamped exactly at the watermark was already picked up. If updated_at stays NULL until a row is first edited, brand-new rows have no updated_at, so fall back to created_at with COALESCE.

Example (different data):

SELECT p.*
FROM products p
JOIN sync_state s ON s.feed = 'products'
WHERE COALESCE(p.modified_at, p.added_at) > s.synced_until;

Your task

Fernhill's customer sync copies only what changed since its last run. The watermark for each source table lives in de_watermarks, and the next run needs its list of rows from de_src_customers.

Using the 'customers' watermark in de_watermarks, return every customer created or updated after it: customer_id, email, change_type ('new' if the row was created after the watermark, otherwise 'changed') and changed_at (updated_at, or created_at when updated_at is NULL). A change stamped exactly at the watermark was already loaded. Sort by changed_at, then customer_id.

Starting Postgres in your browser…

Checking your access…