Change Data Capture (CDC) Explained With SQL

Why CDC exists

A full table extraction re-reads every row on every run, regardless of how much actually changed. On a table with a few hundred rows that's a non-issue; on a table with hundreds of millions of rows updated by a handful of transactions per minute, it's an enormous, mostly wasted amount of I/O repeated on every pipeline run. CDC exists to extract only what actually changed.

Query-based CDC (timestamp polling)

-- Pull only rows updated since the last successful run
SELECT *
FROM orders
WHERE updated_at > '2026-08-09 23:00:00';  -- last watermark

-- The new watermark for next run is simply the max updated_at just pulled
SELECT MAX(updated_at) FROM orders;

Simple to implement, requiring only a reliably-maintained updated_at column on the source table. Its major weakness is deletes — a deleted row vanishes from the table entirely, leaving nothing for this query to find and report as removed.

Trigger-based CDC

CREATE TABLE orders_changelog (
  change_id    BIGINT IDENTITY PRIMARY KEY,
  order_id     INT,
  change_type  VARCHAR(10),  -- INSERT, UPDATE, DELETE
  changed_at   DATETIME DEFAULT GETDATE(),
  new_amount   DECIMAL(10,2)
);

CREATE TRIGGER trg_orders_change
ON orders
AFTER INSERT, UPDATE, DELETE
AS BEGIN
  INSERT INTO orders_changelog (order_id, change_type, new_amount)
  SELECT i.order_id, 'UPSERT', i.amount FROM inserted i;
  INSERT INTO orders_changelog (order_id, change_type)
  SELECT d.order_id, 'DELETE' FROM deleted d
  WHERE NOT EXISTS (SELECT 1 FROM inserted i WHERE i.order_id = d.order_id);
END;

Every change, including deletes, gets explicitly logged the moment it happens. The tradeoff is that every write to orders now also writes to orders_changelog, adding latency to the source system's own transactions.

Log-based CDC

Rather than adding triggers or polling, log-based CDC reads the database's own transaction log — the internal record every relational database already keeps for crash recovery and replication:

-- SQL Server's native CDC feature, once enabled on the database and table:
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo', @source_name = N'orders', @role_name = NULL;

-- Reading captured changes since a given log sequence number (LSN)
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_orders(@from_lsn, @to_lsn, N'all');

The source table itself is never touched by triggers, and every change type — including deletes — is captured with minimal overhead, since the database was writing to this log anyway. PostgreSQL's logical replication and MySQL's binlog serve the same role for those engines, typically consumed through a dedicated CDC tool.

Applying captured changes with MERGE

MERGE INTO target_orders AS t
USING captured_changes AS c
ON t.order_id = c.order_id
WHEN MATCHED AND c.change_type = 'DELETE' THEN DELETE
WHEN MATCHED THEN UPDATE SET t.amount = c.new_amount, t.updated_at = c.changed_at
WHEN NOT MATCHED AND c.change_type != 'DELETE' THEN INSERT (order_id, amount, updated_at)
  VALUES (c.order_id, c.new_amount, c.changed_at);

Regardless of which CDC method captured the changes, applying them downstream follows the same shape — a single MERGE statement that inserts new rows, updates matched ones, and deletes rows flagged as removed, run once per batch of captured changes.

Comparison

Common mistakes

• Relying on an updated_at column an application doesn't reliably maintain on every write path, silently missing changes made through a different code path.
• Assuming query-based CDC handles deletes without adding a soft-delete flag or a separate reconciliation step.
• Not deduplicating multiple changes to the same row within one captured batch before applying the MERGE, risking applying an out-of-order update.
• Enabling trigger-based CDC on a high-write table without measuring the added write latency first.