Why change
| Old approach | Problem |
|---|---|
| Triggers writing to audit tables | Extra write load and locking on the OLTP database; fragile with schema changes |
WHERE last_modified > :watermark polling | Misses deletes; misses rows whose timestamp isn't updated; heavy scans |
| Full nightly extracts | Slow, expensive, stale |
Log-based CDC reads the database's transaction log (binlog, WAL, SQL Server CDC tables): captures inserts, updates and deletes, with low overhead.
1. Prepare the source
MySQL
-- my.cnf: binlog_format=ROW, binlog_row_image=FULL, gtid_mode=ON, enforce_gtid_consistency=ON
CREATE USER 'debezium'@'%' IDENTIFIED BY '...';
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'debezium'@'%';
PostgreSQL
-- postgresql.conf: wal_level = logical
CREATE ROLE debezium WITH LOGIN REPLICATION PASSWORD '...';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO debezium;
CREATE PUBLICATION dbz_publication FOR TABLE public.orders, public.customers;
SQL Server
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Orders', @role_name = NULL;
Set source log retention longer than any plausible connector outage.
2. Deploy the connector
{
"name": "orders-cdc",
"config": {
"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
"database.hostname": "pg-prod", "database.port": "5432",
"database.user": "debezium", "database.password": "${file:/secrets/pg.properties:password}",
"database.dbname": "shop", "topic.prefix": "shop",
"plugin.name": "pgoutput", "publication.name": "dbz_publication",
"slot.name": "debezium_orders",
"table.include.list": "public.orders,public.customers",
"snapshot.mode": "initial",
"signal.data.collection": "public.debezium_signal",
"key.converter": "io.confluent.connect.avro.AvroConverter",
"value.converter": "io.confluent.connect.avro.AvroConverter"
}
}
curl -X PUT -H "Content-Type: application/json" http://connect:8083/connectors/orders-cdc/config -d @orders-cdc.json
The initial snapshot loads existing rows, then streaming starts from the log position.
3. Land changes with MERGE
Each event has op (c, u, d, r), before, after and source metadata (LSN/binlog position, timestamp). In the lakehouse:
MERGE INTO silver.orders t
USING (SELECT * FROM bronze_orders_changes_latest_per_key) 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 *;
Take the latest event per key per batch (order by source LSN/position), or Delta Live Tables / Lakeflow APPLY CHANGES does this for you.
4. Run in parallel, then remove triggers
Compare the CDC-fed table with the trigger-fed one for a week (counts, key sets, deletes). Then drop triggers and audit tables, and remove the polling jobs.
Tip
Add a heartbeat (
heartbeat.interval.msand, for PostgreSQL, a heartbeat action query) so replication slots advance on quiet databases and WAL doesn't pile up.
Checklist
- Source configured for log-based CDC with adequate log retention
- Connector deployed with secrets externalised and a signal table
- Sink applies inserts/updates/deletes idempotently
- Lag and connector state alerts
- Triggers and polling jobs removed after parallel run