Duplicated Loyalty Emails After an Enrichment Was Added
A loyalty campaign has run daily for a year: Scheduler at 02:00, Query on recipients with loyalty_points > 500, Deduplication on @email set to 'keep first', Enrichment linking to the 9M-row orders schema to add last order date and amount, then an Email delivery. Until last month it targeted 240,000 recipients. Since the enrichment was introduced the delivery reports 512,000 target records, roughly 60,000 contacts receive the same email twice, and the run finishes at 02:47 instead of 02:20. Separately, an operator changed the scheduler to 'every 30 minutes' during a load test and never restored it; average run time is now 45 minutes. The orders schema holds up to 30 rows per recipient. The client's call centre is handling complaints about duplicate vouchers.
1. Explain why duplicates appeared and give the corrected design, naming the activity settings involved.
Read the worked answer
Two independent defects.
1. The deduplication is in the wrong place. It runs before the enrichment, so it can only remove duplicates that existed at that point. The Enrichment activity is a 1:N join: each matching order row produces a new line in the outbound transition, so 240,000 recipients fan out to 512,000 lines. The deduplication must be the last activity before the delivery.
2. It keys on @email. Addresses are not unique: shared family or company mailboxes are legitimately duplicated, and empty or malformed addresses collapse together. Keying on the recipient internal id is the safe choice; if a contact must never appear twice in one send, key on the internal id, not the address.
But the real fix is cardinality. The campaign only needs the last order date and amount, so make the enrichment return at most one line per recipient: order the returned rows by order date descending and enable the 'limit to the first matching line' behaviour, or aggregate in the enrichment (max of date, and a separate sum if an amount total is needed). The 512,000 figure is a fan-out count, not a real audience - deduplicating afterwards hides the symptom while you keep paying for 512,000 rows through the join and sort.
2. The scheduler has been left at 30 minutes while a run takes 45. Describe the failure mode and the guard you would put in place.
Read the worked answer
Failure mode. Adobe Campaign fires a new execution on each scheduler trigger; it does not wait for the previous one to finish. With a 45-minute run and a 30-minute period, execution n+1 starts while execution n is still writing its target. The loyalty score has not changed, so the same recipient is selected again, and the second delivery reaches them minutes after the first. You also get two large joins and two delivery preparations competing for the same database connections, which is why the run time climbs further and the log tables grow.
Fix, in order.
- Restore the intended schedule first - that is the root cause.
- Make the workflow idempotent regardless of the schedule. Add a guard at the start: a JS or Test activity that checks a small control table for a successful run for the current business date, and routes to End if present. Write the flag only after the delivery has been started, and include the date in the delivery label so a manual check is possible.
- As a safety net, add a typology rule capping the same offer to one message per recipient per 24 hours; it will not fix a broken workflow but it protects customers.
The guard must be a persisted state, not a workflow variable - instance variables do not survive between executions.
3. The enrichment accounts for 12 of the 22 extra minutes. Propose a cheaper pattern and say what you would monitor afterwards.
Read the worked answer
Move the aggregation off the critical path.
Precompute. A separate incremental workflow runs after the order feed lands and aggregates orders per recipient into a narrow table (or fields on an extended recipient schema): last order date, last order amount, order count, plus a technical lastChanged timestamp. It processes only orders created or modified since the previous run, so its cost stays proportional to daily activity rather than to 9M rows.
The loyalty workflow then joins 1:1 on an indexed key, and the enrichment no longer fans out at all - removing both the 12 minutes and the duplicate-line problem at the source.
Trade-offs to accept openly. The value is now one day old; state that in the campaign brief. The aggregation workflow becomes a new dependency that must succeed before the loyalty run, so add an alerting step and a freshness check (fail the loyalty workflow if the aggregate has not been refreshed today).
Monitor afterwards: run duration trend, rows in the aggregate table, outbound transition counts versus expected target size (a jump signals fan-out again), and delivery log counts versus the target count for the same run.