Configuring Dataverse Table Capacity and Storage Partitioning: Managing Large-Table Performance, Elastic Tables, and Time-Series Data Strategies in Model-Driven Apps | Wicked Smart Data
Configuring Dataverse Table Capacity and Storage Partitioning: Managing Large-Table Performance, Elastic Tables, and Time-Series Data Strategies in Model-Driven Apps
When your Dataverse tables grow to tens of millions of rows, standard optimization tricks stop working. This lesson teaches you the architectural decisions — elastic tables, partition key strategies, TTL, and time-series patterns — that determine whether your model-driven app scales or buckles under production load.
🔥 Expert31 min readSep 22, 2026Updated Sep 22, 2026
Configuring Dataverse Table Capacity and Storage Partitioning: Managing Large-Table Performance, Elastic Tables, and Time-Series Data Strategies in Model-Driven Apps
Introduction
Picture this: your field service organization has been running a model-driven app for three years. The technician visit log table has grown to 47 million rows. Users are complaining that views take 12–15 seconds to load, Quick Find searches time out, and your Power Automate flows that aggregate weekly metrics are failing with timeout errors. Your Dataverse storage capacity dashboard is showing a frightening upward trend, and you have a leadership meeting in two weeks where someone is going to ask what you're doing about it.
This isn't a hypothetical. It's one of the most common pain points for teams that built thoughtful Dataverse data models early on but didn't architect for scale. The good news is that Dataverse has evolved significantly in this area. Elastic tables, storage partitioning, and time-series design patterns give you real architectural tools — not just indexing tricks — for handling large-table scenarios. The bad news is that these tools require deliberate configuration decisions made before your table hits critical mass, and they involve trade-offs that aren't obvious from the documentation.
By the end of this lesson, you'll understand not just how to configure these features, but why Dataverse's underlying storage architecture makes them necessary, how to evaluate whether elastic tables are right for your scenario, and how to design time-series data strategies that scale gracefully inside model-driven apps.
What you'll learn:
How Dataverse storage is physically organized and why table size creates performance degradation
How to evaluate a table's growth trajectory and identify when standard tables will fail at scale
How elastic tables differ from standard tables architecturally and when to choose one over the other
How storage partitioning works in elastic tables and how to design a partition key strategy for time-series data
Practical performance patterns for surfacing large-table data inside model-driven app forms and views without overwhelming users or the platform
Prerequisites
This lesson assumes you're comfortable with Dataverse fundamentals — you understand tables, columns, relationships, and the difference between managed and unmanaged solutions. You should have hands-on experience building model-driven apps, including configuring views, forms, and security roles. Familiarity with the Dataverse Web API and basic OData query syntax will help for the sections on API-level partition key queries. If you need a refresher on table fundamentals, start with Dataverse Fundamentals: Tables, Columns, and Rows Explained for Power Apps Makers.
How Dataverse Actually Stores Your Data
Before you can make good architectural decisions about large tables, you need a mental model of what's happening underneath. Dataverse doesn't just drop your rows into a generic SQL Server database. It's a multi-tenant, metadata-driven platform that abstracts storage into a structured hierarchy, and that abstraction has specific performance implications at scale.
Standard Dataverse tables are backed by Azure SQL Database. Each environment gets a dedicated logical database, but the physical infrastructure is shared across tenants (with appropriate isolation). Your rows live in Azure SQL tables, and Dataverse's query layer — the Organization Service and Web API — translates your FetchXML or OData queries into T-SQL, applies security filtering (including your row-level security model from business units), and returns results.
This architecture is excellent for transactional workloads: creating records, updating fields, enforcing business rules, triggering workflows. Azure SQL is highly optimized for OLTP patterns. Where it starts to struggle is with tables that have:
Very high row counts (typically above 10–20 million rows, depending on column count and query patterns)
Wide rows (many columns, especially text-heavy columns)
Mixed access patterns — some users need current records (last 30 days), but the table retains years of history
High write velocity — IoT-style scenarios where thousands of records per minute are being inserted
The reason these patterns hurt performance isn't mysterious. When you run a Dataverse view query against a 50-million-row table, Azure SQL has to execute that query while simultaneously respecting Dataverse's security predicates (which add JOINs for business unit and team-based access — see Dataverse Security: Business Units, Security Roles, and Teams for context on how those work). Even with good indexing, the query planner has to consider massive amounts of data. Index fragmentation grows faster. Statistics go stale faster. And because this is a managed, multi-tenant service, you can't just run REBUILD INDEX whenever you want.
Dataverse does maintain automatic indexes on primary keys, lookup columns, and columns you mark as searchable. But you cannot create arbitrary composite indexes, and you cannot control index maintenance schedules. This is the fundamental constraint that makes architectural decisions at the table design level so important.
Key insight
The performance problems you'll hit with large Dataverse standard tables aren't primarily about missing indexes you could add. They're about query plan complexity in the presence of mandatory security predicates applied to very large datasets. You can't index your way out of this — you need to architect around it.
Diagnosing a Large-Table Problem
Before you reach for elastic tables or any other advanced pattern, it's worth diagnosing exactly where your performance problem lives. Not every slow view is a storage problem.
Start in the Power Platform Admin Center. Navigate to your environment, then to Resources > Capacity. The Dataverse capacity dashboard shows you database consumption broken down by table. Sort by "Table Size" descending. You're looking for tables where the row count and storage consumption are growing at a rate that will become a problem in the next 6–12 months — not just tables that are large today.
A table with 5 million rows but stable growth (say, it adds 10,000 rows per month) is very different from a table with 2 million rows growing at 500,000 rows per month. The latter needs architectural attention now.
Next, look at your actual query performance. The Dataverse query execution engine will return performance data in API response headers. When you're testing a view query via the Web API, watch for the Retry-After header (indicating throttling) and check the actual elapsed time. You can also use the Performance Center in some environments (accessible via the Power Apps maker portal under Settings) to see slow plugin and query metrics.
For model-driven app views specifically, open your browser's developer tools (F12), go to the Network tab, and trigger a view load. Look for the XHR request to /api/data/v9.x/ — the response time on that request is your raw query time before any rendering overhead. If you're seeing 3+ seconds on a simple view query against a large table, you have a storage/query problem worth addressing.
Common diagnostic findings and what they tell you:
Symptom
Likely Cause
View loads slow but searches fast
Missing view-level filter; too many rows returned
Quick Find times out
Searchable column set is too broad; no effective filter
Aggregation queries fail
Table too large for real-time rollup calculation
Insert operations slow down over time
Table fragmentation; large row count in clustered index
Performance degrades after business hours
Cross-table security predicates on very large tables
Understanding Elastic Tables: Architecture and Trade-offs
Elastic tables are Dataverse's answer to high-volume, high-velocity data scenarios. Introduced in 2023 and now generally available, elastic tables are backed by Azure Cosmos DB (specifically, the NoSQL API) rather than Azure SQL. This is not a cosmetic distinction — it's a fundamentally different storage architecture with different strengths and limitations.
Cosmos DB is a distributed, schema-flexible NoSQL database optimized for horizontal scale and high throughput. It stores data as JSON documents and partitions that data across physical nodes based on a partition key you define. Within a partition, data is organized by a "row key" (analogous to a primary key). Queries that filter on the partition key are extremely fast because Cosmos DB can route them directly to the appropriate physical node — no full table scan required.
Here's what this means practically for your Dataverse architecture:
What elastic tables do well:
Storing very high volumes of records (hundreds of millions to billions of rows) without significant query degradation
Absorbing high write velocity (thousands of inserts per second) because Cosmos DB is designed for this
Serving queries that are naturally partitioned — "give me all telemetry for device X in the last 7 days"
Storing variable-structure data where not all rows have the same columns populated
What elastic tables do NOT do well:
Many-to-many relationship patterns (no native M:M support via relationship tables in the usual sense)
Queries that need to aggregate or filter across partitions (cross-partition queries are slow and expensive in Cosmos DB)
Complex security models using Dataverse's standard business unit hierarchy (elastic tables have simplified security)
FetchXML features that depend on SQL JOIN semantics
Lookup columns pointing to elastic tables from standard tables (limited support)
Warning
If your application requires the full Dataverse relational model — including cascade relationships, record sharing, field-level security, and complex security role inheritance — elastic tables are not a drop-in replacement for a standard table. They're a specialized tool for specific data patterns.
Creating an Elastic Table
You create elastic tables in the Power Apps maker portal or programmatically via the Web API. In the maker portal, navigate to Tables, click New table, and in the Advanced settings section, change the Table type from "Standard" to "Elastic."
The critical fields you configure at creation time:
Partition ID: Every elastic table has a built-in partitionid column (a string column). This is the partition key. When you insert a record, you specify the partition ID. When you query records, you should filter by partition ID whenever possible. You cannot change the partition ID of an existing record after insert without deleting and re-inserting it. This decision is irreversible at the record level.
Time to Live (TTL): Elastic tables support automatic record expiration. You can configure a default TTL at the table level (records expire after N days by default) or set TTL per record. This is invaluable for transient data — event logs, telemetry readings, session data — where you want automatic cleanup without building a custom purge process.
JSON columns: Elastic tables support a special "JSON" data type column that stores arbitrary JSON. This is the variable-structure capability — useful when different records need different fields (e.g., event data where each event type has a different payload structure).
After creating the elastic table, you add columns just as you would with a standard table. The table appears in the model-driven app table list and can be added to forms and views with some caveats we'll cover shortly.
This is where most implementations either succeed or fall apart. Your partition key strategy determines whether your elastic table performs like a dream or a nightmare under production load. Getting it wrong is expensive because migrating data between partitions means deleting and recreating records.
The fundamental principle: your partition key should match the most common filter in your query pattern. If 90% of your queries ask "give me records for Customer X," then Customer ID is your partition key. If 90% of queries ask "give me records for the past 7 days for Device Y," you need to think carefully about whether to partition by device or by time period.
Anti-Pattern: Using a High-Cardinality, Query-Agnostic Value as the Partition Key
A common mistake is using the Dataverse row ID (GUID) as the partition key, or using a value that distributes data perfectly evenly but doesn't match any real query pattern. Yes, even distribution across partitions is good for write throughput. But if you never query "give me all records with partition ID = {this specific GUID}," you've optimized for writes at the cost of reads. Every query becomes a cross-partition query.
Anti-Pattern: Partitioning by Calendar Month When You Query by User
Another mistake is letting time drive the partition key when time isn't actually the primary query filter. In a support ticket log scenario, if users always look at "my tickets" rather than "all tickets this month," partitioning by month means that every user-specific view is a cross-partition query that has to hit every time-based partition.
Good Pattern: Composite Logical Partition Keys for Time-Series Data
For time-series scenarios where you genuinely need both entity-scoped and time-scoped filtering, a common effective pattern is a composite partition key — concatenating two values into a single string partition ID.
Consider an IoT device telemetry table. Devices send readings every 30 seconds. You have 10,000 devices. Users query "show me the last 100 readings for Device X." Operations teams query "show me all anomalous readings from the last hour across all devices."
A partition key of {DeviceId}_{Year}_{Month} gives you:
All readings for a specific device in a given month in one partition
Manageable partition sizes (readings per device per month, not all readings for all time)
Efficient queries for the common "latest readings for Device X" pattern (filter on partitionid starting with DeviceId)
Acceptable cross-partition queries for the "anomalies in last hour" pattern (querying 1–2 months' worth of partitions, not all of them)
In practice, setting this partition key when inserting via the Web API looks like this:
GET /api/data/v9.2/cr_devicetelemetries
?$filter=partitionid eq 'DEV-10042_2025_01'
and cr_readingtimestamp gt 2025-01-14T00:00:00Z
&$orderby=cr_readingtimestamp desc
&$top=100
This query hits a single partition and returns in milliseconds even if the total table has 500 million rows. The partition key filter is the key — without it, you'd be cross-partition scanning.
Partition Key Strategy for Customer-Facing CRM Data
In a B2B CRM scenario, consider an activity log table that records every interaction (email, call, meeting, note) across your customer base. You have 5,000 accounts and expect 2,000 activity records per account per year.
At year 5, that's 50 million rows. The primary query pattern is "show me all activities for Account X." Secondary pattern is "show me all activities in the last 30 days for my territory."
Partition key: {AccountId} — simple, direct, matches the primary query.
The secondary pattern (cross-territory, cross-account) is inherently a cross-partition query. You accept that this is slower and potentially build a dedicated aggregate/summary table for territory reporting rather than trying to make cross-partition elastic table queries fast. This is the right trade-off for this scenario.
Key insight
When designing partition keys for elastic tables, you're essentially making a bet on your primary access pattern. Secondary patterns that don't match the partition key will be slower. The solution isn't to find a partition key that satisfies all patterns equally — it's to accept the trade-off and build separate aggregation or caching strategies for secondary patterns.
Time-Series Data Strategies in Model-Driven Apps
Elastic tables solve the storage and query performance problem for time-series data. But surfacing that data meaningfully inside a model-driven app requires additional design decisions, because model-driven apps were built around the assumption of relatively small, human-readable record sets.
A model-driven app view is not designed to display "the last 48 hours of temperature readings for 10,000 IoT devices." If you point a standard Dataverse view at your elastic telemetry table, you'll get a paginated grid showing 50 rows at a time with no meaningful context. Users will struggle to get value from it.
The most robust pattern for time-series data in model-driven apps is a two-table architecture:
Summary table (standard Dataverse table): One row per entity per time period. For IoT telemetry, this might be one row per device per day, storing aggregate metrics — min/max/avg reading, anomaly count, last reading value. This table has tens of thousands of rows (10,000 devices × 365 days × 3 years = ~11 million, which is manageable). Users see this in a standard model-driven view.
Detail elastic table: All raw readings. Hundreds of millions of rows. Users never browse this directly. It's queried programmatically when they click into a specific summary row to see the raw data behind it.
In the model-driven form for a device summary record, you surface the raw detail data through a custom page (using a Canvas app component embedded in the model-driven shell) rather than a subgrid. The canvas component takes the Device ID and date from the current form context, constructs a filtered Web API call to the elastic table with the appropriate partition key, and renders the results in a gallery or chart.
Pattern 2: Contextual Filtered Views with Aggressive Defaults
If you do want to expose elastic table data directly in model-driven app views, you need to design those views with aggressive filtering so that no view ever executes a cross-partition query.
Start with the view filter. In the view designer, set a filter that includes the partition key column if the view is always accessed from a specific account or entity context. For example, a view embedded as a subgrid on an Account form should always be pre-filtered to partitionid eq {current account ID}. The subgrid relationship filter handles this automatically if you've set up the table relationship correctly.
For standalone views in the site map, set a default filter for time range. A view of "Device Telemetry - Last 7 Days" with a filter on cr_readingtimestamp gt {today - 7 days} will be much faster than an unfiltered view even if it's still cross-partition — because Cosmos DB can skip partitions outside the time range if your partition key includes time components.
Tip
When you include time-period segments in your partition key (like DeviceId_Year_Month), Dataverse's query layer is smart enough in many cases to prune partitions that don't overlap with your time filter. This is called partition elimination and it's why composite time-based partition keys can significantly improve even "secondary" time-range queries.
Pattern 3: Time-to-Live for Automatic Data Lifecycle Management
One of the most practical time-series strategies that model-driven apps don't natively expose — but that you absolutely should configure — is TTL (Time to Live) on elastic tables.
For raw telemetry data, you almost certainly don't need to retain every reading forever. You need high-resolution data for recent periods (last 30 days) and aggregated summaries for historical periods. The raw telemetry older than 90 days can be deleted. TTL handles this automatically.
You configure TTL at the table level via the Dataverse Web API or the maker portal:
In the maker portal, edit the elastic table and look for the "Time to live" setting in the table properties. Set the default TTL in seconds. For 90 days: 90 × 24 × 60 × 60 = 7,776,000 seconds.
You can also set TTL per record by populating the ttlinseconds column at insert time, which overrides the table default. This is useful when certain high-value records should be retained longer.
TTL deletion happens asynchronously in Cosmos DB — Cosmos marks records as expired and deletes them in the background. They become immediately invisible to queries once expired, but the storage isn't reclaimed instantaneously. This is fine for most scenarios, but don't rely on TTL for compliance-grade deletion where you need a specific deletion timestamp guarantee.
Large Standard Tables: What You Can Do Without Elastic
Not every large-table problem requires elastic tables. Many scenarios involve tables with 5–20 million rows that need relational features, complex security models, or tight integration with the model-driven app UX that elastic tables don't support. Here's your toolkit for these scenarios.
Archival Table Pattern
Create a parallel "archive" version of your table — for example, Service Visit (active, last 2 years) and Service Visit Archive (historical, 3+ years old). Build a scheduled Power Automate flow or Azure Function that moves records from the active table to the archive table monthly.
The archive table can be a standard Dataverse table with reduced columns (only the fields needed for historical reference, not operational fields), which keeps it leaner. You surface archive records in the model-driven app as a separate read-only view or a separate section on the main entity form, so users know they're looking at historical data.
This pattern works best when there's a clear temporal boundary and when users rarely need to query across both active and archive tables simultaneously.
Selective Column Optimization
Wide tables — tables with 80+ columns — degrade faster than narrow tables at the same row count because each row takes more storage and the query planner has more to work with. Audit your table's columns rigorously.
Are you storing data in Dataverse columns that doesn't need to be there? JSON blobs, large text fields with unstructured data, files stored as column values rather than as Dataverse file attachments? These belong in Azure Blob Storage or the Dataverse file/image column types (which store in Azure Blob, not in the SQL database rows), not in text columns that inflate every row.
For columns related to Configuring Dataverse Auditing and Field-Level Change History in Model-Driven Apps — be deliberate about which columns have auditing enabled. Audit history generates its own records in the audit log table, which can itself become a massive table. Disable auditing on high-write columns where you don't genuinely need change history.
View Optimization for Large Tables
For large standard tables, your view design is your first line of defense. Every view exposed to users should:
Have a meaningful default filter. "Active records" isn't enough if "active" still means 10 million rows. Add time-based filters: "Created in the last 90 days," "Modified in the last 30 days."
Avoid unindexed sort columns as primary sort. Sorting by a lookup column's related name (e.g., sorting Service Visits by the Customer Name) requires a JOIN and is significantly slower than sorting by a native column on the table.
Limit displayed columns. Every column in a view is retrieved in the query. A view with 15 columns on a 20-million row table retrieves significantly more data than a 5-column view.
Disable Quick Find across all columns. The Quick Find view configuration determines which columns are searched when a user types in the search box. If you have 20 columns marked as searchable, every Quick Find query runs a LIKE predicate across 20 columns. Reduce this to 3–5 truly meaningful search columns for large tables.
The "All Records" view, if exposed in your model-driven app site map for a large table, will execute a query with no meaningful filter except security predicates. For tables with millions of rows, this view will always be slow. Consider removing it from navigation entirely and replacing it with context-specific filtered views.
Rollup Column Strategy for Large-Table Aggregations
If you have a parent table that needs to show aggregated metrics from a large child table (count of visits, sum of revenue, last activity date), configuring rollup columns on the parent table is far more efficient than running aggregate queries at view time. Rollup columns are recalculated by a background system job on a schedule (every hour by default), so the aggregate value is always pre-computed. When a user opens an Account record and sees "Total Revenue: $2.4M," that number was calculated in the background, not computed from a live aggregate query across millions of opportunity rows.
The limitation: rollup columns have a maximum of 50,000 related records considered in the aggregation. Above that limit, the rollup calculation silently returns incorrect results. This is a hard ceiling that isn't well-documented. If your child table can exceed 50,000 records per parent, you need a custom aggregation approach (a scheduled Power Automate flow or Azure Function writing a summary to a field on the parent record).
Elastic Tables in Model-Driven App Security Context
Elastic tables have a simplified security model compared to standard Dataverse tables. Understanding the differences matters before you design your security architecture.
Standard Dataverse tables support the full security hierarchy: organization-level, business-unit-level, team-based, and owner-based record access. The business unit hierarchy that's explained in Dataverse Security: Business Units, Security Roles, and Teams does not apply to elastic tables in the same way.
For elastic tables, security is enforced at the table level (can this security role read/write/delete from this table?) but not at the individual record level using owner-based security. There is no record owner concept in elastic tables. A user who has read access to an elastic table can read all records in it (subject to any explicit query filters you apply in code, but not enforced by the platform's row-level security).
This is a significant architectural constraint. For IoT telemetry data where all users legitimately see all device data, it's fine. For customer-specific data where user A must not see user B's records, it's not fine — and you can't solve it by using the standard Dataverse security model on an elastic table.
In these cases, your options are:
Apply security at the application layer: your custom pages and API queries always filter by the current user's authorized entities, and you trust that no user will construct their own Web API query (appropriate for internal apps with controlled access)
Use standard tables for the data that needs record-level security, accepting the scale constraints
Implement a partition key strategy where partition IDs correspond to organizational boundaries, and use application-layer logic to enforce that users can only request their authorized partitions
None of these is as clean as Dataverse's native record security. Factor this into your elastic table decision.
Hands-On Exercise
This exercise walks you through designing and testing an elastic table for a field service work order event log scenario. You'll create the table, insert records with partition keys, query them efficiently, and examine the performance difference between partition-key-filtered queries and cross-partition queries.
Setup
You'll need:
A Power Apps environment with System Customizer or System Administrator privileges
The Dataverse Web API accessible (your environment URL, formatted as https://{orgname}.api.crm.dynamics.com)
A tool for making API calls — Postman, Insomnia, or the built-in Power Apps developer tools
Step 1: Create the Elastic Table
Navigate to make.powerapps.com and select your environment.
Go to Tables in the left navigation.
Click New table, then select Add columns and data to get to the full table editor.
In the table properties panel, expand Advanced options.
Change Table type to Elastic.
Name the table Work Order Event Log with schema name cr_workordereventlog.
Save the table.
Add these columns:
cr_workorderid — Text, Required (will store the work order number as a string for partitioning)
cr_technicianid — Text (technician's Dataverse user GUID as string)
cr_eventdetails — Multiline Text
cr_locationcoordinates — Text (store as "lat,lng" string for simplicity)
Note that the partitionid column already exists on every elastic table — you don't add it.
Step 2: Define Your Partition Key Strategy
For work order events, the primary query is "show me all events for Work Order WO-20250115-4423." The partition key should be the work order ID.
Work orders also have a time dimension. A single work order typically has 8–15 events over its lifecycle, so one partition per work order is fine — they'll be small. You won't use composite keys here.
Step 3: Insert Test Records via Web API
Insert events for two different work orders. Notice the partitionid matches the cr_workorderid value.
POST https://{orgname}.api.crm.dynamics.com/api/data/v9.2/cr_workordereventlogs
Content-Type: application/json
Authorization: Bearer {your_token}
{
"cr_workorderid": "WO-20250115-4423",
"cr_eventtype": 1,
"cr_eventtimestamp": "2025-01-15T08:00:00Z",
"cr_technicianid": "a8f3c2e1-...",
"cr_eventdetails": "Work order created by dispatch",
"partitionid": "WO-20250115-4423"
}
Insert 6–8 events for WO-20250115-4423 and 6–8 for a different work order (WO-20250115-4424), with increasing timestamps.
Step 4: Query with and without Partition Key
Query 1 — With partition key (fast):
GET https://{orgname}.api.crm.dynamics.com/api/data/v9.2/cr_workordereventlogs
?$filter=partitionid eq 'WO-20250115-4423'
&$orderby=cr_eventtimestamp asc
Query 2 — Without partition key (cross-partition, slower):
GET https://{orgname}.api.crm.dynamics.com/api/data/v9.2/cr_workordereventlogs
?$filter=cr_eventtype eq 1
&$orderby=cr_eventtimestamp desc
In your API tool, compare the response times. With only a handful of test records the difference will be small, but the query plan difference is visible in the response. In production with millions of rows, Query 1 stays fast while Query 2 degrades.
Step 5: Configure TTL
Update the table via the Web API to set a default TTL of 365 days (31,536,000 seconds). In the maker portal, edit the Work Order Event Log table, find the TTL setting in advanced options, and set it to 31,536,000 seconds.
Observe that the view loads quickly because the filter is partition-key-aligned. Then remove the partition ID filter from the view and reload — notice the difference.
Common Mistakes & Troubleshooting
Mistake 1: Creating an Elastic Table When You Need Relationships
Elastic tables don't support being the "many" side of a standard 1:N relationship with full cascade behaviors. If your scenario requires that deleting a parent Work Order also cascades to delete all related elastic table records, you'll need to implement that cleanup logic yourself (via plugin or Power Automate triggered on work order deletion).
Teams often discover this after the elastic table is in production. Plan your delete and archival logic explicitly.
Mistake 2: Changing Partition Strategy Mid-Flight
You cannot update the partitionid of an existing record. If you deploy with partition key = {WorkOrderId} and later decide you want partition key = {WorkOrderId}_{Year}, you need to:
Write a migration script that reads all existing records and reinserts them with new partition IDs
Delete the old records
At 50 million rows, this is a massive operation. Get your partition key strategy right before you go live.
Mistake 3: Using Elastic Tables for Data That Needs Auditing
Dataverse's standard auditing capabilities don't apply to elastic tables in the same way. Elastic tables don't support the full audit log feature (tracking field-level changes over time). If your data needs compliance-grade audit trails, either use a standard table or implement your own audit trail (a second elastic table that logs changes to the first).
Mistake 4: Forgetting Security Implications of No Row-Level Security
The most dangerous operational mistake: deploying an elastic table with sensitive customer data and relying on application-layer filtering to enforce data isolation, without testing that your API endpoints actually enforce those filters for every possible query path.
A determined user with Web API access and the right table-level privilege can call the API directly without your application-layer filters. In a standard table, row-level security enforced by Dataverse prevents this. In an elastic table, it doesn't. Audit your application's API access patterns and consider whether the elastic table's simplified security model is acceptable for your data classification.
Troubleshooting: Cross-Partition Query Timeouts
If you're seeing timeout errors on elastic table queries, the first diagnostic step is checking whether your query includes a partitionid filter. If it doesn't, you're doing a cross-partition query. The fix is almost always to add the partition key filter.
If your query does include a partition key filter but still times out, check:
Is the partition growing too large? A single partition with 10 million records is large even for Cosmos DB. Consider adding a time segment to your partition key.
Are you returning too many columns? Add $select to your query to fetch only the columns you need.
Is your sort column the partition key or row key? Sorting by arbitrary columns within a large partition can be slow.
Troubleshooting: TTL Not Expiring Records
TTL in Cosmos DB is eventually consistent. Records marked as expired become query-invisible immediately but may not be physically deleted for hours. If you're seeing "expired" records still appearing in results, check:
Is the ttlinseconds value set correctly on the record (or the table-level default)?
Is there a clock skew between when you set the TTL and the Cosmos DB server time?
Have you enabled TTL at the table level? Per-record TTL only works if TTL is enabled on the table.
Summary & Next Steps
You've covered significant ground in this lesson. Let's consolidate the key decisions you'll make when confronting large-table scenarios in Dataverse.
The decision tree: When you identify a table approaching scale limits, first ask whether you need the full Dataverse relational security model, cascade relationships, and audit logging. If yes, you're working with standard tables — optimize through archival patterns, view filtering, selective column reduction, and rollup pre-aggregation. If no, elastic tables open up Cosmos DB's scale and velocity capabilities, but you're trading row-level security and relational features for performance.
Partition key design is architecture: Your elastic table partition key is as important as your table schema. It must reflect your primary query pattern, remain stable over the data's lifetime, and distribute data into reasonably sized chunks. Composite keys combining entity ID and time period are the most flexible for time-series scenarios.
Model-driven app UX must adapt: Elastic tables and large standard tables both require rethinking how users interact with data. Views need aggressive default filters. Aggregations need to be pre-computed. Raw detail data should be surfaced through custom pages or context-specific filtered subgrids, not "all records" views.
TTL is your lifecycle management tool: For transient and time-series data, configure TTL from day one. It prevents table bloat and eliminates the need for custom purge processes.
Large-scale Dataverse architecture rewards deliberate design. The tables you build today will be carrying production load three years from now. The investment in understanding these patterns before you hit critical mass is the difference between a platform that grows with your organization and one that becomes a crisis.
