A food-delivery marketplace of the kind Zomato operates syncs a restaurant health score from the warehouse to its CRM nightly, matching records on the contact email. Over six weeks the CRM grows from 180000 accounts to 214000 and sales reports two owners for the same restaurant with conflicting scores. No sync run has errored. What failed and which design decision allowed it?

The trigger

The sync upserts on a mutable attribute. When a restaurant changes its contact email, the next run finds no match in the CRM and does the only other thing an upsert can do: it inserts. The old record stays, keeping the last score it was given, still in every segment and every campaign filter built on that field.

Nothing errors because nothing is wrong by the sync's own definition. It was asked to write rows and it wrote them.

Why it propagated quietly

The arithmetic is in the question. 34000 extra accounts over six weeks against 180000 is about a 3% monthly churn in one attribute - an ordinary rate for a contact field maintained by salespeople - compounding because each change creates a record that can itself go stale.

Detection lagged for three reasons:

• The sync reports rows written, not rows matched. An insert and an update look the same in a success count.
• No uniqueness constraint exists on email in the CRM, and there should not be - shared mailboxes and group addresses are legitimate.
• Nobody reconciled the destination count against the warehouse count. The two numbers were never compared after go-live.

The signal that would have caught it in week one: insert-to-update ratio per run, which in a steady state should be near zero, with an alert at anything above a few percent of the synced population.

The structural fix

Match on an immutable key the warehouse owns. Generate a stable identifier for each restaurant in the warehouse, write it once into a dedicated external-id field on the destination record, and match on that field for every run afterwards. Keep the mapping of warehouse key to destination record id in the warehouse, so a sync is idempotent and an engineer can answer "which CRM record is this row" without searching the CRM.

The tempting local fix is a deduplication pass plus an email uniqueness rule. It clears the backlog and the duplicates return next month, because the mechanism is untouched - and the uniqueness rule will reject a legitimate shared address at the worst moment.

Cleanup is a merge driven by the mapping, not by a fuzzy match: for each pair, keep the record carrying the activity history, move the mapping to it, and let the sync correct the fields.

The general lesson

A destination's match key is part of the interface, and it must be as stable as a primary key because that is what it is being used as. Any field a human edits - email, phone, company name, domain - is disqualified. This is the same requirement as an idempotency key on a payment API, and it fails the same way when the key is derived from mutable content.

When this is the wrong answer

If the destination has no custom external-id field and no merge API - some small tools do not - then matching on a mutable field is all there is. Make the sync update-only, never inserting, and route unmatched rows to a human queue with the warehouse key attached. A sync that refuses to create records cannot create duplicates, and the queue tells you how often the key breaks.