Change Data Capture

Problem statement (interviewer prompt)

Design a pipeline that streams every change in a production OLTP database to a data warehouse, a search index, and a cache invalidator - without dual-writes and without losing events on failure. Cover CDC, the outbox pattern, schema evolution, and exactly-once semantics.

flowchart LR
  APP[App] --> DB[(Primary DB)]
  DB -. WAL / binlog .-> CDC[CDC tailer<br/>Debezium / DMS]
  CDC --> BUS[Kafka / Pub-Sub]
  BUS --> CACHE[Cache invalidator]
  BUS --> SEARCH[Search index]
  BUS --> DWH[Data warehouse]
  BUS --> AUDIT[Audit / event log]

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class APP,CACHE,SEARCH service;
    class DB,DWH datastore;
    class BUS,AUDIT queue;
    class CDC compute;

flowchart TB
  subgraph Sources[Source DBs]
    PG[(Postgres - logical WAL,<br/>wal2json / pgoutput)]
    MY[(MySQL - binlog ROW)]
    MG[(MongoDB - oplog / change streams)]
    DY[(DynamoDB Streams)]
    OR[(Oracle - LogMiner / GoldenGate)]
    SQ[(SQL Server CDC tables)]
  end

  subgraph Capture[Capture]
    DEB([Debezium connectors])
    DMS[AWS DMS / GCP Datastream]
    MAX[Maxwell / Canal]
    SNAP[Initial snapshot + ongoing tail]
    PUB[(Replication slot / publication)]
  end

  subgraph Bus[Streaming Bus]
    KAF[[Kafka topics<br/>one per table]]
    SR[Schema Registry<br/>Avro / Protobuf]
    COMPACT[Log compaction<br/>by PK]
  end

  subgraph Sinks[Sinks / consumers]
    ES[(Search index<br/>Elasticsearch)]
    CACHE[Cache invalidator]
    DWH[(Snowflake / BigQuery / Redshift)]
    LAKE[(Data lake / Iceberg / Hudi)]
    ML[Feature store]
    AUDIT[Audit immutable log]
    INV[Inverse: SCD2 dim]
    OUT[[Outbox-based event publisher]]
  end

  subgraph Patterns
    OUTB[[Outbox pattern<br/>transactional event emission]]
    DEDU[Dedup at sink via offset/LSN]
    BACK([Backfill bootstrap])
    EVOL[Schema evolution]
    DLQ[[DLQ on transform errors]]
    SAGA[Sagas powered by CDC]
  end

  PG --> DEB
  MY --> DEB
  MG --> DEB
  DY --> DMS
  OR --> DMS
  SQ --> MAX
  DEB --> KAF
  DMS --> KAF
  KAF --> SR
  KAF --> COMPACT
  KAF --> ES
  KAF --> CACHE
  KAF --> DWH
  KAF --> LAKE
  KAF --> ML
  KAF --> AUDIT
  OUTB --- DEB
  SNAP --> KAF
  DLQ --- DEB

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class DMS,MAX,SNAP,SR,COMPACT,CACHE,INV,DEDU,EVOL,SAGA service;
    class PG,MY,MG,DY,OR,SQ,PUB,ES,DWH,LAKE,ML,AUDIT datastore;
    class KAF,OUT,OUTB,DLQ queue;
    class DEB,BACK compute;

CDC mechanics

• Log-based: tail the DB's WAL/binlog → low overhead, captures every change.
• Trigger-based: row triggers write to shadow table → higher overhead, simpler.
• Polling: timestamp/CDC column scan → loses deletes, high lag.

Snapshot + tail

• On first connect, snapshot table(s) to topic with __op=r (read).
• Then attach to log position; events arrive as c/u/d (create/update/delete).

Ordering & exactly-once

• Per-table topic → ordered by PK with partition.key = pk.
• Sinks track offset or (lsn, op) for idempotent upserts.
• Schema registry enforces backward-compat changes.

Real uses

• Cache invalidation: bust Redis key on row change.
• Search index sync: ES via Debezium sink.
• Analytics: Postgres → Kafka → Snowflake/BigQuery (near-real-time).
• Service decomposition: extract a microservice consuming legacy DB CDC.
• Audit log: append-only journal.

Pitfalls

• Schema changes (DDL) need handling; some connectors halt on ALTER.
• Initial snapshot of huge tables - use chunked snapshot mode.
• Replication slots in Postgres can pin WAL and fill disk if consumers lag.
• Connector failure semantics - Debezium "at-least-once" with offsets; design sinks for replay.

Glossary & fundamentals

Concepts referenced in this design. Each row links to its canonical page; the tag column shows whether it is a high-level (HLD) or low-level (LLD) concept.

Tag	Concept	What it is	Page
HLD	Cache strategies	cache-aside, read/write-through, eviction	caching-strategies
HLD	Pub/Sub & message brokers	topics, consumer groups, delivery semantics	pub-sub-pattern
HLD	Leader/follower replication	sync/semi-sync/async replication, failover	replication-leader-follower
HLD	LSM vs B-Tree engines	WAL, memtable, SSTables, compaction	storage-engines-lsm-btree
HLD	Distributed transactions	2PC, TCC, sagas, outbox/inbox	distributed-transactions
HLD	Change Data Capture	WAL/binlog tailing, outbox publishing	change-data-capture
LLD	Testing strategy	pyramid, doubles, TDD, contracts	testing-strategy
LLD	Immutability	immutable types, persistent collections	immutability

Quick reference

Why CDC

• Single source of truth for "what changed" without dual-writes.
• Dual-writes (write DB + write to Kafka in app) are unsafe - atomicity gap.
• CDC + outbox closes the gap.

Outbox pattern (paired with CDC)

1. App writes domain_row and outbox(event_id, payload) in same DB tx.
2. CDC tails outbox and publishes events.
3. Mark or delete outbox rows post-publish.

This decouples app from broker availability.

Bootstrap modes

• Snapshot-only (one-time export).
• Snapshot + streaming (initial backfill + ongoing).
• Streaming-only (rebuild from log retention).
• Chunked / parallel snapshot for huge tables.

Sink design rules

• Idempotent upserts (MERGE or ON CONFLICT).
• Track last applied (lsn, op_seq) per partition.
• Handle tombstones (deletes) → either physical delete or soft-flag.

Watch out

• Postgres replication slot retention: pin WAL → disk full risk. Set max_slot_wal_keep_size.
• MySQL binlog format: must be ROW (not STATEMENT) for usable CDC.
• DDL events: most tools have schema history topic; coordinate downstream.
• PII redaction at capture (column filter) if downstream is less trusted.

Refs

• Debezium docs and connector reference.
• Martin Kleppmann: "Turning the database inside out", "Online event processing".
• Confluent blog series on CDC.
• AWS DMS, GCP Datastream docs.

FAQ

What is change data capture?

Change data capture (CDC) streams every insert, update, and delete from a database to downstream consumers like a data warehouse, search index, or cache, usually by tailing the write-ahead log.

What is the difference between CDC and dual writes?

Dual writes update the DB and a message broker in application code, which is prone to inconsistency on failure. CDC reads the DB's own log, so it is atomic with the commit and never loses events.

How does Debezium work?

Debezium is a Kafka Connect source that tails Postgres WAL, MySQL binlog, or MongoDB oplog and publishes each row change as a Kafka record. It guarantees at-least-once delivery with offset tracking.

When should I use CDC vs the outbox pattern?

Use CDC when you want to capture every change without app code changes. Use the outbox pattern when domain events are richer than row changes, like business events with computed fields and identities.

What problems does CDC solve?

CDC eliminates dual-write inconsistency, enables real-time analytics, cache invalidation, search index updates, and event-driven microservices without a transactional outbox table.

Related Topics

• Event Sourcing and CQRS: CDC produces the change events that event-sourced systems and CQRS read models consume
• Pub/Sub Pattern: CDC pipelines publish database change events to pub/sub topics for downstream consumers
• Replication: Leader-Follower: CDC typically reads from the database replication log (WAL/binlog) used by leader-follower replication

Further reading

Curated, high-credibility sources for going deeper on this topic.

• 📑 Docs - Debezium documentation - CDC for databases
• ✍️ Blog - Martin Kleppmann - Turning the database inside-out with Apache Samza
• ✍️ Blog - Confluent - Streaming data into Kafka using CDC
• ✍️ Blog - Microservices.io - Transactional Outbox pattern