When Power Automate Hit Its Ceiling: Scaling to 400,000 Validations a Day

Based on architecture I designed in a previous role. A case study for Scaling Power Platform with Azure: Patterns for When Low-Code Hits Its Ceiling.

In brief. An environmental organisation was relying on an unsupported desktop application to validate sample data. We replaced it with Power Platform and an orchestration engine on Azure that now runs around 400,000 validations a day, with batches completing in under an hour and results available as each sample finishes. The return came less from the new software than from removing the manual work, waiting and risk that had built up around the old one.

Part 1: Where the value came from

The cost was in the work around the application, not the application itself

Asked what was wrong with the old system, most people would have described the interface. It was dated, slow and awkward. But the larger cost sat outside it.

Sample data arrived in spreadsheets and had to be uploaded by hand. Validation ran one check after another, so a day's data took hours to clear. When people needed something the system couldn't hold, they stored it in fields meant for something else, and the meaning of that data lived in a few people's heads. Getting information out for analysis meant several rounds of extracting and reshaping before anyone could use it.

Underneath all of that was a continuity risk. The expertise to maintain the application had gone, and parts of the validation software were no longer supported. The organisation was depending on something it could no longer fix.

A like-for-like rebuild would have produced a nicer screen on top of the same process. That was not the goal.

Four sources of return

When I look back at where the value actually came from, it falls into four areas. None of them is "we moved to the cloud".

Capacity. Manual uploads, sequential processing and repeated extracts consumed staff time every day. Automating the validation and the onward flow of data gave that time back to work that needs expert judgement.

Speed to decision. Validation that had taken hours now completes in under an hour, and users don't wait for the batch at all. They see each sample's results as soon as it is done. For people acting on environmental data, the time until they can trust a result matters more than how long the whole run takes.

Risk reduction. Retiring unsupported software removed a single point of failure. Moving data out of repurposed fields and into a proper model, with proper security, made it easier to audit and harder to misinterpret.

A foundation that keeps paying. Validated data now flows automatically into the organisation's data platform. Each future report or analysis starts from clean, current data rather than another manual extract, so the return grows with every new use.

On cost, the validation engine runs on consumption-based hosting. It scales up for the daily peak and costs very little when idle, which suits a workload that arrives in bursts.

What digital transformation should actually be for

Projects like this are often judged by technology milestones: the old system switched off, the data migrated, the new platform live. Those are necessary, but they are not the point.

The point is to change how work flows through the organisation. In this case that meant less handling, less waiting and less dependence on knowledge that existed only in people's heads. The technology choices followed from that intent, not the other way round.

Three principles guided the work, and they apply well beyond environmental data.

Design for the moment someone can act. Total processing time is an engineering measure. The time until a person can use a result is a business one, and it can lead to a different design.

Remove the work around the system, not just the system. Uploads, re-keying, repurposed fields and manual extracts are where much of the cost hides. They rarely appear in a requirements document, so you have to go and look for them.

Build foundations that compound. Connecting operational data to the wider data platform costs little more at the time, but every later question about that data becomes cheaper to answer.

Part 2: How the validation engine works

For architects and engineers.

Why validation at this scale is a dependency problem

Each sample contained many data points, and each data point needed several checks. Some checks were independent of each other. Others needed the results of earlier checks, and some limits were calculated by formula from those results.

That rules out the two simple options. Running every check in parallel ignores the dependencies. Running them in sequence wastes time on checks that could have run together, which is exactly what the old system did.

We built it in Power Automate first, and it didn't scale

The first version of the new validation engine was built in Power Automate. It was functionally correct, and for modest volumes it would have been fine.

At around 400,000 validations a day it struggled. Coordinating thousands of concurrent pieces of work, waiting on dependencies between them and reliably bringing the results back together is not what Power Automate is built for. Dataverse plugins weren't an option either, because they run within strict execution time limits and are meant for short transactional logic.

So I moved the engine to Azure Durable Functions. The change brought a large reduction in processing time, and it was the decision the rest of the design depended on.

Durable Functions suited the problem for three reasons. Fan-out/fan-in and waiting on prior results are built-in patterns, written as ordinary code. Orchestrations checkpoint their progress, so long-running work survives restarts and failures without starting again. And consumption hosting scales out for the daily peak and back down afterwards.

Signs you've outgrown Power Automate

The signals usually appear well before a move becomes urgent. If you recognise several of these in your own flows, the patterns in the guide are worth a look.

• Loops over large record sets that run for hours, even with concurrency turned up
• Steps that depend on each other being forced into a strict sequence
• Retries and manual reruns becoming part of the daily routine
• Daily request quotas or throttling showing up in run history
• Logic for one process spread across many flows that are hard to change safely

Batch, sample and check: the three levels of orchestration

The engine is organised around three levels.

1. A batch orchestrator receives the incoming samples and starts one sub-orchestration per sample.
2. Each sample's sub-orchestrator coordinates that sample's checks. It fans out the independent checks to run concurrently, waits for their results, then runs the checks that depend on them.
3. Activity functions carry out the individual validations, including evaluating formula-based limits against earlier outputs.

Progressive results came almost for free

Because every sample has its own sub-orchestrator, each one can write its results back to Dataverse the moment it finishes. Users see completed samples while the rest of the batch is still running.

This wasn't a separate feature. It fell out of choosing the sample, the unit users actually care about, as the unit of orchestration. Had we orchestrated per batch, we would have optimised total run time and still left users waiting for the slowest sample.

From operational system to analytical source

Validated data flows automatically into Azure Data Lake and from there into the organisation's data platform. The operational system stopped being something data had to be extracted from and became a source the rest of the organisation could build on.

What I'd do the same way again

Map the dependencies before choosing the technology. Knowing which checks were independent, which needed earlier outputs and where calculated limits came in is what made the orchestration design obvious.

Treat low-code as the right starting point, and know where its ceiling is. Building in Power Automate first was not wasted effort. It proved the logic, and it showed exactly where we needed something built for orchestration.

Choose the unit of orchestration to match the unit of value. That single decision gave us progressive results without any extra work.

Facing something similar?

Before scoping a replacement, follow one piece of data through its whole journey: how it is captured, validated, corrected, extracted and finally used. The hand-offs and waits along the way will show you where the real return is.

If you'd like help tracing that journey in your own organisation, get in touch.