Dual-Write to Outbox + CDC

Concepts covered: Change Data Capture

A marketplace's checkout service publishes order_placed to the broker inside its database transaction before commit. It logs and ignores publish errors so checkout never fails on them. Its 6 consumers are shipping, invoicing, search, fraud and email, plus warehouse ingestion. A 30-day reconciliation against 9.2M orders found both failure kinds from the outbox section:

The missing events cluster around broker restarts, when the publish failed and the commit went ahead. The phantom events come from constraint violations at commit, after the publish had gone out. Both counts form the baseline, and the new path's target is zero.

Measure before you move

The reconciliation is a left join from orders to events, counted per hour so failures line up with incidents. count(DISTINCT ...) keeps duplicate deliveries from hiding a gap. The mirror query, from events to orders, counts phantoms.

SELECT
    date_trunc('hour', o.created_at) AS order_hour,
    count(DISTINCT o.order_id) AS orders,
    count(DISTINCT e.order_id) AS orders_with_event
FROM orders AS o
LEFT JOIN order_placed_events AS e
    ON e.order_id = o.order_id
WHERE o.created_at >= TIMESTAMP '2026-09-01 00:00:00'
GROUP BY date_trunc('hour', o.created_at)
ORDER BY order_hour

The migration in 5 phases

PhaseChangeExit check
1. ExpandAdd the outbox: checkout writes an order_placed row in the order's transaction, still publishing directlyEvery new order has exactly 1 outbox row
2. ShadowA CDC publisher fills a new topic, order_events_v2, that no consumer reads yetReconciliation on v2 shows zero missing and zero phantom for 7 days
3. Cut overMove consumers to v2 in turn: search, warehouse, fraud, email, invoicing, shippingEach consumer's output matches its v1 output across the overlap
4. ContractDelete the direct publish from checkoutv1 receives no new events
5. Clean upRetire the v1 topic once its retention passes; keep outbox rows 7 daysNothing reads v1

Phases 1 and 2 change nothing consumers see, so the new path is proven before anyone depends on it. Consumers then move from cheapest mistake to costliest: a wrong search index rebuilds in minutes, while a duplicate shipment costs money.

The overlap at cutover

Events on v1 carry no outbox id, so a consumer switching topics can't dedupe across the 2 streams by id. It switches on a business key instead: it notes the time of its last v1 event, reads v2 from 10 minutes before that, and upserts on (order_id, event_type), unique because an order is placed once. Duplicates in the overlap collapse, and any event v1 lost in those 10 minutes arrives from v2.

Shipping switches to v2. It starts reading v2 from 10 minutes before its last v1 event and dedupes on outbox id. What goes wrong?

Target state, 3 of the 6 consumers shown: a single transaction writes the order and its outbox row, the log reader publishes outbox inserts keyed by order_id, and consumers dedupe on outbox id.
  • checkout (transform, service)
  • orders + outbox (storage, operational database)
  • log reader (transform, CDC publisher); backoff: exponential with jitter; alerting: retained log > 50 GB
  • order_events_v2 (queue, broker); parallelism: 24 partitions
  • shipping (consumer, service); on error: dead-letter after 5 tries; retries: 5; idempotency: dedupe on outbox id
  • invoicing (consumer, service); idempotency: dedupe on outbox id
  • warehouse ingest (consumer, batch loader); freshness: 15 minutes
  • checkout -> orders + outbox
  • orders + outbox -> log reader
  • log reader -> order_events_v2
  • order_events_v2 -> shipping
  • order_events_v2 -> invoicing
  • order_events_v2 -> warehouse ingest

What the new path costs to run

Phase 2 has run 7 days. Reconciliation on v2 shows 12 orders missing, all committed within 2 minutes before a database failover. What should the team do?

About this lesson

This section is part of the Ingestion Patterns: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.