Change Data Capture (CDC) is the process of identifying and capturing changes made to data in a source system so they can be replicated to a downstream system. CDC is foundational in stream processing, real-time analytics, and ELT pipelines. The four main types of CDC are:

1. Log-Based CDC

Mechanism: Parses the database’s transaction log (e.g., MySQL binlog, PostgreSQL WAL, Oracle redo logs) to identify INSERT, UPDATE, and DELETE operations.

Pros:

Low latency and overhead.
High fidelity — exact ordering and atomicity.
Doesn’t require changes to source schema.

Cons:

Requires access to database internals/logs.
May require logical replication privileges or binary format decoding.

Best For: Real-time streaming and large-scale replication scenarios.

2. Trigger-Based CDC

Mechanism: Database triggers on DML events write changes to an audit or shadow table.

Pros:

Fine-grained control over captured changes.
Works on systems where log access is restricted.

Cons:

Adds write latency.
Operational complexity: requires maintaining trigger logic.
Risk of infinite loops if replication targets are not isolated.

Best For: When log access is unavailable or custom logic per change is required.

3. Query-Based CDC (Diffing)

Mechanism: Periodic polling of the source table and comparing rows to previous snapshots using primary keys or hashes.

Pros:

No special database privileges required.
Easy to implement in any SQL-compatible system.

Cons:

High latency and resource usage.
Inefficient on large tables.
Prone to missed or duplicate changes.

Best For: Small datasets or proof-of-concept pipelines.

4. Timestamp-Based CDC

Mechanism: Periodic queries that filter rows based on a last_updated timestamp column.

Pros:

Simpler and more performant than full-table scans.
Easy to implement if the schema has reliable update timestamps.

Cons:

Requires an accurate and updated last_updated column.
Cannot capture deletes.
Can miss updates if clocks are skewed or transactions fail to update the timestamp.

Best For: Append-only or update-heavy workloads with reliable metadata.

Each method represents a tradeoff between implementation complexity, latency, and system impact. Log-based CDC is generally preferred for real-time, production-grade pipelines, while trigger- and timestamp-based CDCs are useful when limited by system constraints.

Types of Change Data Capture (CDC)