How does CDC sync data across systems?

Question

IT Interview Practice · Accepted Answer

**Change Data Capture (CDC)** streams every insert/update/delete out of a source database — typically by **reading its transaction log** — and publishes them as events other systems consume, so downstream stores stay in sync without dual-writes or polling.

```text
  Postgres/MySQL          Debezium            Kafka topic          sinks
 ┌────────────┐  reads   ┌─────────┐  emit   ┌──────────┐  consume ┌───────────┐
 │ WAL/binlog │ ───────▶ │ connector│ ──────▶ │ per-table│ ───────▶ │ search    │
 │ (tx log)   │          │          │  events │  ordered │          │ warehouse │
 └────────────┘          └─────────┘          └──────────┘          │ cache     │
                                                                     └───────────┘
```

## Why log-based CDC

You could sync by polling ("rows changed since T") or by writing to two systems at once (**dual-write**), but polling misses fast updates and dual-writes break atomically — if the second write fails you get divergence. **Log-based CDC** instead tails the database's own write-ahead log (Postgres **WAL**, MySQL **binlog**), the durable record the DB uses for crash recovery. **Debezium** is the standard connector: it reads the log and publishes each change to **Kafka**, one topic per table — capturing *every* change with no load on queries and no missed rows.

## Ordering & idempotency

- **Ordering:** changes to the same row must arrive in order or you'd apply an old value last. Kafka guarantees order **within a partition**, so partition by primary key — all changes for one row land on one partition, in commit order.
- **Delivery is at-least-once:** on a retry or connector restart the same event can be delivered twice. So **sinks must be idempotent** — key the write by primary key + log offset (LSN) so re-applying an event is a no-op. Never assume exactly-once.

## Interview perspective

The interviewer checks whether you know CDC beats dual-writes and *why* (atomicity, no missed changes), and whether you grasp **at-least-once + idempotent sinks**. Good: "Debezium reads the WAL into Kafka." Great: you add partition-by-key for ordering and idempotent sinks for duplicates. Trap: assuming exactly-once delivery, or ignoring that a DDL change on the source can break consumers.

## Trade-offs & failure modes

- **Schema evolution:** a column added/dropped upstream changes the event shape — use a **schema registry** and compatible changes so consumers don't break.
- **Initial snapshot:** CDC only sees changes *from now*; you need a one-time snapshot of existing rows, then switch to streaming.
- **Log retention:** if the source purges its log before the connector reads it, you lose changes — monitor connector lag.
- **Backpressure:** a slow sink lets Kafka lag grow; size retention and consumers for peak change volume.

Why log-based CDC

Ordering & idempotency

Interview perspective

Trade-offs & failure modes

Related questions