The Nightly Duplicate User Factory
An HR CSV lands every night at 02:00 with about 25,000 rows. The transform map targets sys_user and coalesces on Email. Over the past three weeks, roughly 300 duplicate user records have appeared per week. About 4% of source rows have a blank Email; another 6% have mixed case or trailing spaces. Forty of the duplicates already have open incidents where caller_id points to the duplicate record. The integration runs as a service account with import_admin and user_admin. The HR vendor refuses to change the file format before next quarter. You need to stop new duplicates this week and clean up existing ones without breaking incident history.
1. Explain the root cause of the duplicate creation.
Read the worked answer
The transform map coalesces on Email. Coalesce is a lookup key: if the key value is blank, the lookup cannot match an existing record, so the transform inserts a new record. That explains the blank-email rows producing duplicates every night.
Mixed case and trailing spaces make the same person's email fail to match an existing record even when it is present. Email is also mutable (name changes, domain changes), so it is a poor identity key. A stable HR employee number is the correct identity key. If multiple fields are marked as coalesce, all must match, which is stricter, not looser; that is not the fix here.
2. Design the transform-map fix so new duplicates stop this week.
Read the worked answer
Change coalesce to u_employee_number (or the source employee id mapped to a unique field). In onBefore, trim and downcase the email for storage and normalize the employee id; if the employee id is missing, set ignore = true so the row is rejected and logged rather than inserted. Keep email as a non-coalesce field.
Add an onAfter or log to count rejects. Test with a small file containing blank, mixed-case, and duplicate rows. Do not use email as a fallback coalesce if it can be blank; if you must, only after confirming uniqueness. Do not let the service account's broad roles hide the problem.
3. How do you remediate the existing duplicates without breaking incident history?
Read the worked answer
Identify duplicates by employee id (group by, count > 1). Choose a survivor per employee: prefer the record with the most references and the oldest created date. Repoint references (caller_id, opened_by, requested_for, task references) to the survivor with a scripted update; do not delete first. Merge missing field values into the survivor, deactivate the duplicate, then delete after a reference check.
Never delete a user who is the caller on an open incident until references are moved. Re-run the duplicate query afterward to confirm the fix and to catch records created before the transform fix took effect.