Design a Change Data Capture (CDC) Pipeline

How Log-Based CDC Works

PostgreSQL Write-Ahead Log (WAL):
  Every transaction → WAL entry before data pages modified
  WAL is the source of truth for replication

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 via replication protocol
  4. Each change decoded into: {table, op, before, after, source_metadata}

Replication slot guarantees:
  PostgreSQL retains WAL segments until Debezium confirms consumption
  ? No changes are ever lost
  → BUT: if Debezium is down too long → WAL accumulates → disk fills up
  → Monitor: pg_replication_slots → confirmed_flush_lsn lag

Zero impact on production:
  Debezium reads the WAL (written anyway for crash recovery)
  No extra queries, no triggers, no polling. CPU impact: < 1%.

Initial Snapshot + Streaming

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

Debezium Snapshot Flow:
  1. LOCK table (briefly) for consistent snapshot position
  2. Read entire table with SELECT * (chunked by PK for large tables)
  3. UNLOCK table (total lock time: < 1 second)
  4. Switch to streaming mode: read WAL from snapshot position

For very large tables (billions of rows):
  Incremental snapshot (Debezium 1.6+):
  - No global table lock
  - Reads table in chunks (WHERE id BETWEEN 1 AND 10000)
  - Interleaves snapshot reads with streaming reads
  - Can take hours but never locks the table

Change Event Format (Debezium Envelope)

{
  "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
    },
    "op": "u",
    "ts_ms": 1710403200050
  }
}

Schema Evolution Without Downtime

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

What happens:
  1. Debezium reads DDL from WAL
  2. Generates new schema (v2) with discount field
  3. Schema Registry: compatibility check → v2 compatible → registered
  4. New events have discount field; old events do not
  5. No pipeline restart needed

Problematic DDL (breaks compatibility):
  ALTER TABLE orders DROP COLUMN status → BACKWARD incompatible
  Schema Registry REJECTS → Debezium pauses with error

Safe schema changes:
  Add optional field with default ?
  Rename field (with Avro alias) ?
  Remove required field ?
  Change field type ?

Exactly-Once End-to-End

Source → Kafka: Debezium + Kafka acks=all → at-least-once (idempotent via PK key)

Kafka → Sink:
  Elasticsearch: upsert by doc_id = PK → naturally idempotent
  PostgreSQL: INSERT ON CONFLICT (pk) DO UPDATE → idempotent
  S3: Flink exactly-once file sink
  Redis: SET key value → idempotent

Net effect: exactly-once semantics without distributed transactions
The pattern: at-least-once delivery + idempotent consumers = exactly-once

Outbox Pattern

Problem: Application needs to emit a DOMAIN EVENT (not just a row change)
  CDC captures: "order.status changed from 'pending' to 'shipped'"
  But you need: "OrderShipped event with tracking_number, carrier"

Solution: Outbox Pattern
  1. In 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 outbox table → rich domain events to Kafka
  3. Background cleaner: DELETE FROM outbox WHERE created_at < NOW() - INTERVAL '1 hour';

Transactional guarantee: event is produced IF AND ONLY IF business action committed