Why can a CDC consumer see an order row before its order_items, and what fixes it?

## Where the atomicity is lost A source transaction is atomic in the database: either both the `orders` row and its `order_items` rows are visible, or neither is. A change-data-capture pipeline does not carry that property forward. It reads the log and emits one event per *row* change, and those events are then keyed by their own row's primary key so that each row's history stays ordered. Keying by row is exactly what makes per-key ordering work, and it is exactly what splits a transaction: the order's event and the items' events now travel independent routes, are batched independently, and are applied independently. A consumer reading them can therefore observe a state the source never had — an order with no lines, or a payment with no corresponding order. The important qualifier is that this state is **transient**. The missing events are already in flight, and within a batch interval or two the sink converges on the source's state. The bug is never "the data is wrong forever"; it is "a reader who looks at the wrong moment draws a wrong conclusion". ## Response 1: tolerate it, deliberately For most analytical consumers this is the correct answer and the cheap one. Batch models run on a schedule, over a target that has long since converged; the window of inconsistency is smaller than the scheduling interval and nobody sees it. What you must do is make sure no consumer treats an intermediate state as a business fact: an alert that fires on "orders with zero line items" will page someone every few minutes forever. Give such checks a grace period, or run them against a consistent cut rather than the live table. ## Response 2: transaction-boundary markers Some connectors can emit metadata around each transaction: a begin marker, an end marker, and on the end marker the transaction identifier plus a count of events emitted per table. Each data event also carries the transaction id and its ordinal within the transaction. That is enough for a consumer to reassemble atomicity — buffer everything for a transaction id, wait for the end marker, check the counts, then apply the whole set in one sink transaction. ```text tx 8841 BEGIN orders id=500 op=insert order_items id=900 op=insert order_id=500 order_items id=901 op=insert order_id=500 tx 8841 END (event counts: orders=1, order_items=2) ``` The costs are real and are what the interview is actually probing. Buffering needs memory proportional to the largest transaction, and a bulk job that updates ten million rows in one transaction will exhaust it. Latency now waits for the end marker rather than the event. And because the consumer must see a transaction's events together, you have serialised work that per-key routing had parallelised — the throughput ceiling drops. Reach for this when a downstream system genuinely cannot show partial state, not by default. ## Response 3: apply freely, expose a consistent cut A middle path that keeps parallelism: let the sink apply events as they arrive, but track the highest source commit position for which *all* events are known to be applied — a committed watermark — and expose only data up to that position to readers, through a view or a published snapshot marker. Consumers see a consistent point-in-time state that lags slightly, while the loader stays fast. The engineering cost is computing the watermark honestly across all lanes: it is the minimum of the per-lane progress, not the maximum, and one stalled lane holds the whole cut back, which is a monitoring requirement in itself. ## Response 4: fix the shape at the source If the consumer needs a business event rather than a set of row changes, the durable answer is for the producing service to write one record describing the whole change — the outbox pattern, where the application inserts an event row inside the same transaction as its business writes and CDC captures that single row. There is then nothing to reassemble, because atomicity was preserved by construction, and the consumer is decoupled from the source's table layout. This is a source-side design change, so it is available only when you own the producing application. ## Choosing between them Ask what the consumer does with the data. A dashboard refreshed hourly: tolerate. A downstream service that emits a customer-facing notification on seeing an order: it must not act on a partial state, so either buffer by transaction or move it to an outbox event. A replica used for operational reads: expose a consistent cut. A fraud rule that joins three CDC-fed tables in real time: this is the case where partial state produces false positives, and it is the strongest argument for transaction-level delivery. ## What a strong answer includes Name the cause precisely — row-level events keyed per row, so transaction atomicity ends at capture — say that the inconsistency is transient rather than permanent, and then give at least two responses with their costs rather than jumping straight to "buffer the transaction". Volunteering the memory and latency cost of buffering large transactions is what separates someone who has run this from someone who has read about it.