Change data capture

How do you copy every change a database makes without asking the database for anything?

You need a warehouse copy of an operational database that stays current within seconds. The obvious approach is to ask: run a query every so often and take what changed. And that approach, honestly examined, fails in ways that are worth sitting with — not because the query is slow, but because of what a query can and cannot see.

So the sharper question is this: is there a way to learn every change a database made without querying it for the data at all? It sounds like a trick. It is not, and the answer turns on something the database was already doing for its own reasons.

Start by making the polling approach fail properly. You add an updated_at column and every minute ask for rows changed since last time. What do you miss?

Deletes, first. A deleted row has no updated_at to be greater than anything; it simply is not there. Your copy keeps it forever. You could add a soft-delete flag, but now you have changed the source application to serve the copier.

Second, intermediate states. If a row is updated three times inside your one-minute window, you see the final value and never learn there were three changes — and for an audit trail or anything that reacts to transitions, that is the very thing you cared about.

Third, and most subtly: the query competes with production. Every poll scans an index on a live transactional system.

Now the reframe. What does a database already do, without being asked, on every single write? Before it modifies a data page, it records the intended change durably in a sequential log — the write-ahead log in PostgreSQL, the redo log in Oracle, the binary log in MySQL. This is not a feature built for us; it exists so the database can recover from a crash and so replicas can follow the primary. But look at what it contains: every insert, every update, every delete, in commit order, with the values.

So the question answers itself. The changes need not be queried — they have already been written down. A capture process can read that log the way a replica does, and reconstruct the stream of changes without touching a single table.

Consider what that buys. Deletes appear, because a delete is a log record like any other. Every intermediate state appears, because the log records each write rather than a final state. Ordering is inherent. And the load on the source is that of one more replica, which the database was already designed to serve.

One gap remains. The log holds changes, not the current contents, and old segments are recycled. So a new consumer must first take a consistent snapshot of the existing rows, note the log position that snapshot corresponds to, and then begin streaming from exactly that position. Get that handover wrong — start streaming from a later position than the snapshot — and you silently lose every change in between. This snapshot-then-stream handover is where most of the real engineering in tools like Debezium lives.

Three refinements matter.

The first is what the mechanism does not give you. The log records physical or row-level changes, not intent. You learn that a row's status went from 3 to 7; you do not learn that a customer cancelled an order. Deriving business meaning from a change stream requires knowing the source schema well, and that knowledge lives in the producing team — which is why unreviewed change capture is often described as reaching into another team's private state.

The second is the coupling that creates. The consumer now depends on the source's internal table structure, not on any interface the source team agreed to. A refactor that would have been invisible behind an API becomes a breaking change. This is a real cost of the technique, not a detail, and it is the main argument for producers publishing deliberate events instead.

The third is delivery semantics, and here it pays to be precise rather than reassuring. Log-based capture generally offers at-least-once delivery: after a crash the reader resumes from its last committed position and may replay records it already emitted. Exactly-once end to end is achievable, but only when the consumer cooperates — by making writes idempotent, usually keyed on the log position or the primary key, or by participating in a transactional protocol. A pipeline that assumes exactly-once because a vendor page mentions it will eventually double-count something.

Worth adding plainly: trigger-based capture, writing changes into an audit table from within the transaction, also works and avoids log-format dependence — at the cost of write throughput on the production path.

• How a snapshot is taken consistently without locking a busy table.
• Why change streams and event streams differ, and when to publish deliberate events instead.
• How a warehouse applies an update stream, and why merge-on-read and copy-on-write differ in cost.

Every term the collection defines is gathered in the glossary.