CDC Pipeline for Data Lake
Design a Change Data Capture (CDC) pipeline that replicates changes from 200+ source databases (PostgreSQL, MySQL, Oracle, SQL Server) into a centralized data lake in near real time. The system must handle initial ful...
Design a Change Data Capture (CDC) pipeline that replicates changes from 200+ source databases (PostgreSQL, MySQL, Oracle, SQL Server) into a centralized data lake in near-real-time. The system must handle initial full-load snapshots, incremental change streaming, schema evolution without breaking downstream consumers, and fan-out to multiple targets including a lakehouse, data warehouse, search index, and cache layer. Expect around 50,000 change events per second aggregate across all sources, with individual tables ranging from gigabytes to multi-terabyte initial snapshots. How would you design this end to end?
How to Approach This Problem
How to Approach This Problem "Start by naming the three hardest problems specific to CDC, not generic distributed systems problems. Interviewers at data engineering focused companies have heard the generic answers. Naming schema evolution, snapshot stream ordering, and multi table consistency immediately signals that you have built this in production." The Three Hard Problems Unique to CDC Pipelines 1. Schema changes in the source database are the most common production incident A developer adds a NOT NULL column or renames a column. Debezium captures the DDL change. Downstream consumers that expect the old schema crash or silently produce wrong data. This is not a theoretical problem, it ha
Clarifying Questions to Ask the Interviewer
Functional Requirements How many source databases and what types? (PostgreSQL, MySQL, Oracle, SQL Server, MongoDB?) What CDC method is preferred? (Log based via WAL/binlog? Query based polling? Trigger based?) Do we need an initial full load snapshot before incremental streaming? What are the downstream consumers? (Data lake only? Also data warehouse, search index, cache?) How should schema evolution be handled? (Columns added/removed/renamed, should downstream break or adapt?) Do we need ordering guarantees? (Per table? Per primary key? Global?) What operations matter? (INSERTs only? Also UPDATEs and DELETEs? Soft delete vs hard delete?) Do we need to produce a "current state" view (materia
Envelope Estimation & Capacity Planning
Throughput Math Metric Value Calculation Source databases 200+ PostgreSQL, MySQL, Oracle, SQL Server Total tables replicated ~10,000 Avg 50 tables per database Aggregate change events/sec 50,000 Given requirement Events per day ~4.3 billion 50K x 86,400 sec Average CDC event size ~1.5 KB (Avro) Before/after image + metadata Daily raw ingestion ~6.5 TB/day 4.3B x 1.5 KB After Parquet compression ~650 GB/day ~10:1 compression (Avro Parquet) Monthly storage (compressed) ~20 TB/month 650 GB x 30 days Initial Snapshot Sizing Metric Value Reasoning Total source data ~10 TB+ 200 DBs, mix of small and multi TB databases Largest single table ~2 TB E.g., transaction history, event log Snapshot through
Architecture Walkthrough
End to End CDC Flow "The architecture follows a hub and spoke model: source databases push changes into a central Kafka event bus via Debezium, and multiple downstream consumers pull from it independently." Source Layer, 200+ Heterogeneous Databases Database Type CDC Mechanism Debezium Connector PostgreSQL Logical replication (WAL) via pgoutput or wal2json plugin io.debezium.connector.postgresql MySQL Binary log (binlog) in ROW format io.debezium.connector.mysql SQL Server Change Data Capture (CDC) tables or Change Tracking (CT) io.debezium.connector.sqlserver Oracle LogMiner or XStream io.debezium.connector.oracle Each source database gets one Debezium connector instance . The connector rea
Component Deep Dive
Debezium Internals, How WAL Based CDC Works "Debezium doesn't query your tables, it reads the database's transaction log, which means near zero impact on source database performance." PostgreSQL logical replication: MySQL binlog: SQL Server CDC: Connector lifecycle: Snapshot phase → Streaming phase → (if connector restarts) Resume from last committed offset (LSN/binlog position stored in Kafka Connect offsets topic) Exactly Once Delivery, End to End Guarantees "We achieve effectively exactly once by combining idempotent producers, Kafka transactions, and dedup at the consumer layer." Layer 1: Debezium → Kafka (at least once with idempotent writes) Debezium may re send events on connector res
Data Modeling & Schema Design
Core Tables CDC Connector Registry CDC Raw Events (Bronze Layer, Data Lake) Partitioning rationale: Date partitioning aligns with time range queries (e.g., "what changed yesterday"). Sub partitioning by source table enables efficient per table reconciliation queries without scanning the entire day. CDC Schema History CDC Snapshot Progress CDC Replication Lag Tracking Dead Letter Queue Events Storage note: cdc raw events lives in the data lake (Iceberg on object storage). All other tables live in the operational PostgreSQL database. The cdc connector registry table is the central entity with 1:N relationships to all other tables.