Where You Should Store Your Data in Power Platform

In this practical walkthrough, Brian Knight explains how to choose the right data store for Power Apps and Power Automate solutions. He compares five common options—Excel, SharePoint, Dataverse for Teams, Dataverse, and SQL Server—highlighting security, scalability, cost, performance, and maker experience so you can select the best path for your next app.

The “money wall”: licensing shapes your choices

• Included with Microsoft 365 (above the green line): Excel, SharePoint, Dataverse for Teams.
• Premium (below the green line): Full Dataverse and databases like Azure SQL/SQL Server.

Option 1: Excel (Included)

• Pros: Zero learning curve; quick to start; ideal for personal or single‑user prototypes.
• Cons: Excel is not a database. The data must be in an Excel Table with no formulas. Multi‑user apps will see random errors and contention; scalability is limited.

Best for: Solo maker experiments and throwaway prototypes—not team apps.

Option 2: SharePoint Lists (Included)

• Pros: Familiar, easy for makers, already in Microsoft 365; fast to model simple lists.
• Cons: Also not a traditional database. Expect thresholds at ~5,000 items (rigidity and query limits) and change friction beyond ~20,000 items. Under concurrent load, users may notice multi‑second save delays. Security can be tricky: app access typically requires list access, enabling savvy users to find and act on underlying data (e.g., approving their own requests).

Best for: Lightweight team scenarios with modest scale and low data sensitivity.

Option 3: Dataverse for Teams (Included with Teams)

• Pros: Feels familiar to SharePoint makers but adds row‑level security and stronger models. Scales to ~1M rows per table and ~2 GB per Team. Handles “contains” searches without the delegation pitfalls common in SharePoint/Power Apps.
• Cons: Apps run inside Microsoft Teams—which some organizations love and others prefer to avoid.

Best for: Secure team apps that live natively in Teams with mid‑scale data needs.

Option 4: Dataverse (Premium)

• Pros: Enterprise‑grade security (row‑level, business unit, hierarchy) and built‑in auditing. Rich “plumbing” (created/modified metadata, attachments) and a maker‑friendly UX. Scales far beyond Dataverse for Teams (Brian cites a ~4 TB capacity tier).
• Cons: Cost and capacity management. Per‑user Power Apps licenses include a base Dataverse capacity (e.g., ~250 MB per user license), and additional capacity is purchased per GB per month when you exceed the pool. Pricing changes over time, so always verify current rates.

Best for: Enterprise apps that require strong security, auditing, and scalable relational models.

Option 5: Azure SQL / SQL Server (Premium)

• Pros: Highest performance and elasticity—scale up for peak events and back down later. Cost‑effective entry tiers (Brian mentions ~$5–$15/month), with backups, disaster recovery, and features like data masking handled for you in Azure. Ideal when you already have SQL skills or complex relational needs.
• Cons: Requires database design expertise (schema, indexing, performance tuning). More developer‑centric than maker‑centric.

Best for: Performance‑sensitive or SQL‑centric teams and apps with sophisticated relational design.

Decision guide

1. Tiny prototypes or solo use: Excel (avoid for multi‑user).
2. Simple team apps with low sensitivity: SharePoint (mind 5k/20k thresholds).
3. Team apps inside Teams, up to ~1M rows/table: Dataverse for Teams.
4. Enterprise apps needing security & auditing or >1M rows: Dataverse.
5. Peak performance or existing DBA skills: Azure SQL/SQL Server.

Extra tips from Brian Knight

• Delegation & searching: “Contains” searches scale reliably in Dataverse/Dataverse for Teams; SharePoint can hit delegation limits beyond ~2,000 rows in Power Apps.
• Plan for growth: Start with your security and scalability needs, then evaluate the total cost (licenses + capacity) versus development effort.
• Keep pricing current: Microsoft pricing evolves—always confirm before committing.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power Platform  and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.