CDC Patterns cheat sheet
Log-based versus query-based CDC, Debezium, warehouse-native change tracking, and applying changes idempotently.
Capture methods
Log-based: read the database WAL or binlog- Complete and low-impact — captures deletes and every intermediate update. The correct default when you can get log access.
Query-based: poll WHERE updated_at > last_seen- Simple and needs no privileges, but misses deletes and any update that does not touch the timestamp. Know the gaps.
Trigger-based: write changes to an audit table- Works when logs are unavailable, at the cost of write latency on the source. A last resort.
Snapshot plus incremental- Every CDC pipeline starts with a consistent full snapshot, then switches to the log at the snapshot's position.
Debezium
{ "connector.class": "io.debezium.connector.postgresql.PostgresConnector", "plugin.name": "pgoutput", "slot.name": "dbz_orders", "publication.autocreate.mode": "filtered", "table.include.list": "public.orders,public.customers" }- Postgres source config. Always scope table.include.list; capturing an entire database is how disks fill up.
Replication slot lag is the metric that matters- An inactive slot pins WAL forever and will take the source database down. Alert on slot lag, not just connector status.
Envelope: before, after, op, ts_ms, source- op is c, u, d, or r (read/snapshot). Your apply logic branches on op, so never discard it.
ExtractNewRecordState SMT- Flattens the envelope for simple sinks. Keep __op and __deleted or you lose the ability to apply deletes.
incremental.snapshot.enabled with signals- Re-snapshot one table without stopping the connector or blocking the source with a lock.
Warehouse-native change tracking
create stream s_orders on table analytics.bronze.orders- Snowflake streams expose inserts, updates, and deletes since the last consuming DML. Offset advances only on consumption.
METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID- An update appears as a delete plus insert pair with ISUPDATE true. Handle the pair, not the rows individually.
Databricks: table_changes('t', start_version)- Change Data Feed on Delta. Enable delta.enableChangeDataFeed at creation; retrofitting only captures future changes.
BigQuery: CHANGES / APPENDS table-valued functions- Change history over a time range, bounded by the table's change history window.
Applying changes
merge into analytics.silver.orders t using ( select * from staging_changes qualify row_number() over ( partition by order_id order by ts_ms desc ) = 1 ) s on t.order_id = s.order_id when matched and s.op = 'd' then delete when matched then update set ... when not matched and s.op <> 'd' then insert ...- Dedup to the latest change per key before merging. A batch with two changes for one key breaks MERGE otherwise.
Guard with ts_ms > t.updated_at- Out-of-order delivery is normal. Without the guard, a redelivered old change overwrites current data.
Soft delete instead of physical delete- is_deleted plus deleted_at preserves auditability and lets downstream models decide what to filter.
Append changes to bronze, apply in silver- Keep the raw change log immutable so a bug in the apply logic is fixable by replaying, not re-ingesting.
Schema change
Additive changes should not break the pipeline- New nullable columns must flow through without a deploy. Land the payload as VARIANT in bronze to guarantee this.
Column drops and type changes need a human- Fail loudly on incompatible change rather than silently coercing. A null-filled column is worse than a failed load.
Schema Registry compatibility mode BACKWARD- Consumers built on the old schema keep working. The right default for CDC topics with many readers.
Pitfalls
Query-based CDC and silent deletes- Rows deleted at the source live forever in the warehouse. Reconcile counts periodically or you will not notice for months.
Assuming ordering across tables- Per-table ordering is guaranteed; cross-table transactional consistency is not. Join CDC streams with care.
No dedup before MERGE- Nondeterministic merge errors, or worse, silently applying the wrong version. Dedup is not optional.
Unmonitored replication slots- The single most common CDC production incident. A stalled consumer eventually stops the source database.
From DataLane — tutorials at/blog, practice SQL live in theplayground.