Change Data Capture (CDC)

CDC systems identify and isolate only the data that has changed (inserts, updates, deletes) since the last capture cycle, rather than processing entire datasets. This is achieved by monitoring the database's transaction log (e.g., MySQL's binlog, PostgreSQL's WAL).

• Key Benefit: Drastically reduces data transfer volume and processing latency compared to full-table scans or batch dumps.
• Example: A customer's address update generates a single update record, not a copy of the entire customer table.

Modern CDC implementations operate with minimal performance overhead on the source database. By tailing the transaction log, they avoid placing expensive locks on production tables.

• Mechanism: Acts as a passive consumer of the log, similar to a database replica.
• Result: Enables real-time or near-real-time data propagation, supporting event-driven architectures and live dashboards without degrading source system performance.

CDC transforms database changes into a stream of ordered change events. This event stream becomes the single source of truth for propagating state to downstream systems.

• Use Case: Maintaining eventual consistency across microservices, data warehouses (like Snowflake), search indexes (like Elasticsearch), and caches.
• Pattern: Facilitates the Event Sourcing architectural pattern, where application state is derived from an immutable log of events, enabling perfect audit trails and state reconstruction.

The immutable, sequential log of changes created by CDC is critical for rollback strategies. It allows systems to reconstruct past states or execute compensating transactions.

• Rollback Protocol: To revert a faulty agent action, the system can query the CDC stream to determine the exact change made and trigger a logically inverse operation.
• Integration with Checkpointing: CDC events provide the granular data needed to restore an agent's external context to a previous checkpoint, ensuring data integrity during recovery.

Major cloud providers offer managed CDC services, reducing operational complexity.

• AWS Database Migration Service (DMS): Provides continuous replication for homogeneous and heterogeneous database migrations.
• Google Cloud Datastream: A serverless CDC service for replicating data to BigQuery, Cloud SQL, and more.
• Azure SQL Data Sync: Offers change tracking and synchronization capabilities between Azure SQL databases. These services handle schema evolution, monitoring, and scalability, allowing teams to focus on consuming the change stream.

CDC is the foundational technology for real-time ETL/ELT pipelines. Instead of periodic bulk loads, CDC streams inserts, updates, and deletes directly from the transactional database (OLTP) to the analytical data warehouse or data lake (OLAP). This enables:

• Sub-minute data freshness for dashboards and reports.
• Reduced load on source systems compared to full-table scans.
• Support for slowly changing dimensions (SCD) Type 2 and Type 3 by tracking historical changes. Example: A financial trading platform uses CDC to stream order book changes to a columnar data warehouse for real-time risk analysis.

CDC acts as a reliable change publisher in distributed systems. By tailing the database log, it emits change events (e.g., OrderCreated, UserUpdated) to a message broker like Apache Kafka or Amazon EventBridge. This enables:

• Loose coupling between services; consumers react to state changes without direct API calls.
• Event sourcing by maintaining an immutable log of all state changes.
• Implementation of the outbox pattern, ensuring reliable message delivery within a transaction. Example: An e-commerce service uses CDC to publish InventoryUpdated events whenever stock levels change, triggering notifications in other services.

CDC is the engine behind log-based replication for creating read replicas, failover nodes, and geo-distributed copies. It provides:

• Low-latency synchronization with minimal performance impact on the primary database.
• Consistency guarantees by replicating transactions in commit order.
• Support for heterogeneous replication (e.g., Oracle to PostgreSQL, MySQL to cloud object storage). This is essential for disaster recovery (DR) plans, load balancing read traffic, and maintaining active-active or active-passive architectures across regions.

CDC keeps full-text search engines (like Elasticsearch or OpenSearch) and caches (like Redis) in sync with the primary database. For every data change, CDC triggers an immediate update to the corresponding search document or cache entry. This ensures:

• Search results reflect the latest data, eliminating stale information.
• Eventual consistency between the system of record and the search index.
• Elimination of batch re-indexing jobs that cause latency spikes. Example: A product catalog change in a PostgreSQL database is instantly propagated to Elasticsearch, ensuring users see accurate search and filtering results.

CDC provides an immutable, verifiable trail of who changed what data and when. By capturing the before and after state of each row change, it creates a definitive audit log for:

• Regulatory compliance (GDPR, SOX, HIPAA) requiring data lineage and change history.
• Forensic analysis to understand the sequence of events leading to a data issue.
• Non-repudiation by linking changes to specific database transactions and users. This log is often stored separately in a durable, append-only system for security and integrity.

CDC enables live migration between database versions or platforms. The typical process is:

1. Take an initial consistent snapshot of the source database.
2. Start CDC to capture all changes occurring after the snapshot.
3. Load the snapshot into the target system.
4. Apply the CDC stream until the target is synchronized, then cut over. This minimizes downtime and risk during major upgrades, database refactoring, or cloud migration projects. It also supports blue-green deployment strategies for database-backed applications.

An architectural pattern where the state of an application is derived from an immutable, append-only sequence of events. Unlike traditional CRUD, state is reconstructed by replaying the event log. This is a complementary pattern to CDC, as CDC streams can often feed an event-sourced system, enabling perfect audit trails and state reconstruction for rollback.

• Core Principle: State is a derivative of events, not a primary source.
• Rollback Mechanism: State is reverted by truncating the event log and replaying up to a desired point.
• Example: A banking system stores AccountOpened, MoneyDeposited, and MoneyWithdrawn events. The current balance is computed by replaying all events for an account.

A design pattern for managing long-running, distributed transactions by breaking them into a sequence of local transactions. Each local transaction publishes an event or command that triggers the next step. If a step fails, compensating transactions are executed to undo the previous steps. This pattern provides a rollback mechanism for business processes that span multiple services, where a simple database rollback is insufficient.

• Orchestration vs. Choreography: Can be centrally orchestrated or decentralized via event choreography.
• Compensating Logic: Each step must have a defined inverse operation (e.g., CancelReservation compensates for CreateReservation).

Example: An e-commerce order saga involves ReserveInventory, ProcessPayment, and ScheduleShipping. If payment fails, compensating transactions release the inventory and cancel any shipping label.

A distributed consensus protocol that ensures atomicity across multiple participants (e.g., databases, services). It coordinates a commit or abort decision through a prepare phase and a commit phase, managed by a central coordinator. While not directly CDC, 2PC is a classic protocol for achieving transactional consistency, against which more modern, eventually consistent patterns (often powered by CDC) are contrasted.

• Phases: 1) Prepare: Coordinator asks all participants if they can commit. 2) Commit/Abort: If all vote yes, commit is issued; otherwise, abort is issued.
• Blocking Nature: It is a blocking protocol; if the coordinator fails, participants may remain in an uncertain state.
• Use Case: Primarily used within tightly coupled, homogeneous database clusters.

An operation that can be applied multiple times without changing the result beyond the initial application. This is a critical property for safe retries and rollbacks in systems processing CDC streams or event logs, where the same message might be delivered more than once (at-least-once delivery semantics).

• Mathematical Property: f(f(x)) = f(x).
• Implementation Techniques: Using unique idempotency keys, conditional updates (e.g., UPDATE table SET status = 'processed' WHERE id = ? AND status = 'pending'), or ensuring the operation is a pure setter.

Example: A payment service receiving a DebitAccount($10) command. If the command is processed twice due to a retry, the account should only be debited $10 total, not $20.

A method for implementing fault-tolerant services by ensuring a collection of replicas start from the same initial state and execute the same commands in the same total order. This guarantees consistency across replicas. CDC can be the mechanism that propagates the ordered log of state-changing commands (events) to all replicas.

• Core Requirement: Deterministic Execution. Given the same state and input command, each replica must produce the same new state and output.
• Log Replication: A consensus algorithm (like Raft) is typically used to agree on the order of commands in the log.
• Application: The foundation for highly available key-value stores, configuration services, and database replicas.

A logically inverse operation executed to semantically undo the effects of a previously committed transaction in a distributed system. Unlike a database rollback, which is a technical revert, a compensating transaction is a business-level operation. It is the fundamental rollback mechanism in the Saga pattern and is crucial when dealing with CDC events that have triggered irreversible external actions.

• Business Logic, Not Technical Rollback: CancelOrder is a compensating transaction for PlaceOrder.
• Eventual Consistency: The system may be in an inconsistent state between the original and compensating transaction.

Example: After a CDC event triggers a ShipItem command to a logistics API, a subsequent failure might require issuing a CancelShipment compensating transaction, which may involve API calls, fees, and notifications.