Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 36 ยท Ingestion and Batch Processing

Incremental Loading and Change Data Capture

Compare full and incremental loads, use watermarks, handle deletes with soft deletes and snapshots, and understand how log-based change data capture works.

Copying an entire table every night is simple and correct, and it stops working the day the table has a billion rows. Incremental loading moves only what changed. It is faster and cheaper but trickier: you must detect changes, including updates and deletes, and you must not lose or duplicate any. This lesson compares the techniques and ends with an explanation of change data capture (CDC).

Full load versus incremental load

Full load Incremental load
What moves The whole table every run Only new and changed rows
Simplicity Very simple Needs change detection
Deletes Handled automatically (they vanish) Need special handling
Cost Grows with table size Grows with change volume
Best for Small tables, dimension data Large, append-heavy or slowly changing tables

Start with a full load. Switch to incremental when the runtime or cost hurts, not before.

Technique 1: timestamp watermark

If every row has an updated_at that the source maintains reliably, extract rows with updated_at greater than the last high-water mark, then upsert them into the target.

Python Watermark extraction with upsert

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Run 1 moved two rows, run 2 only the changed and new rows, run 3 nothing.

Watermark pitfalls

  • Unreliable timestamps. If some code path updates rows without touching updated_at, changes are missed. Test it.
  • Equal timestamps. Using > can skip rows that share the last second with rows you already took. A common safety is to re-read a small overlap window (say, the last few minutes) and rely on upserts to absorb the repeats.
  • Late commits. A transaction may start before your run but commit after it, with an earlier timestamp than your mark. The overlap window also helps here.
  • Deletes are invisible. A deleted row has no updated_at to find.

Technique 2: soft deletes

Make the source mark deletes instead of removing rows: deleted_at or is_deleted. The deletion then appears as an update the watermark can see, and the target can flag or remove the row.

Python Propagate soft deletes

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Technique 3: compare snapshots

When the source gives neither reliable timestamps nor delete markers, take a full snapshot and compare it with the previous one using a hash of each row. This costs a full read but sends only differences downstream and detects deletes.

Python Detect inserts, updates and deletes by diff

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Key sets give inserts and deletes; a fingerprint comparison gives updates.

Technique 4: log-based change data capture

CDC reads the database's own transaction log (the write-ahead or binary log that the database uses for recovery and replication) and turns each committed insert, update and delete into an event. Tools such as Debezium do this and publish the events to a stream.

Advantages over polling with a watermark:

  • It captures every change, including deletes and multiple updates between polls.
  • It adds almost no query load to the source, since it reads a log the database already writes.
  • Events keep the order of commits.

The cost is operational: you run the connector, configure the database to retain its log, and handle schema changes.

flowchart LR
    A["Source DB"] --> B["Transaction log"]
    B --> C["CDC connector"]
    C --> D["Change events<br/>(insert / update / delete)"]
    D --> E["Warehouse merge"]

Every committed change becomes an event.

A CDC event typically carries the operation, the row before and after, and a log position. We cannot run a real CDC connector here, so this stand-in applies a hand-written list of events to a target table:

Python Apply CDC events (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Applying the same event twice gives the same table, so the apply step is idempotent, as long as events are applied in order.

Choosing a method

  1. Small table: full load.
  2. Reliable updated_at and no hard deletes: watermark with an overlap window.
  3. Deletes matter and no CDC available: soft deletes or snapshot diff.
  4. High volume, low latency or full change history needed: log-based CDC.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which problem do plain timestamp watermarks NOT solve by themselves?

    A deleted row no longer exists to carry a timestamp, so deletes need soft deletes, snapshot diffs or CDC.
  2. 2. Why re-read a small overlap window past the watermark?

    The overlap protects against rows committed late or sharing the boundary timestamp.
  3. 3. What does log-based CDC read?

    CDC tails the transaction log, turning committed changes into events.
  4. 4. How does a snapshot diff detect updated rows?

    A changed hash for the same key means the row content changed.

Summary

  • Full loads are simple; incremental loads scale but need change detection.
  • Watermarks need reliable timestamps, an overlap window and a separate answer for deletes.
  • Soft deletes and snapshot diffs expose deletes; log-based CDC captures every change in order.
  • Apply changes with upserts so reruns are safe.

Next: Batch Transformations