The Overnight Report That Times Out
A nightly process model generates a 40,000-row reconciliation report. The design is a single subprocess that loops over a query result of 40,000 record rows using an XOR gateway, writing each row into a process variable list before calling a once-per-night integration. In production the instance fails at roughly the 30-minute mark with a transaction timeout, and the process administrator sees a growing activity-class instance count. The integration endpoint accepts at most 500 rows per call and returns a per-row status. The business needs the report by 06:00, and the source data is stable between 01:00 and 05:00. Stakeholders want to know whether to raise the timeout, split the loop, or redesign.
1. Diagnose why the current design fails and recommend a redesign.
Read the worked answer
The loop accumulates a 40,000-row list in a single process variable and executes 40,000 gateway cycles in one transaction. Each cycle writes the variable, so the transaction grows monotonically and eventually exceeds the engine's transaction timeout. Raising the timeout only postpones the failure and risks memory pressure; it does not fix the unbounded transaction.
Redesign around bounded work units:
- Query the source once into a data store or staged record (not a process variable).
- Use a subprocess with a batch size of 500 - either a
MNmulti-node or a looping subprocess - so each transaction handles at most 500 rows and calls the integration once per batch. - Persist per-row status to a record so progress survives a restart; do not hold it in a variable.
- Add a checkpoint: if the process restarts, resume from the last unprocessed batch keyed by a batch ID.
With 80 batches of 500 and a stable 4-hour window, the run completes well inside 06:00 even at modest per-batch latency. The activity-class count should flatten rather than grow, because each batch transaction closes before the next opens.
2. The integration returns per-row failures for about 2% of rows. How should the process handle them without failing the whole night's run?
Read the worked answer
Do not let a partial failure abort the instance. Treat the batch call as a soft failure boundary:
- Record each returned status against its row in the status record.
- For rows marked failed, write them to a retry queue record with attempt count and last error.
- Continue to the next batch; the night's run completes and produces a report showing 98% success plus a retry list.
- A separate short process (or a scheduled retry) reprocesses the queue a bounded number of times, then escalates to a human task for manual resolution.
Only escalate to a hard error (process error / alert) for infrastructure failures - endpoint unreachable, authentication failure - where retrying the same batch is pointless. The distinction matters: a per-row business rejection is expected data, not an exception.