Why your Salesforce/NetSuite sync creates duplicate records (and how to fix it)

Somebody in finance opens NetSuite and finds two customer records for the same company. Then three. Then a sales order attached to the wrong one. In Salesforce, the account list has "Acme Corp", "Acme Corp." and "Acme Corporation", each with a slice of the real history. Nobody changed anything. The integration has been running for months.

This is the most common failure mode in CRM↔ERP integration work, and it is almost never caused by the thing people first suspect. It is rarely a bad mapping and rarely a vendor bug. It is nearly always one of four root causes, and they show up in a predictable order of frequency.

This guide walks through each cause, how to tell which one you have, and the concrete fix pattern for it.

First: stop the bleeding

Before diagnosis, put a guard on the write path. Every place your middleware calls "create customer" or "create sales order", add a lookup on your correlation key first, and convert the call to an update when the lookup hits.

That one change is usually a single deploy, and it turns an accelerating problem into a static one. You will still have the duplicates you already made, but you stop making new ones while you do the real work. Deduplicating a dataset that is still actively duplicating is a treadmill.

Also: turn on record-level logging for the sync if it is not already on. You want, for every write, the source event ID, the correlation key you matched on, whether the lookup hit or missed, and the resulting record ID in the target system. Nearly every diagnosis below is a five-minute query against that log and a multi-day archaeology project without it.

Cause 1: non-idempotent retry logic

This is the single most frequent cause, and it accounts for more duplicate records than the other three combined.

Every event-driven integration retries. Salesforce outbound messages retry. Webhook deliveries retry. Your own queue retries on worker crash. Your middleware's HTTP client retries on a 502. And a retry is indistinguishable, from the receiving side, from a genuine second event — unless you make it distinguishable.

The classic shape: your handler creates a NetSuite customer, the create succeeds, and then the response times out on the way back. Your middleware sees a timeout, marks the job failed, and retries. The second attempt creates a second customer. The record was never the problem; the acknowledgement was.

How to recognize it

• Duplicates appear in tight clusters — two records seconds apart, not days apart.
• Duplicate counts spike during incidents, deploys, or periods of target-system slowness.
• The duplicates are byte-identical, including fields a human would have varied.
• Your logs show more successful writes than distinct source events.

The fix: idempotency keyed on the event

Treat every inbound event as having a stable identity, and record that you have processed it before you do anything user-visible.

// Pseudocode for the write path in your middleware
async function handleEvent(event: SyncEvent) {
  // 1. The event's own ID is the idempotency key.
  const key = event.id;

  // 2. Claim it. A unique constraint on `event_id` makes this atomic.
  const claimed = await db.processedEvents.insertIfAbsent({
    eventId: key,
    status: "in_progress",
  });
  if (!claimed) {
    const prior = await db.processedEvents.get(key);
    if (prior.status === "done") return prior.result; // replay: no-op
    throw new RetryLater(); // another worker holds it
  }

  // 3. Do the work as an upsert, not an insert (see Cause 4).
  const result = await targetSystem.upsertCustomer({
    externalId: event.correlationKey,
    fields: event.payload,
  });

  // 4. Record completion, including the target record ID.
  await db.processedEvents.complete(key, result.recordId);
  return result;
}

Three details matter more than the shape of the code:

The uniqueness must be enforced by the database, not by application logic. A "check then insert" in application code is not atomic and will lose the race under exactly the concurrency conditions that cause the bug.

The event ID must be stable across retries. If your middleware generates a fresh UUID per delivery attempt, you have not built an idempotency key, you have built a counter. Use the identifier the source system assigns, or derive one deterministically from the source record ID plus its modification timestamp.

Store the resulting target record ID. When a replay arrives, you want to return the record you already created, not merely skip. Downstream steps often need that ID.

This is the same pattern as deduplicating payment webhooks; if you have implemented idempotent Stripe webhook handling, it is the identical mechanism with different nouns.

Cause 2: matching on the wrong field

The second most common cause is a matching rule that looks reasonable and is not stable.

Middleware needs to answer "does this record already exist on the other side?" If it answers that question by comparing company name, email address, phone number, or a fuzzy combination, it will eventually answer wrong. Names get re-typed. Emails change when someone leaves. A trailing period, a "Ltd" vs "Limited", a leading space from a paste — any of these flips a match to a miss, and a miss becomes an insert.

Worse, fuzzy matching fails in both directions. Too loose and you merge two genuinely different subsidiaries. Too strict and you duplicate.

How to recognize it

• Duplicates differ in punctuation, casing, whitespace, or legal-entity suffix.
• Duplicates cluster around records that were recently edited by a human.
• The rate correlates with data-entry activity rather than with system load.
• Records created through one channel (say, a web form) duplicate while records created through another do not.

The fix: a durable external ID on both sides

Pick one system as the identity authority for each object type — customers usually originate in the CRM, items and invoices usually originate in the ERP — and mint an opaque, immutable ID there. Then store that ID in a dedicated field on both sides.

In Salesforce, that means a custom field marked as an External ID and Unique. In NetSuite, that means the record's External ID field, or a dedicated custom field if External ID is already spoken for by another integration. Whichever you choose, three rules apply:

1. It is never edited by a human. Make it read-only in every page layout and form.
2. It never encodes business meaning. No customer names, no account numbers that finance might renumber, no email addresses. The moment an ID means something, someone will want to change it when the meaning changes.
3. It is populated on creation, in the same transaction as the record. A field that gets backfilled by a nightly job leaves a window in which the record is invisible to matching — and that window is exactly when the duplicate gets created.

Once both sides carry it, matching is a single indexed lookup rather than a heuristic, and the entire class of near-miss duplicates disappears.

For the records that already exist without an ID, do a one-time reconciliation: match as carefully as you can with human review, write the ID onto both records, and treat anything that cannot be confidently matched as a manual queue rather than an automated guess.

Cause 3: race conditions from concurrent syncs

Third: two processes touch the same logical record at the same moment, both check for an existing record, both find nothing, and both insert.

This is the cause that hides successfully in staging. It requires concurrency to reproduce, and staging environments rarely have it. It surfaces in production during bulk operations, during the first big data migration, or the week after somebody scaled the worker pool from two to twenty to make the sync faster.

It also appears in a subtler form: a bidirectional sync where a write to NetSuite fires an event back to Salesforce, which fires an event back to NetSuite. If the loop is not cut, each pass can create a record.

How to recognize it

• Duplicates appear in pairs created within the same second, from different source events.
• The rate scales with worker count — doubling parallelism roughly doubles duplicates.
• The problem started when someone increased throughput, not when anyone changed logic.
• Both duplicates have valid, distinct event IDs, so idempotency (Cause 1) does not catch them.

The fix: serialize per record, not globally

The instinct is to reduce concurrency to one. Do not. You lose all throughput to fix a problem that only exists between events touching the same record.

Instead, partition the work so that all events for one logical record are handled in sequence, while different records still run in parallel. Two standard approaches:

Queue partitioning by key. Most managed queues support a per-message group or partition key. Set it to your correlation key. The queue then guarantees ordered, single-in-flight delivery per key while fanning out across keys. This is the lower-operational-cost option and should be your default.

A distributed lock. Acquire a short-lived lock on the correlation key before the read-check-write sequence and release it after. Redis or your primary database both work. Set a TTL slightly longer than your worst-case write latency, make lock acquisition part of the retry loop, and never hold a lock across an unbounded external call.

Whichever you pick, back it with a unique constraint in the target system as the last line of defense — the Unique attribute on the Salesforce external ID field, or the equivalent uniqueness on the NetSuite side. A constraint turns a race that would have silently produced a duplicate into a loud, catchable error. Then handle that specific error by re-reading and updating instead of failing the job.

For the echo-loop variant, tag writes made by the integration (a "last modified by integration" flag or a dedicated integration user) and have each side's event filter ignore changes attributed to itself.

Cause 4: insert where you meant upsert

The fourth cause is the simplest and, once the first three are fixed, often the last one standing: the code path just calls create.

Sometimes this is a genuine oversight. More often it is a partial implementation — the main sync path upserts correctly, but a secondary path does not. Backfill scripts, manual replay tools, the "resync this account" button in the admin UI, and error-recovery handlers are the usual suspects, because they were written later and separately.

How to recognize it

• Duplicates appear only for records that went through a specific workflow.
• They correlate with someone using an internal tool, or with a scheduled backfill.
• The main event-driven path is clean and the duplicates come from somewhere else.

The fix: one write function, upsert-only

Make every path — primary sync, backfill, manual replay, error recovery — go through a single function that upserts keyed on the external ID. Delete the raw create calls entirely, so a future contributor cannot reach for one.

Both platforms support this natively rather than requiring a read-then-branch:

• Salesforce's REST API supports upsert by PATCHing to the external-ID endpoint for the object, which creates or updates based on whether the supplied external ID already exists. The field must be marked as an External ID and, for upsert, Unique.
• NetSuite's REST and SOAP web services support upsert semantics addressed by External ID, which similarly creates on miss and updates on hit.

Using the platform's own upsert is better than emulating it, because the create-or-update decision happens inside the target system's own transaction rather than across two round trips of yours.

While you are in there, audit for auto-numbering. If a record type is configured to auto-generate its identifier on create, that generated number is not a matching key — every insert produces a fresh one, which can make duplicates look intentional in reports.

Cleaning up the duplicates you already have

Once new duplicates have stopped, work through the backlog in this order:

1. Freeze the sync for the object type you are cleaning, or put it in update-only mode.
2. Build the duplicate groups with a query, not by eye. Group on the strongest available signals and export the candidates for review.
3. Choose a survivor per group on an explicit rule — usually the oldest record, or the one carrying posted transactions, since that one is hardest to move.
4. Merge, don't delete. In Salesforce, merging re-parents child records onto the survivor. In NetSuite, records with posting transactions generally cannot be deleted; re-point what you can and inactivate the rest.
5. Write the external ID onto the survivor on both sides before resuming, or the next sync will happily recreate what you just merged.
6. Keep the mapping of old ID to survivor ID. Historical reports and any downstream warehouse will need it.
7. Resume, and watch the write log for a full business cycle — including a month-end close, when volume and concurrency both peak.

The test that proves it is fixed

Reproduce the failure deliberately before you call it done.

Take a real sync event, send it three times in immediate succession, and confirm exactly one record exists afterwards. Then send the same event from three workers simultaneously and confirm the same thing. Then kill a worker mid-write and let the retry fire, and confirm it again. If all three produce one record, the integration is idempotent under replay, concurrent under load, and safe under partial failure — which is the whole problem.

Add those three as a recurring check against a staging instance, not as a one-time manual verification. Duplicate bugs come back when someone adds a new write path, and the new path is always the one nobody tested.

---

Duplicate records are rarely a data-quality problem. They are an architecture problem wearing a data-quality costume: the sync has no stable notion of identity, no guarantee that doing the same thing twice is the same as doing it once, and no ordering guarantee where it needs one. Fix those three properties and the duplicates stop being something you clean up periodically and start being something that cannot happen.

If you are dealing with this on a live system, enterprise integration work is a large part of what I do — including the unglamorous part where somebody has to decide which of the four Acme Corps is the real one.