Introduction
Every team syncing an operational database to a warehouse eventually hits the same fork in the road. You have a mode: incremental replication that runs on a schedule, it works, and then a report comes back wrong. A row that a customer cancelled is still in the warehouse. Nobody deleted it there; it was deleted in the source, and the sync never noticed.
That is not a bug. It is the boundary between the two ways of moving changes, and knowing which side of it you are on decides how you build the pipeline.
Sling gives you both. Query-based incremental (mode: incremental) polls the source table and pulls what changed since the last run. Log-based change data capture (mode: change-capture) reads the database transaction log and streams every insert, update, and delete. This guide runs both against the same MySQL table so you can see exactly where the first one stops being enough, and decide which one your workload needs.
The two mechanisms
The difference is in where each mode looks for change.
Query-based incremental asks the source a question on every run: give me the rows where update_key is greater than the largest value I already have. The target table is the bookmark. Sling reads max(update_key) from the target, then pulls only newer rows. It is simple, it needs no special database privileges, and it runs against anything Sling can query. Its blind spot is built into the design. A SELECT returns rows that exist, and a row deleted at the source is not in the result set, so the target never hears about it.
Log-based CDC does not query the table at all after the first load. It reads the transaction log, the running record every relational database keeps of what changed. MySQL calls it the binary log, Postgres calls it the write-ahead log, SQL Server exposes change tables. Deletes are in that log next to inserts and updates, so CDC applies them too. The price is more setup and a higher privilege level, which is the rest of this article.
Both modes are declared in an ordinary replication file. In Sling's open-source core, change-capture sits right next to incremental and full-refresh as a mode, and you can read the mode and its option struct in the public code (config.go, CDCOptions). Running the CDC reader itself needs a CLI Pro Max token or an Advanced Platform plan. Incremental mode is free.
Query-based incremental in practice
Here is a MySQL table of 50,000 orders. It has a numeric primary key, a timestamp we can use as a cursor, and a nullable column so the type handling has something to chew on.
CREATE TABLE orders (
order_id BIGINT NOT NULL PRIMARY KEY,
customer_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL,
amount DECIMAL(12,2) NOT NULL,
note VARCHAR(100) NULL,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
The incremental replication pairs a primary_key with an update_key. That combination is Sling's "new data upsert" strategy: pull only rows newer than the checkpoint, then merge them on the primary key so re-processed rows update in place instead of duplicating.
source: MYSQL
target: DUCKDB
defaults:
mode: incremental
primary_key: [order_id]
update_key: updated_at
object: main.orders
streams:
demo_cdc.orders:
The first run is a full load:
$ sling run -r incremental.yaml
INF streaming data
INF inserted 50000 rows into "main"."orders" in 2 secs [19,910 r/s]
INF execution succeeded
Now change the source the way a real application does. Add some new orders, change some statuses, and delete a handful of cancellations:
-- 2,000 new orders, stamped with a newer updated_at
INSERT INTO orders SELECT ... ; -- ids 50001..52000
-- 3 status changes, stamped newer
UPDATE orders SET status='delivered', amount=999.99, updated_at=NOW()
WHERE order_id IN (7, 42, 100);
-- 5 cancellations, physically deleted
DELETE FROM orders WHERE order_id IN (1, 2, 3, 4, 5);
The source now holds 51,995 rows. Run the replication again:
$ sling run -r incremental.yaml
INF getting checkpoint value (updated_at)
INF writing to target database [mode: incremental]
INF inserted 2003 rows into "main"."orders" in 1 secs
INF execution succeeded
It pulled 2,003 rows: the 2,000 inserts and the 3 updates, every one of them carrying an updated_at past the checkpoint. Under the hood, DuckDB has no native MERGE, so Sling applies the batch with a delete-then-insert on the primary key. This is the same merge machinery documented for upserts:
DELETE FROM "main"."orders" tgt
WHERE EXISTS (
SELECT 1 FROM "main"."orders_sling_duckdb_tmp" src
WHERE src."order_id" = tgt."order_id"
);
INSERT INTO "main"."orders" (...)
SELECT ... FROM "main"."orders_sling_duckdb_tmp" src;
The updates land correctly and nothing duplicates. So far this is a clean, cheap, well-behaved sync.
The gap: deletes
Count the target and the arithmetic gives the mode away.
SELECT count(*) FROM main.orders; -- 52,000
SELECT count(*) FROM main.orders WHERE order_id IN (1,2,3,4,5); -- 5
The source has 51,995 rows. The target has 52,000. The difference is exactly the five deleted orders, sitting in the warehouse as ghost rows. The delete statement only ever touches keys that arrive in the incoming batch, and a deleted row does not arrive in any batch. It is gone from the SELECT. Query-based incremental did nothing wrong. It did what it is built to do, and noticing absence is not part of that.
For a lot of pipelines this is fine. Append-only event tables never delete. Dimension tables where you want to keep history never delete. But an orders table where cancellations physically remove rows will drift a little further from the truth on every run, and the drift is silent.
Log-based CDC in practice
Same table, same 51,995 rows. This time the replication uses mode: change-capture. CDC needs a state store to remember its position in the log, so we point SLING_STATE at a connection:
source: MYSQL
target: DUCKDB
defaults:
mode: change-capture
primary_key: [order_id]
object: main.{stream_table}
change_capture_options:
run_max_duration: 20s
soft_delete: false
env:
SLING_STATE: STATE_CONN/sling_state
streams:
demo_cdc.orders:
The first run does an initial snapshot, and the log lines show the two-phase design at work:
$ sling run -r cdc.yaml
DBG CLI Pro Max token validated
INF performing initial CDC snapshot for demo_cdc.orders
INF captured pre-snapshot position: mysql-bin.000003, gtid :1-17, offset 2620194
INF initial snapshot: 1 chunks of 100000 rows (pk_type=integer)
INF execution succeeded
Sling records the binlog position before it reads a single row, so no change that happens during the snapshot is lost. It then copies the table in primary-key-range chunks. The chunks are resumable, so an interrupted snapshot picks up at the last completed chunk instead of starting over. The target lands with 51,995 rows, each tagged in a new _sling_synced_op column with S for snapshot. Sling also adds _sling_synced_at and a monotonic _sling_cdc_seq for ordering.
Now the same kind of mutation as before (inserts, an update, and deletes), followed by a second run:
$ sling run -r cdc.yaml
DBG loaded CDC state: snapshot_complete=true, stream=demo_cdc.orders
DBG CDC reader: starting position mysql-bin.000003:2620194, server head :2621421, lag=1.2KB
INF read 6 CDC events | tables=1 duration=64ms
DBG CDC reader position: advanced to mysql-bin.000003:2621421 | lag=0B | caught_up=yes
INF execution succeeded
Six events: two inserts, one update, three deletes. This run never queried the orders table. It resumed at the binlog offset it saved last time, read forward to the server's head, applied the changes, and saved the new position. Check the target:
SELECT count(*) FROM main.orders; -- 51,994 (source is 51,994)
SELECT _sling_synced_op, count(*) FROM main.orders GROUP BY 1;
-- I 2
-- U 1
-- S 51,991
The target row count matches the source exactly. The three deleted rows are gone. Every changed row carries the operation that produced it. This is the difference the ghost-row arithmetic was pointing at. CDC tracks the source, deletes and all.
The decision: which one your workload needs
Reach for query-based incremental when:
- The source never deletes rows, or deletes do not matter downstream (append-only events, logs, immutable facts).
- You are happy to model deletes another way — a soft-delete flag the application already sets, which becomes an ordinary
updateyourupdate_keypicks up. - You cannot get elevated database privileges. Incremental needs only
SELECT. - The table has a reliable, monotonic
update_key. Without one there is no cursor to move. - You want the simplest thing that works, in the open-source CLI, with no state store to operate.
Reach for log-based CDC when:
- Deletes happen in the source and have to reach the target. This is the single clearest signal.
- You need low latency. CDC reads a log that is already being written; it does not re-scan the table, so you can schedule it every few seconds without hammering the source.
- The source is write-heavy and repeated
WHERE updated_at > ?scans are getting expensive, or there is no trustworthyupdate_keyto scan on at all. - You need an audit trail of what changed and how — the
_sling_synced_opcolumn is that record. - You can provision the log-reading setup and run on a paid plan.
A useful tie-breaker: query-based incremental answers "what does the source look like now?" CDC answers "what happened to the source?" If your warehouse only needs the current state and the source never deletes, the first question is enough and cheaper to ask. If you need the history of change, and deletions in particular, you need the second.
What CDC costs to run
CDC is not a free upgrade, and the honest version of "when to use it" includes the operational bill.
A transaction log has to be on and retained. MySQL needs binlog_format = ROW, Postgres needs wal_level = logical. On managed databases these are configuration flags, and they are not always the default.
Sling reads the log but does not create it. The server-side capture object, a Postgres publication or a SQL Server capture instance, is provisioned once by a DBA. That keeps Sling's own role read-only and least-privilege, which is good for a security review and one more step at setup.
State is mandatory. Without SLING_STATE, CDC has nowhere to save its log position and would re-snapshot on every run. The state connection is part of the deployment.
Finally, the log holds space until you consume it. A Postgres replication slot pins WAL segments until Sling reads past them, so a paused CDC job can grow disk. Scheduling and a WAL-retention cap are operational concerns that incremental mode simply does not have.
None of this is a reason to avoid CDC. It is the reason not to reach for it when a query and an update_key would do.
Verification
The whole argument reduces to one query you can run on both targets: does the row count match the source?
-- query-based incremental target
SELECT count(*) FROM main.orders; -- 52,000 vs source 51,995 → 5 ghost rows
-- change-capture target
SELECT count(*) FROM main.orders; -- 51,994 vs source 51,994 → exact
If your source deletes rows and you cannot afford the drift, that five-row gap is your answer. If it never deletes, the gap never opens and the simpler mode is the right call.
Conclusion
Change data capture is not automatically better than incremental. It is better at a specific thing, deletes and change history, and it charges setup and privileges for it. Sling lets you make the call per replication with a one-word change to mode, and keeps the same primary keys and merge behavior underneath both. Start with query-based incremental. Move a stream to change-capture when the ghost rows show up, or when you know they will.
For the merge mechanics both modes share, see Upserts with Sling. For a related MySQL-source walkthrough, see How to Replicate MySQL to BigQuery with Sling and Migrating from Airbyte to Sling. The full CDC option reference lives in the Change Capture docs, and the incremental strategies in the modes reference.
