CDC vs Polling
One asks the database what is true now, on a loop. The other observes what the database committed. The difference is not speed — it is which changes are structurally invisible.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
A nightly job selects every row where updated_at is newer than the last run. Which real changes does that query structurally fail to see, and when is it still the right answer?
A pipeline owner deciding how a table gets into the platform, and the analyst who will later ask why the fact table contains orders that were cancelled and deleted upstream three months ago. The consumer's real requirement is almost never "faster" — it is "complete, including the things that stopped existing" (Who Actually Consumes This Data).
Polling produces one row per entity per extraction window, holding whatever state that entity was in at read time. CDC produces one row per committed change. These are different grains and they are not interchangeable: a polled row is a sample of state, a CDC row is an event about state (Event vs Snapshot Modeling).
Add an updated_at column, index it, and extract WHERE updated_at > :last_run on a schedule. It needs no special privileges, works against any source you can issue a SELECT to, is trivial to reason about, is trivial to re-run, and is what a very large fraction of working data platforms actually do (Incremental Extraction).
A hard DELETE removes the row. There is no row left to select, so the extract cannot possibly report it. The downstream table keeps the order forever, and every revenue total that includes it is wrong by exactly that amount (Missing Rows).
- A hard
DELETEremoves the row. There is no row left to select, so the extract cannot possibly report it. The downstream table keeps the order forever, and every revenue total that includes it is wrong by exactly that amount (Missing Rows). - A row is updated twice between two polls. The extract sees the final state and the intermediate one never existed as far as the platform is concerned — which is fatal for anything measuring transitions, like time-in-status or funnel progression.
updated_atis assigned by the application at statement time but the transaction commits seconds later, after the next extract has already read past that timestamp. The row is skipped permanently, and nothing about the pipeline looks wrong (The High-Water Mark).- A backfill script or an admin
UPDATEforgets to touchupdated_at. The change is real, committed, and permanently invisible to a predicate that keys on a column the writer did not maintain. - The polling query itself becomes the load: an unindexed
updated_aton a large table means a full scan every interval, competing with the application for exactly the pages it needs (Workload Isolation). - Clock skew between application instances writes timestamps that go backwards relative to each other, so a strictly increasing high-water mark skips whatever landed on the slow clock (Sequence Numbers, ACKs and Reassembly).
What is actually happening
- Polling asks a state question: "which rows currently satisfy this predicate?" The answer is whatever is true at read time, and everything that happened between reads is compressed away by construction. That compression is not a bug in the implementation — it is what a state query means.
- CDC asks nothing. It observes a log the database wrote as part of committing, so it sees every committed change exactly as the database recorded it, including deletes and including each intermediate value (Write-Ahead Logging).
- The predicate is where polling's correctness lives, and it is weaker than it looks.
updated_at > Xis sound only if the column is assigned at commit, is monotonic across all writers, is maintained by every writer, and is never rewritten backwards. Real systems violate at least one of those routinely (A Transaction, Inside the Engine). - A soft-delete convention repairs the delete gap —
deleted_atmakes a removal visible as an update — but it repairs it only where every writer honours it, and aON DELETE CASCADEnever will. - Full-snapshot polling avoids the predicate problem entirely by reading the whole table each time and diffing. It is correct, it sees deletes, and its cost scales with table size rather than change volume, which is why it is used for small dimensions and abandoned for large facts (Full Refresh vs Incremental).
- CDC's cost is retention rather than reads, and its coupling is to the source's physical schema rather than to one column's discipline. It trades one class of correctness risk for a different class of operational risk (Change Data Capture).
The query, and the three changes it cannot see
Here is the extract almost every data platform starts with. It is short, it is indexable, it re-runs cleanly, and for a large class of tables it is correct. It deserves to be taken seriously rather than dismissed, because the alternative costs a privileged connection into production.
What it cannot do is not a matter of tuning. A SELECT returns rows that exist and satisfy a predicate. A deleted row exists nowhere; an overwritten intermediate value satisfies nothing; a row whose updated_at was set before commit and read after has already been passed by. All three are consequences of asking a state question, and all three survive any interval you choose.
The third case is the one that catches experienced engineers. The timestamp is assigned when the statement runs; the row becomes visible when the transaction commits. If an extract reads at 03:00:05 and a transaction that stamped 03:00:03 commits at 03:00:07, the watermark has already advanced past a row that was never returned. Nothing logs an error, no count looks unusual, and the row is gone from the platform forever (Transactions and ACID).
1-- The version everyone writes first2SELECT *3FROM orders4WHERE updated_at > :last_run -- exclusive lower bound5ORDER BY updated_at;6 7-- The version that survives commit-time skew.8-- Re-reads a margin of already-seen rows on purpose; the merge9-- downstream must be idempotent on order_id for this to be safe.10SELECT order_id, status, amount_minor, currency, updated_at11FROM orders12WHERE updated_at >= :last_run - :safety_margin13 AND updated_at < :read_started_at -- never read into the open edge14ORDER BY updated_at;15 16-- Neither version can return this row, because it is not there:17-- DELETE FROM orders WHERE order_id = 88123;18-- and neither can return the intermediate state of this one:19-- UPDATE orders SET status = 'paid' WHERE order_id = 88124;20-- UPDATE orders SET status = 'refunded' WHERE order_id = 88124;The safety margin converts a permanent data-loss failure into a duplicate-delivery problem, which an idempotent merge already solves. The exclusive upper bound matters just as much: reading up to now() means reading into a window that is still receiving commits.
The comparison, made honestly
The table below is the argument in full. Read it as two columns of consequences rather than as a scoreboard — several rows favour polling, and a comparison that did not would be advocacy rather than engineering.
The rows worth arguing about are deletes and access. Deletes are the one capability gap that cannot be closed by effort: no polling schedule, index or convention makes a hard delete visible to a state query in general. Access is the one operational gap that often decides the matter before any technical argument starts, because replication privilege on a production database is a security review, not a config change.
Everything else on the list is a trade you can make deliberately in either direction, and the right choice differs per table inside the same platform.
| Dimension | Polling (`updated_at > X`) | Log-based CDC |
|---|---|---|
| Hard deletes | Structurally invisible. A soft-delete convention helps only where every writer honours it. | Delivered as a delete event with the key, and with the previous row image where the source is configured for it. |
| Intermediate states | Compressed away. Two updates between polls look like one. | Every committed change is a separate event, so transitions and time-in-status are answerable. |
| Load on the source | A scan per interval, sized by the table and the index — paid whether or not anything changed. | No queries. Costs retained log and one replication connection. |
| Ordering | Only what the extract's ORDER BY imposes. Says nothing about true commit order. | Source log position, which is the true commit order — until the stream is partitioned downstream. |
| Correctness dependency | A column maintained by every writer, assigned monotonically, at commit. | The connector's position staying inside the source's log retention. |
| Replay window | Unbounded in time, bounded in content — you can always re-read, but only current state. | Bounded by retention, complete in content — you can replay actual history, until you cannot at all. |
| Access required | A read-only role, scoped to tables or columns. Works against sources you do not own. | Replication privilege on a production database, plus source-side configuration. |
| Schema coupling | To the columns the query names, plus the semantics of one timestamp. | To the full physical table shape, including columns added later. |
| Failure blast radius | A slow extract competes with the application. Stopping it costs nothing upstream. | A stalled connector can hold the source's log open until disk pressure becomes an outage. |
| Operational surface | A scheduled query and a stored watermark. | A connector process, its position store, its schema handling, and its own on-call. |
Choosing per table, not per platform
This decision is made per table and it changes over a system's life. A dimension of a few thousand rows and a fact table receiving millions of changes a day do not deserve the same mechanism, and a platform that applies one uniformly is optimising for architectural tidiness rather than for correctness or cost.
The dominant input is not freshness and not volume. It is which questions the consumer must be able to answer — specifically, whether any of them involve something that stopped existing or something that passed through a state. If yes, the mechanism is decided regardless of everything else.
The second input is access. If the source is a SaaS product or another company's database, the entire left-hand branch of this tree is unavailable and the design work moves to making a watermark trustworthy against an API you cannot inspect.
What does the consumer need to be able to answer, and what access do you actually have to the source?
when Small table, changes rarely, deletes matter, and re-reading it entirely is cheap. Most dimension tables.
cost Cost scales with table size and interval rather than with change volume. Sees deletes by set difference but still cannot see intermediate states. Simplest correct thing available (Full Refresh vs Incremental).
when Large table, no deletes or soft deletes only, no question depends on transitions, and you have read access but not replication access.
cost Correctness depends on a column's discipline across every writer. Requires an overlapping window and an idempotent merge to be safe at all (The High-Water Mark).
when You control the source, hard deletes occur, or a consumer needs transitions, ordering by commit, or low latency without scan load.
cost Privileged access, a connector to operate, coupling to physical schema, and a retention deadline that turns a downstream stall into an upstream storage problem (CDC Failure Modes and the Retention Deadline).
when The consumer needs a *business* event with meaning the row change does not carry, and the producing team is willing to own it.
cost Application work in the producing service and a table that must be pruned. Buys a stable published contract instead of a leaked physical schema (The Transactional Outbox).
when Polling is forced by access, but deletions genuinely matter to a consumer.
cost A full key-set comparison on a schedule — expensive on the source, and it detects deletions late rather than observing them. The honest compromise when CDC is unavailable (Reconciliation).
Store the maximum `updated_at` seen, and next run extract everything strictly greater than it, up to `now()`.
Extract from the stored watermark minus a safety margin, up to a read timestamp captured before the query started, and merge into the target on the business key.
Rows become visible at commit, not at the moment their timestamp was assigned, so a strictly-greater bound against a still-open window silently excludes anything that committed late. Overlap converts that permanent loss into re-delivery, and re-delivery is already handled by the idempotent merge every pipeline needs for other reasons.
How to build it
Most important first.
- Choose by what the consumer must be able to answer, not by freshness. If any question involves deletions, transitions, or "what did this look like at time T", polling cannot answer it at any interval and the discussion is over.
- If polling is the right answer, key the watermark on a column the *database* controls — a commit-ordered sequence or a system change column — rather than on an application-assigned timestamp, and overlap the window rather than abutting it (The High-Water Mark).
- Always overlap. Re-reading a safety margin of already-extracted rows costs duplicated work and is made harmless by an idempotent merge; abutting windows cost you rows and are not recoverable (Upserts and Merges).
- Where you cannot get replication access — a SaaS API, a vendor database, a partner's system — polling is not a compromise, it is the only mechanism available, and the design work is in making the watermark trustworthy (Ingestion Sources).
- Reconcile whichever you choose against the source on a closed period. Polling gaps and CDC gaps look identical downstream and are found by the same check (Reconciliation).
- Do not run both against the same table hoping for belt and braces. Two ingestion paths with different grains converge on one table and you will spend the incident arguing about which one was right.
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- CDC guarantees at-least-once delivery of every committed change, ordered by source log position, while the connector's position stays inside retention. Polling guarantees delivery of every row that satisfied its predicate at read time — which is a strictly weaker and much more conditional statement.
- Neither guarantees deduplication. Polling with an overlapping window re-delivers by design; CDC re-delivers after a restart. Both require an idempotent sink (Idempotent Data Pipelines).
- Neither guarantees transactional atomicity downstream. A poll that reads two tables in two statements sees them at two different moments; CDC publishes one transaction's changes as independent events (CDC Ordering and Transaction Boundaries).
- Polling guarantees no ordering at all beyond the sort of the extract query, and specifically does not tell you whether two changes to the same row happened in the order you inferred.
- Polling guarantees completeness only for changes that (a) left a row behind and (b) moved the watermark column. Neither is a property you can verify from inside the pipeline.
- CDC guarantees nothing about schema stability, and polling with
SELECT *guarantees nothing either — an added column changes the extract's shape without any code change (Breaking Schema Changes).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The decisive check for a polled table is a presence reconciliation: compare the set of primary keys in the source against the set in the serving table for a closed period. Row counts alone will not find a deletion gap, because the deleted row is missing from one side and present on the other in a way a count of the destination cannot reveal.
- For CDC, the decisive check is a gap check on log position: assert that the sequence of received positions has no discontinuity larger than the connector could legitimately produce.
- The presence check misses rows that were created and deleted entirely between two extracts — they never existed on either side at check time, so no comparison can find them. Only a change stream can.
- Both checks miss the case where source and destination are wrong in the same way, which is what happens when a shared filter is incorrect (The Pipeline Succeeded. The Data Is Wrong.).
- Polling's freshness is stepped and predictable: data is on average half an interval stale and at worst one full interval, and that shape is easy to communicate in an SLO (The Freshness SLO).
- CDC's freshness is continuous and variable: usually far fresher, occasionally much worse during a bulk change or a restart. A consumer who was promised "seconds" and observes forty minutes once a quarter will trust the platform less than one who was promised an hour and always got it.
- Shortening the polling interval does not converge on CDC. It converges on a source under constant scan load that still cannot see deletes — the missing information is structural, not temporal.
- The interval is also a cost dial in a way CDC's is not: halving a polling interval doubles the scanning, while CDC's work is proportional to change volume regardless of how promptly it is read (Cost vs Freshness).
- Polling couples you to one column's semantics. The day someone changes how
updated_atis set — moving it into a database default, or letting a bulk job skip it — the extract silently changes what it captures with no schema change at all (Semantic Changes). - CDC couples you to the whole physical table shape, which is a broader surface but a more visible one: a column drop tends to produce an obvious failure rather than a quiet gap (CDC and Schema Drift).
- Switching from polling to CDC mid-history is itself an evolution event. The two periods have different completeness — deletes are absent before the switch and present after — and any trend that crosses the boundary is not comparable.
- Record the ingestion method as a column or a partition property on the raw data, so that a future analyst can see where the method changed instead of discovering it from a discontinuity (Metadata: Technical, Operational and Business).
- Polling is trivially replayable: move the watermark back and re-run. There is no retention deadline, because the source still holds the rows. This is polling's single largest operational advantage and it is routinely undersold.
- What polling cannot recover is anything the predicate never saw. Rewinding the watermark re-reads current state, so a row deleted last month is still absent and a value that was overwritten is still gone.
- CDC recovery is bounded by retention on the source and, more usefully, by retention on the downstream log — which is under your control and is why a broker between CDC and everything else is worth its operational cost (Replay from the Log).
- Both require the same downstream property to be safe: writes keyed on the business key, applied as a merge, so a replay converges rather than accumulates (Deduplication).
What can go wrong
- The watermark advances past rows that had not yet committed, skipping them permanently and silently. This is polling's signature failure and the reason overlap exists.
- The watermark is stored in the same place as the job's output, so a partial failure leaves the watermark advanced and the data absent (Partial Failure).
- A polling extract holds a long-running read against a busy table and either blocks vacuum-style maintenance or reads an inconsistent set across pages (Isolation Levels).
- CDC's connector stalls and holds the source's log open until disk pressure becomes a production incident (CDC Failure Modes and the Retention Deadline).
- The mitigation fails too: a soft-delete convention introduced to fix polling's delete blindness is honoured by application code and bypassed by the one nightly cleanup job that uses raw SQL.
- "Polling is just CDC with more latency." Latency is the least important difference. Polling cannot see deletes or intermediate states at any interval, and no amount of frequency fixes a structural blindness (What a CDC Event Contains).
- "We added
deleted_at, so polling sees deletes now." It sees the deletes that went through the code path that sets it. Cascades, bulk cleanups and direct SQL do not. - "CDC is always cheaper on the source." It is cheaper in queries and more expensive in retention risk. A stalled connector costs the source far more than a nightly scan ever would.
- "An index on
updated_atmakes polling free." It makes the lookup selective. It does not make the timestamp trustworthy, and untrustworthiness is the actual problem. - "CDC means we can drop the batch extract." Keep one periodic full-snapshot reconciliation regardless of method. It is the only independent evidence that the streaming path is complete (Reconciliation).
- Polling exports only the columns the query names, which is a genuine minimisation advantage — an extract selecting six columns cannot leak the seventh (Data Minimization).
- CDC exports every column of the captured table by default, including columns added later, so column filtering has to be configured deliberately and reviewed when the source schema changes (PII in Pipelines).
- CDC requires replication-level access to production; polling can be granted a read-only role scoped to specific tables or even specific columns, which is a materially smaller blast radius (Database Privileges and Blast Radius).
Operating it
- For polling: watermark value over time, plotted against wall clock. A flat line is a stalled extract; a jump is a manual intervention nobody documented.
- For polling: rows extracted per run against the same weekday historically, which is the cheapest detector of a predicate that quietly stopped matching (Volume Anomalies).
- For CDC: connector position lag as a log-position difference, plus retained log size on the source (Pipeline Metrics).
- For both: a periodic count of primary keys present in the source but absent downstream — the only signal that distinguishes a delete gap from a normal quiet day (Reconciliation).
- At 10x table size, polling's scan cost grows and CDC's does not — the crossover is driven by the ratio of changed rows to total rows, and a large slowly-changing table is where CDC wins most clearly.
- At 10x change rate, the comparison inverts: polling still costs one scan per interval while CDC now emits ten times the events, and the broker and consumers become the constraint (Topics and Partitions).
- At 100x source count — dozens of SaaS APIs and partner databases — polling wins by default, because replication access to systems you do not own is rarely obtainable at any price (Ingestion Sources).
- Consumer count is where CDC pulls ahead permanently: one connector feeding a log serves twenty consumers, while twenty polling jobs are twenty scans against the same table.
- Polling costs the source a scan per interval whose size is driven by table size and index selectivity, not by how much changed. A table where nothing changed still costs a full interval of work (Scan Cost).
- CDC costs the source retained log bytes and costs the pipeline one event per change. On a table that is rewritten constantly this can exceed the cost of just re-reading it.
- Polling with
SELECT *moves every column of every matched row across the network every interval, which is usually the largest and least examined line in a polling pipeline. - CDC adds a permanent operational cost that polling does not: a connector to run, monitor, upgrade and page on (Scoring Operational Complexity).
- Polling buys simplicity, portability, no privileges, unlimited replay and a source you cannot break. It costs you deletes, intermediate states, and correctness that depends on a column's discipline.
- CDC buys completeness, deletes, intermediate states, ordering by commit, and no query load. It costs privileged access, a coupling to physical schema, an operational surface, and a hard retention deadline.
- The honest default for a source you control, where deletes matter, is CDC. The honest default for a source you do not control, or a small dimension table, is polling. Most platforms run both and are right to.
- Choosing CDC because it is "more modern" and then running it into an hourly transformation buys nothing and costs everything on the list above.
Polling gap simulator
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
| id | updated_at | committed | Fate | Why |
|---|---|---|---|---|
| 1 | 100 | 100 | earlier poll | ordinary row |
| 2 | 118 | 118 | LOST | ordinary row |
| 3 | 119 | 124 | LOST | long transaction — stamped before the poll, committed after it |
| 4 | 120 | 120 | LOST | exactly on the boundary |
| 5 | 120 | 121 | LOST | same second as the boundary, committed just after |
| 6 | 117 | 117 | LOST | clock behind by three seconds on a second writer |
| 7 | 130 | 130 | next poll | after the boundary |
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThat a state query cannot observe a deletion or an intermediate value, while a change log can, is a property of the two mechanisms rather than of any product. It holds for a Postgres table, a SaaS REST endpoint and a spreadsheet export alike.
- SOURCE-SPECIFICWhether polling can be made correct depends on what ordering the source exposes: a database offering a commit-ordered system column gives a trustworthy watermark, an application-assigned
updated_atdoes not, and a SaaS API paginating by an opaque cursor may offer no re-readable ordering at all. - SCALE-SPECIFICFor a small dimension table, full-snapshot polling is more correct than incremental polling and simpler than CDC, because re-reading everything sidesteps the watermark question entirely. That answer stops being available somewhere between a table that fits comfortably in a scan and one that does not.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns why a timestamp assigned on one machine is not a reliable order across machines, and what a logical clock buys instead. Polling's watermark failure is a clock problem wearing a data-engineering costume, and the depth belongs there.