DEV Community: Pipeline Whiteboard

Running CDC in Production: The WAL Slot Trap

Pipeline Whiteboard — Mon, 02 Nov 2026 18:58:15 +0000

At 03:02 the primary database ran out of disk and Postgres shut down. There was no runaway query. A CDC connector had been paused nine days earlier, and its replication slot had been holding every byte of write-ahead log since.

This is Part 6 of CDC in Production, the final part. In Part 5 we shipped schema changes without breaking the warehouse. This post is about keeping the pipeline, and the database under it, alive: the WAL slot trap, heartbeats, the three lag numbers that matter, the alerts and runbook you need, and what all of it costs.

Prefer to watch? Here's the 8-minute video version with animated diagrams:

Video link: Running CDC in Production: The WAL Slot Trap

The WAL slot trap

The write-ahead log (WAL) is where Postgres records every change before it counts as committed. A replication slot is a promise about that log. It records the oldest WAL position its consumer still needs, the restart_lsn, and Postgres keeps every WAL file from that point on.

If the connector stops, the slot doesn't. Writes keep coming and the retained WAL keeps growing. And by default there's no ceiling:

# postgresql.conf
max_slot_wal_keep_size = -1    # the default: a slot may retain unlimited WAL

That's the incident. A connector was paused for a migration and nobody resumed it. In this example the database wrote about 40 GB of WAL a day and the disk had 360 GB free, so nine days later it was full. When the disk holding the WAL fills up, the Postgres docs are blunt: the server can panic and shut down.

The quiet slot

There's a sneakier version, with the connector running the whole time.

Debezium confirms its WAL position to Postgres as it commits offsets for the events it sends. Now suppose the captured tables rarely change while the rest of the database is busy. The connector reads the log but sends almost nothing, so the position it confirms falls behind, and Postgres keeps WAL it no longer needs.

The same happens when you capture a quiet database on a server that also hosts a busy one. Slots are per database, but the WAL is shared by the whole server.

On Amazon RDS, even an idle instance generates WAL, because RDS writes to its own system tables about every five minutes.

Heartbeats

The fix is heartbeats. heartbeat.interval.ms defaults to 0, which means off. Set it, say to ten seconds, and the connector emits a heartbeat on that interval, which gives it the chance to confirm the latest position it has read. Postgres can then recycle the old WAL.

For the quiet-database case, go one step further and make each heartbeat a real change in the captured database. Create a tiny table:

CREATE SCHEMA cdc;
CREATE TABLE cdc.heartbeat (
  id      int PRIMARY KEY,
  beat_at timestamptz NOT NULL
);
GRANT USAGE ON SCHEMA cdc TO debezium;
GRANT SELECT, INSERT, UPDATE ON cdc.heartbeat TO debezium;

-- Without this, the connector never sees the heartbeat rows
ALTER PUBLICATION dbz_shop ADD TABLE cdc.heartbeat;

Then point heartbeat.action.query at it, so the connector runs the statement on every beat:

"heartbeat.interval.ms": "10000",
"heartbeat.action.query": "INSERT INTO cdc.heartbeat (id, beat_at) VALUES (1, now()) ON CONFLICT (id) DO UPDATE SET beat_at = EXCLUDED.beat_at"

The upsert keeps the table at a single row, so it never grows.

Safety valves

Postgres also has safety valves for when everything else fails:

max_slot_wal_keep_size caps how much WAL a slot may hold. Past that limit the slot can be marked lost, and the connector can't resume from it.
idle_replication_slot_timeout (Postgres 18 and later) invalidates slots that have sat idle for too long.

You can watch a slot approach the cap in pg_replication_slots.wal_status, which moves from reserved to extended to unreserved and finally lost. The safe_wal_size column tells you how many more bytes can be written before the slot is in danger.

Both valves protect the database at the cost of the pipeline, because a lost slot means a fresh snapshot. That's usually the right trade. Just make it on purpose, before the incident.

Three kinds of lag

You need all three, because each one lies on its own.

1. Slot lag, in bytes. How much WAL each slot is holding:

SELECT slot_name, database, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_held,
       pg_size_pretty(safe_wal_size) AS safe_wal_size,
       inactive_since                                -- Postgres 17+
FROM   pg_replication_slots
WHERE  slot_type = 'logical'
ORDER  BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

On RDS, the CloudWatch metrics ReplicationSlotDiskUsage and OldestReplicationSlotLag show the same risk.

2. Time lag. Debezium's MilliSecondsBehindSource metric is the gap between a change's commit timestamp and the connector processing it.

3. Sink lag. Consumer lag on the change topics, plus the age of the newest row in the warehouse (freshness).

Why one isn't enough: time lag is measured on events, so when the captured tables go quiet it has nothing new to report, while the slot keeps growing. It also includes any clock difference between the database server and the connector. Slot bytes show the risk to the database. Warehouse freshness shows what your users feel. Put all three on one dashboard.

Alerts, with an owner

Turn those numbers into alerts, and give each alert an owner, a person or rotation that actually gets woken up.

Severity	Condition	Where to read it
Page	A slot has been inactive for 15 minutes	`pg_replication_slots.active`, `inactive_since`
Page	`wal_status` is `unreserved` or `lost`	`pg_replication_slots`
Page	Free disk below your threshold	Host metrics, or `FreeStorageSpace` on RDS
Page	A connector task is `FAILED`	Kafka Connect REST API
Warn	Slot lag high for 30 minutes	Slot query or RDS metrics
Warn	Warehouse freshness past your SLA	Freshness query

Tune the numbers to your system. The shape is what matters.

The runbook

When the slot alert fires, run the same four steps every time.

1. Look at the slot. Run the query above. Is it active? What's its wal_status? How much WAL is it holding?

2. Check the connector and restart failed tasks:

curl http://connect:8083/connectors/shop-cdc/status

curl -X POST \
  'http://connect:8083/connectors/shop-cdc/restart?includeTasks=true&onlyFailed=true'

If it comes back healthy, watch slot lag fall.

3. If the disk will fill before it catches up, make the call. Drop the slot to save the database, and plan a fresh snapshot. Stop the connector first, because an active slot can't be dropped:

SELECT pg_drop_replication_slot('debezium_shop');

When the connector restarts, its stored position is gone. A snapshot mode such as when_needed (from Part 2) takes a new snapshot in that case.

4. Afterwards, reconcile. Run the bucketed reconciliation from Part 3 to prove nothing is missing.

Plan the routine events

Major Postgres upgrades. Slots don't survive a major upgrade, and a new slot only sees changes made after it exists. Debezium's documented procedure, in short:

Stop writes to the database.
Let the connector drain the slot, then stop it gracefully.
Drop the slot.
Upgrade.
Create the slot again before writes resume, for example with SELECT pg_create_logical_replication_slot('debezium_shop', 'pgoutput');. Otherwise the changes made in between are lost.
Check the publication still exists, then restart the connector (the procedure has you recreate it under a new name).
Resume writes.

Failover. Since Postgres 17, logical slots can be synchronized to a physical standby. Set slot.failover to true in Debezium, turn on sync_replication_slots (with hot_standby_feedback) on the standby, and list the standby's physical slot in synchronized_standby_slots on the primary. After a promotion, the connector continues on the new primary. Part 3 covers what happens without this.

What it costs

A stuck slot doesn't only risk an outage. On RDS with storage autoscaling, the volume grows to absorb the WAL, and it doesn't shrink back afterwards. Getting allocated storage down later means a blue/green deployment or a new instance.

Elsewhere in the pipeline, Kafka storage grows with retention and replication factor, and in the warehouse every MERGE uses compute. But the cheapest line item is the slot alert, because it protects the database and the bill.

Five common mistakes

Leaving a slot behind after pausing or deleting a connector.
No heartbeats on a quiet database.
Alerting on time lag alone.
slot.drop.on.stop=true in production. Debezium's docs say it's for testing only.
An alert with no owner, so the page goes to a channel nobody reads at three in the morning.

Recap

A slot keeps every WAL byte its consumer hasn't confirmed, without limit by default.
Heartbeats let a quiet connector confirm its position.
Cap the risk with max_slot_wal_keep_size, and choose that trade on purpose.
Watch slot bytes, time lag and warehouse freshness together.
Alert with an owner, and keep a runbook for the slot, upgrades and failover.

This week, run one query: list every replication slot, whether it's active, and how much WAL it holds. If one surprises you, you've found your first incident early.

The CDC in Production series

That's the end of the series. Here's every part, from the basics to this runbook:

Part 1: Change Data Capture (CDC) Explained (article · video)
Part 2: Debezium Postgres to Kafka: Every Setting Explained (video)
Part 3: The Silent CDC Bug: Data Loss With No Errors (video)
Part 4: CDC Deletes, Tombstones and the Idempotent MERGE (video)
Previous, Part 5: CDC Schema Changes: What Breaks and How to Ship Them (video)
Part 6: Running CDC in Production: The WAL Slot Trap (this post · video)

If this was useful, the video version has the animated diagrams for every step: watch it on YouTube. Subscribe to Pipeline Whiteboard for more diagram-first data engineering explainers. Thanks for reading the whole series.

CDC Schema Changes: What Breaks and How to Ship Them

Pipeline Whiteboard — Sat, 31 Oct 2026 18:58:27 +0000

On Friday at 17:40, someone renamed one column in the orders table. The pipeline stayed green all weekend. On Monday, the revenue dashboard showed zero for Saturday and Sunday. Nothing failed, and that was the problem.

This is Part 5 of CDC in Production. In Part 4 the sink learned to survive duplicates, late events and deletes. This time the table itself changes. We'll follow a schema change through the pipeline, look at the registry rules that decide what's allowed, see what Snowflake and the lakehouse can absorb, and finish with a migration pattern that never breaks either side.

Prefer to watch? Here's the 7-minute video version with animated diagrams:

Video link: CDC Schema Changes: What Breaks and How to Ship Them

How a schema change travels

Postgres logical decoding (the feature that turns the write-ahead log into row changes) doesn't carry DDL. So Debezium never sees the ALTER TABLE itself. It notices the new shape on the next row change for that table and refreshes its in-memory schema. That event goes out with the new structure.

If you use Avro, the converter then registers the new structure as a new schema version in Schema Registry, a service that stores every version of each message schema. Versions are grouped under a subject; with the default naming, the value schema for topic shop.public.orders lives under shop.public.orders-value. Before accepting a new version, the registry runs a compatibility check against what's already there.

The converter is configured on the connector (or the Connect worker):

"value.converter": "io.confluent.connect.avro.AvroConverter",
"value.converter.schema.registry.url": "http://schema-registry:8081"

Registry compatibility modes

Compatibility is the rule the registry uses to decide whether a new version may be registered. The default is BACKWARD.

Mode	Guarantee	Upgrade first
`BACKWARD` (default)	Consumers using the new schema can read data written with the old one	Consumers
`FORWARD`	Consumers using the old schema can read data written with the new one	Producers
`FULL`	Both directions	Either side, independently

By default the check compares only against the latest version. The _TRANSITIVE variants (BACKWARD_TRANSITIVE and so on) compare against every version, which matters if a consumer might replay old data from the start of a topic.

What does that allow in practice, with Avro?

Change	`BACKWARD`	`FORWARD`	`FULL`
Add an optional field (has a default)	✅	✅	✅
Remove an optional field	✅	✅	✅
Add a required field (no default)	❌	✅	❌
Remove a required field	✅	❌	❌

All of it comes from one rule in Avro's schema resolution: if the reader's schema has a field with no default, and the data was written without that field, the read fails. With Debezium, a nullable Postgres column becomes an optional field with a null default. A NOT NULL column becomes a required field, unless Debezium can carry the column's default value into the schema (it does this for many defaults; for function defaults, see mistake 4 below).

The rename, two ways

amount_cents became total_cents. To Debezium, that's one field gone and a new one added.

If the column is NOT NULL, the new field is required, with no default. Under BACKWARD, the registry rejects it. The converter throws, and with Kafka Connect's default errors.tolerance=none, the connector task fails. That's loud, and loud is good: someone gets paged on Friday.

In the real incident, the column was nullable. Both fields are optional, so the registry accepts the change and nothing turns red. Snowflake, with schema evolution switched on, adds a TOTAL_CENTS column. The old AMOUNT_CENTS column gets NULL on every new row. Every dashboard still sums AMOUNT_CENTS, so weekend revenue reads zero.

A failed connector is the good outcome. The dangerous schema changes are the ones every system accepts.

What Snowflake absorbs

Schema evolution lets a load add columns to the target table automatically. It needs three things:

ALTER TABLE analytics.orders SET ENABLE_SCHEMA_EVOLUTION = TRUE;

-- The loading role needs EVOLVE SCHEMA (or OWNERSHIP) on the table
GRANT EVOLVE SCHEMA ON TABLE analytics.orders TO ROLE loader;

and a supported loading method: COPY INTO (with MATCH_BY_COLUMN_NAME), Snowpipe, or Snowpipe Streaming. Plain INSERT can't evolve a table.

And it does only two things:

it adds new columns, and
it drops NOT NULL from columns that are missing in new data.

It never renames and never changes a type.

Type changes through ALTER TABLE ... ALTER COLUMN are stricter still:

Change	Allowed?
Make a `VARCHAR` longer	✅
Raise a number's precision	✅
Change a number's scale	❌
Make a `VARCHAR` shorter	❌
Turn a number into a `VARCHAR`	❌

So if Postgres moves price from numeric(10,2) to numeric(12,4), Snowflake can't follow in place. The scale changed. You add a new column, backfill it, and switch readers over, which is the pattern at the end of this post.

The lakehouse: more forgiving, not magic

Apache Iceberg tracks every column by a unique field ID, not by its name. Renames and drops are metadata changes, so no data files are rewritten. A new column gets a fresh ID, so it never reads old data by mistake, even if it reuses an old name. Type changes are limited to safe widening: int to long, float to double, or a decimal with more precision and the same scale.

Delta Lake can rename and drop columns only with column mapping enabled:

ALTER TABLE orders SET TBLPROPERTIES (
  'delta.columnMapping.mode' = 'name',
  'delta.minReaderVersion'   = '2',
  'delta.minWriterVersion'   = '5'
);

That's a protocol upgrade you can't undo, and after a rename or drop it can break readers of the table's change data feed.

Here's the catch for both formats: a CDC sink still sees a rename as a dropped field and a new one, because that's what the events say. Unless you rename the table column yourself, you get the same split as in Snowflake.

Expand, then contract

The safe pattern is expand/contract: make the schema bigger first, move everything over, and only then make it smaller. A rename becomes four steps:

-- 1. Expand: add the new column as nullable (or with a constant default)
ALTER TABLE orders ADD COLUMN total_cents bigint;

-- 2. Backfill in small batches, and have the application write both columns
UPDATE orders
SET    total_cents = amount_cents
WHERE  total_cents IS NULL
  AND  id BETWEEN 1 AND 50000;

-- 3. Move every reader (views, models, dashboards) to total_cents

-- 4. Contract, weeks later, once nothing reads the old column
ALTER TABLE orders DROP COLUMN amount_cents;

Because amount_cents was nullable, every step only adds or removes an optional field, which the registry accepts in any mode. (Dropping a NOT NULL column removes a required field, which passes BACKWARD but not FORWARD or FULL.) Two practical notes:

The backfill is CDC traffic. Every row it updates becomes a change event, which is why it runs in batches.
Snowflake won't drop the old column for you. Schema evolution never removes columns. Drop AMOUNT_CENTS in the warehouse yourself once readers have moved.

Two guardrails

Test schemas in CI. Schema Registry has a compatibility endpoint that tells you whether a schema would be accepted, without registering it:

# Wrap the new Avro schema in the request body the registry expects
python3 -c 'import json, sys; print(json.dumps({"schema": open(sys.argv[1]).read()}))' \
  orders-value.avsc > payload.json

curl -X POST \
  -H 'Content-Type: application/vnd.schemaregistry.v1+json' \
  --data @payload.json \
  'http://schema-registry:8081/compatibility/subjects/shop.public.orders-value/versions/latest?verbose=true'
# {"is_compatible":false,"messages":[...]}

Fail the build when is_compatible is false. Remember that a nullable rename passes this check, so it catches only the loud kind of break.

Watch the warehouse. Alert when a new column appears on a captured table, and when a column's null rate jumps:

SELECT DATE_TRUNC('hour', updated_at)              AS hour,
       COUNT(*)                                    AS rows_loaded,
       COUNT_IF(amount_cents IS NULL) / COUNT(*)   AS null_rate
FROM   analytics.orders
WHERE  updated_at >= DATEADD(day, -2, CURRENT_TIMESTAMP())
GROUP  BY 1
HAVING null_rate > 0.2
ORDER  BY 1;

The threshold is yours to pick. Either alert would have caught Friday's rename on Friday night.

Five common mistakes

Renaming a column with a single ALTER. Use expand/contract.
Changing a numeric column's scale in place. Snowflake can't follow.
Setting compatibility to NONE just to get a failed connector running again. You've switched off the only alarm.
Trusting function defaults. Debezium exports a default like CURRENT_TIMESTAMP(6) as a zero value (1970-01-01) in the schema.
Changing a schema during an incremental snapshot. The Postgres connector doesn't support it.

Recap

Postgres doesn't send DDL, so the next event carries the new schema.
The registry checks compatibility, BACKWARD by default. Optional fields are safe to add or remove; required fields are not.
Snowflake adds columns, but never renames or changes types.
In the lakehouse, your sink still sees a rename as a drop and an add.
So expand, then contract.

This week, find the last schema change on a captured table and check what it did in the warehouse.

The CDC in Production series

Part 1: Change Data Capture (CDC) Explained (article · video)
Part 2: Debezium Postgres to Kafka: Every Setting Explained (video)
Part 3: The Silent CDC Bug: Data Loss With No Errors (video)
Previous, Part 4: CDC Deletes, Tombstones and the Idempotent MERGE (video)
Part 5: CDC Schema Changes: What Breaks and How to Ship Them (this post · video)
Next, Part 6: Running CDC in Production: The WAL Slot Trap (video, out November 2)

CDC Deletes, Tombstones and the Idempotent MERGE

Pipeline Whiteboard — Fri, 30 Oct 2026 18:32:10 +0000

Order 1042 was deleted at 14:10. At 15:00 it's back in the warehouse, marked shipped. Nobody re-created it. A retried batch did.

This is Part 4 of CDC in Production. In Part 3 we caught silent data loss and repaired it by re-sending changes. Crashes re-send changes too. So this post covers the order CDC events really arrive in, how deletes and tombstones work, and a Snowflake MERGE that gets the right answer even when an event shows up twice, or late.

Prefer to watch? Here's the 7-minute video version with animated diagrams:

Video link: CDC Deletes, Tombstones and the Idempotent MERGE

The ordering promise, and its fine print

Start with what is actually guaranteed. Debezium uses the row's primary key as the Kafka message key. Kafka writes every message with the same key to the same partition (one ordered slice of a topic), and a partition is read in exactly the order it was written. So changes to one row arrive in order. Changes to different rows, on different partitions, have no order between them at all.

Two pieces of fine print:

Adding partitions breaks the mapping. The partition for a key is derived from the key and the partition count. Add partitions to a live topic and new events for a row can land on a different partition from its old ones, so a consumer can read them out of order.
Delivery is at least once. After a crash, Debezium can re-send events it already sent, because its stored position is only committed every so often. Your sink must expect duplicates.

So the sink rule is: expect duplicates, and never assume an order across keys.

Every change already has a version

To cope with duplicates and late events, each change needs a version number that only goes up. You already have one. Every Debezium event has a source block, and on Postgres it carries lsn, the log sequence number: the position of the change in the write-ahead log (WAL). On Oracle the equivalent is scn, the system change number.

"source": {
  "connector": "postgresql",
  "table": "orders",
  "txId": 88240,
  "lsn": 3456121520
}

Row locks mean only one transaction can change a given row at a time, so for a single row a later change always has a higher position. That's exactly the property we need. A timestamp doesn't have it: many changes can share the same millisecond.

Deletes and tombstones

A delete event has op set to d, the old row in before, and after set to null. With the default replica identity (see Part 2), before holds only the primary key:

{ "before": { "id": 1042 }, "after": null, "op": "d" }

Then, by default (tombstones.on.delete is true), Debezium sends a second message: a tombstone, with the same key and a null value. It exists for log compaction, the Kafka cleanup mode (cleanup.policy=compact) that keeps only the latest message per key. A tombstone tells compaction it may eventually drop every message for that key.

Three things trip people up here:

Tombstones expire. On a compacted topic, delete.retention.ms keeps them for one day by default (86400000), then they're removed. A consumer that is more than a day behind can miss the delete entirely.
The Debezium JDBC sink ignores deletes by default. delete.enabled is false. Turning it on also requires primary.key.mode=record_key.
Flattening can drop them. Most warehouse loaders want flat rows, not the before/after envelope, so they use the ExtractNewRecordState transform. Its delete.tombstone.handling.mode defaults to tombstone: the delete event is dropped and only the tombstone is kept. A loader that skips null values then never sees the delete.

Set the mode to rewrite and you get a flat row instead, with a __deleted field. Add the log position and commit time while you're there:

transforms=unwrap
transforms.unwrap.type=io.debezium.transforms.ExtractNewRecordState
transforms.unwrap.delete.tombstone.handling.mode=rewrite
transforms.unwrap.add.fields=op,lsn,source.ts_ms

A delete of order 1042 now arrives as an ordinary record:

{ "id": 1042, "status": null, "amount_cents": null,
  "__deleted": "true", "__op": "d", "__lsn": "3456121520", "__source_ts_ms": "1792591800000" }

The other columns are null because, with the default replica identity, the delete only carried the key. rewrite also removes the tombstones; if you still need them for compaction, there's a rewrite-with-tombstone mode.

Primary key changes are two rows

One more event shape catches people out. If an UPDATE changes the primary key (say id 1042 becomes 2042), Debezium doesn't send an update. It sends a delete and a tombstone for the old key, then a create for the new key. The delete carries a __debezium.newkey header and the create carries __debezium.oldkey, linking the two.

Those are different keys, so they can land on different partitions and nothing orders them. Your sink must treat them as two independent rows.

How the zombie row happened

Back to order 1042. The loader writes micro-batches (small groups of events loaded every few minutes) into Snowflake:

Batch 7 holds the update to shipped. It times out.
Batch 8 holds the delete. It succeeds, and the row is gone.
The loader retries batch 7. The MERGE finds no row with that id, so it inserts one.

The deleted order is back, and the counts even look plausible. Two design choices made it possible: the warehouse forgot the delete, and it applied events without checking whether they were newer than what it already had.

The fix: soft deletes plus "only newer wins"

The fix has two parts:

Never forget a delete. Keep it as a soft-deleted row: is_deleted = TRUE, plus the position it happened at.
Only apply an event if its position is higher than the one the row already holds.

Now the retried update from batch 7 (at lsn 500, say) arrives after the delete (at lsn 520). Is 500 greater than 520? No, so it's ignored.

The target table needs two extra columns, and a third if you want to purge by age later:

CREATE TABLE orders (
  id           NUMBER PRIMARY KEY,
  status       VARCHAR,
  amount_cents NUMBER,
  is_deleted   BOOLEAN NOT NULL DEFAULT FALSE,
  lsn          NUMBER  NOT NULL,
  changed_at   TIMESTAMP_NTZ
);

Each batch lands in a staging table, orders_changes, with the flattened fields mapped to columns: is_deleted from __deleted = 'true', lsn from __lsn cast to a number, and changed_at from TO_TIMESTAMP_NTZ(__source_ts_ms, 3) (the 3 means the value is in milliseconds).

The idempotent MERGE

Idempotent means applying the same input again leaves the result unchanged. Here's the MERGE that gets you there:

MERGE INTO orders t
USING (
  SELECT * FROM orders_changes
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY id ORDER BY lsn DESC) = 1
) s
ON t.id = s.id
WHEN MATCHED AND s.lsn > t.lsn THEN UPDATE SET
  status = s.status, amount_cents = s.amount_cents,
  is_deleted = s.is_deleted, lsn = s.lsn, changed_at = s.changed_at
WHEN NOT MATCHED THEN INSERT
  (id, status, amount_cents, is_deleted, lsn, changed_at)
  VALUES (s.id, s.status, s.amount_cents, s.is_deleted, s.lsn, s.changed_at);

Step by step:

Deduplicate the batch. QUALIFY filters on a window function, keeping only the latest event per id (highest lsn). This isn't optional in Snowflake: by default, several source rows matching one target row makes an updating MERGE fail with a nondeterministic-merge error (ERROR_ON_NONDETERMINISTIC_MERGE).
Join on the key.
When matched, only newer wins. The update runs only if s.lsn > t.lsn, and it sets the deleted flag too, so a delete becomes a soft delete and a later re-insert of the same key revives the row.
When not matched, insert, even a delete. That way the delete's position is remembered, and a late update for the same row loses.

If you'd rather keep the last known values on deleted rows instead of the nulls a delete brings, use status = IFF(s.is_deleted, t.status, s.status) and the same for other columns.

Why this is safe:

Same batch twice: every lsn is equal, not higher, so nothing changes the second time.
Batches out of order: older events lose to newer ones already applied.
Re-snapshots from Part 3: read events go through the same check, so they can't overwrite a row the warehouse already holds at a newer position.

You can still hard delete, as a separate job that only removes soft-deleted rows older than any replay you'd ever run:

DELETE FROM orders
WHERE  is_deleted
  AND  changed_at < DATEADD(day, -30, CURRENT_TIMESTAMP()::TIMESTAMP_NTZ);

Pick the window to be longer than your Kafka retention and your longest possible backfill.

Test it on purpose

Don't trust the MERGE until you've tried to break it. Take one batch, shuffle it, apply it twice, then apply an older batch on top. The final table must equal one clean, in-order pass. With both results in tables, the comparison is two set differences:

(SELECT * FROM orders_test  MINUS SELECT * FROM orders_clean)
UNION ALL
(SELECT * FROM orders_clean MINUS SELECT * FROM orders_test);

Zero rows means they match. Any rows means you have an ordering bug, and it's better you find it than production.

Five common mistakes

Ordering by a timestamp. Many changes share the same millisecond. Use the log position.
Hard deletes in the sink. The position is forgotten, and late events bring rows back.
Leaving the JDBC sink's delete.enabled at false. Deleted rows live forever downstream.
Adding partitions to a live change topic. Per-key order breaks for existing keys.
Capturing a table with no primary key. With the default replica identity, Debezium emits only creates for it, no updates or deletes. Give it a key, or change its replica identity, before you capture it.

Recap

Order is per key, and only while the partition count stays put. Delivery is at least once.
Deletes arrive as a delete event plus a tombstone, and tombstones expire.
Version every row by its log position and keep deletes as soft deletes.
Deduplicate each batch, then apply only newer events.

If you run one test this week, replay yesterday's batches into a copy of the table, shuffled and twice, and compare it with the real one.

The CDC in Production series

Part 1: Change Data Capture (CDC) Explained (article Â· video)
Part 2: Debezium Postgres to Kafka: Every Setting Explained (video)
Previous, Part 3: The Silent CDC Bug: Data Loss With No Errors (video)
Part 4: CDC Deletes, Tombstones and the Idempotent MERGE (this post Â· video)
Next, Part 5: CDC Schema Changes: What Breaks and How to Ship Them (video, out October 31)
Coming up: Part 6 (the WAL slot trap)

The Silent CDC Bug: Data Loss With No Errors

Pipeline Whiteboard — Tue, 27 Oct 2026 16:21:38 +0000

Every dashboard is green. The connector says RUNNING, capture lag is under a second, and the warehouse is missing 4,812 orders. Nobody gets paged, because nothing failed.

This is Part 3 of CDC in Production. In Part 2 we built a Debezium pipeline from Postgres to Kafka. This post covers four ways that pipeline can lose data without a single error, and the reconciliation check that catches all of them.

Prefer to watch? Here's the 8-minute video version with animated diagrams:

Video link: The Silent CDC Bug: Data Loss With No Errors

Why nothing alerts

The usual signals measure flow, not completeness. Task status tells you the connector is alive. Lag tells you how far behind the newest event is. Neither tells you whether every change made it. A pipeline that skipped a thousand rows an hour ago looks exactly like a healthy one now.

Failure 1: the slot moves on after a failover

A replication slot is Postgres's bookmark for a logical decoding reader: it remembers the last position the reader confirmed. Here's how it goes wrong.

The primary dies at 14:00 and a standby is promoted. On self-managed Postgres 16 or earlier, logical slots don't follow a promotion. So someone recreates the slot on the new primary and the connector reconnects. A new slot starts at the current position in the log, so everything written between the promotion and that moment is never decoded.

Why no error? The connector's stored offset is now behind the slot. Debezium's offset.mismatch.strategy defaults to no_validation, and on Postgres 15 and newer the server streams from the slot's confirmed position. The gap is skipped and the connector reports RUNNING throughout.

Defences:

Postgres 17+: failover slots. Set slot.failover to true in the connector so it creates a failover-enabled slot, and keep it in sync on the standby:

  # primary: postgresql.conf
  synchronized_standby_slots = 'standby1_slot'   # the standby's physical slot

  # standby: postgresql.conf
  sync_replication_slots = on
  hot_standby_feedback = on
  primary_slot_name = 'standby1_slot'
  # primary_conninfo must include a dbname for slot sync to work

Managed services. Debezium's docs note that some managed services, Amazon RDS among them, keep the slot through a failover because they replicate the storage. Check what yours does before you need it.
Fail loudly. "offset.mismatch.strategy": "trust_offset" makes the connector stop instead of silently skipping. It's a technology preview in current Debezium, so test it first.

Failure 2: the table that was never captured

Someone adds public.refunds to table.include.list and restarts the connector. But the publication was created by hand (as in Part 2), and nobody added refunds to it. Postgres only decodes tables in the publication, so no refund events ever arrive. The connector is healthy; the topic is just empty.

Fix the publication first:

ALTER PUBLICATION dbz_shop ADD TABLE public.refunds;
GRANT SELECT ON public.refunds TO debezium;

Then there's a second trap: adding a table only captures changes from that point on. Existing rows are never sent unless you ask. Debezium's docs suggest an incremental snapshot (a chunked copy that runs alongside streaming) after you add a table. You trigger one through a signal table, a small table Debezium watches for commands:

CREATE TABLE public.debezium_signal (
  id   varchar(42) PRIMARY KEY,
  type varchar(32) NOT NULL,
  data varchar(2048)
);
GRANT SELECT, INSERT ON public.debezium_signal TO debezium;
ALTER PUBLICATION dbz_shop ADD TABLE public.debezium_signal;

Point the connector at it with "signal.data.collection": "public.debezium_signal", then send the request:

INSERT INTO public.debezium_signal (id, type, data)
VALUES ('add-refunds', 'execute-snapshot',
        '{"data-collections": ["public.refunds"], "type": "incremental"}');

Until you do, the warehouse has every new refund and none of the old ones.

Failure 3: the TOAST placeholder

Postgres stores large values out of line, in what it calls TOAST storage. When an update doesn't change a TOASTed value, that value isn't included in the WAL message, unless the table uses REPLICA IDENTITY FULL. Debezium can't safely fill it in, so it sends a placeholder: __debezium_unavailable_value.

Now picture a sink that writes the after image straight into the table. Order 1042 has long delivery notes. Someone changes only the status. The event arrives as:

{ "id": 1042, "status": "shipped", "notes": "__debezium_unavailable_value" }

and the sink overwrites the real notes with the placeholder. Row counts still match; the data is quietly wrong.

Two fixes. Set REPLICA IDENTITY FULL on tables with large columns you need downstream. Or make the sink keep the old value when it sees the placeholder, for example in a Snowflake MERGE:

WHEN MATCHED THEN UPDATE SET
  t.status = s.status,
  t.notes  = IFF(s.notes = '__debezium_unavailable_value', t.notes, s.notes)

Failure 4: dropped downstream

This one happens after Kafka. In Kafka Connect, errors.tolerance defaults to none, so any bad record stops the task. Teams flip it to all to stop the pages. Now records that fail conversion or transformation are skipped. Without a dead letter queue (DLQ, a topic that collects failed records), the only trace is a log line, and only if error logging is on. If you tolerate errors in a sink connector, at least keep them:

errors.tolerance=all
errors.log.enable=true
errors.log.include.messages=true
errors.deadletterqueue.topic.name=dlq.shop.orders
errors.deadletterqueue.context.headers.enable=true

(The DLQ settings apply to sink connectors.) Then alert when the DLQ topic receives anything.

The other downstream gap is retention. A topic keeps messages for seven days by default (retention.ms=604800000). If the sink is down longer than that, the oldest events are deleted before it reads them. When it comes back, its committed offset no longer exists, so a Connect sink resets to the earliest message that's left and carries on.

Reconciliation: compare the data itself

All four failures share one property: the only reliable way to catch them is to compare the data. Reconciliation means running the same query on the source and on the warehouse and comparing the answers.

Not one grand total, though. Split the table into buckets, say one per hour of updated_at, so a mismatch tells you where to look. For each bucket compute three numbers:

Row count catches missing rows.
Sum of a numeric column like amount_cents catches wrong amounts.
Checksum catches everything else: hash each row's key and values, turn the hash into a number, and add them up. Addition doesn't care about row order, so both systems can agree.

The Postgres side:

SET TIME ZONE 'UTC';

SELECT date_trunc('hour', updated_at)  AS bucket,
       count(*)                        AS row_count,
       sum(amount_cents)               AS amount_sum,
       sum(('x' || left(md5(concat_ws('|',
             id, status, amount_cents, coalesce(notes, '<null>'))), 15)
           )::bit(60)::bigint)         AS checksum
FROM   public.orders
WHERE  updated_at < '2026-10-21 15:00:00+00'   -- the watermark
GROUP  BY 1
ORDER  BY 1;

The Snowflake side builds the identical string and hash. TO_NUMBER with an X format parses hex:

ALTER SESSION SET TIMEZONE = 'UTC';

SELECT DATE_TRUNC('hour', updated_at)  AS bucket,
       COUNT(*)                        AS row_count,
       SUM(amount_cents)               AS amount_sum,
       SUM(TO_NUMBER(LEFT(MD5(CONCAT_WS('|',
             id, status, amount_cents, COALESCE(notes, '<null>'))), 15),
           'XXXXXXXXXXXXXXX'))         AS checksum
FROM   analytics.orders
WHERE  updated_at < '2026-10-21 15:00:00'
GROUP  BY 1
ORDER  BY 1;

(If the warehouse keeps soft-deleted rows, as in Part 4, add AND NOT is_deleted so they don't count as extra rows.)

Look at the WHERE clause. That's the watermark: only rows last updated before a cutoff (here, at least 30 minutes ago, rounded down to the hour) are compared, because anything newer may still be in flight. Compute the cutoff once and pass the same value to both queries.

Load both result sets into one place and compare bucket by bucket:

SELECT COALESCE(p.bucket, w.bucket) AS bucket,
       p.row_count AS src_rows, w.row_count AS wh_rows,
       p.checksum  AS src_sum,  w.checksum  AS wh_sum
FROM   recon.pg_buckets p
FULL OUTER JOIN recon.wh_buckets w ON p.bucket = w.bucket
WHERE  p.row_count  IS DISTINCT FROM w.row_count
    OR p.amount_sum IS DISTINCT FROM w.amount_sum
    OR p.checksum   IS DISTINCT FROM w.checksum
ORDER  BY 1;

In the video's example, the 14:00 bucket is short by 4,812 rows: that's the failover window. A later bucket has matching counts but a different checksum, so the values are wrong, not missing (hello, TOAST placeholder). A row changed during the check can cause a false alarm, so re-check a mismatched bucket before alerting. Real loss stays; in-flight changes go away.

Repair: re-snapshot only the bad bucket

Repair is targeted. The signal table accepts an execute-snapshot request with additional-conditions, so you re-snapshot only the broken bucket, not the whole table:

INSERT INTO public.debezium_signal (id, type, data) VALUES (
  'repair-orders-1400', 'execute-snapshot',
  '{"data-collections": ["public.orders"],
    "type": "incremental",
    "additional-conditions": [{
      "data-collection": "public.orders",
      "filter": "updated_at >= ''2026-10-21 14:00+00'' AND updated_at < ''2026-10-21 15:00+00''"
    }]}');

The rows arrive as read events (op = r) while streaming carries on. Your sink has to apply them safely on top of what's already there, which is exactly what Part 4 is about.

Five reconciliation mistakes

Treating RUNNING and low lag as proof nothing is missing.
Reconciling one total count. A lost insert and a lost delete cancel out.
No watermark. Comparing live data produces false alarms and teaches everyone to ignore the alert.
Hashing differently on each side. Timestamps, decimals and nulls must become exactly the same text in both systems before hashing. (Note that Snowflake's CONCAT_WS returns NULL if any argument is NULL, while Postgres skips nulls. Hence the COALESCE.)
Assuming a failover keeps your slot. Check your Postgres version and provider before the failover, not after.

Recap

Silent loss comes from slots that move on, tables never captured, placeholders that overwrite real values, and records dropped downstream.
Status and lag can't see any of it.
Bucketed counts, sums and checksums below a watermark can.
A targeted incremental snapshot repairs what they find.

If you add one check this week, make it an hourly row count on your most important table, source versus warehouse.

The CDC in Production series

Part 1: Change Data Capture (CDC) Explained (article · video)
Previous, Part 2: Debezium Postgres to Kafka: Every Setting Explained (video)
Part 3: The Silent CDC Bug (this post · video)
Next, Part 4: CDC Deletes, Tombstones and the Idempotent MERGE (video, out October 29)
Coming up: Part 5 (schema changes), Part 6 (the WAL slot trap)

Debezium Postgres to Kafka: Every Setting Explained

Pipeline Whiteboard — Tue, 27 Oct 2026 16:18:51 +0000

About fifteen lines of JSON. That's all it takes to stream every insert, update and delete from Postgres into Kafka with Debezium. And two of the defaults hiding in those lines will hurt you in production if you leave them alone.

This is Part 2 of CDC in Production. Part 1 explained what change data capture (CDC) is. This post opens up the most common open-source setup, Debezium in Kafka Connect reading Postgres: what to prepare in the database, what every setting does, what happens on first start, and what lands in each Kafka message.

Prefer to watch? Here's the 9-minute video version with animated diagrams:

Video link: Debezium Postgres to Kafka: Every Setting Explained

The moving parts

Read the pipeline left to right:

Postgres (WAL) -> replication slot + publication -> Debezium in Kafka Connect -> Kafka topics -> your sinks

Write-ahead log (WAL): Postgres writes every change here before it counts as committed. It's the database's own ledger.
Logical replication slot: a named bookmark in the WAL. It's the database's promise to keep the log from that point on until a reader confirms it has processed it.
Publication: the list of tables whose changes Postgres decodes for logical replication.
Kafka Connect: a framework for running connectors that move data in and out of Kafka. Debezium is a source connector inside a Connect worker. It reads the decoded changes, turns each one into an event and writes it to a Kafka topic.

Kafka Connect also stores the connector's position (the last log sequence number, or LSN, it handled) in an internal offsets topic. Offset plus slot is how Debezium resumes after a restart. Part 3 is about what happens when they disagree.

Step 1: prepare Postgres

Postgres needs three things before any connector config.

-- postgresql.conf (restart required)
wal_level = logical
-- On Amazon RDS, set rds.logical_replication = 1 in the
-- parameter group instead (static parameter, reboot needed).

-- A dedicated CDC user with only what it needs
CREATE ROLE debezium WITH LOGIN REPLICATION;
GRANT SELECT ON public.orders, public.customers TO debezium;
-- Set its password from psql with: \password debezium
-- On RDS, grant the rds_replication role instead of the REPLICATION attribute:
-- GRANT rds_replication TO debezium;

-- Run as the table owner: capture only the tables you need
CREATE PUBLICATION dbz_shop
  FOR TABLE public.orders, public.customers;

wal_level = logical makes the WAL carry enough row-level detail to decode. The user needs REPLICATION plus SELECT (for the initial snapshot). And the publication lists only the tables you want.

Why create the publication yourself? With pgoutput, Debezium's default publication.autocreate.mode is all_tables. If the publication doesn't exist, it tries to run CREATE PUBLICATION ... FOR ALL TABLES, which needs superuser rights that managed databases rarely hand out. Set publication.autocreate.mode to filtered so Debezium builds it from your table list, or to disabled and own the publication like any other database object. This post uses disabled.

Replica identity: what goes into `before`

Replica identity is the table setting that decides how much of the old row Postgres writes to the WAL for updates and deletes. With the default, the before image in a Debezium update or delete event carries only primary key columns (and for an update, only when the key itself changed). If downstream consumers need the full old row, switch that table to FULL:

ALTER TABLE public.orders REPLICA IDENTITY FULL;

The trade-off: every update and delete on that table now writes the whole old row to the WAL, so the log grows faster.

Step 2: the connector config, line by line

You register a connector by sending JSON to the Kafka Connect REST API. Here's the whole thing:

{
  "name": "shop-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "tasks.max": "1",
    "database.hostname": "shop-db.internal",
    "database.port": "5432",
    "database.user": "debezium",
    "database.password": "${file:/secrets/pg.properties:password}",
    "database.dbname": "shop",
    "topic.prefix": "shop",
    "plugin.name": "pgoutput",
    "slot.name": "debezium_shop",
    "publication.name": "dbz_shop",
    "publication.autocreate.mode": "disabled",
    "table.include.list": "public.orders,public.customers",
    "snapshot.mode": "initial",
    "heartbeat.interval.ms": "10000"
  }
}

curl -X POST http://connect:8083/connectors \
  -H 'Content-Type: application/json' \
  --data @shop-cdc.json

curl http://connect:8083/connectors/shop-cdc/status
# {"name":"shop-cdc","connector":{"state":"RUNNING",...},"tasks":[{"id":0,"state":"RUNNING",...}],...}

To change it later, PUT just the inner config object to /connectors/shop-cdc/config.

What each line does:

connector.class names the Debezium Postgres connector. tasks.max is 1 because this connector always runs a single task (one slot is read by one reader).
database.* says where to connect. Don't put the password in plain text. That file: reference in the password line is a Kafka Connect config provider: the worker reads the value from a file at runtime. Enable it in the worker config:

  config.providers=file
  config.providers.file.class=org.apache.kafka.common.config.provider.FileConfigProvider

topic.prefix becomes the first part of every topic name. The Debezium docs say not to change it later, so pick it carefully.
plugin.name is the first default that bites. The default is decoderbufs, a logical decoding plugin you'd have to install on the database server yourself. pgoutput is built into Postgres 10 and later, so set it explicitly.
slot.name and publication.name default to debezium and dbz_publication. Give every connector its own. Two connectors on one slot means changes go missing without an error.
table.include.list is the allow-list of tables to capture. snapshot.mode is covered below.
heartbeat.interval.ms is the second default that bites. It's 0 (off) by default. Turn it on. Without heartbeats, a connector whose tables rarely change can stop confirming its position, and the slot holds WAL until the disk fills. Part 6 covers that trap in depth.

Step 3: snapshot, then stream, with no gap

So what happens on the first start?

Debezium creates the replication slot if it doesn't exist.
It opens a transaction and records the current log position. Call it P.
It scans every included table, emitting one event per row with op = r (read).
It records in its offsets that the snapshot is complete.
It streams changes from P.

That last step is the whole trick. Changes made during the scan wait in the slot after P and are streamed once the snapshot is done. Nothing is skipped. If an interviewer asks how Debezium avoids a gap between snapshot and stream, that's the one-sentence answer.

One caveat: if the connector dies halfway through the initial snapshot, the snapshot starts again from the beginning.

Other snapshot.mode values worth knowing:

Mode	What it does
`initial`	Snapshot on first start (no offsets), then stream. The default.
`always`	Snapshot on every start, then stream. Can help after a failover.
`no_data`	Skip the snapshot; stream only new changes.
`initial_only`	Snapshot, then stop. No streaming.
`when_needed`	Snapshot only if there are no offsets or the stored position is no longer available.

For tables too big to scan in one go, there are incremental snapshots: you send a signal (Part 3 shows one), and Debezium copies the table in chunks while streaming keeps running.

Topics, keys and partitions

Each table gets its own topic, named <topic.prefix>.<schema>.<table>. So public.orders becomes shop.public.orders.

The message key is the row's primary key, for example {"id": 1042}. That matters because Kafka sends every message with the same key to the same partition, and a partition is read in the order it was written. So all changes to order 1042 stay in order. Across different keys there's no ordering promise at all. Part 4 builds on exactly that.

You'll also see a heartbeat topic (named __debezium-heartbeat.shop with this prefix) and Kafka Connect's internal topics for configs, offsets and status. Decide partitions, replication factor and retention for your change topics up front, either by creating the topics yourself or with Connect's topic creation settings in the connector config:

"topic.creation.default.partitions": "6",
"topic.creation.default.replication.factor": "3",
"topic.creation.default.retention.ms": "604800000"

Don't let change topics inherit whatever the broker defaults happen to be.

Inside a change event

Say order 1042 moves from paid to shipped. The key is just the primary key. The value is an envelope:

{
  "before": null,
  "after":  { "id": 1042, "status": "shipped", "amount_cents": 12990, "customer_id": 77 },
  "source": {
    "connector": "postgresql",
    "name": "shop",
    "ts_ms": 1792591391000,
    "snapshot": "false",
    "db": "shop",
    "schema": "public",
    "table": "orders",
    "txId": 88231,
    "lsn": 3456120880
  },
  "op": "u",
  "ts_ms": 1792591391412
}

before is the old row: null here because the table uses the default replica identity. With REPLICA IDENTITY FULL it would hold "status": "paid" and the rest of the old row.
after is the new row.
op is the operation: c create, u update, d delete, r snapshot read.
source is metadata: connector, database, schema, table, transaction ID and LSN.
Two timestamps: source.ts_ms is when the change was committed in the database; the top-level ts_ms is when Debezium processed it. Subtract one from the other and you have capture lag for every single event (412 ms in this example).

A delete has after set to null, and by default it's followed by a tombstone: a message with the same key and a null value, so Kafka log compaction can eventually drop that key. And with the plain JSON converter and schemas.enable=true, every message carries its full schema alongside the payload. Avro or Protobuf with a schema registry sends a small schema ID instead.

Five common Debezium mistakes

Leaving plugin.name at the default. decoderbufs isn't installed on most servers, so the connector won't start.
Two connectors sharing one slot. That's silent data loss. One connector, one slot, one publication.
slot.drop.on.stop=true outside a dev environment. Stop the connector and the slot, with its position, is gone.
Expecting before to hold the old row without REPLICA IDENTITY FULL.
No heartbeat and no alert on how much WAL the slot is holding. That one can take down the database itself.

Recap

Postgres needs logical WAL, a replication user and a publication you own.
The connector needs pgoutput, its own slot, a fixed topic.prefix and heartbeats.
On first start it snapshots as of a recorded log position, then streams from that exact position.
Each table becomes a topic keyed by primary key, so ordering is per row.
Each event carries before, after, source, op and ts_ms.

The CDC in Production series

Part 1: Change Data Capture (CDC) Explained (article · video)
Part 2: Debezium Postgres to Kafka (this post · video)
Next, Part 3: The Silent CDC Bug: Data Loss With No Errors (video)
Coming up: Part 4 (deletes, tombstones and the idempotent MERGE), Part 5 (schema changes), Part 6 (the WAL slot trap)

Change Data Capture (CDC) Explained: From the Transaction Log to a Snowflake MERGE

Pipeline Whiteboard — Fri, 23 Oct 2026 01:50:08 +0000

Your sales dashboard says there are 500 units in stock. Run a query against production and you get 12. Nobody broke anything. The nightly batch job ran nine hours ago, and in those nine hours the product sold out.

Change Data Capture (CDC) is the standard way to close that gap without hammering your production database. This post walks through how it works, what the events look like, a real Postgres to Snowflake pipeline, and the mistakes that tend to show up only once the pipeline is live.

Prefer to watch? Here's the 6-minute video version with animated diagrams:

Video link: Change Data Capture (CDC) Explained in 6 Minutes

What CDC actually means

CDC means you treat every row-level change in a source database as an event. When a row is inserted, updated or deleted, you capture that change in the order it was committed and send it to whatever needs it downstream: a warehouse like Snowflake, a search index, a cache, or another service.

The point is to stop copying the whole table every run. Batch reloads move the entire table to find a handful of differences. CDC moves only the rows that changed.

The database already keeps a ledger

Think of a bank vault. One way to know the balance is to walk in every night and count every note. That's slow, it gets in everyone's way, and the number is out of date by morning. The other way is to read the ledger: every deposit and every withdrawal, in order. Replay the ledger and you always know the balance.

Databases already keep that ledger. Before a change is considered durable, the engine writes it to a transaction log, which it uses for crash recovery, replication, or both:

Engine	Its "ledger"
PostgreSQL	Write-ahead log (WAL)
MySQL	Binary log (binlog)
Oracle	Redo log
SQL Server	Transaction log

Log-based CDC reads this log, much like a read replica does, and turns it into a stream of change events.

Three ways to capture changes

1. Query-based (polling). Every few minutes you run something like:

SELECT * FROM orders WHERE updated_at > :last_run;

It's easy to build, but it has three blind spots. It can't see hard deletes, because a deleted row no longer matches anything. It only sees the latest state at poll time, so intermediate updates between polls are lost. And it relies on every writer setting updated_at correctly, including the one-off script someone ran last quarter.

2. Trigger-based. Triggers on the source table copy each change into a shadow table such as orders_changes. That catches deletes, but every write in production now does extra work inside the same transaction, and you own a set of triggers that has to keep up with schema changes.

3. Log-based. A connector reads the transaction log directly. It sees inserts, updates and deletes, in commit order, with very little extra load on the source. That's why it's the default today, and why tools such as Debezium, AWS Database Migration Service (DMS) and Google Datastream work this way.

Anatomy of a change event

Here is a simplified Debezium-style event for the stock example, showing an update to one inventory row:

{
  "op": "u",
  "before": { "product_id": 42, "qty": 500 },
  "after":  { "product_id": 42, "qty": 12 },
  "source": {
    "connector": "postgresql",
    "db": "shop",
    "schema": "public",
    "table": "inventory",
    "lsn": 24023128,
    "txId": 7781,
    "ts_ms": 1761123456789
  },
  "ts_ms": 1761123456912
}

op: the operation. c = create (insert), u = update, d = delete, r = read, meaning a row emitted during the initial snapshot.
before / after: the row state on each side of the change. For a delete, after is null; for an insert, before is null. One Postgres caveat: by default before only contains the full old row if the table uses REPLICA IDENTITY FULL. Otherwise you get the key columns or nothing at all.
source: where the change came from. lsn is the position in the log and source.ts_ms is when the change was committed in the database (the top-level ts_ms is when the connector processed it). This metadata lets you order events correctly and resume from the right spot after a restart.

A real pipeline: Postgres on RDS to Snowflake

Here's a setup that comes up a lot. An online shop runs PostgreSQL on Amazon RDS, and the analytics team wants orders in Snowflake within minutes instead of the next morning.

PostgreSQL (RDS) -> AWS DMS -> S3 (Parquet) -> Snowpipe -> Snowflake staging -> MERGE -> orders

Step 1: enable logical replication. In the RDS parameter group, set rds.logical_replication = 1. That sets wal_level = logical, so the WAL contains row-level changes a connector can decode. It's a static parameter, so plan for a reboot.

Step 2: create a DMS task with migration type "full load and ongoing replication" (full-load-and-cdc in the API). DMS copies the existing rows first, then keeps streaming changes from the WAL.

Step 3: write changes to S3 as Parquet. Each CDC row has an Op column: I for insert, U for update, D for delete. Two S3 endpoint settings are worth turning on: TimestampColumnName, which adds a timestamp column to every row (the commit time for CDC rows, the load time for full-load rows), and IncludeOpForFullLoad, so full-load rows also carry Op = 'I' instead of having no Op value.

Step 4: load and merge in Snowflake. Snowpipe (or a scheduled COPY INTO) loads the files into a staging table. A MERGE then applies them to the final table:

MERGE INTO analytics.orders AS t
USING (
    SELECT order_id, status, amount, op, commit_ts
    FROM raw.orders_stage
    -- keep only the latest change per key in this batch
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY order_id
        ORDER BY commit_ts DESC
    ) = 1
) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'D' AND s.commit_ts >= t.updated_at THEN
    DELETE
WHEN MATCHED AND s.commit_ts >= t.updated_at THEN
    UPDATE SET t.status     = s.status,
               t.amount     = s.amount,
               t.updated_at = s.commit_ts
WHEN NOT MATCHED AND s.op <> 'D' THEN
    INSERT (order_id, status, amount, updated_at)
    VALUES (s.order_id, s.status, s.amount, s.commit_ts);

A few details matter here:

The QUALIFY ROW_NUMBER() step collapses many changes to the same order into the latest one. Without it, MERGE would see several source rows matching one target row, which Snowflake treats as nondeterministic and rejects by default.
Deletes are applied as deletes, not stored as new rows.
The commit_ts >= t.updated_at guards mean an older event replayed after a restart can't overwrite newer data, which makes the load safe to re-run.
If two changes to the same key can share a commit timestamp (for example, within one transaction), add a tie-breaker such as a log position or sequence column to the ORDER BY.
Track which staged rows have already been merged. A Snowflake stream on the staging table works well for this.

The result: Snowflake runs minutes behind production instead of hours, and the source database barely notices.

Common CDC mistakes

These are the ones that tend to show up in production rather than in the demo.

Skipping the initial snapshot. CDC only captures changes from the moment it starts. Without a consistent full load taken at a known log position, you're missing all history before that point, and any row that never changes again never shows up. Use a tool mode that does snapshot + stream from the same position (DMS full load and CDC, Debezium's initial snapshot).
Treating events as append-only. If you insert every event as a new row, updates turn into duplicates and deleted rows never go away. Apply changes with a MERGE, or model deletes explicitly with an is_deleted flag if you need history.
Assuming exactly-once delivery. Most CDC pipelines are at-least-once. After a connector restart you can see the same event twice. Make loads idempotent and deduplicate on primary key plus log position.
Forgetting the log itself. In PostgreSQL, a replication slot that nobody is consuming keeps holding on to WAL, and the disk keeps filling until the database is in trouble. In MySQL, if binlog retention is shorter than your connector's downtime, the connector loses its place and you need a fresh snapshot. Monitor replication lag and slot size:

   -- PostgreSQL: how much WAL each slot is holding back
   SELECT slot_name,
          active,
          pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
   FROM pg_replication_slots;

On PostgreSQL 13+, max_slot_wal_keep_size puts an upper bound on retained WAL. On RDS for MySQL, set binlog retention with CALL mysql.rds_set_configuration('binlog retention hours', 24);.

Bonus: ignoring schema changes. A column renamed upstream can quietly break the staging table, the MERGE, and every dashboard after that. Decide in advance how schema changes get from the source to the warehouse, and alert on them.

When not to use CDC

CDC has more moving parts than a batch job: connectors, log and slot monitoring, and streaming infrastructure that costs money. If a small table changes once a day and a nightly load meets the business need, batch is simpler and cheaper. Pick the freshness the business actually needs, not the most impressive architecture.

Recap

CDC captures inserts, updates and deletes as ordered events.
Log-based CDC reads the database's own transaction log, so it's low impact and it sees deletes.
Start with a consistent snapshot, apply changes with a MERGE, make every load idempotent, and keep an eye on your logs and slots.

DEV Community: Pipeline Whiteboard

Running CDC in Production: The WAL Slot Trap

The WAL slot trap

The quiet slot

Heartbeats

Safety valves

Three kinds of lag

Alerts, with an owner

The runbook

Plan the routine events

What it costs

Five common mistakes

Recap

The CDC in Production series

CDC Schema Changes: What Breaks and How to Ship Them

How a schema change travels

Registry compatibility modes

The rename, two ways

What Snowflake absorbs

The lakehouse: more forgiving, not magic

Expand, then contract

Two guardrails

Five common mistakes

Recap

The CDC in Production series

CDC Deletes, Tombstones and the Idempotent MERGE

The ordering promise, and its fine print

Every change already has a version

Deletes and tombstones

Primary key changes are two rows

How the zombie row happened

The fix: soft deletes plus "only newer wins"

The idempotent MERGE

Test it on purpose

Five common mistakes

Recap

The CDC in Production series

The Silent CDC Bug: Data Loss With No Errors

Why nothing alerts

Failure 1: the slot moves on after a failover

Failure 2: the table that was never captured

Failure 3: the TOAST placeholder

Failure 4: dropped downstream

Reconciliation: compare the data itself

Repair: re-snapshot only the bad bucket

Five reconciliation mistakes

Recap

The CDC in Production series

Debezium Postgres to Kafka: Every Setting Explained

The moving parts

Step 1: prepare Postgres

Replica identity: what goes into before

Step 2: the connector config, line by line

Step 3: snapshot, then stream, with no gap

Topics, keys and partitions

Inside a change event

Five common Debezium mistakes

Recap

The CDC in Production series

Change Data Capture (CDC) Explained: From the Transaction Log to a Snowflake MERGE

What CDC actually means

The database already keeps a ledger

Three ways to capture changes

Anatomy of a change event

A real pipeline: Postgres on RDS to Snowflake

Common CDC mistakes

When not to use CDC

Recap

Replica identity: what goes into `before`