External-ID Upsert vs Custom Idempotency Table for Retry-Safe Writes in Salesforce

Context

When Apex callouts or asynchronous jobs fail transiently and are retried, the same logical write can execute multiple times. Two documented patterns prevent duplicate records: marking a field as an External ID and using upsert, or maintaining a custom Idempotency Key object that stores a hash of the request payload with a processed flag.

Trade-offs

External-ID upserts rely on the platform’s native matching logic and require the field to be Unique or Case-Sensitive Unique; they add no extra storage but constrain the deduplication key to a single field on the target object. A custom idempotency table decouples the deduplication key from the business schema, supports composite keys, and works across objects, yet it consumes additional data storage, demands a cleanup strategy, and adds a read-before-write round trip.

Open Decision

Both approaches handle at-least-once delivery from Platform Events or Bulk API retries, but neither is a silver bullet. The choice hinges on whether the team prefers zero-schema-overhead simplicity or cross-object flexibility with operational overhead.

Which pattern scales better when the deduplication key must span multiple fields across unrelated objects? How do storage costs and cleanup complexity compare at millions of monthly retries? Does the lack of a native idempotency header in the REST API shift the balance toward one approach?