Incremental Loads and Watermarks
Load only what changed since the last run: high-water marks, choosing the right column, overlap windows and the rows a naive watermark misses.
ETL / ELTIntermediate2 min readUpdated 11 Oct 2026
ETL / ELTFull vs incremental
| Full load | Incremental load | |
|---|---|---|
| Reads | Every row, every run | Only new or changed rows |
| Simple? | Yes | Needs state |
| Scales? | Poorly | Well |
The high-water mark
-- stored after each successful run: last_watermark = '2026-01-05 00:00:00'
SELECT * FROM orders
WHERE updated_at >= :last_watermark AND updated_at < :run_upper_bound;Then advance the watermark in the same transaction as the load, so a failed run never moves it.
Choosing the column
| Column | Catches | Misses |
|---|---|---|
| Auto-increment id | Inserts | Updates |
updated_at | Inserts and updates | Hard deletes; rows whose timestamp is set late |
| Ingestion / arrival time | Everything that reached the source | Nothing, if the source sets it on write |
| CDC log position | Inserts, updates, deletes | — |
Traps
- Late timestamps: a transaction commits at 10:05 with
updated_at = 09:58, after you read up to 10:00. Use a fixed upper bound slightly in the past, or overlap windows and dedupe. - Equal timestamps at the boundary: use
>=with deduplication, or a strictly increasing column. - Clock differences between servers: prefer a database-generated timestamp.
Finished this note?