Change data capture for finance AI pipelines

Change data capture reads the database’s own transaction log and turns every insert, update, and delete into an ordered event. Instead of repolling the ledger for rows that might have changed, you subscribe to the changes as the source commits them. For finance AI, that means features and retrieval indexes stay within seconds of the system of record, and the source system barely notices you are there.

The usual alternative is a scheduled query that pulls everything modified since the last run. It works until it does not. A repoll is only as fresh as its interval, it misses any row that was updated and reverted between runs, and it cannot see a delete at all unless you diff full snapshots. Worse, the query that finds recent changes is often a full scan on a table without the right index, so the freshness you want costs the transactional system exactly the load it can least afford at quarter-end.

Why the log beats the poll

A transaction log already contains the thing you are trying to reconstruct with polling: the exact sequence of committed changes, each with a timestamp and a position. Reading it directly buys you three things a poll cannot.

• The log is complete. Deletes and intermediate updates each arrive as their own event. A poll that keys on updated_at never sees a row that was inserted and deleted between two runs, and in finance that row might be a corrected payment or a reversed entry you need in the audit trail.
• The log is ordered. Log positions give you a total order per table and a way to reason about causality. When a payment status moves pending → settled → returned in eight seconds, you get all three transitions in sequence, not whichever one happened to be current when the poll fired.
• The log costs the source almost nothing. The database already writes it for durability and replication, and a CDC connector tails it the way a read replica does. You are not piling query load onto the primary during the busiest hours of the month.

We reach for log-based CDC by default: Postgres logical decoding, MySQL binlog, SQL Server’s CDC feature, or the change streams that Debezium normalises across all three. Query-based CDC on an updated_at column is a fallback for sources where we cannot get a replication slot, and we flag it as lossy on deletes so nobody downstream assumes it is complete.

Getting point-in-time correctness from a stream

The reason CDC matters for models, and not just for dashboards, is that the change log is a clean substrate for point-in-time correct features. Every event carries the source commit timestamp. If you compute features as of that timestamp rather than as of when your pipeline happened to process the event, you get a feature history that matches what was actually knowable at each moment.

That distinction is where leakage hides. Suppose a customer’s risk tier was upgraded on the 14th, and a model is deciding on a transaction dated the 10th. If your feature store reflects the current tier, you have quietly leaked four days of future information into training and your offline eval will look better than production ever will. CDC lets you avoid this cleanly, because the log tells you the tier as of the 10th was the old one. You read the log up to the label’s decision point and stop.

A few things we insist on to keep this honest:

• Separate event time from ingestion time everywhere. Event time is the source commit; ingestion time is when the connector saw it. Features key on event time. Latency monitoring keys on the gap between them.
• Treat late and out-of-order events as normal, because in a distributed source they are. Windowed aggregates need a grace period and a defined policy for what happens when a change lands after the window closed.
• Keep the lineage. When a feature value changes, you should be able to trace it back to the specific change event and source transaction that produced it. That trace is what makes a model decision explainable to a reviewer or an auditor months later.

Where CDC quietly breaks, and how we contain it

CDC is not free, and the failure modes are specific. The main one is schema drift. A source team adds a column, renames another, or changes a type, and a naive connector either drops the field or halts the stream. We put a data contract in front of the connector so a schema change is a negotiated event with a version, not a 3 a.m. page. Compatible changes flow through; breaking ones fail loudly at the boundary instead of silently corrupting features.

The second is exactly-once delivery, which is genuinely hard end to end. Most CDC pipelines are at-least-once, meaning the same change can arrive twice after a connector restart. For finance that is fine only if your downstream is idempotent. We key writes on the source primary key plus log position so a replayed event overwrites rather than double-counts. A duplicated settlement event should leave the balance unchanged, not move it twice.

The third is the reconciliation gap. Streams drop events, connectors lag, replication slots get dropped under disk pressure. We never let CDC be the only path. A daily batch recomputes the same aggregates from a full snapshot and compares them against what the stream produced. When they disagree beyond a tolerance, the batch figure wins and the difference gets logged. That job is boring and it is the thing that lets you trust the fast path the rest of the time.

Delete handling deserves its own mention. In many finance systems a record is never physically removed, it is superseded by a correcting entry, and a hard delete usually signals something you want to know about. Capture deletes as first-class events, keep the tombstone, and let the downstream decide whether to remove or mark superseded. Throwing the delete away because your sink only understands upserts is how a reversed transaction lives on in a feature that should have forgotten it.

Done with these guardrails, CDC turns the data layer from something you poll on a schedule into something that tells you when it changed. The models on top see the same world the ledger sees, only a few seconds behind, and they see the history of that world in the order it actually happened.