CDC Pipeline Architecture

Capture and stream database changes in real-time using Change Data Capture. Covers Debezium setup, log-based CDC, outbox pattern, event transformation, exactly-once delivery, and the patterns that turn database mutations into reliable event streams.

A CDC pipeline architecture enables real-time data synchronization by capturing changes from a source system and propagating them to target systems. This approach is essential when maintaining data consistency across distributed systems, especially in scenarios where latency-sensitive applications depend on recent data states.

Pipeline Components and Data Flow

The architecture consists of three core components: change capture, change queue, and change application. The source database must support transaction log reading or row-level change tracking. The pipeline reads changes from a persistent queue and applies them to target systems in order.

Change Capture Configuration

Source databases must be configured to maintain transaction logs or change tracking tables. For PostgreSQL, enable logical replication by setting wal_level = logical and creating publication with CREATE PUBLICATION. For MySQL, configure binary logging with log_bin = ON and binlog_format = ROW.

The capture process reads from the source's transaction log. In PostgreSQL, use pg_logical_slot_get_changes() to read changes from a logical replication slot. The function returns records in format:

{
  "action": "I",
  "table": "public.users",
  "columns": {
    "id": 12345,
    "name": "John Doe"
  }
}

Change Queue Implementation

Changes are stored in a durable queue system. Kafka is commonly used, where each change becomes a message in a topic. Messages must be structured with consistent schema for downstream consumers. Each message includes:

{
  "timestamp": "2023-10-15T14:30:00Z",
  "source": "postgresql",
  "table": "users",
  "operation": "INSERT",
  "data": {
    "id": 12345,
    "name": "John Doe"
  },
  "old_data": null
}

Change Application Logic

Target systems consume messages from the queue. The application must handle idempotent operations to avoid duplicate processing. Each operation should be wrapped in a transaction that can be rolled back if the change fails.

For database targets, use upsert operations with conflict resolution:

INSERT INTO target_table (id, name) VALUES (12345, 'John Doe')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

Configuration and Deployment

Kafka Topic Setup

Create a topic for change events:

kafka-topics.sh --create --topic user-changes \
  --partitions 6 --replication-factor 3 \
  --config retention.ms=2592000000

Pipeline Process Configuration

The pipeline process requires these configuration parameters:

Required Permissions

Source database must allow read access to replication slots or change tracking tables. Target database requires write permissions for the destination tables.

Error Handling and Recovery

Duplicate Processing

The pipeline must handle duplicate messages gracefully. When processing a message with the same ID as previously processed, the system should silently ignore it rather than failing. This is critical because Kafka guarantees at-least-once delivery.

Transaction Rollback

When applying changes to the target system fails, the entire transaction must roll back. If only part of a batch succeeds, the pipeline should restart from the failed message's offset, not reprocess successful messages.

Offset Management

Kafka consumer groups maintain offsets for each partition. The pipeline must commit offsets after successfully processing all messages in a batch. Committing offsets too early may cause data loss if subsequent processing fails.

Failure Modes and Silent Failures

Message Loss During Processing

If the pipeline crashes between reading a message from Kafka and committing its offset, the same message will reappear on restart. This creates a silent duplicate unless the application handles idempotency properly.

Schema Evolution

When source table schemas change, the pipeline must handle both forward and backward compatibility. Changes to column types or additions of new columns must not break existing consumers. The system should validate schema changes before processing.

Dead Letter Queues

Failed messages should be moved to a dead letter topic with metadata about the original error. This allows for manual intervention without restarting the entire pipeline.

Monitoring and Observability

Health Checks

The pipeline exposes a /health endpoint that returns status information including:

Metrics Collection

Key metrics include:

Data Consistency Patterns

Exactly-Once Semantics

To achieve exactly-once delivery, the pipeline must maintain state about which messages have been processed. This typically involves storing offsets in a durable store separate from the Kafka consumer group.

Transactional Processing

Each batch of changes should be processed atomically. If any operation in a batch fails, all operations in that batch must be rolled back.

Performance Considerations

Batch Sizing

Processing messages in batches improves throughput. The optimal batch size depends on network latency and target system performance. Too small batches cause overhead; too large batches increase memory usage and recovery time.

Parallel Processing

Multiple consumer instances can process different partitions of the change queue in parallel, but must coordinate to avoid duplicate processing.

Configuration Examples

Minimal Pipeline Configuration

source:
  connection_string: "host=localhost port=5432 dbname=source_db user=replicator"
target:
  connection_string: "host=localhost port=5432 dbname=target_db user=writer"
queue:
  bootstrap_servers: "kafka1:9092,kafka2:9092"
  topic_name: "user-changes"
pipeline:
  batch_size: 100
  max_retries: 3

Error Handling Configuration

error_handling:
  dead_letter_topic: "user-changes-errors"
  retry_backoff_ms: 1000
  max_retry_attempts: 5
  timeout_seconds: 30

Operational Considerations

Deployment Updates

During updates, the pipeline should gracefully shut down and commit its current offset before terminating. This prevents message reprocessing during maintenance windows.

Schema Changes

When source tables are altered, the pipeline must be able to handle changes in column structure without requiring full restarts. This includes handling new columns, dropped columns, or type modifications.

Scaling Operations

Adding more pipeline instances requires careful coordination to ensure no messages are processed multiple times. The consumer group management handles this automatically through Kafka's group rebalancing mechanism.

The architecture is resilient to network partitions, database failures, and temporary target system unavailability. It maintains consistent state through proper offset management and idempotent processing patterns.

This page was rewritten on 15 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.