[object Object]

A solution architect inherits a request: surface 8 million rows of inventory data inside Dynamics for sales reps to see real-time stock. The first instinct is data integration; build a sync, refresh hourly, store in Dataverse. The second-look answer is virtual tables. The federation pattern saves the storage cost and the sync maintenance, and it works when the underlying source is fast and the access pattern is read-heavy.

What Virtual Tables Are

Dataverse tables backed by external data (SQL, SharePoint, custom REST). Schema lives in Dataverse; data stays external. Read-write for some providers. Every query against the virtual table translates into a call to the external source at query time.

Virtual table mechanics:
- Schema definition lives in Dataverse metadata
- Data provider plugin handles the external call
- Query translation maps FetchXML or OData to source query
- Results stream back through Dataverse API

The query translation step is where performance lives or dies. A push-down filter is fast; a filter that requires fetching everything and filtering in-memory is slow.

OData Provider

Most common. Map Dataverse columns to OData fields. Out-of-box setup; usable with any OData 4.0 source. Configuration is metadata-only; no code needed for the standard mapping cases. The provider auto-discovers the OData metadata and presents the entities for selection.

Setup steps:
1. Register the OData connection in maker portal
2. Pick the entity to virtualize
3. Map columns from OData to Dataverse types
4. Set the primary key column
5. Save and test with a list view

The primary key step is non-negotiable. Without a unique key, the platform cannot deduplicate or update rows.

When to Use

Reference data in another system, dashboards that need fresh data, avoiding duplication costs. If the data does not need to live in Dataverse, federation is cheaper than sync. Examples that work: product catalog from PIM, inventory from WMS, account hierarchy from MDM, billing data from ERP.

Good federation cases:
- Read-mostly reference data, large volume
- Source has its own master and is the system of record
- Real-time freshness matters
- Storage cost in Dataverse would be material

When Not

High-volume transactional writes — latency through the virtual table adds up. Complex queries that need joins across real plus virtual tables can perform poorly. The platform cannot push a join across a virtual and a native table; one side fully materializes before the join.

Bad federation cases:
- Write-heavy with sub-second latency requirement
- Joins between virtual and native required for common reports
- Source is slow or unreliable
- Audit trail must live in Dataverse

Performance

Cache aggressively where possible. Filters that push down to the source perform well; ones that do not cause full-table pulls. The OData provider supports filter push-down for equality, comparison, and contains; complex filters with computed columns do not push down.

<fetch>
  <entity name="virtual_inventory">
    <attribute name="sku" />
    <attribute name="qty" />
    <filter>
      <condition attribute="warehouse" operator="eq" value="WH-001" />
    </filter>
  </entity>
</fetch>

This filter pushes down cleanly. A condition like “qty greater than ten percent of capacity” does not, because the platform cannot rewrite the predicate.

Custom Provider for Non-OData Sources

When the source is not OData, write a custom provider. The provider is a plugin assembly that implements the Retrieve, RetrieveMultiple, Create, Update, and Delete messages. Full freedom on how to translate; full responsibility for query optimization.

Custom provider responsibilities:
- Auth to source
- Query translation
- Pagination
- Error handling
- Result shape conversion

Budget two to three weeks for a quality custom provider. The shortcuts here come back as performance complaints.

Security and Auth

The virtual table inherits Dataverse security at the table level but the actual data fetch runs as the configured connection identity, not the user. Field-level filtering happens after the fetch returns. This means the source sees the same query for every user; row-level security must be modeled in Dataverse, not in the source.

Monitoring

Watch the virtual table query latency in App Insights. A virtual table that averages above 1.5 seconds per fetch is a user experience problem; rethink the access pattern or move to scheduled sync.

What to do this week

Audit your data integrations for federation candidates: read-mostly, large-volume, real-time-fresh data sources. Build a virtual table for one candidate, measure the user experience, and decide whether to expand or fall back to sync.

[object Object]
Share