Change Data Capture Pattern: Log-Based Data Movement Without Full Reloads

Scenario context

Change data capture moves committed database changes into downstream systems without full reloads. It is most useful when freshness matters, replay matters, and the source database must remain the system of record.

sequenceDiagram
  participant DB as PostgreSQL
  participant W as WAL / Binlog
  participant D as Debezium Connector
  participant K as Kafka Topic
  participant C as Consumer
  DB->>W: COMMIT row change
  W->>D: stream LSN records
  D->>D: persist source offset
  D->>K: emit change event (op+before+after)
  K->>C: deliver insert/update/delete
  C->>C: idempotent upsert by LSN key
  C-->>K: commit offset on success

Observable outcome: 📊 WAL to Kafka: Log-Based CDC Sequence This sequence diagram traces a single committed row change from PostgreSQL's WAL all the way through Debezium and into a Kafka topic consumed by a downstream service.

Stage 4 — Concrete Config Example: Debezium PostgreSQL Connector

• This Debezium PostgreSQL connector JSON configuration captures all changes from the orders and order items tables and streams them to Kafka using the pgoutput logical replication plugin.
• Debezium is the de facto log-based CDC tool for PostgreSQL, and this connector config is the entry point for the entire log-to-Kafka pipeline described in the diagrams above.
• Pay close attention to plugin.name , slot.name , and table.include.list — these three fields determine the replication mechanism, the durable replication slot name, and the exact set of tables being captured.
• Why these fields matter operationally: - slot.name is part of recovery and retention behavior on the source database.

{
  "name": "orders-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "orders-db",
    "database.port": "5432",
    "database.user": "debezium",
    "database.dbname": "orders",
    "plugin.name": "pgoutput",
    "slot.name": "orders_cdc_slot",
    "publication.name": "orders_cdc_pub",
    "table.include.list": "public.orders,public.order_items",
    "snapshot.mode": "initial",
    "tombstones.on.delete": "true"
  }
}

from confluent_kafka import Consumer
import json, logging

logger = logging.getLogger(__name__)

consumer = Consumer({
    "bootstrap.servers": "kafka:9092",
    "group.id":          "orders-cdc-consumer",
    "auto.offset.reset": "earliest",
    # Disable auto-commit — commit only after idempotent write succeeds
    "enable.auto.commit": False,
})
consumer.subscribe(["postgres.public.orders"])

def process_change_event(event: dict):
    op     = event.get("op")           # "c"=insert "u"=update "d"=delete "r"=snapshot
    after  = event.get("after")        # new row state (None for deletes)
    before = event.get("before")       # old row state (None for inserts)
    lsn    = event.get("source", {}).get("lsn")   # PostgreSQL LSN — idempotency key

    if op == "d":
        # Handle deletes explicitly — never silently drop them
        order_id = before["id"] if before else None
        logger.info(f"DELETE order_id={order_id} lsn={lsn}")
        upsert_to_warehouse({"id": order_id, "_deleted": True, "_lsn": lsn})
    elif op in ("c", "u", "r"):
        upsert_to_warehouse({**after, "_lsn": lsn})

def upsert_to_warehouse(record: dict):
    # Idempotent write keyed by (id, _lsn) — safe to replay without duplicates
    pass  # replace with actual sink write

try:
    while True:
        msg = consumer.poll(timeout=1.0)
        if msg is None or msg.error():
            continue
        payload = json.loads(msg.value())["payload"]
        process_change_event(payload)
        consumer.commit(asynchronous=False)  # commit after successful write only
except KeyboardInterrupt:
    consumer.close()

Observable outcome: Why these fields matter operationally: - slot.name is part of recovery and retention behavior on the source database.

Stage 5 — Real-World Applications: What to Instrument and What to Alert On

• Shopify: CDC Across 200+ Production Tables Shopify uses Debezium to stream PostgreSQL changes from orders , products , and inventory into Kafka, feeding Elasticsearch (search), Snowflake (analytics), and fraud detection.
• Operating numbers: CDC lag under 300ms from WAL commit to Kafka topic.
• A full snapshot of their 500M-row orders table takes 6–8 hours and is scheduled during low-traffic windows.
• The 2020 schema incident (a nullable column broke Elasticsearch for 4 hours) drove adoption of mandatory schema compatibility checks in CI before any table migration merges.

Observable outcome: The 2020 schema incident (a nullable column broke Elasticsearch for 4 hours) drove adoption of mandatory schema compatibility checks in CI before any table migration merges.

Stage 6 — Operator Field Note: What Fails First in Production

• Shopify's 4-hour silent Elasticsearch corruption: The Debezium connector was green.
• But a schema change on orders — adding a nullable updated reason column — wasn't backward-compatible with Elasticsearch's strict index mapping.
• The connector serialized null as a missing field; Elasticsearch silently dropped those records from search results.
• Merchant dashboards showed order counts, but many orders were invisible in search.

Observable outcome: Merchant dashboards showed order counts, but many orders were invisible in search.

End-to-end outcome

CDC is the right pattern when operational systems remain authoritative but other systems need timely copies of change events.

Alternatives considered

Decision Guide for Data Movement

If you cannot explain how deletes and replays behave, the CDC design is not ready.

Failure and degraded modes

When Not to Use CDC

• Detection: CDC is not automatically the best answer for every data sync problem.
• User or operator impact: CDC is not automatically the best answer for every data sync problem.

Trade-offs & Failure Modes: Pros, Cons, and Alternatives

• Detection: Trade-offs & Failure Modes: Pros, Cons, and Alternatives
• User or operator impact: Trade-offs & Failure Modes: Pros, Cons, and Alternatives