An overnight refresh that takes eight hours is almost never short of compute. Usually it is a pipeline rebuilding a whole table because about one per cent of the rows changed. The fix is older than whatever warehouse you run it on: move the change, not the table.
Why a full reload is slow when only one per cent of rows changed
A full reload’s work is proportional to the table’s size, not to what changed in it.
The rest of this article argues that against Kettlebridge, which is invented: a 31-person company selling mooring and berth management software, a sector we don’t work in. Every figure below was chosen so the division can be checked, not measured from anybody’s system. Its orders table holds 400 GB and one extract stream sustains 15 MB/s.
Take the unit ladder a rung at a time. 400 GB is 409,600 MB, and 409,600 divided by 15 is 27,307 seconds — seven hours thirty-five minutes. Parallelising the unload raises the rate, not the volume.
The row side agrees and borrows none of those bytes. At 2 KB a row, 15 MB/s is 7,680 rows a second, and 409,600 MB times 1,024 divided by 2 KB is 209,715,200 rows. Divide by 7,680: 27,307 seconds again.
Now the change. Two million rows at 2 KB each is 4,000,000 KB, which is 3,906 MB, or 3.8 GB, and 3,906 divided by 15 is 260 seconds. Seven hours thirty-five minutes delivered 2,000,000 of 209,715,200 rows, which is 0.95 per cent.
Log-based CDC against query-based capture
Query-based capture re-runs a query against a timestamp column, log-based change data capture reads the database’s write-ahead log, and only one of the two can see a delete.
A scheduled WHERE updated_at > :last_run lies in three ways. Deletes leave nothing to select, so a deleted row lives on downstream forever. An update that misses updated_at is invisible. And updated_at is stamped at statement time while the row only becomes visible at commit, so a transaction that starts before your watermark and commits after it leaves rows no later query will ever select.
Log-based capture reads the write-ahead log instead: logical replication on PostgreSQL, the binlog on MySQL, the transaction log on SQL Server read by a capture job into change tables, redo on Oracle. Reading takes no locks. The stream is ordered by commit, carries deletes, and stamps each record with a resumable position: an LSN, a (binlog file, position) coordinate, an SCN. A MySQL GTID is a set of ranges rather than a single number, as the MySQL 8.0 reference on GTID options sets out, so restart from gtid_executed and compare binlog coordinates inside the merge.
What log-based CDC needs from the source database
Log-based CDC needs one full read of the table on day one, a server restart on PostgreSQL, and a usable replica identity on every captured table.
Change data capture doesn’t avoid the full read; it pays it once. The first run snapshots the table and records a start position, so Kettlebridge’s seven hours thirty-five minutes lands on day one, and again whenever a snapshot is forced.
Then the restart. PostgreSQL’s write-ahead log configuration page says of wal_level that it “can only be set at server start”. On RDS you can’t set it directly at all: AWS’s page on logical replication for RDS for PostgreSQL tells you to set the static parameter rds.logical_replication to 1 and reboot, after which wal_level reports logical.
Check replica identity before you publish a table. Getting it wrong breaks writes on the source, not rows in the warehouse. The PostgreSQL publication documentation is blunt: “Tables with a replica identity defined as NOTHING, DEFAULT without a primary key, or USING INDEX with a dropped index, cannot support UPDATE or DELETE operations when included in a publication replicating these actions. Attempting such operations will result in an error on the publisher.” A production write failure, not a merge inconvenience. REPLICA IDENTITY FULL supplies an identity and costs WAL on every update. Decoding runs in a walsender on the primary and spills to disk above logical_decoding_work_mem, which the resource configuration page says “defaults to 64 megabytes”.
Make the CDC merge safe to run twice
Delivery is at-least-once, so the merge has to dedupe by key inside the batch, compare positions before it writes, and soft-delete. Below, raw_orders_changes holds change records as landed and orders is the merged current-state table.
sql
MERGE INTO orders t
USING (
SELECT * FROM (
SELECT c.*, ROW_NUMBER() OVER (PARTITION BY order_id
ORDER BY lsn DESC, tx_seq DESC) rn
FROM raw_orders_changes c WHERE c.lsn > :last_applied_lsn
) d WHERE rn = 1
) c ON t.order_id = c.order_id
WHEN MATCHED AND c.lsn > t.lsn THEN UPDATE SET
status = c.status, is_deleted = (c.op = 'd'), lsn = c.lsn
WHEN NOT MATCHED THEN INSERT (order_id, status, is_deleted, lsn)
VALUES (c.order_id, c.status, c.op = 'd', c.lsn);The :last_applied_lsn predicate keeps the dedupe proportional to the batch rather than to every change record ever landed; store that watermark in the merge’s own transaction. tx_seq breaks ties, because positions are not unique per row: Oracle’s SCN is a commit SCN shared by the whole transaction, and SQL Server says the same with __$seqval beside __$start_lsn. Deletes are soft, because a hard delete throws away t.lsn, the watermark that makes replay safe. Drop the row and a replayed update from behind the delete resurrects it.
Land the change records as Parquet as well, partitioned by date. I have no measurement of my own on compaction size, so take a documented one: Apache Iceberg’s write.target-file-size-bytes defaults to 536870912 bytes, which is 512 MB. A landing zone holding only what the merge has already consumed is not a replay path. It is a resync nobody has tested.
Fail loudly when the source schema changes
A new nullable column is easy, a dropped column or a narrowed type or a changed primary key is not, and the setting you want is the one that stops the connector rather than the one that guesses. Debezium’s inconsistent.schema.handling.mode defaults to fail, with warn and skip there if you would rather lose events quietly. The dlt library’s schema contracts default to evolve, with freeze, discard_row and discard_value for strictness. Choose the strict end at both. A primary-key change or a type narrowing commonly forces a fresh snapshot, which for Kettlebridge is another seven hours thirty-five minutes, and which is how a schema decision taken in the source database turns into a line on the data-movement bill.
When is a full reload still the right answer?
Reload the lot whenever the copy fits your window with room to spare, because a reload keeps no state between runs and so it cannot drift.
Derive that threshold rather than guessing it. Multiply the window by the extract rate you have measured, then divide by row size: four hours at 15 MB/s carries 216,000 MB, about 110 million rows at 2 KB each. Keep a quarter of it in hand for everything that goes wrong on a Tuesday, and anything under roughly 83 million rows is a reload I wouldn’t think twice about. If the table is 200 MB, reload it. Where the source has no usable change log, polling with a reload fallback is honest and calling it CDC is not.
A full reload isn’t foolproof. Truncate-then-load leaves an empty table when the second half fails, and reading a live source mid-write gives you a copy that is internally inconsistent and passes a row count. But it has fewer places to hide state, which is its real advantage. CDC holds state, and state drifts.
Fivetran’s row meter against Airbyte’s GB meter
Fivetran bills monthly active rows and Airbyte Cloud bills database sources by the gigabyte, so the same change pattern can be cheap on one meter and dear on the other.
Quote the definitions, not the prices: a definition is stable and a price is stale within a quarter. Fivetran’s usage-based pricing documentation counts distinct rows synced in a calendar month — “We only count a row once per month, even if it syncs multiple times” — and “We separately count primary keys by account, destination, connection, table, activation, and activation sync”, so the meter is per table rather than per source. Airbyte’s credit management page puts database and file sources at 4 credits per GB against 6 credits per million rows for API sources, and adds the sentence that matters: “Airbyte measures the data volume observed by the Airbyte Platform during the sync.” The gigabytes you are billed for are not the gigabytes on disk.
Run Kettlebridge through both. Two million changed rows a day over thirty days is at most 60 million active rows, fewer wherever the same keys churn. The gigabyte meter sees 3,906 MB a day, about 114 GB a month, and sync frequency is free there only while each row changes at most once between syncs.
The trap bites both meters, in opposite ways. One UPDATE ... SET last_seen_at = now() across the table activates all 209,715,200 rows that month whatever the sync frequency: 210 million against the 60 million the real workload generates, a 3.5x bill for a column nobody reads. On the gigabyte meter that one statement moves the whole 400 GB. Audit the jobs that touch every row. And get in writing which events force a re-sync, and whether a re-sync is metered.
What CDC costs to run after it ships
CDC costs you a replication slot that can fill the source’s disk, two alerts nobody writes down, and one named owner.
A replication slot on PostgreSQL holds write-ahead log files until its consumer reads them. Stop the consumer over a long weekend and the source’s disk fills: you have taken out a production database to speed up a dashboard. The replication configuration page says max_slot_wal_keep_size is -1 by default, which means “replication slots may retain an unlimited amount of WAL files”, so set it and accept the trade, which is a fresh snapshot. The quieter failure has no disk symptom at all: a logical slot holds back the catalog xmin, so the system catalogs bloat while the volume looks healthy.
Wire two alerts before you ship. Lag in time, which Debezium exposes as MilliSecondsBehindSource, and separately retained WAL per slot in bytes. PostgreSQL won’t hand you the second one: pg_replication_slots has no bytes column, so compute it as pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn). I went looking for safe_wal_size first, and it is no use here — the view’s documentation says it is NULL when max_slot_wal_keep_size is -1, which is the default you are trying to detect. Set the level from free disk rather than a round number: say 200 GB spare on the WAL volume and 4 GB of WAL an hour, and an alert at 50 GB retained leaves thirty-seven and a half hours before the volume fills.
One more number before anybody signs for a connector. Suppose my 15 MB/s is pessimistic by four times and the stream really sustains 60 MB/s: 409,600 divided by 60 is 6,827 seconds, under two hours, so the reload fits a four-hour window and the answer flips back to reloading the table. Run that derating on your own figures. CDC adds a moving part, and moving parts need an owner, so name that person in the runbook. Where nobody will own it, a slower reload that runs unattended is the better engineering decision, and saying so now is cheaper than finding out in January.