Scaling Power Automate, Part 1: The Loop That Wouldn't Quit

This is Part 1 of a three-part series on scaling Power Automate flows that process thousands of Dataverse records. Part 2 covers finding the real bottleneck, and Part 3 covers throttling, retries, and designing for partial failure.
I recently built a flow that keeps a native Dataverse Contact Profile table in sync with a SQL-backed Dataverse virtual table holding identity data. On paper it's a simple job: for each contact, look up the matching identity record, compare a few values, update the contact. With a few hundred test rows it was fine.
Then the numbers got real. The Contact Profile population started in the low thousands and is heading toward tens of thousands. The identity virtual table already holds more than 300,000 rows. The flow still worked, but runs took hours, a batch quietly never ran, and at one point the flow processed the same 4,000 records over and over until it hit its timeout.
Here's what I picked up along the way. The short version: once a flow is handling thousands of records, tuning individual actions matters a lot less than the architecture around them. That means batching, cutting external calls, controlling concurrency, pushing joins back toward the data, handling throttling, and designing for partial failure. This first part covers the batching pattern and the two Do Until traps it walked me straight into.
How to read this series
I use three labels throughout so it's clear what kind of claim each one is:
Documented: behavior Microsoft documents (links in the References at the end).
Observed: what I saw in this specific implementation.
Recommendation: what I'd do again, or do differently next time, based on the first two.
Every timing in this series is a measurement from my environment: my licensing, my connector path, my SQL backend, my network, my time of day. None of them are benchmarks. Use them to understand proportions (what's slow relative to what), not to predict your own runtimes.
The problem
The original flow looked roughly like this:
Trigger
└─ List rows: Contact Profile (pagination on)
└─ Apply to each contact
├─ List rows: Identity — find the employee
├─ List rows: Identity — find the employee's Admin
├─ List rows: Identity — find the employee's Functional Supervisor
├─ Set variable(s) with the values to compare
└─ Update a row: Contact Profile
Nothing exotic. Every piece of it is something you'd build in your first month with Power Automate (Microsoft Flow, if you've been around a while). The trouble is that every inefficiency in that loop gets multiplied by the number of contacts. Production had 8,819 of them.
Why the obvious fixes weren't enough
My first instincts were the usual ones.
"Turn on pagination." Documented: the Dataverse List rows action returns 5,000 rows by default. Turning on Pagination in the action's settings lets you set a threshold up to 100,000, which gets rounded up in increments of the page size. The overall paginated-items limit is 5,000 on the Low performance profile and 100,000 on the others. Observed: with pagination on, List rows happily returned all 8,819 contacts. Getting the rows was never the problem. Doing something useful with each of them was.
"Turn up concurrency on the loop." I couldn't do it safely, because the loop was full of variables. That's a Part 2 story.
"Give it a longer timeout." That helped with one specific failure, but on its own it just lets a slow flow be slow for longer.
None of these were wrong. They just treated symptoms. The real fixes were structural.
Building the batching pattern
I wanted the work split into predictable chunks with a clear progress pointer. The pattern:
varBatchStart (Integer), initialized to 0
varBatchSize (Integer), initialized to 4000
A Do Until loop
Inside it, a Compose named Current Batch
An Apply to each over Current Batch
After the Apply to each, Increment variable varBatchStart by varBatchSize
The Current Batch expression:
take(
skip(
outputs('List_rows')?['body/value'],
variables('varBatchStart')
),
variables('varBatchSize')
)
The Do Until exits when:
@greaterOrEquals(
variables('varBatchStart'),
length(outputs('List_rows')?['body/value'])
)
With 8,819 records, that gives:
Batch | varBatchStart | Records |
1 | 0 | 1–4,000 |
2 | 4,000 | 4,001–8,000 |
3 | 8,000 | 8,001–8,819 |
(exit) | 12,000 | 12,000 ≥ 8,819 |
One thing worth being clear about: skip() and take() here operate on the array the flow already has in memory. They are not a server-side $skip (Documented: the Dataverse Web API doesn't support $skip at all). So batching like this doesn't reduce what List rows retrieves. It chunks the processing: bounded units of work, a single progress pointer, and a natural spot to log or checkpoint.
Recommendation: compute the total once in a Compose named Total Rows (length(outputs('List_rows')?['body/value'])) and reference that in the loop condition and anywhere else you need it. It reads better and you'll want it again in a minute.
If the population keeps growing past what one run should reasonably hold, the same idea extends to a "relay" design where each flow run processes a slice and hands off to the next. I didn't need that yet, but it's the natural next step.
The hidden Do Until failure modes
Batching introduced two failure modes I didn't see coming. Both are perfectly logical once you see them, which is the most annoying kind.
Failure mode 1: the timeout covers everything inside the loop
Documented: a Do Until has two limits: Count (default 60, max 5,000) and Timeout (default PT1H). The timeout applies to the whole loop, including all the actions inside every iteration. It's checked between iterations: an iteration that's already running isn't cut off, but once the timeout has passed, the next iteration doesn't start.
Observed: batch 1 (4,000 records) took almost exactly one hour. The iteration finished, the loop checked its timeout, and it exited. The flow looked fine at a glance. Batch 2 never ran. List rows had retrieved all 8,819 records; the loop just stopped asking for more.
The fix was to set the Timeout to PT6H and leave Count at 60. Count was never the constraint: 60 iterations × 4,000 records is 240,000 records of headroom. Time was.
Recommendation: add a guard right after the Do Until. If variables('varBatchStart') is still less than Total Rows, the loop exited early, so Terminate the run as Failed with a message saying how far it got. A loop that exits on a timeout should never look like a success.
Documented bonus gotcha: run history doesn't show the details of individual Do Until iterations until the whole loop completes. If you want to watch progress on a multi-hour run, log something (a row, a message) at the end of each batch.
Failure mode 2: one bad record, the same batch forever
This one gave my flow its own personal Groundhog Day.
One record in batch 2 failed. Because one iteration failed, the Apply to each was marked Failed. My Increment varBatchStart action was configured (by default) to run only when the previous action succeeded, so it was skipped. Then:
varBatchStart stayed at 4,000.
The Do Until condition was still false, so it looped again.
Current Batch produced the exact same records 4,001–8,000.
The same bad record failed again.
Increment was skipped again.
Repeat until the PT6H timeout finally ended it.
Thousands of good records were re-processed for hours because of one bad one.
The fix: open Configure run after on the Increment action and have it run when the Apply to each is successful, has failed, and has timed out. That changes the design from "one bad record stops everything" to "move forward and handle exceptions separately."
Why this matters so much for sync flows specifically:
Bad data is normal at scale. In 8,819 identity records, some email address will be malformed, some lookup will come back empty, some value will exceed a column length. A design that needs 100% clean data will fail on most runs.
Progress has to be monotonic. If a failure can rewind or freeze your progress pointer, you don't have a batching pattern, you have a loop.
The cost is asymmetric. One failed record costs one record. One failed record that blocks a batch costs thousands, plus hours of capacity.
Recommendation: treat run after on the Increment as the safety net, not the main mechanism. The main mechanism is handling per-record errors inside the loop so the Apply to each succeeds in the first place. I'll walk through that Try/Catch pattern in Part 3.
Up next
At this point the flow was batching correctly and moving forward past bad records. It was also still painfully slow: close to four hours for a couple thousand records. In Part 2, I'll dig into why, starting with the variables that were blocking concurrency and ending with the run-history numbers that pointed at the real culprit (it wasn't the one I expected).
I hope this helps someone out there battling a similar scenario.
Try. Fail. Learn. Repeat.




Comments