Implement Robust Change Data Capture Pipelines

This skill provides expertise in Change Data Capture (CDC) systems, with deep knowledge of database transaction logs, streaming architectures, and real-time data synchronization, designing reliable, scalable CDC pipelines across use cases. It compares CDC approaches: log-based CDC (reading transaction logs directly - WAL, binlog, redo logs), trigger-based CDC (database triggers, higher overhead), timestamp-based CDC (polling with timestamp columns, less reliable), and snapshot-plus-log (an initial snapshot combined with continuous log-based capture) - recommending log-based CDC for production systems due to minimal performance impact and guaranteed capture of all changes. Key design considerations cover exactly-once delivery (idempotent processing and deduplication), schema evolution (correctly handling DDL changes), ordering guarantees (maintaining per-partition order where needed), and backpressure handling (preventing downstream bottlenecks from impacting source systems).

Debezium implementation patterns include a Kafka Connect connector configuration for PostgreSQL CDC (replication slot, publication, table include list, an unwrap transform to flatten the envelope, JSON converters, and snapshot mode) and a custom Kafka CDC consumer that processes messages by operation type - handling create (c), update (u), delete (d), and snapshot-read (r) operations distinctly, including tombstone messages for deletes. Database-specific configuration covers PostgreSQL setup (enabling logical replication via wal_level=logical, creating a dedicated replication user, and creating a publication scoped to specific tables) and MySQL binlog configuration (server-id, binlog format ROW with full row images, and GTID mode enabled for reliable position tracking). Schema evolution strategies cover using a schema registry (Confluent Schema Registry, Apicurio), implementing backward/forward-compatible schemas, versioning data structures, and planning graceful degradation, with a concrete Schema Registry integration pattern defining an Avro schema for CDC events.

Use this skill when designing or implementing a Change Data Capture pipeline that needs reliable, low-overhead real-time data synchronization from a production database - especially with Debezium/Kafka Connect. It is well suited to PostgreSQL or MySQL sources needing log-based capture with exactly-once semantics. It is not meant for batch ETL with no real-time requirement, or for databases without accessible transaction logs where log-based CDC isn't feasible.

Input: the source database (PostgreSQL, MySQL), the tables to capture, and downstream consumers of the change stream.

Output: a Debezium/Kafka Connect connector configuration, database-specific replication setup, a CDC message consumer, and a schema evolution strategy. Example Debezium connector configuration:

{
  "name": "postgres-cdc-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "slot.name": "debezium_slot",
    "table.include.list": "public.orders,public.customers",
    "snapshot.mode": "initial"
  }
}

Builds on Debezium, Kafka Connect, and Kafka consumers, with PostgreSQL logical replication or MySQL binlog as the source, and Confluent Schema Registry/Apicurio for schema evolution.

Data engineers building real-time CDC pipelines from production databases, and teams that need exactly-once, ordered change streams with proper schema evolution rather than batch polling.