The 03:00 workflow that now finishes at 08:40
An ACS org runs a nightly workflow: Incremental query on profiles changed in the last day (typically 900,000), enrichment with a custom Store resource, deduplication, split into three regional branches, then three deliveries of about 300,000 each. It used to finish at 04:30 and has drifted to 08:40 over three weeks, twice delaying the morning transactional queue. Interim results are kept for 30 days and the database has grown from 6 to 11 million profiles. Each delivery preparation takes 70 minutes, 40 of them in analysis. A new loyalty events custom resource with 22 million rows was linked to profiles and is used in the enrichment. The query filter applies an upper-case function to the email domain. Regional branches are chained so one waits for the previous one. There is no alerting; the team hears about problems from the business.
1. Build a prioritised diagnosis. What evidence would you collect first, and which hypotheses does the timeline support?
Read the worked answer
Collect evidence before changing anything. The workflow execution log gives per-activity duration and status, and row counts between activities show which step inflates or stalls.
Priors, in order:
- The upper-case function on the email domain makes the filter non-sargable. The database cannot use an index on a transformed column, so an incremental query on 11 million profiles becomes a scan. That alone can add an hour.
- The enrichment now joins 900,000 rows to a 22 million-row resource. If the join key is not indexed and the relationship is many-to-one, the engine can do per-row lookups. Drift over three weeks matches data growth, which a configuration change would not.
- Three deliveries at 70 minutes each is 3.5 hours by itself, and the chained regional branches serialise them. This part is not drift; it is the baseline the other two are pushing over the edge.
- Thirty days of retained interim results bloats work tables and slows every temporary-table creation and cleanup.
Test on a 10% sample: remove the enrichment and normalise the filter. If preparation drops to 15 minutes, the model is the cause, not the server.
2. Propose the fixes you would make to the workflow itself, in the order you would apply them, and say what you expect each to buy.
Read the worked answer
Order matters, because the cheapest structural fix changes the size of everything downstream.
First, the filter. Store a normalised domain (lower-case, trimmed) at write time and filter on equality against a normalised value, so the index can be used. If the field cannot be indexed on a custom resource, filter on lastModified and the profile key instead, and apply the domain condition later to a smaller population. Expect the largest single gain here.
Second, reorder: query, deduplicate, split, then enrich each branch. The 22 million-row join then runs on 300,000 rows per region instead of 900,000 once. Better still, if the store attributes are stable, flatten them onto the profile or a small store snapshot refreshed nightly, turning a large join into a narrow one.
Third, parallelism. Run the three regions as separate workflows with their own schedules and stagger them, so a failure or a slow preparation in one region does not hold the other two and does not collide with the transactional queue.
Fourth, housekeeping: turn off interim-result persistence, shorten history to what debugging needs, and purge old work tables. Expect no single dramatic gain from this, but it removes a slow, compounding tax on every run.
3. The team currently learns about failures from the business. Design monitoring and guardrails that would have caught this drift before an SLA miss.
Read the worked answer
Add three layers.
Per-run instrumentation: write row counts and elapsed time per stage into a small audit resource or the workflow log, so drift is visible across days rather than discovered after three weeks. A single run at 08:40 is an incident; a trend from 04:30 to 05:10 is a warning you can act on.
A watchdog: a separate lightweight workflow that runs at 05:30 and checks whether the day's deliveries have reached the expected status or broadLog volume. If not, branch to an Alert activity to the operator group. Because it is separate from the main workflow, it still fires when the main workflow is stuck, which is exactly when alerting matters.
Platform-level: review execution history and job monitoring for failed or long-running jobs, and alert on the delivery's own audit and exclusion counts so a silent fall in target volume is caught as well as a failure.
Finally, a change guard. The loyalty events enrichment was added to a nightly job without a performance test on realistic volume. Require a timed sample run for any new join against a large resource, and treat duration trend as a capacity signal rather than a recurring incident.