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…