Power Apps Data Architecture: Dataverse vs Azure SQL, Delegation Limits and Performance

Most technical mistakes announce themselves — a page errors, a build fails, a query times out. The Power Apps data-source mistake does something worse: it works, and it lies. An app that exceeds a delegation limit does not crash; it silently processes only the first tranche of rows (500 by default, up to a configurable cap) and returns a total, a count or a filtered list that looks right and is wrong. The demo passes because the test data is small. Production fails quietly because the data is not.

This is why the Dataverse-versus-Azure-SQL question has to be answered at design time with delegation in mind, not treated as an implementation detail. The two options behave differently against Power Apps' delegation model, and the difference determines whether your app is correct at scale or confidently incorrect.

Delegation is the mechanism by which Power Apps pushes a query — a filter, a sort, an aggregate — down to the data source to execute, rather than pulling rows into the app and processing them locally. It matters because the app only ever retrieves a limited number of rows (the delegation limit); anything the source cannot handle is done on that limited set. A delegable filter runs across all two million rows; a non-delegable one runs across the first 500 and silently ignores the rest.

Which functions are delegable depends on the connector. Dataverse has broad delegation support; Azure SQL supports delegation for many operations but not all, and the gaps are where apps go wrong. The practical discipline is the same for both: know which functions delegate for your source, watch for the delegation warnings in the maker studio, and design queries to stay delegable — never assume a filter or aggregate ran across the whole table just because it returned a number.

Dataverse is the native Power Platform data platform and the default for app-centric data. It brings a rich security model (table, row and field-level security tied to Entra ID), relationships, business rules, calculated and rollup columns, auditing, and strong delegation support — so the correctness trap is far smaller. For data that belongs to the app and its processes, where governance and security matter and volumes are moderate, Dataverse is usually the right answer and the lowest-risk one.

The costs are real but predictable: Dataverse consumes capacity (database, file and log) that is licensed, and modelling data into it is an exercise in its own right rather than pointing at an existing table. For a greenfield app with its own entities, that overhead buys security and delegation you would otherwise have to engineer; for a thin app over a large existing relational store, it can be unnecessary duplication.

Azure SQL suits apps over large, existing relational data, heavy transactional volume, or a system of record that other applications also use. Data teams know it, it scales cost-effectively, and it avoids duplicating a warehouse of data into Dataverse just to surface it in an app. For an app that reads and writes to data the business already manages in SQL, pointing Power Apps at it directly is often the pragmatic, cheaper choice.

The catch is delegation: the Azure SQL connector delegates many operations but not all, and the non-delegable gaps are where the silent-wrong-number risk concentrates. The mitigations are design-time — use views or stored procedures to shape data into delegable patterns, keep the app's queries within the delegable function set, filter server-side, and never rely on client-side processing across a large table. Done with that discipline, Azure SQL is a strong backing store; done naively, it is where apps quietly break.

It is not a binary choice, and the best architecture is frequently both. Use Dataverse for the app-specific entities that benefit from its security, relationships and delegation — the records the app itself owns and governs — and Azure SQL for high-volume or system-of-record data that lives elsewhere and is shared with other systems. The app reads from each where it fits, rather than forcing all data into one store.

This mirrors a sound data architecture generally: the operational system of record stays where it belongs, and the app layer holds only what it owns. In a Microsoft estate it also composes cleanly with the rest of the stack — Dataverse and Azure SQL both surface into Fabric for analytics, so the transactional app and the reporting layer share a coherent foundation rather than diverging.

The commonest failure is ignoring delegation warnings until production exposes them, by which point the fix is a re-architecture under pressure rather than a design decision made calmly. No data source removes the need to design for delegation; it only changes which functions are safe. Treat every delegation warning as a defect, not a suggestion.

Cost and governance also cut both ways: Dataverse capacity costs can surprise teams who modelled a large dataset into it unnecessarily, while Azure SQL used naively pushes security and access logic into the app where it is harder to govern than Dataverse's built-in model. And a hybrid adds its own complexity — two data layers to secure, monitor and keep coherent — so it is the right answer when the data genuinely has two homes, not a default to reach for because choosing is hard.

Decide the data layer from the data's nature and the delegation behaviour, before building screens.

• Classify the app's data: app-owned entities (lean Dataverse) versus large or shared system-of-record data (lean Azure SQL)
• For any source, list the queries the app will run and confirm each is delegable for that connector
• For Azure SQL, shape data into delegable patterns with views or stored procedures before the app grows
• Treat every delegation warning in the studio as a correctness defect to resolve, not ignore
• Test against production-scale data volumes, not demo data — the trap only appears past the delegation limit