Dataverse Virtual Tables: Real-Time Data Without Replication

Most integrations start with the same reflex: export, transform, load, and keep syncing forever. It works—until it doesn’t. Batch jobs drift. Schemas change. Users see stale data.

Dataverse Virtual Tables flip that script. Instead of copying external data into Dataverse, they project it into the platform at runtime—read or read/write—so apps, flows, and search can use it without replication.

This post covers when to use virtual tables, how to model them cleanly, security and performance realities, and the patterns I use so projects don’t stall at the first limitation.

Use VT when you need live access to authoritative data that shouldn’t (or can’t) be duplicated: ERP inventory levels, product catalogs, large operational datasets, regulated data that must remain in-place.

Choose VT when:

• Freshness beats latency. Users must see current values without waiting for a job.
• Data sovereignty or size makes replication impractical.
• One source of truth must remain external (finance/ERP, LOB SQL, legacy).
• You need Dataverse features (model-driven UI, lookups, security roles, business rules) over that external data.

Avoid VT when:

• You need offline, mobile caching, or auditing inside Dataverse.
• You must trigger Dataverse data events (some server-side features don’t fire for VT rows).
• You require heavy write throughput (depends on the provider).
• The external API has poor filtering—you’ll fight performance.

Rule of thumb: If users mostly view operational data in real time, VT shines. If you must own the data lifecycle in Dataverse (audits, plugins, offline, rich server behaviors), replicate.

You can build VTs via multiple providers; the choice defines capabilities:

• SQL Server provider (Azure SQL/SQL Managed Instance/on-prem via gateway)
  • Best for tabular data; supports filters/ordering push-down.
  • Read-only or read/write depending on mapping.
  • Performance depends on indexing/queries on the SQL side.
• Virtual Connector Provider (VCP) using Power Platform connectors (e.g., SharePoint, OneDrive/Excel, custom connector)
  • Fast to stand up, wide source coverage.
  • Capabilities mirror the connector—often read-heavy, pagination may be limited.
• Custom data provider (bespoke plugin implementing IDataSource)
  • Maximum control, supports complex backends.
  • More engineering effort; great when you need write semantics or special auth.

Pick the provider that pushes filters to the source; otherwise, Dataverse will pull too much and your users will feel it.

Before you click “Create table from external data,” pin down:

• Stable key (natural/business key) that never changes.
• Primary name column users recognize.
• Filterable fields you’ll need for views and lookups (and ensure they’re indexed at the source).
• Change detection strategy (timestamps, rowversion) if you’ll support writes or reconcile.

• Create a Connection to the source.
• Define the external table/view and select fields.
• Map the external key to the VT’s primary key (and set the Primary Name).
• Set searchable fields for Dataverse search.
• Mark relationship candidates (foreign keys) you’ll convert into lookups.

Tip: Keep VT schemas thin—only map what you’ll actually use. You can extend later.

Users expect lookups to “just work.” In VTs, you can model lookups if you have a reliable foreign key:

• Add a Lookup column on the VT pointing to a real Dataverse table (e.g., external CustomerId → Dataverse account).
• Use a mapping table when keys differ (external customer code ↔ Dataverse accountid).

This unlocks subgrids, filters, and views across virtual + native tables.

1. Push filters down. Build views and forms that filter on indexed source columns. Avoid client-side filters that force full scans.
2. Narrow the select list. Don’t bring large memo/blob columns unless you need them; prefer a separate VT for heavy payloads.
3. Paginate intentionally. Where the provider supports it, ensure page size isn’t tiny.
4. Avoid N+1. In forms that show subgrids, minimize multiple VT subgrids on first load.
5. Precompute joins in the source. For expensive joins, expose a SQL view with the join done server-side and map that as the VT.

Sanity check: Open the form and watch the network. If initial loads exceed a couple of seconds, tune queries/filters/columns before users see it.

• Dataverse roles still gate access to the VT object and columns, but row-level security is enforced by the source when using providers that pass through identity.
• For SQL, prefer Azure AD auth with per-user passthrough where feasible; otherwise, document the service principal’s access scope.
• DLP: VTs participate in environment DLP. Keep them in governed environments—they can surface regulated data into apps/flows.
• Auditing: VT reads/writes aren’t audited in Dataverse like native tables. If you need audit, log in the source or add Dataverse-side “touch” records.

Compliance note: Document what data leaves where. VTs can simplify audits because data stays put, but you still need lineage.

Many sources will be read-only; some support create/update/delete. If you enable writes:

• Validate key generation (does the source create keys? do you need a surrogate?)
• Apply business rules in the source (constraints, triggers) so Dataverse UI can’t bypass them.
• Expect error passthrough—surface friendly messages on forms and flows.

If writes are rare, consider a command pattern: keep VT read-only, and perform writes via Power Automate or custom APIs to centralize validation.

Treat VTs like any other enterprise artifact:

• Make VT definitions solution-aware (tables, columns, relationships, views, forms).
• Use connection references and environment variables for connection strings, server names, and source object names.
• In Dev, point to a dev database or masked data. In Test/Prod, switch via env vars—do not hard-code.
• Include test forms and views in the solution so QA can validate filters and loads like users do.

• Hub-and-spoke catalog: A single Product VT used across Sales, Service, and custom apps, with lookups into native tables (quotes, cases).
• Heavy history, light surface: Expose a summary VT for lists/forms and a separate detail VT (or on-demand tab) for large payloads.
• Bridge table: When external keys don’t match Dataverse GUIDs, maintain a bridge (mapping) table that’s native, and use it in lookups.

• No offline and limited auditing in Dataverse.
• Some server-side events/plugins don’t fire for VT rows.
• Advanced find/search works, but relevance depends on which fields you marked searchable.
• Calculated/rollup columns on VT aren’t supported like native tables—compute in source or via views.

Have a Plan B for any capability you must have that VT can’t provide.

• Slow forms? Reduce columns, push filters to indexed fields, pre-join in a SQL view, avoid multiple VT subgrids at once.
• Missing rows? Verify connection identity has rights; confirm filters map correctly (data types, collations).
• Broken lookups? Check bridge mappings and ensure the lookup column points to the correct table and key.
• Writes failing? Capture and surface source errors; validate keys and required fields.

Virtual Tables let you use external data where it lives while giving users the Dataverse experience—forms, views, lookups, and security. They aren’t a silver bullet, but with the right provider, lean schemas, filter pushdown, and clean relationships, they remove entire classes of ETL and sync headaches.