The Macro That Runs 40,000 Times
A batch macro is used to call a REST API once per region_id, then parse and union the JSON responses. There are 40,000 regions. The macro currently contains the Download tool, a JSON Parse, a Select, and an Output Data tool that writes a per-region CSV. A full run takes 6 hours and occasionally fails partway, forcing a restart from scratch. The team wants incremental behavior: skip regions already written, and cut runtime. They also noticed the macro rewrites the same schema 40,000 times and that the Output tool inside the macro is a bottleneck.
1. Identify the performance and reliability anti-patterns in this macro design.
Read the worked answer
Several anti-patterns compound:
- Output inside the macro, per iteration. Writing 40,000 small CSVs means 40,000 file opens, 40,000 schema writes, and no atomicity. This is the biggest bottleneck and the reason a crash loses everything.
- No checkpointing / idempotency. A restart re-does all 40,000 calls because there's no record of what succeeded.
- No batching of the API calls. One region per call maximizes latency; if the API supports multi-region queries, batching cuts round-trips dramatically.
- JSON Parse on every iteration re-parses and re-derives schema; schema churn 40,000 times is expensive.
- No error routing. A single failing region can abort the batch; there's no
E/error path to log and continue.
2. Redesign the macro and its driver to be resumable and faster, keeping the per-region detail.
Read the worked answer
Move output out of the macro and make the run idempotent:
- Driver query first: read the list of
region_idfrom a source of truth (or a "done" log) and anti-join (Join on region_id, keep Left-unjoined) against the set of already-completed regions. Only unprocessed regions enter the batch. - Batch macro (no Output tool inside): Download → JSON Parse → Select, emitting parsed rows on the output anchor. Keep the macro pure (transform only).
- Single Output after the macro: write one consolidated file (or partitioned output) once per run, not once per region. If per-region files are mandatory, write with a stable filename pattern and append a completion log row per region after the write succeeds.
- Error handling: route API failures to an error log with region_id and status; do not abort the whole batch. Retry transient failures with a bounded retry.
- Resumability: the completion log is the checkpoint. On restart, the anti-join skips completed regions automatically.
- Batch API calls where the endpoint allows multiple regions per request to cut round-trips.
This turns a 6-hour all-or-nothing run into an incremental, restartable one where progress is durable.