# 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.

- Source: https://akshaythakare.com/notes/etl-elt/incremental-loads-and-watermarks
- Topic: ETL / ELT
- Level: intermediate
- Reading time: 2 min
- Tags: etl, incremental, watermark, interview
- Updated: 2026-10-11

## Full 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

```sql
-- 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.

> **Tip: Interview framing**
>
> "I pick a column that changes on every write, bound each run with an upper limit, dedupe on the business key, and advance the watermark only when the load commits."