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:
- 4,140
- 610
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_hourThe migration in 5 phases
| Phase | Change | Exit check |
|---|---|---|
| 1. Expand | Add the outbox: checkout writes an order_placed row in the order's transaction, still publishing directly | Every new order has exactly 1 outbox row |
| 2. Shadow | A CDC publisher fills a new topic, order_events_v2, that no consumer reads yet | Reconciliation on v2 shows zero missing and zero phantom for 7 days |
| 3. Cut over | Move consumers to v2 in turn: search, warehouse, fraud, email, invoicing, shipping | Each consumer's output matches its v1 output across the overlap |
| 4. Contract | Delete the direct publish from checkout | v1 receives no new events |
| 5. Clean up | Retire the v1 topic once its retention passes; keep outbox rows 7 days | Nothing 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?
- Nothing goes wrong: outbox ids are unique, so every repeated order in the overlap is caught.
- Orders in the 10-minute overlap ship twice, because their v1 copies carry no outbox id to match.
- Orders in the overlap never ship, because the dedupe treats their v2 events as repeats.
- Events from v2 arrive out of order, so shipments are created before their orders exist.
- 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?
- Move on to phase 3: 12 misses is far below the dual-write baseline, so the new path already wins.
- Keep the direct publish permanently as a safety net running next to the outbox publisher.
- Switch to a polling publisher, since a log reader cannot survive a database failover.
- Hold phase 3, replay outbox rows by id range from before the failover, and make that replay part of every failover.
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.