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

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.

1. 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).
2. 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.
3. 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.
4. 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);.

1. 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.

---

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. Next up: putting Kafka in the middle of this pipeline, and why so many teams do it.