Dataverse Data Modeling Patterns Every Architect Should Know

Bad Dataverse models cost you for years. Performance suffers, security gets impossible to reason about, and migration to a new model becomes an enterprise project. Get the foundations right and your Power Platform estate scales gracefully through every release wave.

The five modeling decisions that matter most

For every meaningful entity in your model:

1. Standard table or elastic table?
2. Owned by user/team, or organization-owned?
3. Use Activity entity behavior or not?
4. Lookup, polymorphic lookup, or many-to-many?
5. Virtual table over an external system, or replicate into Dataverse?

Make these consciously, not by accident.

Standard vs Elastic tables

Standard tables are the default — they support relationships, business rules, security roles, audit, all the goodies.

Elastic tables (NoSQL-style, on Azure Cosmos DB) are the new option for:

• High write throughput (10K+ writes/sec).
• Documents with variable schema.
• Time-series data and IoT telemetry.
• Logging, telemetry, audit trails you don't query relationally.

Trade-off: elastic tables don't support all standard table features (full security model is more limited, fewer built-in relationship types). Use them for the right workload, not as a general default.

Ownership — pick once, hard to change

• User or team owned — record-level security, can be assigned, supports owner-based business processes. Use for transactional records (cases, opportunities, quotes).
• Organization-owned — shared across the org, simpler, faster. Use for reference data (product catalog, country list, settings).

Switching between the two requires re-creating the table. Pick deliberately.

Activity entity behavior — easy to over-use

Marking a custom entity as an Activity gives you:

• Inclusion in the timeline control on related records.
• Native support in queues and routing.
• Out-of-the-box reports on activities.

Costs:

• All activities share the same regarding-object pattern (polymorphic).
• Some platform features assume task-like semantics.

Use it for things that are genuinely activities (call logs, custom interactions). Don't use it as a shortcut to get records onto the timeline.

Lookup vs polymorphic vs many-to-many

• Lookup (1:N) — child has a single typed parent. Default choice.
• Polymorphic lookup — child can point to one of several parent types (like Activity's regarding). Powerful but harder for users and developers to query.
• Customer lookup — special polymorphic lookup that handles Account or Contact. Use it.
• Many-to-many (N:N) — pure relationship. No payload. Easy.
• Manual N:N (intersect entity) — when you need attributes on the relationship itself (start date, role, weighting).

The hidden cost of polymorphic lookups: Power BI and Fabric treat them differently than typed lookups. If reporting is a primary use case, prefer typed lookups + multiple tables over polymorphic.

Virtual tables — when to externalize

Virtual tables let Dataverse query an external source (SQL, OData, custom provider) live, no replication.

Use when:

• The source is the system of record and must remain so.
• Data volume is too large to replicate.
• Real-time freshness is essential.

Avoid when:

• You need rich relationships to other Dataverse tables.
• You need server-side workflows, business rules, audit.
• The external system can't handle Dataverse's query patterns at scale.

The right pattern is often: virtual table for browse/lookup, replicated subset for transactional editing.

Naming conventions that don't break things later

• Schema names: pub_entityname with a real publisher prefix from day one. Resist new_.
• Display names: human-readable, plural for the entity name (Accounts) and singular for fields.
• Avoid prefixes in field names — they just take up screen space.
• Set table names in solution with intent. Renaming later breaks relationships and code.

Indexing and performance

Dataverse autoindexes primary keys, lookups and standard searchable fields. For larger workloads:

• Use enable indexing on string fields you filter heavily.
• Create alternate keys for external IDs you'll use for upsert.
• For elastic tables, design your partition key carefully — it determines scale.

Audit, change tracking and CDC

• Audit — turn on at table+field level. Costs storage and write performance. Be selective.
• Change tracking — enable for entities you'll sync to Fabric, Synapse or external systems. Cheap; turn it on by default for transactional tables.

Common modeling anti-patterns

• One giant "Custom" table with 200 fields representing five conceptual entities.
• Polymorphic lookups everywhere because "we might want to extend later."
• All entities as Activity for "free timeline."
• Hard-coding entity names in plug-ins. Use the EntityLogicalName constants.
• Storing JSON blobs in long text fields when you should use elastic tables.

FAQs

How many tables is too many? There's no hard limit, but if you have 200+ custom tables and growing, evaluate: are you modeling the right grain? Could some collapse? Could some externalize?

Can we move data between standard and elastic tables? Yes via Dataflows or Power Automate, but the schemas often differ enough that it's a re-implementation rather than a re-host.

Should we use Dataverse or Azure SQL? Dataverse for anything that has a UX, security model and process ties. Azure SQL for back-office data plumbing and analytics. They complement each other.

What about Microsoft Fabric? Dataverse links cleanly to OneLake via the Fabric Link feature. Use Dataverse for transactional + Fabric for analytical, with link refresh handling sync.

Designing a Dataverse model? We do paid one-week architecture reviews — outputs include a detailed model diagram, security plan and 18-month evolution path. Get in touch.