Day 35 of 45 · Pro lesson

Incremental Processing & Watermarks

The curriculum for this day stays visible. The lesson, code, and workspace unlock with Pro.

Teaches: Full refresh vs incremental processing trade-offs, Watermark tracking & high-watermark state stores, Lookback windows for late-arriving records, Atomic MERGE & upsert mechanics (is_incremental() macro), Unique key deduplication before warehouse write, Handling hard deletes and tombstones

Build: Incremental lakehouse pipeline: high-watermark state tracking → late-arriving event lookback buffer → idempotent atomic MERGE → audit log reconciliation.

•Complete 15 drills on high-watermark queries, lookback window sizing, MERGE condition optimization, and delete tombstoning.
•Run 6 Python modules simulating watermark state transitions, late-record reconciliation, and idempotent rerun guarantees.
•Simulate 5 production failures: premature watermark advance, missing lookback window, duplicate rows from retry, and tombstone desync.
•Defend 10 senior incremental processing questions and pass the blank-page challenge.

Always include a lookback window (e.g. current_watermark - interval '3 hours') in incremental filters to catch out-of-order and late-arriving records.

This section walks through the idea with a short example, then the trade-offs you should mention in an interview.

In practice you start from the raw rows, apply the transform step by step, and check the shape of the result before you move on.

A common mistake is to jump straight to the final query without naming the grain or the join keys that keep the result correct.

Once the core path works, you harden it for nulls, duplicates, and late data so the pipeline stays reliable under load.

The Pro write-up covers the full explanation, worked examples, and the code you can run in the studio.

# Locked example
result = transform(frame)
print(result.head())

Already on Pro? Go to your account. Need a pass? See pricing.