Change Data Capture Architectures

Query-based replication strategies relying on updated_at timestamps or incrementing integer IDs impose significant limitations on modern data platforms. These approaches create high latency intervals, increase load on the source database query engine, and critically, fail to capture hard deletes. To achieve high-throughput ingestion with near real-time consistency, engineers must implement Log-Based Change Data Capture (CDC). This architecture bypasses the SQL layer entirely, reading directly from the database's write-ahead log (WAL) or binary log to extract a stream of modification events.

Log-Based Extraction Fundamentals

Relational databases guarantee durability through transaction logs. Before a change is committed to the storage files (tablespaces), it is appended to a sequential log structure. In PostgreSQL, this is the Write-Ahead Log (WAL); in MySQL, the Binary Log (binlog); and in Oracle, the Redo Log.

Log-based CDC tools act as a replication client. They register with the database to receive a continuous stream of these log records. Because this process involves reading binary files rather than executing SQL SELECT statements, the performance impact on the source system is negligible.

The extraction process transforms the internal database log format into a platform-agnostic event stream. A single event generally encapsulates the state transition of a row. If we denote the state of a row $r$ at time $t$ as $r_t$, the CDC event $E$ captures the transition:

$E_t = (r_{t-1}, r_t, \text{metadata})$

Here, $r_{t-1}$ represents the "before" image (necessary for specific update logic), $r_t$ is the "after" image, and metadata includes the operation type (INSERT, UPDATE, DELETE) and the transaction commit timestamp.

Architecture flow demonstrating the separation of log extraction from the primary database engine.

The Structure of Change Events

In a distributed warehouse environment, the payload structure determines how efficiently you can merge data. A standard CDC payload contains the data required to reconstruct the current state and the metadata required to order events correctly.

Consider a typical JSON payload structure generated by a connector like Debezium:

1. Payload (After): The new state of the row.
2. Payload (Before): The previous state (important for identifying primary changes or specialized delta calculations).
3. Source Metadata: Includes the server ID, transaction ID, and the Log Sequence Number (LSN).
4. Op Code: A single character indicating the operation (c for create, u for update, d for delete, r for snapshot read).

The inclusion of the "before" image allows for advanced validation. For example, if the warehouse receives an UPDATE operation but the "before" image does not match the current state in the warehouse, it may indicate a missed event or a synchronization issue.

Handling Deletes and Schema Drift

The primary advantage of log-based CDC over polling is the reliable capture of DELETE operations. When a record is physically removed from a source table, a polling query simply ceases to return it. The warehouse has no way of distinguishing a deleted record from one that hasn't changed.

In a log-based architecture, a delete triggers a specific event type (op: 'd'). The ingestion pipeline must process this by issuing a logical delete (soft delete) or a physical delete in the destination warehouse. A common pattern is to append this delete marker to the raw history table and use a view to filter it out:

$View_{current} = \{x \in Table_{history} \mid x.op \neq 'd' \land x.timestamp = \max(timestamp)\}$

Schema drift represents another significant complexity. When a column is added or renamed in the source, the binary log format changes. CDC connectors utilize a Schema Registry. The connector detects the DDL change in the log, updates the schema definition in the registry, and versions the subsequent messages. The warehouse ingestion pipeline must be configured to handle these evolving schemas, often by using semi-structured data types (like Snowflake's VARIANT or BigQuery's JSON type) to accommodate new fields without breaking the pipeline.

Snapshotting and Consistency

A pure log reader cannot construct a database copy from scratch because logs are often rotated or archived. To initialize the warehouse, the system must perform a "consistent snapshot."

The most efficient snapshotting technique involves a lock-free approach:

1. The connector opens a repeatable read transaction.
2. It records the current log position (LSN).
3. It scans the full tables, emitting them as READ events.
4. Once the scan is complete, it switches to streaming the log starting from the recorded LSN.

This transition ensures zero data loss. However, it introduces a period where the warehouse may receive updates for records that have not yet been snapshotted. The pipeline logic must handle "upsert" semantics to ensure that an update arriving before the initial load record does not get overwritten by older data.

Resource Utilization Comparison

Moving to CDC significantly alters the resource profile of the database. While polling creates periodic CPU spikes (Sawtooth pattern) that scale poorly with dataset size, CDC maintains a constant, low-overhead footprint proportional to the rate of data change (velocity), not total data volume.

Comparison of CPU load profiles. Polling induces high-load spikes during execution, whereas CDC maintains a consistent, lower baseline dependent on transaction volume.

Architectural Challenges in Distributed Systems

Implementing CDC introduces distributed system challenges that are less prevalent in batch processing.

Ordering Guarantees: While the database log is strictly ordered, the message bus (e.g., Kafka) usually guarantees ordering only within a partition. If events for the same Primary Key are distributed across different partitions, the warehouse may process an UPDATE before the corresponding INSERT. To prevent this, producers must use the Primary Key as the partition key. This ensures that all events for a specific entity land in the same partition and are consumed sequentially.

Duplicate Delivery: CDC pipelines typically guarantee "at-least-once" delivery. Network timeouts can cause a connector to re-send unacknowledged events. The ingestion layer must be idempotent. If the warehouse receives the same event twice (same LSN/Offset), the second application must result in the same state. We will define the mathematical proof for this in the following section on Idempotency.

Transaction Boundaries: A single database transaction may update ten different tables. In the log, these appear as a sequence of events wrapped in BEGIN and COMMIT markers. Most CDC connectors stream these as independent records. If the data warehouse ingests these records in micro-batches, it is possible to load only half of a transaction, resulting in temporary referential inconsistency. Advanced implementations buffer events until the commit marker is observed or use stream processing frameworks to reassemble transactions before writing to the warehouse.