Day 35Phase 07 · dbt + Data Quality
Incremental Processing
The curriculum for this day stays visible. The lesson, code, and workspace unlock with a pass.
By the end of today you can
- Track state with high watermarks to process only newly inserted or modified records.
- Incorporate lookback windows to capture late-arriving and out-of-order data updates.
- Execute idempotent MERGE updates into analytical warehouse tables without generating duplicates.
The Economics of Incremental Processing
When ShopKart launched, its orders table contained 10,000 total rows. Running a Full Refresh (CREATE OR REPLACE TABLE fct_orders AS SELECT ...) finished in 3 seconds.
Fast-forward 3 years: ShopKart has 50 million historical orders across 1,000 days. Patrons return 5,000 items, and 20,000 new orders land daily. Re-scanning and rewriting 50 million rows every 15 minutes to process 25,000 daily changes is wasteful: it spikes warehouse compute bills, locks storage partitions, and misses SLA deadlines.
This is the first section of the lesson. The rest unlocks with a pass.
Core topics: 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.
Always include a lookback window (e.g. current_watermark - interval '3 hours') in incremental filters to catch out-of-order and late-arriving records.
Already have a pass? Go to your account. Need a pass? See pricing.