CDC: log-based vs query-based tradeoffs for database-to-warehouse sync

The CDC Decision Nobody Explains Well: Log-Based vs. Query-Based

Every team that moves data from an operational database to a warehouse eventually lands on the same fork in the road: do you read from the database's transaction log, or do you just poll with a query?

The honest answer is that both approaches work, but they fail in very different ways. And most write-ups skip past that part.

What Each Mode Actually Does

Query-based (also called poll-based or timestamp-based) CDC is exactly what it sounds like. You run something like SELECT * FROM orders WHERE updated_at > :last_run on a schedule. Simple, low-overhead, easy to reason about. Most data teams start here.

Log-based CDC taps into the database's replication stream, PostgreSQL's logical replication, MySQL's binlog, etc. Instead of asking "what changed since I last looked?", you're reading a continuous feed of write operations as they happen.

Where Query-Based Falls Apart

Query-based works fine when your latency tolerance is measured in minutes and your data model cooperates. The word "cooperates" is doing a lot of work there.

The approach breaks down when:

• Rows get soft-deleted (a deleted_at timestamp doesn't exist until the row is updated, but you'd need to catch the delete itself)
• Tables have no reliable updated_at column, and there are more of these than you'd think in production systems
• You need sub-minute latency and the query itself takes 20 seconds to scan
• Multiple rapid updates to the same row collapse into one, and you lose the intermediate states

If any of those apply, you're papering over a correctness problem, not solving it.

Where Log-Based Gets Complicated

Log-based sounds like the obvious fix, and for high-frequency writes or strict correctness requirements, it usually is. But it comes with its own set of costs.

You need replication slots (Postgres) or binlog access (MySQL), which means database admin buy-in. Replication lag and slot retention are real failure modes, a lagging consumer can bloat your WAL files until the database itself is at risk. Schema changes require careful coordination. And the connector setup is meaningfully more complex to operate in production.

For a low-write-volume reference table you're syncing once an hour, this is a lot of machinery to maintain.

The Actual Decision Framework

The choice usually comes down to three variables: write frequency, latency requirement, and whether you need deletes.

If you're syncing a slowly-changing dimension table to a warehouse once a day, query-based is fine. If you're capturing every state transition on a financial order or a fraud signal, you need log-based, the intermediate states matter, and polling won't give them to you.

A rough heuristic: if the answer to "what happens if I miss a row or a delete for 10 minutes?" is "nothing important," query-based is probably fine. If the answer is "bad downstream decisions get made," you want the log.

The Latency Gap Is Getting Harder to Ignore

There's a quieter shift happening here that's worth naming. More downstream consumers are real-time now, ML feature stores, risk engines, dashboards that are expected to reflect the last few seconds, not the last few minutes. That moves a lot of use cases from the query-based column into the log-based column over time.

Query-based CDC was designed for a world where "near real-time" meant hourly batches. That world still exists, but it's a shrinking part of the data infrastructure landscape.

A Note on Deleted Data

Deletes are the edge case that exposes the real tradeoff most clearly. Query-based CDC simply cannot detect hard deletes, the row is gone, and there's nothing to query. If your application does hard deletes and your downstream system needs to reflect them, you're either introducing soft-delete logic at the app layer, doing full table diffs (slow and expensive), or switching to log-based. There's no fourth option.

What to Actually Do

Start with query-based if your schema supports it and your latency needs are relaxed. It's easier to debug and has fewer operational dependencies. When you hit a wall, missing deletes, intermediate state loss, sub-minute latency requirements, that's the right moment to invest in a log-based pipeline. Not before.

The mistake is usually picking log-based CDC upfront because it sounds more correct, then spending weeks debugging replication slot issues on a table that gets 50 writes per day.

Know what you're optimizing for. The mode follows from that.