How do you avoid dual‑writes and safely use CDC?

Dual writes happen when an application updates two systems in one flow, for example a database and a message broker. If one update succeeds and the other fails you get split brain state, ghost events, or lost updates. Change Data Capture reads the database change log and turns committed changes into events. Used together with the outbox pattern you avoid dual writes while still driving near real time workflows like search indexing, cache warmup, analytics, and fanout. This guide shows the safe path that works in production and in a system design interview.

Why It Matters

Interviews look for strong reasoning about atomicity, ordering, and delivery guarantees under scale. In real distributed systems, inconsistent side effects create billing errors, double notifications, and broken counters. A safe CDC pipeline with an outbox keeps a single source of truth, preserves order per entity, supports retry without duplication, and gives you replay for backfills and audits. It also scales across microservices without fragile cross service transactions.

How It Works Step by Step

Select a single source of truth Always start by designating one system (typically your primary database) as the authoritative source of truth. Every side effect—cache update, event emission, or analytics trigger—should originate from this committed state.
Create an outbox table in the same database Add an outbox table that records small, structured event entries. Each record should include the entity ID, event type, payload, version, and an idempotency key.
Write business data and outbox entry in one transaction Insert both the main data (like order or payment) and the outbox entry in a single database commit. This atomic operation ensures that either both succeed or both fail, preventing inconsistent states.
Use CDC or relay to publish after commit A Change Data Capture (CDC) tool or relay service monitors committed transactions and publishes events only after they are durably stored in the database. This eliminates the timing gap that causes dual writes.
Partition events to maintain ordering Use a deterministic partition key such as user_id or order_id to ensure that all events for the same entity are processed sequentially.
Make consumers idempotent Consumers should safely handle duplicate messages by tracking processed event IDs or sequence numbers to avoid applying the same change twice.
Handle failures and backpressure Failed events remain in the outbox for retry. Implement retry with exponential backoff and use Dead Letter Queues (DLQs) for events that repeatedly fail.
Monitor CDC lag and reliability metrics Track outbox table growth, CDC lag, message latency, and consumer offset progress. These metrics ensure the system stays healthy under high load.
Support replay and backfill Because all changes are recorded in a durable log, you can replay events to rebuild downstream systems (e.g., search indexes or caches) without touching the application code.

Real World Example

Think of an e commerce service that updates inventory and also must update a search index. The service writes the inventory adjustment into the database and inserts an outbox row with product id, delta, and version. A relay reads the outbox and publishes to a topic keyed by product id. The search indexer consumes from that topic and updates the document only if the incoming version is newer than what it has. If the indexer crashes, the message stays in the log and will be processed later. No dual writes risk, and ordering per product stays correct under high throughput.

Common Pitfalls or Trade offs

Publishing before commit Sending an event before the database transaction commits leads to ghost events if the transaction rolls back. Always publish only after commit through CDC or relay.
Polling instead of log-based CDC Polling tables can miss transient data and increase load on the database. Prefer log-based CDC that reads from the Write-Ahead Log (WAL) for stronger ordering guarantees.
Incorrect partition key selection Using a random or time-based key breaks per-entity ordering. Always partition by a stable entity identifier.
Oversized event payloads Large payloads slow delivery and cause consumer timeouts. Keep payloads minimal and include a reference for extended data if needed.
Schema drift without compatibility management Changing database columns or types can break downstream consumers. Adopt versioned schemas and ensure backward compatibility.
Event feedback loops CDC might recapture derived writes from downstream consumers, causing event loops. Mark the event source or filter derived tables to avoid this.
Relying on exactly-once delivery Most CDC systems and brokers guarantee “at least once” delivery. Design for idempotency instead of expecting perfect delivery semantics.
Data privacy leakage Never expose sensitive fields (e.g., user PII or tokens) through CDC events. Mask, encrypt, or tokenize data before publishing.

Interview Tip

Expect a scenario like create order then publish OrderCreated to a topic. The interviewer may ask what happens if the publish fails. Sketch the outbox table, show the single commit of order row and outbox row, explain the relay or CDC that publishes after commit, then cover idempotent consumption using sequence numbers. Close with the monitoring metrics you would track and how you would replay for recovery.

Key Takeaways

Avoid dual writes by writing business state and an outbox row in one transaction and only publishing after commit.
Use log based CDC or an outbox relay to turn commits into ordered events with per entity keys.
Make every consumer idempotent using idempotency keys, sequence checks, and small dedupe stores.
Monitor CDC lag, outbox growth, and dead letter volume, and keep schemas compatible to avoid breakage.
Durable logs enable replay, backfill, and safe recovery without touching the source application.

Table of Comparison

Option	Core idea	Guarantees	Cost and complexity	When to prefer
Naive dual writes	App writes database and also publishes to broker	No atomicity, possible lost or ghost events, ordering not guaranteed	Simple code but fragile at scale	Prototypes only, never for core flows
Outbox with relay	App commits state and outbox in one transaction, relay publishes	Atomic per write, per entity ordering with proper keys, at least once delivery	Moderate ops, simple mental model	Most transactional services that need side effects
Log based CDC	Tool reads write ahead log and emits change events	Reflects committed state, preserves commit order, at least once delivery	Tooling and schema evolution management	Teams that want minimal app code and broad replication
Two phase commit	Coordinator manages commit across resources	Strong atomicity across systems	High latency and coordination risk	Rare cases with legacy systems where audit requires it
Event sourcing	Event store is the source of truth and rebuilds state	Natural ordering and replay, strong audit trail	Larger design shift and new abstractions	Domains where audit and replay are top priority

FAQs

Q1. What is dual writes and why is it risky?

Dual writes means an app updates two systems separately, for example a database and a queue. If one step fails you get inconsistent state, which leads to lost messages or side effects that do not match committed data.

Q2. How does the outbox pattern eliminate dual writes?

The app updates business state and inserts an outbox row in one commit. A relay or CDC publishes only after the commit, so side effects always reflect durable state.

Q3. Is CDC a replacement for a message broker?

CDC turns commits into events. You still want a durable log or queue to distribute those events, buffer bursts, and support replay. CDC and a broker complement each other.

Q4. How do I preserve ordering across partitions with CDC?

Use a stable key per entity when publishing, such as order id or user id. This keeps all events for that entity on one partition, which preserves total order for that entity.

Q5. Do I get exactly once delivery with CDC?

Most CDC tools and brokers are at least once. Design idempotent consumers that ignore duplicates using idempotency keys and last seen sequence numbers.

Q6. How do I prevent CDC from creating loops?

Exclude derived tables from capture or tag events with a source label and filter them out downstream. Keep a clear contract about which tables are authoritative for emission.

Further Learning

Level up your interview playbook with the step by step modules in Grokking the System Design Interview.
Build production grade intuition about CDC, idempotency, and replay with Grokking Scalable Systems for Interviews.