Skip to main content
← PROBLEMS / ARCHITECTURE
An end-to-end domain learning journey

Building Reliable Change Data
Capture (CDC) Pipelines.

A Debezium PostgreSQL connector (pgoutput) streams 8,500 writes/sec from an orders database to Kafka, feeding Elasticsearch and Snowflake. After a Kafka broker disk failure and an accidental DROP_REPLICATION_SLOT during disk-full remediation, the connector restarts with missing offsets in connect-offsets. Because snapshot.mode=when_needed was set without heartbeat.interval.ms on a low-write-table slot, 4 hours of orders updates (1.2M rows) are silently skipped, and Elasticsearch returns stale order statuses to customers. Design a fault-tolerant Debezium CDC pipeline that prevents offset/LSN loss, detects gaps automatically, and repairs missed rows without full-table locks.
Start the journey5 stages
Loading diagram…
Building Reliable Change Data Capture (CDC) Pipelines architecture sketch diagram
FIRST PRINCIPLES. PRODUCTION SCALE.
The idea to keep with you

A Debezium PostgreSQL connector (`pgoutput`) streams 8,500 writes/sec from an `orders` database to Kafka, feeding Elasticsearch and Snowflake. After a Kafka broker disk failure and an accidental `DROP_REPLICATION_SLOT` during disk-full remediation, the connector restarts with missing offsets in `connect-offsets`. Because `snapshot.mode=when_needed` was set without `heartbeat.interval.ms` on a low-write-table slot, 4 hours of `orders` updates (1.2M rows) are silently skipped, and Elasticsearch returns stale order statuses to customers. Design a fault-tolerant Debezium CDC pipeline that prevents offset/LSN loss, detects gaps automatically, and repairs missed rows without full-table locks.

From patterns to decisions.

A Debezium PostgreSQL connector (`pgoutput`) streams 8,500 writes/sec from an `orders` database to Kafka, feeding Elasticsearch and Snowflake. After a Kafka broker disk failure and an accidental `DROP_REPLICATION_SLOT` during disk-full remediation, the connector restarts with missing offsets in `connect-offsets`. Because `snapshot.mode=when_needed` was set without `heartbeat.interval.ms` on a low-write-table slot, 4 hours of `orders` updates (1.2M rows) are silently skipped, and Elasticsearch returns stale order statuses to customers. Design a fault-tolerant Debezium CDC pipeline that prevents offset/LSN loss, detects gaps automatically, and repairs missed rows without full-table locks.

01
ARCHITECTURE · STAGE 01L4 · FUNDAMENTALS

Implementation & Guardrails (1).

Losing connect-offsets (RF=1) plus dropping the Postgres slot caused when_needed to create a fresh slot at the current WAL head, bypassing the 4-hour gap.

ChoiceWhat it buysWhat it costs
Trade-off 1Dropping a slot immediately frees WAL disk but permanently severs LSN continuity.

Step 1

Trace how RF=1 on connect-offsets caused offset loss on a single broker disk failure.

Step 2

Explain how dropping the replication slot erased Postgres's memory of the old LSN.

Step 3

Show why when_needed with no offset and a new slot only streams from the new LSN forward unless an initial snapshot is triggered.

Alternative 1

Setting max_slot_wal_keep_size = 100GB so Postgres manages disk bounds predictably.

Failure Mode 1

Silent data drift in Elasticsearch and Snowflake.

-- Inspecting slot health and WAL lag before ever touching DROP_REPLICATION_SLOT
SELECT slot_name, active, confirmed_flush_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS wal_lag
FROM pg_replication_slots;
02
ARCHITECTURE · STAGE 02L5 · APPLIED DESIGN

Use RF=3 compacted connect-offsets, 10s.

Use RF=3 compacted connect-offsets, 10s heartbeats, and public.debezium_signal with additional-conditions to backfill the 4-hour window in ~30 seconds.

ChoiceWhat it buysWhat it costs
Trade-off 1Incremental snapshots take 30s for 1.2M rows with zero table locks, vs. 14 hours for a full snapshot.

Step 1

Configure connect-offsets with cleanup.policy=compact, RF=3, min.insync.replicas=2.

Step 2

Enable heartbeat.interval.ms=10000 and heartbeat.action.query.

Step 3

Insert an execute-snapshot row scoped to the 4-hour updated_at range.

Alternative 1

Running a separate Kafka producer script reading from a PostgreSQL read replica.

Failure Mode 1

Missing index on updated_at causing sequential scan during chunked additional-conditions.

INSERT INTO public.debezium_signal (id, type, data) VALUES (
  'repair-4h-gap', 'execute-snapshot',
  '{"data-collections":["public.orders"],"type":"incremental","additional-conditions":[{"data-collection":"public.orders","filter":"updated_at >= ''2026-11-12 10:00:00+00''"}]}'
);
03
ARCHITECTURE · STAGE 03L6 · SCALE & TRADE-OFFS

Implementation & Guardrails (3).

Debezium uses watermarked window deduplication in-flight, and Elasticsearch enforces version_type=external keyed by monotonic row_version as an end-to-end invariant.

ChoiceWhat it buysWhat it costs
Trade-off 1Requires a monotonic version column or LSN-derived integer on every indexed document.

Step 1

Include a monotonically increasing version column on orders.

Step 2

Configure the Elasticsearch sink connector to pass version with version_type=external.

Step 3

Elasticsearch automatically rejects any update with incoming_version <= stored_version.

Alternative 1

Using PostgreSQL xmin + epoch or Kafka Streams stateful deduplication.

Failure Mode 1

Using updated_at epoch seconds where two updates in the same second collide.

{
  "version.type": "external",
  "document.id.strategy": "record.key",
  "behavior.on.version.conflict": "ignore"
}
04
ARCHITECTURE · STAGE 04L6 · SCALE & TRADE-OFFS

The primary DB stays online.

The primary DB stays online safely; on recovery, recreate the slot and trigger a targeted incremental snapshot from the last debezium_heartbeat timestamp.

ChoiceWhat it buysWhat it costs
Trade-off 1Bounds primary disk risk to 100 GB while making recovery deterministic.

Step 1

Read the last committed timestamp from connect-offsets or sink watermark.

Step 2

Recreate the Debezium connector with a fresh slot.

Step 3

Emit an execute-snapshot signal covering [last_committed_ts - 5m, now].

Alternative 1

Archiving WAL segments to S3 and restoring via pgBackRest (much higher complexity).

Failure Mode 1

No alert on pg_replication_slots.wal_status = 'lost', delaying recovery.

# PostgreSQL postgresql.conf WAL protection
max_slot_wal_keep_size: 102400MB # 100 GB hard cap
wal_keep_size: 4096MB
05
ARCHITECTURE · STAGE 05L7 · STAFF / PRINCIPAL

Run a lightweight Python cron.

Run a lightweight Python cron every 5 minutes comparing row counts and updated_at bucket checksums between a Postgres read replica and Elasticsearch.

ChoiceWhat it buysWhat it costs
Trade-off 1Detects silent gaps within 5 minutes with negligible read-replica load.

Step 1

Query [now - 10m, now - 5m] (allowing 5m replication lag) on both stores.

Step 2

Compare COUNT(*) and BIT_XOR(id_hash).

Step 3

On mismatch, auto-insert a Debezium execute-snapshot signal for that 5-minute window.

Alternative 1

Debezium Server + Prometheus lag metrics alone (detects lag, not silent sink drops).

Failure Mode 1

Comparing [now - 1m, now] and false-alarming on normal 200ms replication lag.

if pg_count != es_count or pg_checksum != es_checksum:
    emit_debezium_signal(window_start, window_end)
    metrics.increment("cdc_drift_auto_repaired")

Theory & Tips and Tricks

Practical heuristics & interview traps

  • 01Interview anchor: Pair compacted, RF=3 connect-offsets topics and Debezium heartbeat tables with PostgreSQL max_slot_wal_keep_size monitoring and DBLog-style incremental snapshots to heal gaps without downtime.
  • 02Trade-off tip: Requires a monotonic version column or LSN-derived integer on every indexed document.
  • 03Watch out for: No alert on pg_replication_slots.wal_status = 'lost', delaying recovery.
Behavioral Progression · Reliable Cdc Debezium Pipelines

Reliable Cdc Debezium Pipelines — Normal → Load / Fault → Recovery

Send Request
1. Normal Request Ingress
Traffic: 1x baseline · Latency (p99): 4 ms
Process State
2. Core Execution & State Lookup
Traffic: 1x baseline · Latency (p99): 9 ms
Inject 10x Spike
3. Scale Spike / Failure Injection
Traffic: 10x burst · Latency (p99): 185 ms

Pair compacted, RF=3 connect-offsets topics and Debezium heartbeat tables with PostgreSQL maxslotwalkeepsize monitoring and DBLog-style incremental snapshots to heal gaps without downtime.

Synthesis / One connected design

Putting Building Reliable Change Data Capture (CDC) Pipelines Together

Pair compacted, RF=3 `connect-offsets` topics and Debezium heartbeat tables with PostgreSQL `max_slot_wal_keep_size` monitoring and DBLog-style incremental snapshots to heal gaps without downtime.

A candidate architecture, not a universal answer

Putting Building Reliable Change Data Capture (CDC) Pipelines Together

Step 1
Step 2
Step 3
  1. Configure connect-offsets with cleanup.policy=compact, RF=3, min.insync.replicas=2.
  2. Enable heartbeat.interval.ms=10000 and heartbeat.action.query.
  3. Insert an execute-snapshot row scoped to the 4-hour updated_at range.
The primary DB stays online safely; on recovery, recreate the slot and trigger a targeted incremental snapshot from the last `debezium_heartbeat` timestamp.
Interview Readiness Check

The primary DB stays online safely; on recovery, recreate the slot and trigger a targeted incremental snapshot from the last debezium_heartbeat timestamp.

Walk through bottlenecks, back-of-the-envelope math, and failure recovery.

Practice / Explain the consequences

Make the trade-off yourself.

Practice Check

Test your understanding

Question 1 of 3

When is Change Data Capture (CDC) the right default for a production team?

Key takeaway

Building Reliable Change Data Capture (CDC) Pipelines

Pair compacted, RF=3 connect-offsets topics and Debezium heartbeat tables with PostgreSQL max_slot_wal_keep_size monitoring and DBLog-style incremental snapshots to heal gaps without downtime.