A technique for identifying and capturing changes made to data in a database, enabling real-time or near-real-time data replication to other systems.
Change Data Capture (CDC) is the trick that makes "near-real-time" data warehousing feasible. Instead of re-scanning a 500M-row transactional table every hour to find what changed — slow, expensive, and guaranteed to miss deletes — you tap into the database's transaction log (Postgres WAL, MySQL binlog, SQL Server CDC tables, Oracle redo logs) and stream each committed change as an event. The source database barely notices; the destination gets updates within seconds; deletes are captured exactly.
Log-based CDC reads the transaction log directly and is the one you want for production. Trigger-based CDC uses database triggers to write to a change table — works on any database but adds write overhead to the source, and triggers get disabled or diverged during schema changes. Timestamp-based "CDC" queries for WHERE updated_at > last_checkpoint — technically not CDC, misses deletes entirely, and breaks whenever someone forgets to update updated_at on a hot path. If a vendor pitches you CDC, ask whether it's log-based. If not, it's not really CDC.
A retail client had a 200M-row Postgres orders table and was doing a nightly "full refresh" to Snowflake — a 4-hour job that locked out analytics every night and still missed same-day updates. We switched to Debezium → Kafka → Snowpipe Streaming, reading the Postgres WAL. End-to-end lag dropped to <30 seconds, source database load dropped ~80%, and the nightly job disappeared. Two operational lessons the hard way: (1) the Postgres replication slot can fill the WAL disk if the consumer lags — we learned this during an outage when Snowpipe Streaming had backpressure and WAL grew 400GB in six hours; (2) schema changes need coordination — an ALTER TABLE on Postgres while the connector is running can orphan state. Plan a "pause CDC / apply schema change / resume CDC" runbook.
Change Data Capture (CDC) is a technique that identifies and captures changes (inserts, updates, deletes) made to data in a database. Instead of copying entire tables, CDC streams only the changes, enabling efficient real-time data replication.
Traditional batch extraction is inefficient:
Source DB → Transaction Log → CDC Tool → Target System
(MySQL, (binlog, WAL) (Debezium) (Kafka, DW)
Postgres)| Tool | Type | Best For |
|---|---|---|
| Debezium | Open Source | Kafka integration |
| Fivetran | Managed | Easy setup |
| Airbyte | Open Source | Self-hosted |
| AWS DMS | Cloud | AWS ecosystems |
| Striim | Enterprise | Complex transforms |
CDC is powerful and sharp. Common pitfalls:
pg_replication_slots.restart_lsn lag and have a runbook for dropping orphaned slots fast.binlog_format=ROW and binlog_row_image=FULL. This needs a MySQL restart on older versions.CDC is a technique for capturing every change (insert, update, delete) made to a database and streaming those changes to other systems, usually within seconds. Instead of re-scanning whole tables periodically, CDC taps into the database's internal transaction log (Postgres WAL, MySQL binlog, etc.) to emit a change event for every committed row. It's how modern data platforms keep warehouses, search indexes, caches, and downstream services in near-real-time sync with source databases without hammering the source with repeated full scans.
Four reasons: (1) efficiency — only changed rows move, rather than re-scanning entire tables; (2) latency — sub-minute freshness vs. hourly or daily batch; (3) completeness — captures deletes, which timestamp-based batch misses; (4) source impact — reading the transaction log is much lighter than repeated table scans. The tradeoff is operational complexity: replication slots to manage, schema-change coordination, and idempotent downstream processing. For tables with low change rates, batch is still simpler; for high-velocity tables, CDC typically wins on both cost and freshness.
Debezium is the leading open-source CDC platform. It runs as a set of Kafka Connect connectors that read transaction logs from Postgres, MySQL, MongoDB, SQL Server, Oracle, and Cassandra, and emit change events to Apache Kafka (or directly via the Debezium Engine API). It's the engine behind most CDC implementations you'll encounter — Airbyte uses Debezium internally for database CDC; Confluent Platform bundles it; Fivetran uses similar log-reading approaches. For teams already running Kafka, Debezium is the default; for teams without Kafka, managed tools like Fivetran or AWS DMS remove the operational burden.
Log-based CDC reads the database's internal transaction log (Postgres Write-Ahead Log, MySQL binlog, SQL Server's native CDC tables, Oracle redo logs) to capture every committed change. It's the most efficient and complete CDC method because (a) the transaction log already has every change the database committed; (b) reading it doesn't add query load on the source; (c) it captures deletes, which query-based methods miss. The alternatives — trigger-based CDC and timestamp-based polling — are fallbacks when log access isn't available.
Managed / SaaS: Fivetran (polished, expensive on high-churn), AWS DMS (good for AWS-native stacks), Striim (enterprise features), Oracle GoldenGate (for Oracle-heavy environments). Open-source: Debezium (the standard, deploys via Kafka Connect or standalone engine), Airbyte (uses Debezium under the hood for DB sources). Cloud-native: Snowflake's own Snowpipe Streaming with Kafka Connect sink, Google Datastream, Azure Data Factory. Choose based on destination — Kafka ecosystem → Debezium; Snowflake-first → Snowpipe Streaming + Debezium or Fivetran; AWS-heavy → DMS.
Five I've seen repeatedly: (1) runaway replication slots filling WAL disk and taking down Postgres — set slot-lag alerts; (2) MySQL binlog in STATEMENT format instead of ROW — CDC will miss before/after states; (3) schema changes breaking the pipeline — coordinate ALTER TABLE with your CDC tool; (4) initial snapshot locking up large tables — use chunked snapshots or snapshot from a read replica; (5) forgetting to handle duplicates — CDC is typically at-least-once, so downstream MERGE or idempotent writes are required. Plan for all five before turning on CDC in production.
CDC is a technique for capturing changes; data replication is the broader goal of keeping two data stores in sync. CDC is one mechanism for replication, but replication can also be done with batch diffs, full table copies, or vendor-native features (Snowflake replication, Oracle Data Guard). Modern replication almost always uses CDC under the hood because it's more efficient. Practically, when someone says "I want real-time replication from Postgres to Snowflake," they mean CDC. The terminology is loose; the mechanics are log-reading + streaming + destination writes.