Cursor-based sync vs. Change Data Capture: what your data movement tool is missing

Every data integration tool promises the same thing: keeping your warehouse in sync with whatever is happening in your production database. How this is done under the hood, however, completely changes how much you can actually trust the data that lands on the other side.

Most tools in the market, including older versions of connectors you've probably used before, solve this using cursor-based sync. It works, until it doesn't, and when it fails, it rarely shows up as an error.

What cursor-based sync actually is

The logic is simple to explain. The tool looks at a column in your table, usually something like updated_at, stores the highest value it saw in the previous run (the cursor), and in the next execution, queries only the records with a value greater than that. Something like:

sql

SELECT * FROM leads WHERE updated_at > '2026-07-27 09:00:00';

If it finds records, it syncs them and advances the cursor to the new maximum value. If not, it does nothing until the next run. It's cheap to run, simple to implement, and works well under one specific condition: the table must have been designed with this in mind, and the data modification pattern must be simple enough to fit into a single timestamp column.

In practice, three things break this condition more often than you might think.

Flaw 1: Not every table has a reliable update column

A good portion of production tables don't have a reliable update column, or don't have one at all. Someone designed that schema thinking about the application running on it, not about whoever was going to consume that data later for analytics, and that is completely reasonable from a software engineering perspective.

The problem appears on the other side. Without a column that safely flags when a record changed, there is no reliable way to know what to sync incrementally. The only remaining fallback is a full refresh: re-reading the entire table on every run to ensure nothing was missed. This costs processing time, warehouse compute costs, and with large tables, it simply might not fit into your available scheduling window. It's the most common reason why data teams give up on syncing a specific table, not because it doesn't matter, but because the chosen method can't handle it.

Flaw 2: Deletes are invisible to cursor-based sync

This is the flaw that gets discussed the least, and it is structural, not a configuration issue. Cursor-based sync looks for records with an updated_at greater than the last seen value. A DELETE statement doesn't update any column; it removes the row. There is no "this was deleted at 2:32 PM" timestamp for a cursor query to find, because the row simply isn't there to be read anymore.

In practice, this means deleted records in the source will exist forever in your warehouse, unless someone runs a full reconciliation periodically, comparing the total row count between source and destination. Teams that aren't aware of this find out the hard way: a report counting active customers or valid orders that includes records that were canceled or deleted months ago in the source database.

Flaw 3: Intermediate states disappear between runs if you need history

Even when the column exists, is correct, and there are no deletes involved, the cursor only sees one thing: the state of the record at the exact moment the read happened. If that same record changed two or three times between one execution and the next, the subsequent run only sees the most recent value.

This really matters when your use case requires an append-only mode in the warehouse, keeping every change as a historical row, rather than just overwriting the current state. If you only need to know the latest state of a record, cursor-based sync still delivers that correctly. The problem appears when you need to reconstruct the entire user journey, and everything that happened between cycles simply isn't recorded anywhere.

It's not a bug, it's the mechanics of the method. A snapshot taken at every run only shows what existed at that exact moment. This usually doesn't show up as a system error. It shows up as an unanswered question: why doesn't this number match what the sales or support team is seeing live in the source tool? And the person answering that question, most of the time, wasn't the one who designed the pipeline.

How things change with Change Data Capture

Change Data Capture (CDC) approaches the problem from a different angle. Instead of asking the database "what has changed since the last time," it reads directly from the transaction log that the database already maintains to ensure its own integrity and enable disaster recovery. In PostgreSQL, this log is the WAL (Write-Ahead Log): before any change is applied to the tables, it is recorded in this log, in order, with full details of the operation, including inserts, updates, and deletes.

A CDC process reads this log continuously through a logical replication slot, and knows, operation by operation, exactly what changed and when, without depending on any special column in the table and without needing to compare before-and-after snapshots. This solves all three flaws at once:

• Tables without an update column are no longer a blocker, because the method doesn't rely on one.
• Deletes are no longer invisible, because a deletion is also an operation recorded in the log, not just the absence of a row.
• No intermediate states are lost between runs, because each change was captured individually at the moment it happened, rather than inferred by later comparison. This is true whether you want to keep only the latest state in the warehouse (overwrite) or need the full row-by-row history (append).

What this requires from your database

It's worth being honest about what you get in exchange for this reliability, because it is not a zero-config setup.

In PostgreSQL, the wal_level must be set to logical, you need to reserve enough replication slots and WAL senders (max_replication_slots and max_wal_senders), and the tables you want to capture need to have their replica identity set to FULL, so that updates and deletes carry the full state of the affected record, not just the primary key. This increases the volume of WAL generated by the database, so it's wise to configure a retention limit on the replication slot (max_slot_wal_keep_size), so that a temporary connection failure doesn't end up filling your database disk with accumulated WAL waiting to be consumed.

The initial connection also requires deciding between two modes: an initial full snapshot of the table followed by streaming changes from there (initial), or starting directly from what is currently available in the WAL, without bringing history prior to the connection (no_data). The right choice depends on whether or not you need the complete current state of the table as a starting point.

None of this is a reason to avoid CDC, but it is a reason not to treat it as a trivial config change. It's worth planning with your DBA or database team before enabling it in production.

CDC is not synonymous with streaming, and that is by design

There is a common confusion in the market: because CDC captures changes in real-time inside the database, many assume that the final goal must also be real-time delivery, requiring a full streaming infrastructure. That's not quite the case, and it's worth separating the two concepts.

Capturing every change at the moment it happens at the source is a matter of data completeness, not delivery speed. What you do with those changes afterward is a separate decision, and it should be guided by whoever actually consumes the data at the end of the line, not by the technical capabilities of the tool.

Think about how many of your data consumers actually process a change the second it arrives. A Power BI dashboard, for example, updates at most a few times a day, and in practice is usually looked at much less frequently than that. BI models, executive reports, and most operational analytics work perfectly fine in cycles of minutes, not milliseconds. Paying for a full streaming infrastructure to feed something that will only be read a few times a day is paying for complexity, and cost, that no one on your team will actually use.

The most sensible architecture choice for most data teams isn't between fast and slow. It's between reliable and unreliable. Log-based CDC solves the reliability part, ensuring no changes are lost at the source, including deletes and intermediate states. The delivery frequency to the destination can, and in most cases should, remain a predictable and cost-effective batch run, not forced streaming.

When cursor-based sync still makes sense

It's worth stating with equal honesty: not every table needs CDC. If the table is small, has a reliable update column, never undergoes physical deletes (only soft deletes with a flag, for example), and you only need the latest state (overwrite), cursor-based sync remains a lighter option with less database configuration. CDC pays off exactly where cursor fails: large tables, actual deletes, the need for full history (append), or where the absence of an update column is already preventing you from syncing that table incrementally today.

Conclusion

If your current data replication tool relies on an update column that your table doesn't have, if deletes in the source are still alive in your warehouse, or if you've ever struggled to explain why a number didn't match between systems, the problem probably isn't your pipeline. It's the replication method.

The Erathos PostgreSQL connector already supports WAL-based CDC, for both overwrite and append modes, with a complete step-by-step configuration guide, including the necessary database adjustments, available in our technical docs: docs.erathos.com/connectors/databases/postgresql

If you prefer to test it directly on your own database before deciding, you can create a free account and connect a Postgres table with CDC enabled in just a few minutes, no credit card required.