Batch Mechanics

The question arrives dressed as a capacity complaint: 'The orders table holds 2 billion rows and the nightly full reload no longer finishes before the dashboards refresh. What do you change?' Every candidate can describe reading only new rows. The score comes from the failure cases, so the interviewer repeats the pattern back to you and then waits for the part where you explain what it misses.

The high-water mark pattern keeps 1 value from the last successful run, the largest updated_at that run saw, in a small control table. The next run asks the source for every row past that value. The mark is written in the same transaction that commits the loaded rows, so a run that fails halfway leaves it untouched and the retry reads the same rows again instead of skipping them.

SELECT
  order_id,
  status,
  updated_at
FROM source.orders
WHERE updated_at > TIMESTAMP '2026-10-18 02:00:00'

Run 3 shows the behaviour the interviewer checks for. It read 1.18 million rows and failed before commit, so the mark stayed at 03:00. Run 4 then covered 2 hours of changes, 2.51 million rows, and many of those rows had already reached the staging area during the failed attempt. The load has to tolerate seeing a row twice, which is the first place the pattern leans on deduplication, and a candidate who moves the mark at the start of the run has described a pipeline that silently drops an hour of orders.

A monotonic id works as the mark too, but only for tables that are append-only. An order_id mark picks up new orders and misses every status change to an existing one, because an update never produces a larger id. The interviewer is testing whether you ask how the source table changes before you choose the column to watch.

The 4 breaks the interviewer expects

• Clock skew: an app server 90 seconds behind its peers stamps a row before the mark, and that row is never loaded.
• Long transactions: a row committed at 03:02 is stamped 02:58, when its transaction opened, so a 03:00 mark skips it.
• Hard deletes: a removed row has no timestamp to cross the mark, so the warehouse copy outlives the source row.
• Wall-clock marks: storing the run's end time skips rows stamped after the query read but before the run finished.

The fix for the first 3 breaks is a lookback: the run subtracts a window from the stored mark before querying, rereading rows it already has in exchange for catching stragglers whose timestamps fell behind. A window longer than the extraction itself also recovers what an end-time mark skipped, though the cleaner fix is to store the largest source value seen. Keeping the run's start time rereads instead of dropping, but only while the extractor's clock agrees with the database's: an extractor 2 minutes ahead opens a 2-minute hole. Deletes are the fourth break, and no window reaches them. Expect to defend the width of that window. It should exceed the longest transaction the source permits plus the worst clock drift between its writers, and every minute you add catches more stragglers but rereads more rows on every run. Say the source caps transactions at 15 minutes and its writers drift up to 90 seconds apart: a 30-minute window clears both, and on hourly runs it means rereading about half of each run's rows, roughly 670,000, to cover those cases. Rereading means duplicates in staging, so the merge step keeps the newest version of each key and discards the rest.

SELECT
  order_id,
  status,
  updated_at
FROM (
  SELECT
    order_id,
    status,
    updated_at,
    ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
  FROM staging.orders_delta
)
WHERE rn = 1

• 2B
• 1.3M
• 1,500x

A row deleted from the source has no updated timestamp to cross the mark, so the warehouse copy stays and the row count drifts upward month after month. The answers that score are a soft-delete flag the application sets instead of removing the row, or a change feed from the database's transaction log that emits the delete as a record of its own. Raising deletes before the interviewer does signals that you have run this in production and been paged for the drift.

Do

• Store the largest value actually loaded, in the same transaction as the load
• Subtract a lookback wider than the longest source transaction
• Deduplicate the overlap on the business key, newest version wins
• Ask how deletes reach the source before choosing the pattern

Avoid

• Record the run's wall-clock end time, or any clock other than the source's, as the mark
• Advance the mark before the load commits
• Use an auto-increment id as the mark on a table that gets updated in place
• Assume the source's clocks agree with each other

About this lesson

This section is part of the How Data Moves: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.