Chapter 15 · Incremental Loading, CDC, Watermarks, High-Water Marks, and Idempotency
Full Refresh vs Incremental Loads: Volume, Freshness, Correctness, and Recovery Tradeoffs
Choose full refresh or incremental loading from correctness, recovery, volume, and freshness evidence, then make the incremental cursor part of the commit boundary rather than an optimistic side file.
Learning outcomes
AtlasMart can rebuild its 690-USD Chapter 14 current state from immutable raw evidence, but doing that for every five-minute refresh eventually becomes wasteful. The tempting answer is “load only rows newer than the last run.” The dangerous part is the phrase newer than: the pipeline needs a source ordering contract, an atomic target-commit boundary, and a cursor that cannot move ahead of durable target state.
Define full refresh and incremental loading by state-selection mechanics rather than by data-platform branding.
Separate source-change detection, target application, and cursor advancement into explicit correctness contracts.
Use low-watermark/high-watermark terminology to reason about one candidate incremental batch and its commit boundary.
Explain why advancing a watermark before target commit can create a permanent gap after a crash.
Compare freshness, volume, recovery, reconciliation, and source-impact tradeoffs without assuming incremental is always better.
Chapter 15 begins from the accepted Chapter 14 presentation state: eight current paid order-line facts, five paid orders, ten units, 690 USD paid GMV, 425 USD cost-at-sale, and 265 USD gross profit. The committed ERP change-stream cursor starts at source sequence 200. This chapter deliberately changes the current analytical state through six governed CDC events (sequences 201–206); after successful commit the target is nine paid lines, seven orders, eleven units, 740 USD GMV, 450 USD cost, and 290 USD gross profit. The change is explicit and reconciled rather than silently replacing earlier controls.
The mandatory lab is synthetic, local, and free. It uses
Python 3 standard library and its bundled
sqlite3 module. Generation-time validation ran
with Python 3.13.5 and SQLite 3.46.1; learners should record
their own versions. The source sequence is a simulated ordered
commit position, not a claim that every source database
exposes an identical integer cursor. The lab demonstrates
cursor, retry, ordering, tombstone, and schema-gate mechanics;
it does not reproduce a production write-ahead log, broker,
connector, distributed transaction, cloud service, or
transport-level exactly-once guarantee.
1. Full refresh and incremental load answer different operational questions
A full refresh derives the target from the complete authoritative input scope for that run. An incremental load derives the next target state from a prior committed target plus a bounded set of changes. Incremental processing is not merely a WHERE clause; it introduces persistent pipeline state—the cursor/watermark—and therefore a new correctness problem.
| Decision surface | Full refresh | Incremental load | AtlasMart judgment |
|---|---|---|---|
| Volume/source impact | Reads/recomputes the full scope. | Reads/processes only change scope when source supports it. | Incremental is attractive after the small teaching fixture grows; no universal row threshold. |
| Freshness | May be limited by complete rescan duration. | Can reduce work and certification delay. | Freshness benefit exists only if change capture and commit are trustworthy. |
| Correctness state | Little cursor state; target is recomputed. | Cursor + target must evolve consistently. | Watermark is part of the data product state. |
| Recovery | Rerun the complete derivation. | Replay from last committed cursor; must tolerate duplicates/late changes. | Chapter 15 focuses on this harder stateful path. |
| Deletes/corrections | Visible if the full authoritative set reflects them. | Need explicit delete/correction evidence. | Ignoring delete events is data loss, not an optimization. |
Full refresh can still be wrong if source semantics, history, or transforms are wrong. Incremental can still be slower on a tiny dataset. The choice is evidence-driven, and many production systems use a hybrid: incrementals for normal operation plus bounded/full reconciliation paths for recovery.
2. Low watermark, high watermark, and the commit rule
The Chapter 15 simulated source log exposes an integer source sequence. The committed watermark is the highest sequence whose effects are durably reflected in the target. At the start of a batch, the current committed value is its low watermark L. After fetching eligible changes, the candidate high watermark H is the highest source sequence included. The safe rule is:
apply all accepted changes in (L, H] → commit target changes
and watermark H atomically.
If the transaction fails, both target changes and watermark advancement roll back. The watermark means “durably applied through here,” not “I once fetched through here.”
low = committed_watermark() # 200candidate = fetch_changes_after(low) # seq 201..206, duplicates possiblehigh = max(e["source_seq"] for e in candidate)BEGINfor event in order_and_deduplicate(candidate): apply_idempotently(event)set_watermark(high) # same transaction as target mutationsCOMMIT3. Chapter 15 change fixture and reconciled state transition
The six logical source changes below are intentionally
heterogeneous. Business event time is not the extraction cursor;
sequence 205 represents a late sale whose business time is
September 17 and whose source_updated_at is even
earlier than sequence 204 because the fixture simulates source
clock skew.
| Seq | Entity/op | Business event time | Source updated_at | Analytical effect |
|---|---|---|---|---|
| 201 | sale INSERT O1006/1 | 2026-09-21 08:00Z | 08:01:00Z | +60 GMV, +35 cost, +1 unit/order/line |
| 202 | sale UPDATE O1003/2 | 2026-09-19 15:00Z | 08:02:00Z | 50 → 55 GMV; cost remains 30 |
| 203 | customer UPDATE C002 | 2026-09-21 08:03Z | 08:03:00Z | current segment Consumer → Growth |
| 204 | sale DELETE O1005/2 | 2026-09-20 07:10Z | 08:04:00Z | remove 100 GMV/60 cost/1 unit; preserve tombstone |
| 205 | late sale INSERT O0999/1 | 2026-09-17 13:00Z | 07:59:58Z | +80 GMV/+50 cost; source clock is behind seq 204 |
| 206 | sale UPDATE O1002/1 | 2026-09-18 11:30Z | 08:05:00Z | 190 → 195 GMV; cost remains 125 |
| Control | Before seq 201–206 | After committed seq 206 | Delta |
|---|---|---|---|
| Paid lines | 8 | 9 | +1 net |
| Paid orders | 5 | 7 | +2 |
| Units | 10 | 11 | +1 |
| GMV | 690 | 740 | +50 |
| Cost | 425 | 450 | +25 |
| Gross profit | 265 | 290 | +25 |
The controls change because Chapter 15 intentionally models new source business activity. This is a migration of current state, not a contradiction of Chapter 14.
4. Controlled failure: advance the cursor before target commit
A practitioner may fetch through sequence 206, immediately
persist watermark=206, then start applying target
rows. If the process crashes after sequence 203, the next run
asks for changes > 206. Sequences 204–206 are
now permanently skipped even though the cursor claims success.
The local lab injects the inverse-safe sequence: it starts a transaction, applies three deliveries, raises a simulated crash, and rolls back. Expected evidence is unchanged controls, unchanged target hash, and watermark 200. Only the restart that applies the complete batch moves the cursor to 206.
try: process_batch(conn, deliveries, "B20260921-CDC-A", crash_after=3)except RuntimeError: passassert sales_controls(conn) == (8, 5, 10, 690, 425, 265)assert committed_watermark(conn) == 200# Restart processes the whole eligible range and commits watermark 206.5. Production judgment: recovery is part of the architecture
An incremental design is acceptable only when the team can state what orders changes, how deletes appear, what the cursor means, what happens on equal timestamps, how the batch is retried, and how target controls are reconciled. A source table with no trustworthy change marker may make periodic full extracts safer than a pretend incremental query.
Throughput and cost are workload-dependent. The tiny fixture cannot justify claims about production scan reduction or latency. Measure source read volume, target write amplification, change lag, retry frequency, lock/merge cost, and reconciliation duration under a representative workload.
Security does not disappear in incremental paths: CDC logs can contain before-images, deleted PII, and values no longer visible in current source tables. Protect raw change evidence, tombstones, checkpoints, and debug payloads according to authorization and retention policy.
Knowledge check
Check your understanding
- What does the committed watermark mean in this chapter?
- Why is fetching through 206 not enough to set watermark 206?
- Does incremental loading automatically improve freshness?
- Why can a full refresh still be useful?
- What control state is expected after seq 206?
Review the answers
1. All accepted source changes through that sequence are durably reflected in the target under the stated stream contract.
2. Fetch success does not prove target commit; a crash can otherwise create a permanent gap.
3. No. It can reduce work, but source guarantees, processing, commit, and certification still determine freshness.
4. It can simplify recovery/reconciliation when complete authoritative state is available, and can coexist with normal incrementals.
5. 9 lines, 7 orders, 11 units, 740 GMV, 450 cost, 290 gross profit.
Authoritative references
- Python documentation — sqlite3Local DB-API harness used to make commit/rollback and deterministic retry behavior observable.
- SQLite — TransactionTransaction boundaries used by the local acceptance lab; production engines have their own semantics.
-
SQLite — UPSERTExact local
ON CONFLICT ... DO UPDATEsyntax used for sequence-guarded convergence. - SQLite — DELETELocal current-state delete mechanics; the lab separately preserves a tombstone as CDC evidence.
- Debezium documentationOptional later-course reference for real CDC connector/envelope semantics. Debezium is not required by this chapter or local lab.
- PostgreSQL — Logical DecodingExample of a real database change-stream mechanism; PostgreSQL-specific details are not generalized to all engines.
- Kimball Group — Dimensional Modeling TechniquesDimensional grain/history semantics that incremental loading must preserve.