[object Object]

Virtual tables let Dataverse pretend an external data source is a native table. You bind a virtual data provider — SQL, OData, custom — and rows of the external source appear in views, on forms, in lookups. No ETL, no sync delay. It is the right answer for a narrow class of problems and the wrong answer for almost everything else. Most teams reach for virtual tables when they should reach for dual-write, integrations, or a plain old custom table.

What virtual tables actually do

A virtual table is a metadata-only entity in Dataverse. There are no rows in the underlying SQL storage. When a query asks for rows, Dataverse delegates to the virtual data provider, which translates the FetchXML or filter into a call against the external source.

The provider implementations:

  • OData V4 provider (built-in). Maps external OData feeds.
  • SQL provider (built-in). Maps an Azure SQL table.
  • Custom provider via the Virtual Connector or a custom plugin assembly.

The platform owns metadata, security mapping (per record where supported), and a thin query translation layer. Everything else is the provider’s responsibility.

When virtual tables win

  • Read-mostly external data that you want to surface inside Dataverse without sync.
  • Reference data that changes frequently in its source of truth (product catalogs, pricing) and where staleness via sync is unacceptable.
  • Compliance scenarios where the data legally cannot be copied into Dataverse.
  • Small to moderate query volumes (hundreds of reads per minute, not thousands).
  • Filters that align with the provider’s filter capabilities.

When virtual tables lose

  • Write-heavy workloads. Many providers do not support writes; those that do are fragile.
  • High-volume reads. Each read calls the provider; latency is the provider’s, not Dataverse’s.
  • Queries that need joins to native tables. Virtual tables do not join cleanly in FetchXML beyond simple lookups.
  • Aggregations. Most providers cannot push aggregates; Dataverse falls back to client-side aggregation over fetched rows. Pagination breaks subtly.
  • Security trimming beyond row-owner. Granular roles and column-level security do not work uniformly.
  • Plugin scenarios. You cannot register a plugin on a virtual table the way you would on a native table.

The latency math

Every read of a virtual row is an external call. Form load loads the row, you pay one call. Subgrid loads ten rows, you pay one call but possibly with a complex filter the provider does not push down efficiently. The provider’s median latency plus jitter becomes the floor of the user experience.

We measured a typical OData provider against an Azure App Service backend:

  • Single row: 80-120 ms (depending on network proximity).
  • Ten-row list with filter: 200-400 ms.
  • Ten-row list with sort + filter on a non-indexed column: 1-3 seconds.

Compare to a native Dataverse query: 20-40 ms typically. The factor-of-ten gap is normal. Plan for it.

Filter capability boundaries

The OData provider supports a subset of FetchXML operators. Common gotchas:

  • like and in operators sometimes translate to full table scans on the provider.
  • eq null translates differently depending on the provider’s null semantics.
  • Date filtering relative to user time zone is the source of the most common discrepancy bugs.

Test every filter you use in a view. The view that ran in dev with five rows can break against production volumes when the filter does not push down.

A concrete OData provider config

For an Azure SQL backend via an OData service, the table definition is metadata-only. The data source is registered as:

{
  "datasourceName": "ProductCatalog",
  "type": "OData",
  "baseUrl": "https://api.contoso.com/catalog/odata",
  "authentication": "OAuth2",
  "metadataUrl": "https://api.contoso.com/catalog/odata/$metadata"
}

Then in the table definition:

{
  "logicalName": "cr_catalogproduct",
  "datasourceId": "<datasource-guid>",
  "primaryName": "Name",
  "primaryId": "ProductId",
  "columns": [
    { "logicalName": "cr_sku", "externalName": "Sku", "type": "string" },
    { "logicalName": "cr_price", "externalName": "Price", "type": "money" },
    { "logicalName": "cr_stock", "externalName": "InStock", "type": "boolean" }
  ]
}

The externalName mapping is what makes this fragile — rename the source column without updating the metadata and reads break silently (column returns empty).

The custom provider case

If the built-in providers do not match your source, you write a custom data provider as a plugin assembly. The plugin implements IPlugin and handles RetrieveMultiple, Retrieve, Create, Update, Delete messages. The Custom Connector Virtual Connector simplifies this for sources reachable via Power Platform connectors, at a cost of capability.

A skeleton:

public class CatalogVirtualProvider : IPlugin {
  public void Execute(IServiceProvider sp) {
    var ctx = (IPluginExecutionContext)sp.GetService(typeof(IPluginExecutionContext));
    switch (ctx.MessageName) {
      case "RetrieveMultiple":
        var query = (QueryExpression)ctx.InputParameters["Query"];
        var rows = QueryExternal(query);
        ctx.OutputParameters["BusinessEntityCollection"] = rows;
        break;
      case "Retrieve":
        var id = (Guid)ctx.InputParameters["Target"];
        ctx.OutputParameters["BusinessEntity"] = GetExternal(id);
        break;
      // Create/Update/Delete only if your source supports them
    }
  }
}

The pain is in the provider implementation: connection management, pagination, error mapping, throttling. Plan for it as a real software project, not a configuration exercise.

What to do instead

Most “I want virtual tables” requests are really:

  • “I want to see external data on a Dataverse form” — embed an iframe or PCF control that talks to the external API directly.
  • “I want to lookup external records from a Dataverse field” — use a custom lookup PCF control with a Power Automate flow as the data source.
  • “I want to import external data” — set up dual-write or a Power Automate sync flow.

Each alternative beats virtual tables in latency, reliability, or both, for most real workloads.

See also

Dataverse elastic tables: when to use — the other “specialized table type” in the Dataverse lineup. The decision matrices are similar in spirit: pick the right tool for the workload, do not default to either.

Pixel notes

When a form shows a virtual table column, show a “live data” indicator near the field. The fetch can be slow, fail, or return stale data. Users who know they are looking at remote data set their expectations accordingly. Surface the freshness, do not hide it.

Forge notes

Virtual data provider exceptions surface as generic Dataverse errors to the user. Catch and translate. The user does not need to see “RetrieveMultiple plugin trace: connection refused”. They need to see “Product catalog is unavailable — try again in a moment”. Translation lives in the provider’s exception handling.

Bottom line

  • Virtual tables fit narrow scenarios: read-mostly, latency-tolerant, compliance-sensitive.
  • Every read is an external call. Plan for 10x native latency.
  • Filter pushdown is the source of most bugs. Test every view’s filter.
  • Custom providers are real software, not configuration.
  • For most “I want external data in Dataverse” requests, embed-in-form or sync beats virtual tables.
[object Object]
Share