Log-based Change Data Capture (CDC) reads modifications directly from database write-ahead logs or binlogs, capturing every insert, update, intermediate status transition, and hard delete with minimal query impact on the source database. Query-based CDC periodically queries tables using timestamp columns like updated_at, which is simpler to implement but misses intermediate updates and hard deletes while placing substantial query load on production databases. Choosing between them involves balancing operational setup complexity and database permissions against data fidelity and source workload overhead.
Mechanical differences in change extraction
The two CDC architectures extract changes through fundamentally different layers:
- Log-based CDC: Engines like Debezium or AWS DMS read the low-level append-only transaction log (PostgreSQL WAL, MySQL binlog, Oracle Redo Log). Because the database engine already writes every transaction to disk for crash recovery, the CDC reader parses these logs asynchronously without executing SQL queries against table data.
- Query-based CDC: An orchestration tool like Airflow runs scheduled SQL queries against tables: SELECT * FROM customers WHERE updated_at > :last_watermark.
Data completeness and intermediate state capture
The most significant distinction between the two approaches is the fidelity of captured records:
Status transitions in 5 minutes: PENDING ---> APPROVED ---> SHIPPED Log-based CDC captures: [PENDING] [APPROVED] [SHIPPED] (3 events) Query-based CDC (15m poll): [SHIPPED] (1 event)
Query-based CDC only captures the row state present at the exact instant the query runs. If an order transitions through three statuses between polling cycles, intermediate states are lost.
If an application physically deletes a record between polls, query-based CDC never detects the removal. Log-based CDC captures every change chronologically, including hard deletes with before-and-after image values.
Operational overhead and database impact
Query-based CDC requires minimal database configuration; any developer with basic SELECT permissions can set it up in minutes. However, frequent table scans consume CPU and memory on production transaction databases, and missed indexes on updated_at can trigger table-locking performance degradation.
Log-based CDC introduces lower query overhead on source databases because it reads sequential transaction files. However, it requires database replication permissions, database configuration changes (such as logical replication slots), and careful monitoring to prevent unconsumed transaction logs from filling up database storage disks.