Data Warehousing
Intermediate 14 min read Data Warehousing

Incremental Loads

Ship only the changed rows — without skipping any

Incremental loads move only new or changed rows, using watermarks, offsets, or CDC deltas. They make warehouse runs fast but borrow risk: watermarks can skip late rows, and idempotency must hold on replay.

incrementalwatermarkCDC deltaidempotencylate-arrivingupstream offset
Prerequisites: Materialized Views & Aggregations ETL / ELT Pipeline Design
Before we start

What you will learn

  • Choose between timestamp, offset, and CDC incremental strategies
  • Pick a watermark that tolerates late arrivals
  • Make incremental replay idempotent
Introduction

What is this?

Incremental loading copies only rows that changed since the last run, using a watermark (high-water of a timestamp/ID), an upstream offset (Kafka/debezium positions), or a snapshot diff. It shrinks load time and cost but demands careful handling of late rows and duplicate replay.

Motivation

Why do you need it?

  • Full reloads die at warehouse scale; increments are the only scalable path.
  • The watermark is the least trusted value in the pipeline — late/overlapped data breaks totals.
  • Idempotent replays are the difference between a safe retry and corrupt aggregates.
Think about it

Mental model

Imagine

Reading only the newsletter pages published since your last issue

Think of

Incremental by watermark

It's fast unless you missed an issue (late row) — then your mental archive has a hole. The fix is knowing the publication schedule and rechecking the gray zone.

Mechanics

How it works

  • Watermark strategy: after loading, record max(updated_at); next run fetches WHERE updated_at > watermark.
  • Offset strategy: consume committed offsets from the event log (CDC/Kafka) for true ordered deltas.
  • Snapshot diff: compare hash sets for tables without timestamps (costly but reliable).
  • Late-arriving data needs a lookback window (fetch last 2 days) so slow updates still surface.
  • Idempotency: on replay, delete-and-reinsert the target's affected keys before merging, not blind append.

Watermark-based incremental loop.

Run N
Transform
Merge
Advance watermark
Run N+1
Hands-on

Examples

A watermark load with a late-arrival lookback

A watermark load with a late-arrival lookback
last = get_run_state("orders_watermark")   # e.g. 2026-03-14T03:59:55
lookback_days = 2
# late-arrival window rewinds the watermark
window = subtract_days(last, lookback_days)
delta = run_sql(f"""
  SELECT * FROM source.orders
  WHERE updated_at > TIMESTAMP '{window}'
""")
merge_into_warehouse(delta, unique_key="order_id")
set_run_state("orders_watermark", max_delta_ts(delta))

The rewind window catches updates that arrived late but carried an older updated_at — the classic hole.

Real world

In production

  • A nightly warehouse load fetches only yesterday's changed rows via a watermark with a 3-day lookback.
  • A Kafka-backed layer consumes committed offsets so exactly-once-ish deltas stream into the warehouse.
  • A table without timestamps uses a staged hash-full-load only for itself, sparing others.
Guidelines

Best practices

  • Always rewind the watermark by your known late-arrival window before merging.
  • Implement merge as delete-by-key-then-insert so replays are idempotent.
  • Persist the watermark in the same transaction/log as the load so state and data cannot drift.
  • Test replays: run the load twice and assert zero drift.
Watch out

Common mistakes

  • Advancing the watermark to max(ts) before late rows could arrive, silently missing data forever.
  • Blind appending incremental rows so replays double-count.
  • Trusting 'updated_at' in the source when the source never updates it for backfills.
When things break

Debugging tips

  • When totals drift from a full-load comparison, check the watermark/lookback window first.
  • A replay that changes totals means non-idempotent merge; switch to delete+insert by keys.
  • Inspect source updated_at semantics (server TZ, clock skew) before blaming the load.
Interviews

Interview checkpoint

Practice

Hands-on lab

Watermark Load With Late-Arrival Discipline
35 min · intermediate
In progress
Objectives
  • Persist a watermark in a state table
  • Simulate late-arriving rows and verify lookback catches them
  • Prove idempotent replay produces zero drift

Watermark Load With Late-Arrival Discipline

35 min · intermediate

0/6 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

The classic failure of a naive watermark load is…

2

An idempotent incremental merge should delete affected keys before inserting on replay.

Go deeper

Official documentation

dbt incremental models
docs
Airflow materialized increments pattern
docs