Watermark Review: The Control Table as ADF Bookmark

Level 8: Batch Pipelines & Orchestrationeasy18 minhigh-water mark reviewmulti-pipeline control tablecreate-watermark-if-missingupsert as safety netADF and Glue vocabularydate arithmetic

Re-run the incremental loop you already know against a control table keyed by pipeline, and name the pattern in the two cloud vocabularies interviewers use.

You have already built this

This is a review lesson and it says so up front. You wrote the whole incremental loop in sql-l5-incremental-watermark-backfill: read a stored value, pull only what is newer, upsert it, advance the value. This lesson restates the chain, adds two small production details, and then spends its remaining minutes on the one thing that is genuinely new, which is what the pattern is called in each cloud.

The causal chain, one sentence per link:

Filter by > watermark. Around 80 percent of incremental loads are exactly this: keep the last-loaded timestamp, pull only source rows newer than it.
Add an overlap margin. A transaction that was in flight when the last run read the source commits afterwards but carries an earlier timestamp, so a strict boundary steps right over it. Subtracting a lookback from the stored value is what catches those rows.
The overlap creates duplicates. Anything inside the lookback window gets read a second time, by design.
So the sink must merge. Because step 3 re-reads rows you already have, the write has to be an upsert on the business key (or a partition replacement). An append here doubles rows on every run.

Order of evaluation

read control rowSELECT last_value WHERE pipeline = ...
filter sourceupdated_at > last_value minus lookback
upsert sinkON CONFLICT(key) DO UPDATE
advance control rowstore the newest value loaded

The incremental loop. The overlap in step 2 is what forces the merge in step 3, which is why the highlighted step is not optional.

The two production details

The control table is keyed by pipeline, not by table. One etl_watermarks(pipeline, last_value) table serves every loader on the platform, one row each. That means a brand new pipeline has no row at all on its first run, so a correct loader creates its bookmark before it reads it. A loader that assumes the row exists works fine for a year and then fails the first time someone adds a pipeline.

The double-run contract now covers the control row too. When this course grades a script by running it twice, it compares the contents of the tables you name. For an incremental load you must name both the sink and the control table, because a watermark that advances a second time is a value change with no row-count change, and a row-count comparison cannot see it.

Two clouds, one pattern

This is the piece worth memorizing. The shape you just reviewed is not a course invention:

Azure Data Factory calls it the watermark pattern: a watermark column in the source, a control table holding the last-loaded value, a filtered source query, and an upsert for changed rows. Across every published ADF question bank it is the single most-repeated interview scenario.
AWS Glue calls the same idea job bookmarks: Glue stores the high-water mark for you between runs instead of making you keep the table.
dbt spells the overlap margin lookback on an incremental model.

Common mistake: advancing the watermark to the run's clock time instead of to the newest value actually loaded. If the load fails halfway, the clock still moved and everything in the gap is lost forever. Advance to MAX(updated_at) of what landed in the sink.

Interview nuance: recite the chain end to end (watermark, overlap, duplicates, idempotent merge) and then name it in whichever vocabulary the interviewer opened with. Saying "this is the watermark control table, which is what Glue automates as a job bookmark" in an ADF interview, or the reverse, is the sentence that reads as platform experience rather than tutorial recall.

On a real platform this differs. The control table lives in a metadata database, not next to your data, and the loader writes it inside the same transaction that commits the load so a crash cannot leave the bookmark ahead of the data. ADF stores it in a SQL Database and updates it with a stored procedure activity; Glue stores it in the job's own bookmark state and you never see the table.

Sample data for this example

CREATE TABLE etl_watermarks (
  pipeline   TEXT PRIMARY KEY,  -- one control row per pipeline, so one table serves them all
  last_value TEXT               -- the newest source timestamp this pipeline has loaded
);
INSERT INTO etl_watermarks (pipeline, last_value) VALUES
  ('orders_incremental',    '2026-03-13 22:00:00'),
  ('customers_incremental', '2026-03-14 03:30:00'),
  ('events_incremental',    '2026-03-14 05:45:00'),
  ('invoices_incremental',  '2026-03-11 23:15:00');

CREATE TABLE etl_run_params (
  param_name  TEXT,
  param_value TEXT   -- the run's injected parameters; the pipeline never reads a wall clock
);
INSERT INTO etl_run_params (param_name, param_value) VALUES
  ('run_started_at', '2026-03-14 18:00:00');

CREATE TABLE source_orders (
  order_id    INTEGER,
  customer_id INTEGER,
  amount_usd  REAL,
  updated_at  TEXT    -- the source's change timestamp; this is the watermark column
);
INSERT INTO source_orders (order_id, customer_id, amount_usd, updated_at) VALUES
  (5101, 301, 120.00, '2026-03-12 07:15:00'),
  (5102, 302,  75.50, '2026-03-12 11:40:00'),
  (5103, 303, 240.00, '2026-03-12 16:05:00'),
  (5104, 304,  59.90, '2026-03-12 20:55:00'),
  -- the two rows that sit inside a 1 day lookback and are already loaded
  (5105, 305, 310.25, '2026-03-13 20:35:00'),
  (5106, 306,  88.00, '2026-03-13 21:50:00'),
  -- strictly newer than the stored watermark: genuinely new work
  (5107, 307, 145.75, '2026-03-14 02:20:00'),
  (5108, 308,  62.40, '2026-03-14 05:35:00'),
  (5109, 309, 199.00, '2026-03-14 08:50:00'),
  (5110, 310, 130.00, '2026-03-14 12:05:00'),
  (5111, 311,  95.60, '2026-03-14 15:30:00'),
  (5112, 312, 275.00, '2026-03-14 17:25:00');

CREATE TABLE ods_orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount_usd  REAL,
  updated_at  TEXT
);
INSERT INTO ods_orders (order_id, customer_id, amount_usd, updated_at) VALUES
  (5101, 301, 120.00, '2026-03-12 07:15:00'),
  (5102, 302,  75.50, '2026-03-12 11:40:00'),
  (5103, 303, 240.00, '2026-03-12 16:05:00'),
  (5104, 304,  59.90, '2026-03-12 20:55:00'),
  (5105, 305, 310.25, '2026-03-13 20:35:00'),
  (5106, 306,  88.00, '2026-03-13 21:50:00');

Worked example (SQL)

-- One control row per pipeline. The orders loader reads its own row, then every source
-- row falls into one of three buckets against the strict boundary and the 1 day lookback.
SELECT o.order_id, o.updated_at,
       CASE
         WHEN o.updated_at > w.last_value THEN 'new (strictly after the watermark)'
         WHEN o.updated_at > datetime(w.last_value, '-1 day') THEN 're-read (inside the lookback)'
         ELSE 'skipped (older than the lookback)'
       END AS load_decision
FROM source_orders o
CROSS JOIN etl_watermarks w
WHERE w.pipeline = 'orders_incremental'
ORDER BY o.updated_at;

Apply

Your turn

The task this lesson builds to.

Write a query that returns the rows the next orders_incremental run will load given the stored watermark and a 1 day lookback, as (order_id, updated_at, already_in_target), oldest first, over source_orders, etl_watermarks, and ods_orders.

A row is in scope when its updated_at is strictly greater than the pipeline's last_value minus one day. Set already_in_target to 1 when that order_id is already in ods_orders and 0 when it is not, so the overlap is visible. Alias the columns exactly order_id, updated_at, and already_in_target.

4 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, plus 2 bonus drills.

Write a script that runs the incremental load for source_shipments into ods_shipments keyed on shipment_id, creating the shipments_incremental watermark row if it is missing, and changes nothing when it is rerun.

etl_watermarks(pipeline, last_value) already serves four other pipelines and has no shipments_incremental row yet, so create one before you read it. Load the source rows strictly newer than that stored value, upsert them into ods_shipments, then advance the row to the newest updated_at now in ods_shipments. The grader runs your script twice and compares the contents of both ods_shipments and etl_watermarks, so the watermark must not drift on the second run.

2 hints and 5 automated checks are waiting in the workspace.