Virtual tables in Dataverse

Sometimes data should not be copied into Dataverse — it lives authoritatively in another system, replicating it would create sync headaches, and consumers just need read access. Virtual tables solve this by making external data appear as Dataverse tables without storing the rows. To a model-driven app or a Power Automate flow, a virtual table looks like any other; underneath, every query is routed to the external source.

What virtual tables provide.

• A Dataverse table shape (columns, datatypes, primary key).
• No row storage in Dataverse.
• Read and (optionally) write operations forwarded to an external system in real time.
• Native participation in model-driven app forms, views, and Power Automate triggers (in some cases).

What they don't provide.

• High-performance large dataset queries (every query is a remote call).
• Full Dataverse features (auditing, change tracking, calculated columns, traditional plugins).
• Reliability if the external source is slow or unavailable.

The virtual provider model. A virtual table needs a virtual data provider — a connector that translates Dataverse queries into requests the external source can answer. Three common providers:

• OData virtual provider — out-of-the-box for OData-compliant sources (SharePoint lists, OData-published systems, F&O entities).
• Cosmos DB / SQL providers — bridge to specific data stores.
• Custom virtual providers — built in C# implementing IVirtualEntityDataSource, registered as plugins.

Custom providers give the most flexibility — any backend with an API can become a virtual table by writing the provider.

Creating a virtual table. In the maker portal:

1. Tables → New table → Virtual.
2. Choose the data source (configured virtual provider).
3. Select the external entity to surface.
4. Map external fields to Dataverse columns; set the primary key.
5. Save and publish.

The table now appears in views, forms, and flows alongside native tables.

Query forwarding. When a user queries the virtual table:

• Dataverse parses the FetchXML / OData query.
• The virtual provider translates it to the external system's query language.
• The external system returns results.
• Dataverse marshals them back as if they came from internal storage.

Latency is the external system's response time plus the marshaling overhead. For sub-second external systems, the experience feels native. For slow systems, it doesn't.

Read vs read-write. Virtual tables are most often read-only:

• Display data without copy.
• Simplify model-driven app integration.
• Avoid sync logic.

Read-write virtual tables exist when the external system supports updates and the provider is built to handle them. CRUD operations route to the external system, but with caveats around transactionality and error handling — if a Power Automate flow updates the virtual table and the external system rejects, the flow sees an error but has no native rollback semantics.

Use cases.

• F&O data in a Power Apps front end — surface customer balances from F&O without copying.
• Reference data from a master data hub — countries, currencies, product catalogue maintained elsewhere.
• External system inventory or pricing — read in Power Apps; user sees current data, system of record stays external.
• Compliance / audit data — query without bringing data under Dataverse's residency boundary.

Limitations to know.

• Performance under load — every query is a network call; high-traffic scenarios hit the external system directly.
• No traditional plugins/workflows — fewer extension points.
• Limited offline support — model-driven mobile offline doesn't replicate virtual tables.
• Auditing limited — Dataverse audit tracks calls but not external-side changes.
• Search limitations — Dataverse search (the global search bar) often doesn't index virtual tables fully.

Comparison to Power Automate "fetch on demand". Some scenarios that look like virtual table candidates are better served by a Power Automate flow that fetches data on demand and displays in a canvas app — simpler to build, no provider development.

Comparison to dataflow replication. When data is high-volume, read-heavy, and tolerates some latency, replicating to a real Dataverse table via dataflow can outperform a virtual table at scale.

Common pitfalls.

• Virtual table for high-traffic master data. Every view load fans out to the external system; the external system becomes a bottleneck for the whole app.
• No caching layer. A custom provider that hits the external API every query without caching is expensive.
• Schema drift. External system changes a field; virtual table fails to load until the mapping is updated.
• Auth complexity. The provider needs auth to the external system; managing service principals or OAuth flows reliably is real work.
• Treating virtual tables as if they were standard. Expectations of plugins, audit, advanced queries don't apply.

Decision framework. Use virtual tables when:

• Data must remain authoritative externally.
• Volume per query is small and per-row latency tolerable.
• Replication would create sync complexity or compliance issues.

Use replicated standard tables when:

• Data volume is moderate and reads are frequent.
• Power Apps responsiveness matters.
• Full Dataverse features (workflows, plugins, audit) are needed.

The two approaches are complementary; many environments use both — virtual tables for some scenarios, replicated tables for others.