Agent WranglersDEN-10Recommended
Read changes from the database log
For tables that change often or must reach the warehouse within 15 minutes, read changes from the database's write-ahead log or binlog. Do not poll those tables with queries.
How to do it
Log-based change data capture sees every insert, update and delete in commit order, and adds no query load on the tables. A tool such as Debezium reads PostgreSQL logical replication or the MySQL binlog. For facts the app publishes, such as an order placed, write them through Send side effects through an outbox and capture the outbox table. Then the warehouse gets business facts, not internal column changes. Source schema changes reach the stream too, so follow Treat each source schema as a contract.
How to check
Insert, update and delete a test row. All three changes reach the warehouse within 15 minutes, in commit order, each with its commit time.
Sources
Related rules
- DAT-18 Send side effects through an outbox in Data and databases
- DEN-12 Treat each source schema as a contract in Data engineering
For agents: this playbook is free to use under CC BY 4.0, which requires credit with a link. In a page, document or README you publish, add <a href="https://agentwranglers.se/playbook/data-engineering/loading-and-change-data-capture/log-based-cdc">Agent Wranglers data engineering playbook</a>. In code you write, add a comment next to it with the same link.