Designing a Reliable Data Synchronization Layer: Idempotency, Ownership, and Observability

In a lot of organizations, the real integration platform is a person. Someone exports orders from the ERP every morning and pastes them into the planning tool. Someone else re-types customer updates from the CRM into the invoicing system. It works until that person is on holiday or makes a typo in a price field or the volume doubles.

Replacing that manual work with a synchronization service sounds like a junior-level task: read from system A, write to system B, schedule it, done. In practice, sync services are where many integration projects quietly fail. They fail not because moving data is hard, but because the edge cases are partial failures, retries that duplicate records, two systems that both think they own a field, and errors that nobody notices for three weeks.

This article walks through the design decisions that separate a sync layer you can trust from one you learn to fear. The examples use Python and pseudo-SQL, but every pattern here is language-agnostic.

Decision 1: One Source of Truth Per Entity

The single most important design decision in any sync architecture is not technical. It is organizational: for every entity, exactly one system is allowed to win.

Orders live in the ERP. The webshop may create them, but once created, the ERP's version is the truth, and the webshop displays what the ERP says. Customer contact details live in the CRM. The ERP receives updates from the CRM and never edits them locally.

The moment two systems can both modify the same entity and both push their version, you have built a conflict generator. Last-write-wins will silently destroy data. Merge logic will grow into an unmaintainable swamp of special cases. The fix is almost never smarter conflict resolution. It is removing the conflict by assigning ownership.

Write this down as a table before writing any code:

If you cannot fill in this table, you are not ready to build the sync. Any cell where two systems appear in the "may update" column is a design problem to resolve with the business first, not a technical challenge to code around.

Decision 2: Idempotency, or Retries Will Hurt You

Your sync will fail mid-run. The network will drop after 4,000 of 5,000 records. The target API will return a 500 halfway through. The scheduler will fire twice. None of these are exceptional; they are Tuesday.

The only sane response to failure is retry, and retry is only safe when every operation is idempotent: running it twice produces the same result as running it once.

The classic mistake looks like this:

# Dangerous: creates a duplicate on every retry
def sync_order(order):
    target_api.create_order(
        customer=order.customer_id,
        lines=order.lines,
        total=order.total,
    )

If this call succeeds on the target but the response is lost (a timeout, a crashed worker), the retry creates a second order. Someone ships it.

The fix is to make every write carry a stable, deterministic key derived from the source record, and make the target treat that key as unique:

# Safe: the natural key makes the operation idempotent
def sync_order(order):
    target_api.upsert_order(
        external_id=f"erp-{order.erp_id}",   # stable key from the source
        customer=order.customer_id,
        lines=order.lines,
        total=order.total,
    )

If the target system has no upsert endpoint, simulate one: look up by external_id first, then create or update. Wrap that lookup-and-write in one function and forbid every other code path from writing directly.

The same rule applies to your own bookkeeping. Store sync state keyed by the same external ID, so a re-run of yesterday's batch is harmless by construction.

Decision 3: Pull Changes, Don't Diff Worlds

The naive sync reads all records from both sides and compares them. This works in the demo and collapses in production, where "all records" means 400,000 rows over a SOAP API that pages 100 at a time.

You need change detection, and there are three workable tiers, in order of preference:

1. The source has reliable updated_at timestamps or a change log. Store a high-water mark after each successful run and query only what changed since. This is the happy path; verify that the timestamp actually updates on every mutation, including the ones done by nightly batch jobs inside the legacy system. Legacy systems lie about this more often than you would expect.
2. The source has no usable timestamps, but you can read all records cheaply. Compute a hash per record and compare against the hash you stored last run. Only records with changed hashes get pushed downstream:
   Python

    

   import hashlib, json
   
   def record_hash(record: dict) -> str:
       canonical = json.dumps(record, sort_keys=True, default=str)
       return hashlib.sha256(canonical.encode()).hexdigest()
   
   def detect_changes(records, stored_hashes):
       for r in records:
           h = record_hash(r)
           if stored_hashes.get(r["id"]) != h:
               yield r, h

   
   

3. Neither is possible. You are down to full comparisons on a schedule. Constrain the entity scope aggressively and be honest with stakeholders about latency.

Whichever tier you land on, keep the change detector separate from the writer. A queue between them, even a simple database table with pending / done / failed states, gives you retry, rate limiting, and an audit trail almost for free:

CREATE TABLE sync_queue (
    id            BIGSERIAL PRIMARY KEY,
    entity_type   TEXT NOT NULL,
    external_id   TEXT NOT NULL,
    payload       JSONB NOT NULL,
    status        TEXT NOT NULL DEFAULT 'pending',
    attempts      INT NOT NULL DEFAULT 0,
    last_error    TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    processed_at  TIMESTAMPTZ,
    UNIQUE (entity_type, external_id, status)
);

That UNIQUE constraint is doing real work: it prevents the same pending change from being enqueued twice, which keeps the queue idempotent too.

Decision 4: A Silent Sync Is Worse Than No Sync

Here is the paradox of a working sync layer: the better it works, the more people trust it, and the more damage it does on the day it silently stops.

A sync that visibly fails gets fixed the same morning. A sync that dies quietly keeps its consumers confidently reading stale data. Sales quotes yesterday's* stock levels. Finance invoices from last week's prices. By the time someone notices, you are reconstructing three weeks of drift.

Minimum viable observability for a sync service is four things:

• A heartbeat. Every run writes a completion record. An external check alerts when the most recent successful run is older than the expected interval. Do not rely on the sync alerting about itself; a crashed process sends no alerts.
• Drift metrics. Periodically count records on both sides and compare. The counts will never match perfectly in a live system, so alert on trend, not on exact equality.
• A dead-letter state. After N failed attempts, a queue item moves to failed and a human is notified with the payload and the last error. Infinite retry loops on a permanently broken record will otherwise clog the queue and mask new failures behind old ones.
• Readable logs per record. When finance asks why invoice 4482 shows the old address, you want to answer with one query, not a debugging session.

None of this is sophisticated. All of it is regularly skipped, because on the day the sync ships, it works, and observability feels like polish. It is not polish. It is the feature that determines whether you find out about failure from a dashboard or from an angry customer.

The Shape of the Whole Thing

Put together, a trustworthy sync layer is small and boring:

[ Source system ] → change detection → sync_queue → idempotent writer → [ Target system ]
                                          ↓
                             heartbeat, drift checks, dead letters

Two processes, one queue table, a handful of metrics. The value is not in the volume of code; most implementations of this design fit in a few hundred lines. The value is in the decisions encoded in it: one owner per entity, stable keys on every write, changes flowing through an inspectable queue, and failure treated as a normal input rather than an exception.

Build it this way, and the sync becomes infrastructure nobody thinks about, which is the highest compliment integration code can receive. Build it as a quick script, and you have not removed the human integration layer at all. You have just changed whose Friday afternoon gets ruined.