Your Enrichment Job Runs Twice and Corrupts Data Silently

A Clay table re-ran overnight because someone bumped a source list, and by morning it had created 1,400 duplicate contacts, burned a second round of enrichment credits on rows it had already enriched, and pushed a second batch of the same records into Salesforce. The enrichment provider charged us twice for the same lookups. The CRM had two of everything. Nobody wrote a bug. The job did exactly what it was told to do, twice.

For years the default has been fire-and-forget: ship the automation, trust that it ran, move on. That works until the run repeats, and it always repeats. A webhook retries, a scheduled sync double-fires on a daylight-savings boundary, an operator re-runs a table to fix one row, a queue replays after an outage. None of those is a bug in your job. They are the normal weather of a connected stack. The fire-and-forget model has no answer for the second run, so the second run corrupts data, and it does it silently: no exception, no alert, just a record count that quietly doubled. The curve below is what that looks like when nobody built a guard, plotted against the number of times the job re-fires.

0Run 1Correct: 700 clean records created

The slope of that line is the cost of fire-and-forget. Every retry adds the full 700-row batch again because the job has no memory of having run. A single external-ID field and a freshness gate flatten it to zero, and the rest of this piece is the four-part build that gets you there: the key, the lock, the backoff, and the dead-letter.

Idempotent means the second run is a no-op

An operation is idempotent if running it N times leaves the system in the same state as running it once. Deposit $100 is not idempotent. Set balance to $100 is. The GTM version: “create a contact for this email” is not idempotent, because run two makes a second contact. “Ensure a contact exists for this email, and if it does, update it” is.

The reason this bites enrichment specifically is that enrichment jobs are expensive and stateful at the same time. Every row you process costs a credit or a per-record charge. Every row you push mutates a downstream system. So a non-idempotent enrichment job does not just make a mess, it makes an expensive mess and it makes it in your system of record. This is not a novel problem: Stripe has required an Idempotency-Key header on every write request for a decade precisely because payment retries would otherwise double-charge, and the enrichment case is the same shape with a credit balance standing in for a card.

The three places it breaks

No dedup key, so inserts pile up. The default Clay-to-Salesforce path, and the default for most tools, is “create record.” If the source runs again, you get another create. Multiply by list size. Our 1,400 dupes were all “create contact” actions with no notion of “does this already exist.”

No processed marker, so credits burn on rows already done. The enrichment step itself has no memory. Row 900 got a company lookup last night. It has no flag saying so. The re-run enriches it again, pays again, and gets the same answer.

No write guard, so downstream fires twice. Even if you dedup the record, the “notify owner” or “push to sequence” side effect fires on every run. Two Slack pings, two sequence enrollments, one confused rep.

Break point	Symptom on run two	The fix
No dedup key	Duplicate records pile up	Stable natural key + upsert
No processed marker	Paid lookups repeat, credits burn	Enriched_At__c + freshness gate
No write guard	Side effects fire again	Gate on state transition, not presence

Each of those maps to a rung of the build order below. The key handles the record write, the freshness gate handles the paid lookup, the state guard handles side effects, and backoff plus a dead-letter queue handle the retries that trigger all three in the first place.

The idempotency build: key, lock, backoff, dead-letter

The fix is not one switch. It is four layers, applied in order, and the run-it-twice test at the end verifies all four at once.

1. 1

   1. Key: a stable natural key + upsert, never insert

   Pick the value that identifies the real-world thing: normalized email for a person, normalized domain for a company. Store it in a case-insensitive External ID field and upsert on it. Salesforce matches on the key and updates in place, so run two mints no twin. This is the layer that flattens the corruption curve from the hero.

2. 2

   2. Lock: one in-flight run per key

   A retry that fires while the first run is still writing races itself. Take a short-lived lock on the job (or the batch key) so a second invocation sees "already running" and exits. In Clay this is a run-state flag; in a script it is a row in a locks table with the batch id and a TTL. The lock is what stops two overlapping re-runs from both passing the "does this exist yet" check before either has written.

3. 3

   3. Backoff: exponential retry with jitter, not fixed interval

   When a call fails, retry with exponential backoff and random jitter so a thundering herd of retries does not hammer the provider in lockstep. AWS documented that adding jitter cut retry volume by more than half. Fixed-interval retries are how a transient 500 becomes a synchronized retry storm that trips a rate limit.

4. 4

   4. Dead-letter: park what still fails, do not silently re-fire

   After a capped number of backed-off retries, move the failed record to a dead-letter queue instead of looping forever. A record that fails five times is a record a human needs to see, not one the scheduler should keep re-attempting at 2 a.m. The dead-letter queue turns a silent infinite retry into a visible, bounded list.

Layers 1 and 2 make a single run safe to repeat. Layers 3 and 4 make the retries themselves well-behaved so you are not generating more repeats than you have to. Skip the key and every retry duplicates. Skip the lock and two concurrent retries both slip past the existence check. Skip the backoff and you turn one failure into a storm. Skip the dead-letter and a poison record retries forever, burning credits on every loop.

Layer 1 in detail: the key and the freshness gate

In Salesforce, the key means an External ID field and the upsert API, not insert:

# Non-idempotent: every run creates
INSERT Contact (Email, Company) VALUES (...)

# Idempotent: keyed upsert, second run updates in place
UPSERT Contact
  MATCH ON Enrichment_Key__c        // lower(trim(email))
  SET Company__c, Title__c, Enriched_At__c = NOW()

The External ID does the work. Salesforce matches on it, so the second run finds the existing record and updates it instead of minting a twin. Enrichment_Key__c should be a unique, external-ID, case-insensitive field carrying the normalized value, not the display email.

For the credit-burn problem, the processed marker gates the paid step. Before the enrichment call, check: does this row already have Enriched_At__c inside the freshness window? Firmographic data decays around 22 percent a year, so “fresh” might be 90 days. If yes, skip the paid lookup.

-- The freshness gate: only pull rows that are new or stale.
-- Everything fresh is skipped, so the re-run pays for nothing.
SELECT id, email
FROM   enrichment_batch
WHERE  enriched_at IS NULL
   OR  enriched_at < NOW() - INTERVAL '90 days';

In Clay that is a conditional run on the enrichment column. For side effects, gate them on a state transition, not on presence. Send the Slack ping only when Enriched_At__c goes from null to set, not every time the row appears in the run. The cleanest version makes the notification itself keyed: “notify once per contact per enrichment event,” with the event id as the dedup key.

The run-it-twice test

Here is the test I now run before any enrichment automation goes live. It takes ten minutes and it verifies all four layers of the build.

1. 1

   Snapshot a fixed sample

   Point the job at 50 known rows. Record the target state: record count, a hash of the enriched fields, the provider credit balance, and a count of side effects fired.

2. 2

   Run once, snapshot again

   Let the job do its normal work on the sample. Take snapshot two. This is your correct-once baseline.

3. 3

   Run again with zero input changes

   Fire the exact same job a second time, no source edits. Take snapshot three. This is the run that exposes the leak.

4. 4

   Assert nothing moved

   Between snapshot two and three: record count identical, field hash identical, credit balance unchanged, side effects fired zero more times. Any movement means the job is not idempotent.

The pass condition is strict, and every line maps to one of the break points and its layer.

Metric	Between run 1 and run 2 must be	Catches	Layer that fixes it
Record count	Identical	Missing dedup key	1. Key
Field hash	Identical	Unstable writes	1. Key
Credit balance	Unchanged (zero paid lookups)	Missing processed marker	1. Freshness gate
Side effects fired	Zero additional	Missing write guard	1. State guard
Concurrent re-run	No double-create	Missing lock	2. Lock

If any of those move on the second run, the job is not idempotent and you have found the exact leak before it hits 1,400 rows in production instead of after.

View as table

Item	Value
Records added	50
Credits burned	50
Slack pings	50

Non-idempotent versus idempotent, side by side

	The 1,400-dupe morning	After the four-layer build
Second run creates	700 more contacts	Zero, matches and updates in place
Enrichment credits	Charged again for the same lookups	Skipped, fresh rows never re-fetched
Concurrent retries	Both slip past the existence check	Lock rejects the second in-flight run
Failed rows	Retry forever, burn credits each loop	Parked in a dead-letter queue for a human
Downstream side effects	Two Slack pings, two enrollments	Fire only on null-to-set transition
The run-it-twice test	Fails every line	Passes every line

The 1,400-dupe morning failed every line of that test. Record count doubled, credits dropped, Slack lit up. Once the key, the freshness gate, the lock, and backed-off retries were in, the second run moved nothing, and the corruption curve from the top of this piece flattened to a line pinned at zero no matter how many times the job re-fired. That is the entire signal: a second run that changes state is a bug, even when it looks like the job “worked.”

Why this compounds across a 25-tool stack

The reason idempotency is not a nice-to-have is that it fails silently and it fails everywhere at once. Across a two-dozen-plus tool stack, a webhook retries, a scheduled sync fires twice on a daylight-savings boundary, an operator re-runs a table to fix one row, and a downstream integration replays a queue after an outage. Each of those is a step to the right on the hero curve. A non-idempotent job treats every one as a fresh instruction to create, charge, and notify again, and stacks another batch on the pile.

The cost is not linear either. One duplicate contact is an annoyance. Fourteen hundred duplicate contacts is a dedup project, a set of confused reps working the same account twice, a routing model that splits one company across three owners, and a data-quality number that quietly drops below the threshold your AI-readiness gate checks for. The job that ran twice did not just double a count. It seeded work in every system that reads from your CRM. Building idempotency once, at the key, prevents all of it.

Where each layer has to live in the pipeline

One subtlety worth stating: idempotency is not a single switch you flip at the end. It has to hold at every stage the data passes through, because a re-run replays the whole pipeline, not just the last step. The dedup key protects the write to your system of record. The freshness gate protects the paid enrichment step. The state-transition guard protects the side effects. The lock protects against two overlapping runs. Backoff and the dead-letter queue protect the retries themselves. Fix only the write and you still burn credits on the re-enrichment. Fix only the enrichment and you still fire two Slack pings. The 1,400-dupe morning was expensive precisely because every layer was unguarded at once, so the re-run did maximum damage at every stage.

The clean mental model is to ask, at each step, “if this exact row arrives again, what should happen?” For the record write, the answer is “update in place, do not create.” For the paid lookup, “skip if fresh, pay only if new or stale.” For the notification, “fire only on the transition that matters.” For a failure, “back off, retry a bounded number of times, then park it.” Every step gets its own small guard, and none of them is hard. The failure is that nobody asked the question, because the job worked the first time and shipped.

The one design rule that prevents all of it

Design every automation so the second identical run is a no-op, and treat any state change on that second run as a bug to fix before launch. That single rule forces the key, forces the freshness gate, forces the lock, forces the backoff and the dead-letter, because those are the only ways to satisfy it, and the run-it-twice test verifies it in ten minutes. An automation that passes is safe to schedule, safe to retry, safe to let an operator re-run to fix one row.

Go find your most expensive scheduled enrichment job, the one with a per-record charge, and run it twice against 50 rows tomorrow morning. Watch the credit balance. If it drops on run two, you are paying for the same data on every cycle, and you are one step to the right on that corruption curve with every retry the stack throws at you. The fix is one external-ID field, a freshness gate, a lock, and backed-off retries away. This is the same data-quality discipline that decides whether an AI agent accelerates you or scales your mess, and the same discipline that keeps a cleaned-up automation pile from re-corrupting the moment a job re-fires.