Adobe Campaign Classic Business Practitioner Expert: The Overnight Import That Started Duplicating Recipients

The Overnight Import That Started Duplicating Recipients

A nightly workflow imports 180,000 customer rows from a vendor CSV into the nms:recipient schema. The import activity has 'Identify by' set to a concatenation of email and crmId, with reconciliation against the recipient table. For three weeks the load ran clean. Then the vendor began sending rows where crmId is an empty string for newly onboarded customers. The deduplication step downstream uses email only, but the import's identification key still includes the empty crmId. Ops reports the recipient table grew by roughly 60,000 rows per night, and the growth matches the count of blank-crmId rows. Existing recipients are now appearing twice in a welcome campaign audience, and a downstream segment on crmId is returning nulls for the duplicated profiles. The vendor cannot add crmId retroactively for two weeks.

1. Diagnose why duplicates appeared and explain what the import identification key is actually doing versus what the downstream dedup does.

Read the worked answer

Two different keys are in play and they disagree.

The import identification key is email + crmId. When crmId is '', the composite key for a given email becomes email + ''. If the existing stored recipient has crmId = 'X', its composite is email + 'X'. These are not equal, so the import does not recognize the row as an existing record and inserts a new one.
The downstream dedup keys on email alone, so it would have collapsed them - but it runs after the insert, and Adobe Campaign's dedup activity removes duplicates from the working set, not necessarily from the recipient table unless explicitly configured to delete. So the table keeps both rows.

The count matching blank-crmId rows confirms it: every blank-crmId row for an already-known email is a fresh insert. The null-returning crmId segment is a symptom of the duplicate rows, not a separate bug.

2. Propose a fix that works within the vendor's two-week constraint and protects the existing recipient data, including how you would prevent recurrence.

Read the worked answer

Short-term (within the two weeks):

Change the import identification key to email only, or better, a key that is stable when crmId is missing. Since email is the only field reliably populated, use it as the reconciliation key for this load.
Add a pre-import activity (e.g., a Query or Enrichment) that flags or drops rows where crmId = '' and the email already exists, so you don't overwrite a good crmId with a blank. Overwriting a populated crmId with '' via update is a real risk when you loosen the key - decide the update mode explicitly (update only non-empty fields, or use a reconciliation field list that excludes crmId).
Clean up the ~180k duplicates already created: run a targeted dedup on email that keeps the record with a non-null crmId, in a controlled workflow with a backup export first.

Prevention: make the vendor contract require crmId on all rows, add a validation step in the workflow that rejects rows missing crmId into a quarantine file for review, and document the identification-key rationale so a future change doesn't silently reintroduce the composite key. Test the change against a staging copy before pointing it at production.