A mobility platform wants operational database changes available to analytics and search within seconds, without adding load to the transactional databases. Design the change-data-capture pipeline and identify its principal failure modes.

Why log-based CDC rather than the alternatives

Query-based polling (WHERE updated_at > last_seen) is the obvious approach and is wrong in specific ways: it adds query load to the primary, misses deletes entirely, misses intermediate states between polls, depends on a correctly-maintained timestamp column, and has a race at the polling boundary where rows committed with an earlier timestamp appear after the cursor has moved past.

Dual writes from the application — write to the database and publish an event — has no atomicity. Crash between the two and the systems diverge permanently, with no mechanism to detect it.

Log-based CDC reads the database's own replication log. It captures every change including deletes, in commit order, with no additional query load, and it is the same mechanism the database already trusts for replication. It is the right default.

The pipeline

1. A connector reads the replication log and emits change events with before and after images, operation type, transaction id and log position.
2. Events land in a partitioned log, keyed by primary key so all changes to one row are ordered within a partition. Per-key ordering is the guarantee that matters; global ordering is neither available nor needed.
3. Consumers materialise their own views — a search index, an analytics table, a cache invalidator — each tracking its own position and able to lag or replay independently.
4. Snapshot plus stream for bootstrap. A new consumer needs an initial consistent snapshot, then the stream from that snapshot's log position. Getting the handover exactly right is the fiddliest part of any CDC implementation.

The principal failure modes

Schema evolution. The most common source of outages. A column added upstream flows through; a column dropped or retyped breaks consumers. Requires a schema registry with compatibility rules and a discipline that database migrations are reviewed as contract changes, because that is what they now are — the database schema has become a public interface whether the owning team intended it or not.

Connector lag and log retention. If the connector stops for longer than the database retains replication log, it cannot resume and requires a full re-snapshot — expensive and disruptive. Retention must exceed the worst realistic outage, and lag must be alerted well before that horizon.

Backpressure onto the source. Some databases retain log segments while a replication slot is behind. A stalled consumer can therefore fill the primary's disk and take down the transactional system. This is the failure that turns an analytics problem into a production incident, and it needs an explicit disconnect policy.

Duplicate delivery. At-least-once means consumers must be idempotent — upsert by primary key rather than insert, and be able to handle the same change twice.

Transaction boundaries lost. Changes to several tables in one transaction arrive as separate events, possibly in different partitions. A consumer reading mid-transaction sees a state that never existed atomically. If cross-table consistency matters, the consumer must group by transaction id and apply atomically — and many implementations quietly do not.

The hidden coupling. Downstream systems now depend on the internal schema of an operational database. A refactor the owning team considers internal breaks three consumers they have never met. The mitigation is an explicit published contract — a projection or view treated as the interface — rather than raw tables.