In a Power Apps app, the first technical decision is the data source, and it’s also the most expensive decision to change later. A SharePoint list starts quickly and needs no extra license; that’s why it becomes the unquestioned default in most projects. Dataverse brings a relational model, row-level security and lifecycle management, and in return asks for licensing and discipline. Below, we compare the two options in terms of record volume, data model, security, delegation, licensing and ALM, and give a decision tree that can be used at the start of a project.
The criteria that decide the choice
There’s no general answer to “which is better?” The decision is determined by the answers to these questions, not for the app as it is today but as it will be in two or three years. Put these questions on the table at project kickoff; asking them six months later is too late:
- Record volume: How many records will the main table produce per year? Will the app work on all records or only on the user’s own records?
- Data model: How many related tables are there? Are one-to-many and many-to-many relationships and master-detail forms (such as an order and its lines) needed?
- Security: Should users see only their own records or their own department’s records? Should some fields be hidden from certain roles?
- Query complexity: Will search, multiple filters and aggregation be done on a large data set?
- Licensing: Do users’ existing Microsoft 365 licenses cover the app, and is there a budget for premium licenses?
- Lifecycle: Will the app be moved in a controlled way between development, test and production environments?
Strengths and weaknesses of a SharePoint list
A SharePoint list stores data in an interface the user already knows: it’s viewed and edited from the browser, exported to Excel and easily triggered with Power Automate. For small and medium department apps such as leave requests, asset registers and training attendance lists, it’s often enough. No problem here.
The problem starts after a certain threshold and is usually noticed in production:
- List view threshold: A list can hold millions of items; but scans exceeding 5,000 items are blocked. Column indexes and selective filters become mandatory. The details are in the metadata and list view threshold guide.
- Delegation: Non-delegable queries process only as many records as the data row limit; operations such as
Searchandinaren’t delegated in the SharePoint connector. On a large list, this means that users can’t see some records and get no error message. The most dangerous failure is the silent one. Limits and solution patterns are in the Power Apps delegation guide. - Relationships: Lookup columns create simple relationships; there are no many-to-many relationships, cascading deletes or integrity rules. The number of lookup columns in a list view is also limited.
- Row-level security: Users seeing only their own records is achieved with a filter in the app. This isn’t a security control; the user can access the list directly from the browser. Real separation requires breaking inheritance at item level, and that doesn’t scale.
- Transaction integrity: Writing a record that spans several lists with “all or nothing” logic (a transaction) isn’t supported; you have to compensate for half-finished writes on the app side.
The management burden of item-level permissions is explained in detail in the SharePoint permission model guide.
The relational model and security Dataverse provides
Dataverse is the Power Platform’s managed database. Tables, relationships, business rules and security are defined in the same layer:
- Relational model: One-to-many and many-to-many relationships, relationship behaviors (cascading delete, assignment), alternate keys and calculated or rollup columns.
- Role-based security: Security roles define create, read, write and delete rights per table at user, business unit or organization scope. The rule “a sales rep reads only their own business unit’s records” is applied at the data layer; it doesn’t depend on an app filter.
- Column-level security: Fields such as salary or ID number can be hidden from certain profiles.
- Auditing: Change history can be kept per table and column.
- ALM: Tables, apps and flows are packaged in the same solution and moved between environments.
- Delegation: The Dataverse connector delegates a wider set of functions; search and filtering on large tables behave more predictably.
The cost comes in three items: premium licensing, capacity consumption and modeling discipline. A Dataverse table isn’t a place end users open and edit in the browser like a list; it’s edited through a model-driven app, a canvas app or the maker portal. User habits change. Write this into the training plan. For a conceptual introduction, Microsoft’s Dataverse documentation is the official starting point.
Licensing impact and verification
In the decision tree, licensing often takes precedence over the technical criteria. The general framework is this: canvas apps that use standard connectors such as SharePoint can be run with Microsoft 365 licenses that include Power Apps use rights. Dataverse (except Dataverse for Teams) and premium connectors, however, require a premium Power Apps license for every user of the app; which of the per-user, per-app and pay-as-you-go models are on sale changes from period to period. Dataverse for Teams is offered with a more limited scope to apps running inside Teams.
An example calculation method: write in a table the number of people who will use the app, how many people it will be opened to and whether these people will use other premium apps. A broad audience using a single premium app and a narrow audience using several premium apps usually lean towards different licensing models; the current price list tells you which is more economical. Redo the calculation with a current quote each time. Last year’s price isn’t today’s decision.
A third option is Azure SQL Database or SQL Server. It’s strong for a relational model and large volumes; but the SQL connector is also in the premium class, and you need to set up the security model separately on the app or database side.
Comparison table and decision tree
| Criterion | SharePoint list | Dataverse |
|---|---|---|
| Record volume | Small to medium; watch the threshold | Predictable at large volumes |
| Relationships | Simple lookup column | 1:N, N:N, relationship behaviors |
| Row-level security | Item permissions; doesn’t scale | Role, business unit, ownership |
| Column-level security | None | Yes |
| Delegation | Limited set of functions | Wider set of functions |
| Licensing | May be covered by M365 | Premium license |
| ALM | List template and scripts | Across environments with solutions |
| User access | Directly from the browser | Through the app |
In corporate business application projects, we run the decision tree in this order:
- Should users see only the records they’re authorized for, and is this an audit requirement? If yes, Dataverse (or SQL with security set up separately).
- Are master-detail relationships, many-to-many links or relationship behaviors such as cascading delete needed? If yes, Dataverse.
- Will the main table exceed tens of thousands of records within a few years, and will the app search across all records? If yes, Dataverse; if no, a SharePoint list may be suitable.
- If there’s no premium license budget and the answer to the first three questions is no, a SharePoint list; if the app is inside Teams and for a small team, Dataverse for Teams can be considered.
If the decision is to start with a list, apply the delegation rules from the very first screen: index the filtered columns, use StartsWith instead of Search, and calculate counters at the source, not in the app. With 50 records in the development environment, everything works. Load test data at real volume.
A scenario for moving from a list to Dataverse
The picture we often come across in the field is this: a purchase request app started with a SharePoint list, the record count grew, users started saying “some of my requests aren’t showing”, and on top of that a need for department-based confidentiality arose. The migration order:
- Model: Map the list columns to Dataverse tables. Choice columns become choice columns, lookup columns become relationships; repeating text fields can be split into separate tables.
- Environment and solution: Create the tables in a solution in a development environment, not in the default environment. For the environment structure, see the Power Platform environment strategy guide.
- Data transfer: Load the list data into the Dataverse table with Power Query-based dataflows. For lookup relationships, move the master tables first, then the related tables; keep the list item ID in a temporary column for mapping.
- Security: Define the security roles and business units and verify them with test users. Don’t test with an admin account.
- App and flows: Change the data source in the canvas app; since column names and types are different, review the formulas one by one. Power Automate flows that use the list as a trigger are rewritten with Dataverse triggers.
- Cutover plan: Before the final transfer, make the list read-only, run a delta transfer and compare record counts on both sides. If the numbers don’t match, the migration isn’t finished.
In the migration, it isn’t the data that takes the most effort but adapting formulas and flows. That’s why, for an app expected to have a permanent data model from the first version, starting with Dataverse from the outset is usually cheaper than moving later. For app and data model design, take a look at our business applications with Power Apps page, and for platform governance, our low-code consulting page.
Frequently asked questions
Up to how many records is a SharePoint list suitable for Power Apps?
There’s no exact number. A list can hold a very large number of items; what matters is whether the app’s queries can be delegated and whether views are designed around the 5,000-item threshold.
Does putting a filter in the app provide row-level security?
No. An app filter only restricts what’s displayed; the user can access the list directly from the browser or another tool. Real separation is provided by Dataverse security roles or item permissions.
Does using Dataverse always require a premium license?
As a general rule, standard Dataverse requires a premium license; Dataverse for Teams, on the other hand, is offered with a more limited scope to apps within Teams. Since the rules can change, verify against the current licensing guide.
How is data moved from a list to Dataverse?
List data is loaded into Dataverse tables with Power Query-based dataflows. For relationships, the master tables are moved first; the list item ID is kept in a temporary column for mapping.