The short answer
Incremental processing only reads and writes new or changed data since the last successful run. A full refresh rebuilds the whole target from scratch every time.
Full refresh each night:
scan ALL history -> rewrite entire table
Incremental:
watermark = last_success_ts
read source WHERE updated_at > watermark
merge into target
advance watermarkCommon incremental strategies
- 1Timestamp watermark on
updated_at/ingestion_time - 2High-water mark on an auto-increment id
- 3 change stream
- 4 append for immutable daily files (
dt=YYYY-MM-DD)
Why incrementals win
Faster, cheaper, less load on sources. Needed when history is terabytes.
Why full refresh still exists
Small dimensions, logic rewrites, or when incremental bugs (late updates, clock skew) make full rebuild safer.
Late update trap
A row updated yesterday with updated_at backdated can be missed. CDC or periodic full reconciliations help.
Interview tip: "Incremental = process the delta; full refresh = rebuild everything." Mention watermarks and merge, plus the late-update risk.