Integrating Dataverse with Azure Cosmos DB: scale-out storage patterns

Dataverse is a relational, transactional, security-aware store for business records. It is not a place for telemetry, clickstreams, IoT readings, chat transcripts, or any table that grows by millions of rows a week. Organisations discover this when their storage bill spikes or when a model-driven app grinds because someone stored sensor pings as custom-table rows. Azure Cosmos DB is the usual answer for the high-volume tier, and the design question is how to pair the two so users still see what they need inside Dynamics 365.

The dividing line

A useful rule: if a record has an owner, a lifecycle, and a security boundary, it belongs in Dataverse. If it is an event, a reading, a log entry, or a document that is written once and read rarely, it belongs in Cosmos DB or similar. Cases, contacts, work orders, and opportunities stay in Dataverse. The ten thousand telemetry readings behind one IoT alert, the full transcript of every chatbot conversation, and the audit trail of an external portal go to Cosmos DB, with a summary or a reference in Dataverse.

Getting this line wrong in the Dataverse direction costs money and performance. Getting it wrong in the Cosmos DB direction costs you the security model, the relationship model, and everything that makes Dataverse useful.

Pattern 1: offload with a summary

The high-volume data is written to Cosmos DB by whatever produces it (a device gateway, a portal, an Azure Function). Dataverse holds one summary record per logical thing, for example one IoT alert with the latest reading and a count, and that record carries a key into Cosmos DB. A model-driven form shows the summary; a button, a PCF control, or an embedded canvas app fetches the detail from Cosmos DB on demand through an API.

This is the pattern to default to. Dataverse stays lean, users get detail when they ask for it, and the two stores have a clear contract.

Pattern 2: virtual tables over Cosmos DB

Dataverse virtual tables let external data appear as a Dataverse table without being stored there. There is no first-party Cosmos DB provider, so you build one: a custom virtual table provider in C#, or a custom connector with OData-style semantics behind the virtual connector provider. Users then see Cosmos DB documents in views and subgrids as if they were Dataverse rows.

It works, and it is elegant when it does, but the constraints are real. Virtual tables need a stable primary key that maps to a GUID, they do not support all Dataverse features (no auditing, no rollups, limited security), and every view is a live query against Cosmos DB with the latency and request-unit cost that implies. A badly filtered view against a large container will be slow and expensive. Use this pattern for lookup-style access to moderate volumes, not as a way to scroll through a billion rows in a grid.

Pattern 3: Dataverse as the event source

The reverse direction: Dataverse changes need to land in Cosmos DB, typically to feed a customer-facing application that cannot query Dataverse directly at scale, or to build a read model for a high-traffic portal. Dataverse publishes changes through Service Bus or Event Grid, an Azure Function consumes them and writes documents to Cosmos DB, and the portal reads Cosmos DB. This is the CQRS pattern with Cosmos DB as the read store, and it is the right shape when Dataverse's API limits would otherwise become the portal's bottleneck.

Design the documents for the reads the portal makes, not as mirrors of Dataverse tables. Cosmos DB is best when each query hits one partition, which means denormalising aggressively and choosing partition keys around how the data is read, usually by customer or account.

Consistency and identity

Cosmos DB is eventually consistent by default across regions and tunable per request. Dataverse is strongly consistent within a transaction. Any pattern that moves data between them needs to accept a lag and design for it: show timestamps, avoid promising users that a write in one place is instantly visible in the other, and make writes idempotent so retries do not duplicate documents. See idempotency in Dynamics 365 integrations.

Use the Dataverse GUID as the document ID, or store it as a dedicated field, so the join between stores is unambiguous. Never rely on names or natural keys.

Security

The Dataverse security model stops at the Dataverse boundary. Anything reading Cosmos DB directly, whether a PCF control or a portal, needs its own authorisation, typically an Azure Function that checks the caller's identity and applies row filtering before returning documents. Do not hand Cosmos DB keys to a canvas app or a browser control.

Cost

Cosmos DB is priced by provisioned or serverless request units plus storage; Dataverse by capacity. Moving a chatty high-volume table out of Dataverse almost always reduces total cost. Moving a low-volume business table out of Dataverse almost always increases it, once you count the Azure Function, the monitoring, and the developer who understands both.

What breaks in practice

Partition key choices made early and regretted later; changing one means rewriting the container. Virtual table providers that were built for a demo and then asked to handle production filters. Portals that hit Cosmos DB cross-partition queries because the read model was designed as a copy of Dataverse rather than as a read model.

Stability verdict

The offload-with-summary pattern and the CQRS read-store pattern are both stable and well understood, and are what you should reach for. Virtual tables over Cosmos DB are viable but bespoke, and you own the provider for its lifetime. If the real driver is Dataverse storage cost rather than performance, read Dataverse storage types explained first; sometimes the answer is cheaper than a second database.