The nightly consent file that sometimes arrives twice
Every night at 01:00 a Scheduler starts a workflow that pulls a CSV of 250,000 to 400,000 consent updates from an SFTP external account, imports it, updates profiles, then waits on an External signal before the downstream delivery workflow proceeds. The partner sometimes delivers the file late, and sometimes twice. When it arrives twice, the second run re-sends roughly 40,000 duplicate SMS. When it is late, the delivery workflow sits in a waiting state until 09:00 and operators restart it manually, occasionally running the delivery twice. The workflow keeps interim results so the team can debug. There is no alerting: the team learns about failures from the business. Compliance also wants an auditable answer to the question of which consent state was used for each send.
1. How do you guarantee at most one SMS per profile per day when the same file can be processed twice? Describe the data-level controls and where they sit in the workflow.
Read the worked answer
Idempotency has to live in the data, not in the schedule. Every consent event in the file should carry an event ID, or at least a stable composite key of profile key, consent type and effective timestamp. Import into a staging custom resource whose deduplication key is that event ID, so a re-imported row is rejected or flagged instead of creating a new event.
Then:
- Deduplicate inside the file on the event ID, with a deterministic rule such as keep the latest effective timestamp.
- Keep a processed-events store, or mark rows in staging after the send.
- Build the delivery target as a query over staging rows not yet marked processed. After the send, an Update data activity marks them processed in the same run, so a second run finds nothing to do.
- Add a second, independent guard: exclude profiles that already have a broadLog entry for the same delivery label today.
- Keep a Deduplication activity on the profile key in front of the delivery, because one profile can legitimately appear in several consent rows.
The success criterion is not a lower duplicate rate. It is that replaying the identical file yields zero new rows to send. Test that in staging before the next live drop.
2. The delivery waits on an External signal and operators restart it manually. Redesign the orchestration so a late or missing file cannot deadlock the delivery, and explain what makes the restart safe.
Read the worked answer
Remove the unbounded wait. Split into two workflows. A poller runs every 30 minutes, transfers the file from the external account, tests presence and format, imports into staging and validates the expected volume. A delivery workflow is triggered only when the poller reports a good load. In the poller, use a Test activity on the import or volume check to branch to success, retry, or an Alert activity to the operator group.
Add a business deadline. At 06:00 the delivery workflow runs with the last known consent state, or is explicitly cancelled with an alert; either way the decision and the consent dataset version are logged. Replace the External signal with this deterministic trigger, or keep the signal and add a deadline branch that takes the no-data route.
Restart safety comes from isolation: the send sits in its own workflow with one trigger and one owner, not in a waiting state where two operators can each restart it. The send step is guarded by the processed-events check, so a restart after a partial failure re-targets only what is left. Alert and execution logs show which path was taken, which also answers the compliance question about which consent state was used.