Design a Change Data Capture (CDC) Pipeline

How Log Based CDC Works

I start with log based CDC mechanics because the core design depends on reading the database transaction log without polling source rows. From there, the design moves through source connector differences, snapshot handoff, schema evolution, filtering, transaction boundaries, and the outbox pattern for domain events.

Log based CDC captures INSERT, UPDATE, and DELETE changes from the database transaction log without recurring source table polling. Lead with WAL and binlog reading before comparing it with polling or dual writes.

PostgreSQL Write-Ahead Log (WAL):
  Every transaction writes WAL before data pages are modified
  WAL is the source log from which logical replication decodes committed changes

Logical Replication (PostgreSQL 10+):
  1. Create PUBLICATION: CREATE PUBLICATION my_pub FOR TABLE orders, users;
  2. Debezium creates a REPLICATION SLOT
  3. Debezium reads decoded WAL changes through the replication protocol
  4. Each row change is decoded into: {table, op, before, after, source_metadata}

Replication slot behavior:
  PostgreSQL retains WAL required by the slot while the slot remains valid and the required WAL has not been removed
  This protects the connector from missing retained WAL during an outage, but it is not an unconditional data-loss guarantee if the slot is invalidated or required WAL is removed
  Caution: A stalled connector can cause WAL growth and exhaust source disk space
  Monitor: pg_replication_slots restart_lsn and confirmed_flush_lsn lag

Source impact:
  Ongoing CDC reads the transaction log instead of repeatedly querying source tables
  Initial snapshots do query source tables, so snapshot concurrency and timing must be controlled
  Source CPU and I/O impact depend on transaction rate, row width, schema, decoding, and snapshot workload

Delete example:
  UPDATE: before={...}, after={...}, op="u"
  DELETE: before={...}, after=null, op="d"
  A PostgreSQL table's REPLICA IDENTITY controls which old column values are available for UPDATE and DELETE events
  DEFAULT normally provides the previous primary key values, whereas FULL provides the previous row image
  A delete can be followed by a Kafka tombstone when the connector is configured to emit tombstones

Source Database Connector Differences

The overall pipeline is shared across database engines, but the change log and recovery mechanics differ by source.

Kafka Connect Runtime and Task Scaling

Debezium normally runs inside a distributed Kafka Connect cluster. The REST API controls connector configuration, while workers execute connector tasks and store connector state in compacted internal Kafka topics. Scale workers for connector concurrency, but do not assume every source connector can split one database transaction log across arbitrary tasks without preserving source ordering.

Kafka Connect cluster:
  Connect Worker 1 ─┐
  Connect Worker 2 ─┼─> distributed worker pool
  Connect Worker N ─┘

Connector:
  PostgreSQL source
      ↓
  Debezium task(s)
      ↓
  Kafka topics

Internal state:
  connect-configs  → connector configuration
  connect-offsets  → source offsets / LSN positions
  connect-status   → connector and task status

Scaling rule:
  Add workers for more connectors and task capacity
  Increase source parallelism only when the connector preserves the required ordering semantics

Filtering, Masking, and Topic Routing

CDC platforms often need to remove sensitive fields, route tables into governed topic names, and apply lightweight record transformations before broad distribution. Keep transformations deterministic and make the source of truth remain the database log rather than the transformed stream.

Source change
  ↓
Debezium capture
  ↓
Column filtering / masking where policy allows
  ↓
Topic routing: cdc.<db>.<schema>.<table>
  ↓
Schema validation
  ↓
Kafka consumers / sinks

Rule:
  Do not treat a transformed topic as the canonical source of database history
  Preserve source identity and ordering metadata for replay and reconciliation

Retries, Quarantine, and Dead Letter Queues

CDC pipelines need a clear boundary between transient failures and permanently invalid records. Retry network and downstream availability failures with bounded backoff. Send non retriable serialization or contract violations to a quarantine topic with the original source identity, partition, offset, and failure reason so operators can repair the consumer and replay the record later.

Processing:
  Kafka record
      ↓
  Validate / deserialize
      ↓
  Retry transient failure with bounded backoff
      ↓
  Success ───────────────> sink
      │
      └─ non retriable ──> quarantine / DLQ

DLQ record should retain:
  source topic + partition + offset
  table + primary key
  source LSN / transaction metadata
  schema version
  failure reason

Rule:
  Never silently drop a CDC record
  Keep the original event replayable after the defect is fixed

Initial Snapshot + Streaming

Every new connector must bootstrap existing rows before streaming. Explain the snapshot to streaming handoff and why incremental snapshots reduce the need for one long blocking operation on billion row tables.

Problem: When a CDC connector starts, the source table already has 100M rows.

Debezium initial snapshot flow:
  1. Start a transaction using the configured snapshot isolation level
  2. Record the current database log position
  3. Scan the captured tables and emit READ events
  4. Finish the snapshot and record successful snapshot completion
  5. Continue streaming changes from the recorded log position so updates committed during the snapshot are not omitted

Important:
  Snapshot locking and isolation depend on connector settings
  The snapshot itself performs source table reads, so it adds bounded read load
  A connector that fails before snapshot completion can restart the snapshot

For very large tables (billions of rows):
  Incremental snapshot:
  - Reads tables in chunks
  - Interleaves snapshot chunk processing with live streaming
  - Deduplicates snapshot rows against overlapping streamed changes
  - Runs without requiring one long table wide blocking operation

Change Event Format (Debezium Envelope)

The Debezium envelope is what every downstream consumer deserializes, so walk through before, after, op, and source metadata to demonstrate a firm grasp of the event contract.

{
  "schema": {...},
  "payload": {
    "before": { "id": 42, "status": "pending", "amount": 99.99 },
    "after": { "id": 42, "status": "completed", "amount": 99.99 },
    "source": {
      "connector": "postgresql",
      "db": "ecommerce", "table": "orders",
      "lsn": 987654321, "ts_ms": 1710403200000,
      "txId": 12345678
    },
    "op": "u",
    "transaction": {
      "id": "12345678:987654000",
      "total_order": 7,
      "data_collection_order": 2
    },
    "ts_ms": 1710403200050
  }
}

Schema Evolution Without Downtime

Schema evolution is a staff level follow up that breaks naive pipelines, so explain Avro registry compatibility before describing what happens on ALTER TABLE ADD COLUMN vs DROP COLUMN.

Scenario: ALTER TABLE orders ADD COLUMN discount DECIMAL(10,2);

What happens:
  1. The source database applies the DDL
  2. Debezium refreshes the captured table schema as needed and updates the emitted record schema
  3. If a Schema Registry backed converter is configured, the new serialized schema is checked for compatibility
  4. New events carry the evolved schema while older events remain readable under the configured compatibility policy
  5. No connector restart is required for a compatible additive change

Important limitation:
  PostgreSQL logical decoding does not emit generic DDL change events to consumers
  Schema metadata refresh and Schema Registry compatibility are separate concerns
  A breaking DDL change must be coordinated with downstream consumers

Safer schema changes:
  Add a nullable or optional field: Usually compatible when the reader can handle its absence
  Remove a field: Compatibility depends on the configured schema evolution policy and consumer behavior
  Rename a field: Treat as a coordinated migration unless the downstream schema layer explicitly supports aliases
  Change a field type: Usually breaking unless the schema format and compatibility policy explicitly permit the widening
  Destructive changes should be deployed in phases so old and new consumers can coexist
  A compatibility check validates serialized schema evolution. It does not replace application level rollout testing

Backfill and Replay Strategy

Backfills are common when adding a new sink or repairing a projection. Use a historical Kafka offset or Debezium snapshot to rebuild the sink, isolate backfill consumers from live consumer groups, and make the sink replay safe before allowing the rebuilt projection to become authoritative.

Backfill:
  1. Choose a source LSN, Kafka offset, or fresh snapshot boundary
  2. Start an isolated consumer group
  3. Rebuild the sink using idempotent upserts
  4. Compare counts / checksums against the source
  5. Replay the live delta after the chosen boundary
  6. Cut over only after reconciliation passes

Exactly Once: Scope and Patterns

Effectively once sink behavior can be achieved without two phase commit by combining at least once delivery with replay safe, idempotent sinks. Use Kafka transactions when the processing topology benefits from Kafka atomicity, but do not extend a Kafka transaction claim to unrelated external systems.

Baseline source to Kafka semantics:
  Debezium provides at least once delivery by default
  Keying a record by primary key does NOT deduplicate updates because multiple valid updates can share the same key

Kafka to sink:
  Elasticsearch: upsert by stable source key, guarded by source version or LSN when an older replay could otherwise overwrite newer state
  PostgreSQL: INSERT ... ON CONFLICT is idempotent when the sink applies the full current state or uses a source version guard for ordered updates
  S3 / Parquet: exactly once file output depends on the processing engine and sink implementation. Replay can create duplicate logical records unless the sink uses commit semantics or deterministic file generation
  Redis: SET of the same versioned value is idempotent, but counters and compare-and-set updates require explicit deduplication or version checks
  ClickHouse: use a schema and table engine that support replay-safe replacement or deduplication, and retain the source version or event identity needed to reconcile late or repeated changes

Net effect:
  Use the term effectively once per sink when the sink is idempotent and replay safe
  Do not claim one global exactly once guarantee across Kafka, databases, object storage, Redis, and external systems without coordinated transactional integration

Transaction Boundaries and Primary Key Changes

Per key ordering is not the same as transaction ordering across multiple table topics. Enable Debezium transaction metadata when downstream logic needs transaction boundaries, and preserve the transaction identifier and event order when coordinating changes across topics. This is useful when a single source transaction updates several tables that feed independent Kafka topics. A primary key update also changes the Kafka message key. Debezium represents that change as a delete and create sequence, so consumers that depend on key based ordering must handle the transition explicitly.

Transaction A updates orders and order_items:
  BEGIN
  order row change      → topic orders
  item row change       → topic order_items
  END

Transaction metadata:
  Enable the connector's transaction metadata option when needed
  transaction.id
  total_order
  data_collection_order

Primary key change:
  old key → DELETE + tombstone
  new key → CREATE
  Debezium also carries headers that identify the new or old key for this transition

Design rule:
  Per key ordering is preserved only while the key and partition mapping remain stable
  Cross table atomicity requires transaction aware consumer logic or an outbox/domain event

Outbox Pattern

CDC alone captures row changes rather than domain events. That makes the Transactional Outbox Pattern essential for emitting rich business events atomically with the database write. Learn more about event streams in Event Sourcing and CQRS.

Problem: The application needs to emit a DOMAIN EVENT rather than only a raw row change
  CDC captures: "order.status changed from 'pending' to 'shipped'"
  Downstream consumers instead need: "OrderShipped event with tracking_number and carrier"

Solution: Outbox Pattern
  1. In the SAME database transaction:
     UPDATE orders SET status='shipped' WHERE id=42;
     INSERT INTO outbox (aggregate_type, aggregate_id, event_type, payload)
       VALUES ('order', '42', 'OrderShipped', '{"tracking":"1Z999"}');
  2. Debezium CDC on the outbox table emits rich domain events to Kafka
  3. Cleanup removes outbox rows only after CDC has safely advanced past them and the configured safety window has elapsed

Transactional guarantee:
  The outbox row exists if and only if the business transaction commits
  Emission to Kafka is asynchronous and normally at least once, so consumers must remain idempotent

Governance at 5,000 Topics

At 5,000 Kafka topics, metadata overhead and ACL sprawl become operational risks. Strict governance keeps the platform maintainable as teams self serve connectors.

One Kafka topic per table across 100 databases yields approximately 5,000 topics, which is manageable but metadata heavy. Governance rules enforce the naming convention cdc.{db}.{schema}.{table}, ensure automatic registration in the schema registry, and categorize topics by priority tier, such as P0 for orders and P3 for audit logs. Retention tiers range from 7 days of hot storage with cold S3 archival for P0 event topics down to 24 hours for lower priority event topics. Compaction is reserved for reconstructable state topics rather than historical audit streams. Access control lists isolate consumer teams so that service teams cannot inspect sensitive tables outside their domain. Monitor topic count growth, broker metadata size, and under replicated partitions. Cap new topics using a Debezium allowlist to prevent uncontrolled CREATE TABLE statements from spawning topics without review.