Change Data Capture (CDC) Strategies

Why CDC exists

Almost every data platform has the same first problem: a transactional database — Postgres, MySQL, Oracle, SQL Server — holds the current state of the business, and the analytics side needs that state without hammering the source. CDC is the discipline of capturing every insert, update, and delete as it happens and shipping it downstream, so the warehouse or lake mirrors the OLTP system with low latency and low load.

The naive alternative — SELECT * nightly, truncate-and-reload — falls apart the moment the table is large or the freshness bar tightens. It scans the whole table (expensive on the source and on the wire), it can't see rows that were created and deleted between runs, and it captures only the latest state, never the history. CDC replaces that with an incremental, ordered stream of what changed.

In an interview, CDC is one of the three named topics, and it's where weak candidates hand-wave ("I'd sync the database into Snowflake"). The differentiator is knowing which mechanism, why, and how you keep it correct across deletes, reordering, and schema drift. This chapter is that depth.

Three approaches, compared

There are exactly three families of CDC, and the interview reward is knowing the trade-offs cold rather than reciting tool names. They differ in how they detect a change.

• Query-based / batch diff. Poll the source on a schedule: WHERE updated_at > :high_water_mark, or snapshot the table and diff against the last snapshot. Simple, works on any database with a timestamp column, no special privileges. But it needs a reliable, monotonic updated_at maintained by the app, it misses hard deletes (a deleted row simply stops appearing), it misses intermediate states between polls, and each poll puts scan load on the source.
• Trigger-based. Install database triggers that write every change into a shadow/audit table, then ingest that. Captures deletes and every intermediate value. But triggers run inside the source transaction — they add write latency and contention to the OLTP path, they're invasive to schema-manage, and DBAs hate them.
• Log-based. Read the database's own replication log — Postgres WAL, MySQL binlog, Oracle redo/LogMiner, SQL Server transaction log — the same stream a read replica consumes. Tools like Debezium decode it into structured change events. It's low-impact (no queries against tables, no triggers in the transaction path), complete (every insert/update/delete, including hard deletes), and ordered (it carries the commit order and a log offset). This is the modern default for anything that matters.

Snapshot + stream architecture

Log-based CDC only sees changes from the moment you start reading the log. But the table already has millions of rows of history. So every real deployment is a two-phase pattern: an initial snapshot of the current table state, then a continuous stream of log events — with a carefully managed handoff so nothing is lost or double-counted at the seam.

  SOURCE OLTP DB              CAPTURE                 TRANSPORT            SINK / PLATFORM
  ┌───────────────┐      ┌──────────────────┐    ┌──────────────┐    ┌────────────────────┐
  │  Postgres     │      │  Debezium         │    │  Kafka topic │    │  Raw change log    │
  │               │      │  (Kafka Connect)  │    │  per table,  │    │  (append-only)     │
  │  orders   ────┼─(1)─▶│  ① snapshot       │───▶│  keyed by PK │───▶│  bronze.orders_cdc │
  │  customers    │ WAL  │     SELECT * ...  │    │  (ordering   │    └─────────┬──────────┘
  │               │      │                   │    │   per key)   │              │ MERGE / upsert
  │  replication  │      │  ② stream         │    │              │              ▼
  │  slot (LSN) ──┼─(2)─▶│     read WAL      │    │  + schema    │    ┌────────────────────┐
  └───────────────┘      │     from LSN      │    │    registry  │    │  Current-state tbl │
                         └──────────────────┘    └──────────────┘    │  silver.orders     │
                              handoff: snapshot           replayable  └────────────────────┘
                              watermark → resume           from offset        │
                              streaming at that LSN                           ▼
                                                                        BI · ML · marts

The handoff consistency is the subtle part. A modern connector (Debezium's incremental snapshotting, based on the DBLog algorithm) reads the snapshot in chunks while the stream is already running, interleaving snapshot chunks and live log events and deduplicating by primary key. The older "consistent snapshot" mode instead takes the snapshot at a single log position (LSN/GTID) and begins streaming from exactly that position, so any change committed during the snapshot is re-observed in the stream. Either way the contract is the same:

• No gap: streaming resumes at (or before) the log position the snapshot was taken at, so nothing committed during the snapshot is lost.
• Overlap is fine, loss is not: a change seen in both the snapshot and the early stream is deduplicated by primary key + log offset. CDC pipelines are built to be idempotent precisely so this overlap is harmless.
• Resumability: the connector checkpoints its log offset (LSN / binlog position / GTID). On restart it resumes from the last committed offset — this is also how you replay or backfill.

The shape of a CDC event

A log-based CDC event is richer than "here's a row." It carries the before and after images of the row, an operation type (create / update / delete / read-from-snapshot), and source metadata including the log position (LSN / binlog offset) and commit timestamp. That metadata is what makes downstream ordering, dedup, and delete-handling possible.

{
  "op": "u",
  "ts_ms": 1726142400123,
  "before": {
    "order_id": 90210,
    "status": "pending",
    "total_cents": 4999,
    "updated_at": "2026-09-12T14:59:58Z"
  },
  "after": {
    "order_id": 90210,
    "status": "shipped",
    "total_cents": 4999,
    "updated_at": "2026-09-12T15:00:00Z"
  },
  "source": {
    "db": "shop",
    "schema": "public",
    "table": "orders",
    "connector": "postgresql",
    "lsn": 24857123992,
    "txId": 558012,
    "ts_ms": 1726142400001,
    "snapshot": "false"
  }
}

• op — c insert, u update, d delete, r snapshot read. A delete carries a populated before and a null after.
• before / after — full row images. before lets you audit exactly what changed and handle deletes; some sources only populate before when REPLICA IDENTITY FULL (Postgres) or equivalent is set — worth flagging.
• source.lsn / offset / txId — the monotonic log position. This is your ordering key and your dedup key. Never order CDC by wall-clock ts_ms; order by the log offset.

Debezium additionally emits a tombstone (a message with the row's key and a null value) right after a delete, so log-compacted Kafka topics physically drop the key. Keep that in mind when the sink is Kafka rather than a warehouse.

Deletes, order & exactly-once

Getting bytes off the log is the easy 20%. The correctness edge cases are where designs earn their keep.

Deletes: soft vs hard

A hard DELETE arrives as an op: d with a before image and null after. You have two choices at the sink: apply a real DELETE to the current-state table, or — usually better for analytics — soft-delete by keeping the row and setting is_deleted = true plus a deleted_at. Soft deletes preserve history and let you reconstruct "what did the table look like yesterday," which analysts and audits both need. On Kafka sinks the delete also produces a tombstone so compaction can reclaim the key.

Updates & the current-state problem

Updates are why you can't just append. Ten updates to one order produce ten change events; the current-state table must reflect only the latest. That's the append-raw-then-MERGE pattern in the next section: keep every change in an append-only log, then upsert the latest into the serving table.

Ordering & out-of-order events

Order is guaranteed per key within a Kafka partition, and CDC connectors key events by primary key so all changes to one row land in one partition in commit order. Across partitions there is no global order — which is fine, because rows are independent. The danger is reprocessing, retries, or a snapshot/stream overlap delivering an older version after a newer one. Defend with a monotonic version column (the LSN/offset, or updated_at) and only apply a change if its version is greater than the row's current version — a "last-writer-wins by log offset" rule.

Exactly-once into the sink

The log itself is at-least-once — a connector restart re-emits events after its last checkpoint. You do not chase distributed exactly-once; you make the sink idempotent so at-least-once becomes effectively-once. The MERGE keyed on primary key + version does exactly that: replaying the same event is a no-op because the version isn't newer.

Schema changes

DDL on the source (a new column, a widened type) surfaces in the log, and connectors publish the new schema to a schema registry (Avro/Protobuf/JSON Schema) with a compatibility mode. Additive changes are safe; drops and type narrowing are breaking. The platform decision — evolve the sink automatically, quarantine, or fail — belongs to schema evolution and data contracts, covered in Chapter 09.

The mechanics of merging late or out-of-order changes into an incremental model — watermarks, reprocessing windows, idempotent merge keys — are the sibling guide's territory. See Incremental Processing and Late-Arriving Data rather than re-deriving them here.

Landing changes in the lake

The canonical pattern is two layers. First, append the raw change events to an immutable log table (bronze) — never mutate it; it's your replayable source of truth and your audit trail. Then MERGE / upsert the latest change per key into a current-state table (silver) that BI and downstream models query.

-- silver.orders = current state, upserted from the raw CDC change log.
-- Deduplicate to the latest change per primary key using the log offset (LSN),
-- then MERGE: update on match, insert new keys, soft-delete on op = 'd'.

MERGE INTO silver.orders AS tgt
USING (
  SELECT *
  FROM (
    SELECT
      after_order_id      AS order_id,
      after_status        AS status,
      after_total_cents   AS total_cents,
      op,
      source_lsn,
      ROW_NUMBER() OVER (
        PARTITION BY COALESCE(after_order_id, before_order_id)
        ORDER BY source_lsn DESC          -- last writer wins by log offset
      ) AS rn
    FROM bronze.orders_cdc
    WHERE ingest_batch_id = :batch_id     -- only the new micro-batch
  )
  WHERE rn = 1                            -- collapse many changes to the latest
) AS chg
ON tgt.order_id = chg.order_id
WHEN MATCHED AND chg.op = 'd' THEN UPDATE
  SET tgt.is_deleted = TRUE,
      tgt.deleted_at = CURRENT_TIMESTAMP,
      tgt._lsn       = chg.source_lsn
WHEN MATCHED AND chg.op <> 'd' AND chg.source_lsn > tgt._lsn THEN UPDATE
  SET tgt.status      = chg.status,       -- guard: only apply strictly newer LSN
      tgt.total_cents = chg.total_cents,
      tgt.is_deleted  = FALSE,
      tgt._lsn        = chg.source_lsn
WHEN NOT MATCHED AND chg.op <> 'd' THEN INSERT
  (order_id, status, total_cents, is_deleted, _lsn)
  VALUES (chg.order_id, chg.status, chg.total_cents, FALSE, chg.source_lsn);

Two things make this correct rather than just plausible: collapsing many changes per key to the latest by source_lsn before the MERGE (so a single micro-batch with multiple updates to one row applies cleanly), and the source_lsn > tgt._lsn guard (so a replayed or out-of-order event is a no-op — the idempotency that turns at-least-once delivery into effectively-once state).

This is deliberately the shape of the merge, not a full tutorial. The mechanics — Iceberg/Delta MERGE performance, partition pruning, dbt incremental strategies, snapshotting for SCD2 history — live in Production Data Pipelines · Incremental Processing. In the interview, sketch this two-layer pattern and cross-reference; don't burn ten minutes on MERGE syntax.

Dual writes & the outbox

CDC captures changes to a database. But sometimes the thing you want downstream isn't a row change — it's a business event ("OrderShipped") that a service wants to emit. The tempting implementation is a dual write: the service writes to its database and publishes to Kafka in the same request. This is a classic distributed-systems bug.

The fix is the transactional outbox. The service writes the business event into an outbox table in the same local transaction as its business data. Because it's one transaction against one database, it's atomic — the event is persisted if and only if the state change commits. Then CDC reads the outbox table from the log and publishes those events to Kafka. The broker is now downstream of a durable, ordered, transactional record.

BEGIN;
  -- 1. the business state change
  UPDATE orders SET status = 'shipped' WHERE order_id = 90210;

  -- 2. the event, in the SAME transaction — atomic with the state change
  INSERT INTO outbox (id, aggregate_type, aggregate_id, event_type, payload)
  VALUES (gen_random_uuid(), 'order', '90210', 'OrderShipped',
          '{"order_id": 90210, "shipped_at": "2026-09-12T15:00:00Z"}');
COMMIT;
-- Debezium tails the WAL, reads new outbox rows, and publishes OrderShipped to Kafka.
-- No dual write: one commit, one source of truth, CDC does the delivery.

Debezium ships an Outbox Event Router SMT that reshapes outbox rows into properly-keyed topic events, so this is a well-trodden pattern rather than bespoke plumbing. In a design discussion, reach for the outbox whenever a service needs to reliably emit events alongside a state change — it's the correct answer to "how do we publish events without losing or duplicating them?"

The tooling landscape

Know the categories and a representative name in each; that's enough to make a build-vs-buy call (Chapter 07) without reciting a vendor catalog.

Rough guide: Debezium when you want open, log-based CDC feeding a Kafka backbone and you can operate connectors and replication slots. Fivetran / Airbyte when connector breadth and "don't make me maintain this" beat control and cost. Cloud DMS / Datastream when you're single-cloud and want the provider to own the replication. Estuary / Confluent when the requirement is genuinely low-latency streaming CDC into a lakehouse.

When to choose what

The decision the interviewer is really after: query-based is fine until it isn't, and log-based is the answer when correctness or latency matters. Reach for the decision sentence — "Given [requirement], I'd choose [approach], because [fit]; I'd revisit if [condition]."

The mature answer names the flip condition: "I'd start query-based on updated_at — it meets the hourly SLA at near-zero ops cost — but I'd move to log-based Debezium the moment we need to capture deletes for compliance, or the freshness bar drops below the poll interval, or the source scans start showing up on the DBA's radar." That's a decision with a boundary, which is what the rubric rewards.

Next, the biggest architectural fork of them all — and the trade-off matrix that decides it: 05 · Batch vs Streaming.

✓ Check yourself

Answer from memory before opening each one.

1. A pipeline polls orders on updated_at every hour. Legal asks for account erasure, and the source rows are hard-deleted. What does the warehouse show a week later, and why?

   Show answer

   The deleted rows are still there. A poll only sees rows that exist, so a row that vanishes produces no signal. Capturing deletes needs the replication log, where a delete is a change event with a before image, or a soft-delete flag in the source. See three approaches, compared.

2. The source takes writes all through an initial snapshot of a large table. How does log-based CDC avoid losing the writes made during the load, and why is a change seen twice harmless?

   Show answer

   The snapshot is anchored to a log position and streaming resumes at or before that position, so nothing committed in between is missed. A change seen in both places is deduplicated by primary key and log offset, because the sink is idempotent. Overlap is allowed, loss is not. See snapshot and stream architecture.

3. After a connector restart the sink receives a change for order 7 with log position 900, then a replayed change for the same order with position 850. What stops the older one from overwriting the newer, and why not compare event timestamps?

   Show answer

   The merge applies a change only when its log position is greater than the one stored on the row, so the replay is a no-op. Timestamps can tie within a millisecond and clocks skew, and ordering by them can revert updates or resurrect deleted rows. See deletes, order and exactly-once.

4. A service updates an order row, then publishes OrderShipped to Kafka in the same request. About one order in ten thousand is shipped in the database but has no event. Diagnose it and fix it.

   Show answer

   This is a dual write: no transaction spans the database and the broker, so a crash or failed publish between them leaves the two disagreeing, and retries add duplicates. Write the event to an outbox table in the same transaction as the state change, then let CDC read the outbox and publish it. See dual writes and the outbox.

5. A reference table has 8 million rows with a trustworthy updated_at, no deletes, and an hourly freshness target. The team has three people. Do you stand up Debezium, and what would change your answer?

   Show answer

   No. A query-based poll meets the SLA, needs only SELECT, and has no connector or replication slot to run. Move to log-based CDC if deletes must be captured, if freshness drops below the poll interval, or if the scans start to load the source. See when to choose what.

6. Update events from a Postgres table arrive with a sparse before image, and your audit needs the old values. What is the likely cause and what does the fix cost?

   Show answer

   By default Postgres writes only the key columns of the old row to the log. Setting the table's replica identity to full records the whole old row, at the price of more log volume and more write overhead. See the shape of a CDC event.

Takeaway

• CDC turns a database's state into an ordered stream of changes. The warehouse table is then a materialised view of that stream.
• Query-based capture is cheap and fine for small, append-heavy tables with an hourly or looser target, but it silently misses hard deletes and intermediate states.
• Log-based capture reads the replication log, so it is low-impact, complete and ordered. It is the default when deletes, latency or table size matter.
• Every deployment is snapshot then stream, anchored to a log position. Overlap is deduplicated by key and offset, and loss is not allowed.
• Order by log position, never by wall clock, and apply a change only if it is newer than the stored one.
• Keep the raw change log append-only, then merge the latest change per key into a current-state table. Replay and new sinks come free.
• Never dual-write. Put the event in an outbox table in the same transaction and let CDC deliver it.

Once changes flow, the next fork is how fast to process them and whether the pipeline should be batch or streaming. → Batch vs Streaming

Further reading

• Debezium connector for PostgreSQL — Debezium project documentation. Snapshots, event structure, replica identity and tombstones, as the chapter uses them.
• DBLog: A Watermark Based Change-Data-Capture Framework — Andreas et al., Netflix, 2020. The algorithm behind incremental snapshots that run alongside the live stream.
• Logical Decoding — PostgreSQL documentation. How the write-ahead log is exposed as a change stream, and what replication slots hold.
• Reliable Microservices Data Exchange With the Outbox Pattern — Gunnar Morling, Debezium blog, 2019. The dual-write problem and the outbox fix in a working example.
• Outbox Event Router — Debezium documentation. How outbox rows become keyed topic events.