Data Synchronization & Consistency

Once the same piece of data lives in more than one system, someone has to decide how “eventually correct everywhere” actually gets guaranteed — that’s data synchronization.

At a glance

• What it is: Techniques for keeping data consistent across systems that each keep their own copy: CDC (Change Data Capture — streaming a database’s changes as they happen, instead of batch-exporting later), idempotency (processing the same message twice has the same effect as processing it once), and reconciliation (a periodic batch comparison that catches drift the real-time path missed).
• Why it matters: Integrations built on messaging or events (see Messaging & Event Brokers) almost always deliver “at least once,” meaning duplicates will happen — and network partitions mean two systems will disagree for some period. The question isn’t whether to prevent this, it’s how to handle it safely.
• For developers: if your event/message handler has a side effect (charge a card, decrement stock, send an email) and isn’t checking “have I already processed this ID,” you have a duplicate-processing bug waiting for its first retry.
• When to use it: Whenever data is copied, cached, or projected into more than one system.
• When not to use it: A single system with a single source of truth and no replicas doesn’t need this — the problem only exists once data is duplicated across systems.
• Prerequisites: Messaging & Event Brokers.

Mental model

graph LR
    DB[(Orders DB)] -- CDC stream --> B{{Event Broker}}
    B --> RW[Reporting Warehouse]
    B --> C[Cache]
    RW -. nightly reconciliation job .-> DB

CDC keeps the fast path close to real time; reconciliation is the safety net that catches whatever the fast path missed or got wrong.

Step-by-step walkthrough

1. Identify the source of truth for the data and every system that holds a copy or derived view of it.
2. Prefer CDC (streaming changes as they happen) over nightly batch exports wherever near-real-time consistency matters.
3. Give every change record a stable, unique identifier (not just a timestamp) so consumers can detect duplicates.
4. Make every consumer’s write idempotent — check the identifier before applying a change, or use an operation that’s safe to repeat (an “upsert” rather than a blind insert).
5. Add a periodic reconciliation job that compares source and copy and reports (or fixes) drift, as a safety net for whatever the real-time path missed.

Worked example

A nightly batch job feeds a reporting data warehouse from the Orders system. Support keeps finding orders in the warehouse that don’t match what’s in Orders — some missing, some showing stale statuses.

• Input: a once-daily batch export with no way to tell whether a given night’s run partially failed, and no protection against re-running the same night’s data twice.
• Process: replace the nightly batch with a CDC stream that publishes every order change as it happens; make the warehouse’s write an idempotent upsert keyed by order ID + change sequence number, so replaying the same change twice (e.g. after a consumer restart) doesn’t create duplicates or apply changes out of order. Add a nightly reconciliation job that diffs a sample of orders between the two systems and alerts if drift exceeds a threshold.
• Output: near-real-time warehouse data, safe replay behavior, and an automated early-warning signal instead of support tickets when something drifts.

// Idempotent upsert keyed by (OrderId, SequenceNumber) — safe to replay
await connection.ExecuteAsync("""
    MERGE INTO WarehouseOrders AS target
    USING (SELECT @OrderId AS OrderId, @Sequence AS Sequence, @Status AS Status) AS src
    ON target.OrderId = src.OrderId
    WHEN MATCHED AND src.Sequence > target.Sequence THEN UPDATE SET Status = src.Status, Sequence = src.Sequence
    WHEN NOT MATCHED THEN INSERT (OrderId, Sequence, Status) VALUES (src.OrderId, src.Sequence, src.Status);
    """, change);

On Azure, Azure SQL Change Data Capture (or Cosmos DB’s change feed) streams order changes into the warehouse via Azure Data Factory, replacing the nightly batch window entirely.

The same idempotent-upsert discipline has to hold across regions, not just across systems, once a database itself replicates. For example, a Cosmos DB account with multi-region writes needs the same sequence-number-based conflict handling as the warehouse sync above:

resource cosmos 'Microsoft.DocumentDB/databaseAccounts@2024-05-15' = {
  name: 'cosmos-orders'
  location: 'eastus'
  properties: {
    databaseAccountOfferType: 'Standard'
    locations: [
      { locationName: 'eastus', failoverPriority: 0 }
      { locationName: 'westus', failoverPriority: 1 }
    ]
    enableMultipleWriteLocations: true
  }
}

Azure SQL and PostgreSQL Flexible Server solve the read side of the same problem with read replicas instead of multi-region writes — a replica lags the primary slightly, which is the same “briefly disagree” window described below, just within one database engine instead of across systems.

Common use cases

Use case	Technique	Notes
Feeding a downstream reporting/analytics store from an operational database	CDC	Avoids nightly batch windows and stale data
A consumer might receive the same event twice	Idempotent writes (upsert keyed by a stable ID)	Makes at-least-once delivery safe
Long-running sync pipeline needs a safety net	Reconciliation job	Catches drift from bugs, outages, or missed messages that the real-time path didn’t

Advanced details

• Idempotency keys: attach a unique ID to each message/event and have consumers record which IDs they’ve already processed, rejecting (or no-op’ing) repeats.
• Ordering: CDC streams can deliver changes out of order across partitions — include a sequence number or version so consumers can detect and discard stale updates.
• Eventual consistency: accept that copies will briefly disagree after a change — the design question is how long “briefly” is allowed to be, and how the discrepancy is surfaced if it exceeds that.
• Replication is the same problem at the database layer: Fowler’s Leader and Followers pattern describes how a single leader accepts writes and followers replicate them — the theory behind both Cosmos DB’s multi-region writes and Azure SQL/PostgreSQL read replicas above.

Quick reference

Term	Meaning
CDC	Change Data Capture — streaming a database’s changes as they happen
Idempotency	Processing the same operation twice has the same effect as processing it once
Upsert	A write that inserts if new or updates if it already exists, keyed by a stable ID
Reconciliation	A periodic batch comparison that detects (or fixes) drift between systems
Eventual consistency	Systems will agree eventually, but may briefly disagree after a change

Troubleshooting

Symptom	Likely cause	Fix
Same record duplicated in a downstream system	Non-idempotent consumer on an at-least-once broker	Key writes on a stable ID and upsert instead of insert
Downstream data occasionally out of order or stale	No sequence/version number on change records	Add a sequence number and have consumers discard older versions
Drift discovered only when a customer complains	No reconciliation job	Add a periodic reconciliation job with alerting

Check your understanding

• Why is “at least once” delivery from a broker not a bug, but a design constraint you need to plan for?
• What would go wrong in the worked example if the warehouse’s write used a plain insert instead of an upsert?

Related pages

Part of Introduction to Integration Architecture. Builds on Messaging & Event Brokers; see also Resilience & Error Handling in Integration for handling failures mid-sync, and the Glossary for term definitions.