Standard vs elastic vs virtual tables

What it does

Three table types with the same maker experience and three very different engines underneath. Standard tables are the relational store and the default. Elastic tables sit on Azure Cosmos DB for very large, high-throughput datasets. Virtual tables hold no data at all — they project an external source into Dataverse at read time. Activity tables are a fourth type, but they're a specialisation of standard rather than a separate engine.

Key facts

Standard tables get the whole feature set: transactions, alternate keys, rollups and calculated columns, business rules, N:N relationships, cascading, duplicate detection, sharing and access teams.
Elastic tables are documented for datasets in excess of tens of millions of rows. They consume Dataverse log capacity rather than database capacity, and support time-to-live expiry set in seconds from last modified.
Elastic tables give up a long list: alternate keys, N:N to standard tables, calculated, rollup, formula and currency columns, business rules, business process flows, charts, duplicate detection, table sharing, access teams, queues, connections, attachments, composite indexes, cascade behaviours, and multi-record transactions.
Virtual tables are organisation owned and don't participate in Dataverse row-level security at all. Whatever security you need has to exist on the external source.
Virtual tables don't support auditing, change tracking, duplicate detection, mobile offline, column security, Dataverse search, charts, dashboards or business process flows, and every query hits the remote system live. Write support depends on the provider.
Elastic tables reach throughput through the bulk messages CreateMultiple, UpdateMultiple and DeleteMultiple, within the same service protection limits. Microsoft cites roughly ten times the throughput of single requests.
All three appear identically in the maker portal, in the Web API and to a Power App. The differences only show up when you use a feature the type doesn't have.

When to use / skip

Start from standard and make someone argue you off it. That's not conservatism — it's that standard tables are the only type where the platform's features all work, and every requirement you meet later ("can we add a rollup?", "can we make it searchable?", "can we share this record?") is free.

Move to elastic only when the shape of the data demands it: millions of rows, arriving fast, semi-structured, short-lived, and not something a business user will build a chart on. IoT telemetry, event streams, coupon codes, staging data. If someone reaches for elastic because a standard table "feels slow", fix the indexes and the queries — elastic is isolation from the relational store, not a faster version of it.

Move to virtual when the data genuinely lives somewhere else and must stay there, and you need it visible in a model-driven app or a related record. Two costs: every view render is a live call to the remote system, so the user experience is only as good as that system's latency and availability; and there's no row-level security, so everyone with table read sees everything. Virtual tables are the right answer for reference data owned elsewhere; they're the wrong answer for anything you want to secure per row, report on or search.

Configuration decisions

Which columns to denormalise into an elastic table up front, since you can't filter across relationships later and retrofitting is expensive.
The TTL value on an elastic table, and whether "hard-deleted after N seconds with no recycle bin" matches the client's retention obligation.
Whether an integration writing to an elastic table uses bulk messages from day one. Retrofitting them into a working integration is real work.
For virtual tables: which provider, and therefore whether writes are on the table at all.
Whether the remote system behind a virtual table has the availability and response time to sit in a user's form load path.

Gotchas

Elastic tables have no multi-record transaction. A synchronous plug-in failing on PostOperation won't roll back the create. Developers who've built validation patterns on standard tables will get this wrong once.
No alternate keys on elastic tables means no upsert by business key. Integration teams whose whole pattern is upsert have to redesign.
Rows expired by TTL after they've synchronised to a lake stay in the lake. The table and the lake diverge silently and nobody notices for months.
Virtual table performance is invisible in design and obvious in UAT. A view over a slow provider makes the whole form feel broken, and the client blames Dataverse.
You can't convert between table types. Choosing wrong means rebuilding the table and migrating the data, so the decision is worth twenty minutes up front.

Consultant notes

The conversation to have with a client proposing elastic tables is about the unsupported feature list, not the row count. That list is where the project risk is.
Get the log capacity conversation in early for elastic. It's a different capacity bucket from the one the client has been watching and it moves fast at those volumes.
For virtual tables, insist on a performance test against the real source with realistic data before the design is signed off. It's the single most common cause of a virtual table being ripped out mid-project.
Push for a proof of concept at realistic volume whenever the answer isn't "standard". The unsupported features surface as surprises during build, and week two is much cheaper than week twenty.

Worth re-reading as the elastic table unsupported list shrinks — it's got shorter every release wave since launch