Almanac
Microsoft/power-appsPower Platform

Consultant KB for Microsoft Power Apps: canvas apps, model-driven apps, Power Fx, data and connections, controls and UI, code and extensibility, mobile and offline, ALM and solutions, governance and security, licensing and performance, plus cross-cutting decision guides. Implementation notes, configuration decisions and the gotchas that bite on real projects. Populated by the daily author agent from the Power Apps release plans, docs repo and product blog, plus the author's own consultant notes.

power-apps/11-decision-guides/feature-data-source-selection.md
feature-data-source-selection.mdv1 · history
CurrentApplies to AllUpdated last monthSource Microsoft Learn ↗

What it does

The choice between Dataverse, SharePoint, SQL Server and Excel as the store behind an app. It looks like a cost decision and it's actually a decision about delegation, security and how much of the platform you get to use.

Key facts

  • Delegation is the whole game. Where a Power Fx expression can't be translated into a query the source understands, Power Apps pulls back the data row limit — 500 by default, settable between 1 and 2,000 — and evaluates locally. If any part of an expression is non-delegable, none of it delegates.
  • That failure is silent in the way that matters. The app returns results; they're just the wrong ones, and only above the row limit, which is why it passes testing and fails in production.
  • Dataverse is the only source that gives you model-driven apps, native mobile offline, business rules, plug-ins, auditing, and row and column-level security. It's also the source that makes the app premium.
  • SharePoint delegates a useful subset — equality across types, comparisons on numbers and dates, Filter, Lookup, Sort, SortByColumns, StartsWith. And and Or delegate; Not doesn't. Complex column types defer the decision to the subfield, and most system columns don't delegate at all.
  • SharePoint list IDs display as numbers but are text underneath, so only = delegates against them. Relational comparisons on an ID quietly fall back.
  • SQL Server is delegable and gives you views and stored procedures. Views support queries, not updates — use a stored procedure to write. Tabular sources page in 100-record increments; action calls like stored procedures don't page at all, so a procedure returning many thousands of rows will hurt. Guidance is to stay under 2,000.
  • Excel has to sit in cloud storage with the data formatted as a named table, and it delegates nothing. No concurrency control, no meaningful security, and a file someone will open and lock.
  • SQL Server, on-premises gateways and Dataverse are all premium triggers. SharePoint and Excel via standard connectors are not, which is the whole reason this argument happens.

When to use / skip

Dataverse is the default answer for anything that's a business application rather than a list. Relationships, security you can explain to an auditor, one data model across several apps, an ALM path that actually works — that's what you're buying, and it's the reason the premium licence exists. If the client needs any two of relational integrity, row-level security, offline and model-driven, the decision is already made.

SharePoint is genuinely right for a list-shaped problem: a few thousand rows, flat structure, no referential integrity requirement, and an owner who already lives in the site. It's the wrong answer the moment someone needs a second related table, because you'll build relationships by hand and maintain them forever.

SQL Server suits an app over data the enterprise already owns and governs elsewhere — an existing warehouse, a line-of-business database with a DBA and a schema nobody's moving. Views and stored procedures give you a proper escape hatch from delegation limits, which is the strongest technical argument in this whole comparison. It's premium either way, so if you're paying premium anyway, the question becomes where the data should live rather than what it costs.

Excel is not a data source for an app. It's a file. If a client's app runs on Excel, the work isn't making it better; it's costing the migration.

The cheap option costs more in a specific, predictable way. Someone picks SharePoint to avoid a licensing conversation, the list crosses the delegation threshold, and the app starts returning subtly wrong data. The fix is a migration plus an app rewrite, paid for out of a support budget, roughly eighteen months after the saving. Ask for a row count and a growth rate before agreeing to anything — "about a hundred a month" is the number that settles this, and nobody volunteers it.

Configuration decisions

  • Row count today and growth rate, checked against the delegation limit for the operations the app actually performs.
  • Whether the security requirement is per-row or per-list. Per-row on SharePoint means item-level permissions, which is a maintenance job nobody signs up for.
  • Where the system of record sits. An app over a staging copy is a different architecture from an app over the master, and both are legitimate as long as somebody says which it is.
  • Whether the delegation escape hatch matters. On SQL that's views and stored procedures; on Dataverse it's a much wider delegable surface; on SharePoint and Excel there isn't one.
  • Whether the app needs offline, because that answer is Dataverse.
  • How dev and production point at different data. Dataverse and SQL handle it with environment variables and connection references; Excel has no environment concept at all.

Gotchas

  • Raising the data row limit to 2,000 feels like a fix and is a deferral. It moves the cliff edge, makes the app slower, and guarantees the failure lands later, in production, with more data.
  • The SharePoint ID column being text underneath catches everyone, every time. Filter( List, ID > 500 ) doesn't delegate and doesn't obviously explain why.
  • UpdateIf and RemoveIf against SharePoint simulate delegation by pulling records down in batches to evaluate them. On a large list that's slow and easy to mistake for a delegated operation.
  • SharePoint's own list view threshold is a separate ceiling from the Power Apps delegation limit. Two mechanisms, two failure modes, and clients conflate them constantly.
  • SharePoint list permissions and app sharing are unrelated. Sharing the app grants no list access, and granting list access lets people bypass the app entirely.
  • A stored procedure returning too much data will slow or crash the app, because action calls aren't paged the way tables and views are.

Consultant notes

  • Get the delegation conversation onto the table in discovery, in business language: "above this many rows, this app can silently show incomplete results". Non-technical sponsors understand that framing and don't understand delegation warnings.
  • Frame the Dataverse decision as buying security and lifecycle, not storage. Compared on storage it always loses, and storage was never the point.
  • If you inherit an Excel-backed app, price the move as a data migration plus an app rewrite. It isn't a connector swap and shouldn't be absorbed into a change.
  • Where SQL is the client's answer, get the DBA in the room early. Views and stored procedures are the difference between a good SQL-backed app and a slow one, and that's their work, not yours.
  • Write the choice and its reasoning into the solution document. Data source decisions get questioned every time a new architect arrives, and a paragraph now saves a re-run of the whole argument later.

Recheck the delegable operations tables each wave — the SharePoint and SQL lists have both grown, and the recommendation moves with them

Was this accurate?
Related
SharePoint and Excel as data sources04-data-and-connectionsWorking with Dataverse in apps04-data-and-connectionsOn-premises data gateway04-data-and-connectionsPower Fx language03-power-fx-and-logicPremium connector licensing for apps10-licensing-and-performanceDelegation03-power-fx-and-logic