You built it clean. Logical tables, sensible relationships, and a data model that made perfect sense on the whiteboard in Sprint 1.
Eighteen months later, you've got 2 million records, 14 integrated systems, and a team of developers who've each added "just one more column." That same schema is now the reason your queries time out and your Power Automate flows are dying at 2 AM on a Tuesday.
If that sounds familiar, you're in good company. And if you're still in the early stages, lucky you. This will help you anyway.
The frustrating thing about Dataverse schema problems is that they're not dramatic at first. There's no breaking error, no deployment failure, no moment where the system says this is a bad idea. The platform lets you do nearly anything. Four hundred columns on a single table?
Go ahead. JSON blobs stuffed into a text field? Nobody stops you. A many-to-many relationship implemented twice, once through a custom junction table and once through a native N:N for the same two entities? Technically possible. Genuinely awful.
Dataverse won't throw errors. Your solution will import. Your app will work.
For a while.
The damage accumulates quietly in the SQL layer you can't see, in API payloads slightly larger than they need to be, in async jobs that take a little longer each week. By the time performance issues are visible in production, the architectural debt is months deep and scattered across dozens of customisations. That's what makes this genuinely risky at enterprise scale: there's no single moment of failure, just a slow erosion that becomes a crisis at the worst possible time.
Here's where it usually starts, and what to do about it.
The Table That Ate Everything
Every enterprise Dataverse environment has one. Usually it's Account or Contact. Sometimes it's a custom entity that started as a simple tracker for a project record, a service request and evolved, column by column, into a 300-field monster.
It happens for understandable reasons. A new requirement comes in. The path of least resistance is adding a column to the existing table. Then another. Then a dozen more across six different sprints, each one individually justifiable, collectively a problem.
The performance impact is real. When Power Automate or a Canvas App retrieves a record from an overgrown table, it's not surgically pulling five fields. It's often dragging significantly more than it needs, inflating payload size and slowing API response times in ways that compound as record volumes grow.
The fix isn't glamorous: audit your tables for actual column usage. Microsoft's Dataverse Analytics in the Power Platform Admin Center gives you usage telemetry. Columns that haven't been populated or queried in 90 days are candidates for deprecation. More importantly, institute a governance rule going forward, every new column needs a business owner and a justification. Use related tables to extend entities rather than endlessly appending to a core one.
It sounds bureaucratic. It is, a little. It's also the difference between a schema that holds up at 5 million records and one that doesn't.
.jpg )
Getting the Column Type Wrong (and Living With It)
This is the one that stings, because it's almost impossible to fix cleanly once it's in production with real data on top of it.
A text field used where a Choice column should have been. Currency values stored as decimals instead of proper Currency types. Whole number fields that later need to support lookup filtering in ways the type doesn't handle well. These feel like minor decisions in a dev environment with 200 records.
At 2 million records with reporting layers and integrations on top, they become structural liabilities.
Choice columns are stored as integers in the underlying database. They filter and sort faster than free text. Currency columns carry built-in exchange rate handling which matters the moment your deployment goes multi-currency, and in enterprise contexts, it almost always does eventually. Getting data types right at creation isn't perfectionism; it's just doing the job properly the first time.
If the damage is already done, the honest answer is: plan a phased data migration sprint. It's painful. It's significantly less painful than trying to re-architect a live production environment while the business is using it.
Cascading Behaviour: Fine in Testing, Catastrophic at Scale
Relationships in Dataverse carry more than structure. They carry cascading rules that govern what happens to child records when a parent is deleted, reassigned, or merged.
The defaults look harmless. In early testing, with a few hundred records, they are harmless. At scale, they're not.
Here's a scenario that plays out in real enterprise environments more often than it should: a business decision to reassign 50,000 Account records to a new owner. If your related Opportunities, Cases, and Activities all have cascade-on-assign enabled, that single business action triggers a background process attempting to update potentially millions of rows simultaneously, in production.
The result is a system slowdown, an async job backlog, and a support ticket that reads like a post-mortem.
Define cascading behaviour explicitly for every relationship at design time. Parental cascading should be reserved for genuinely dependent child entities records that have no business existence independent of the parent. For most cross-entity relationships, set cascade to User-Owned or None and handle ownership logic through business rules or plugins where it needs to be handled at all. Document this decision in your schema of documentation, not as an afterthought but as a deliberate architectural choice.
Alternate Keys: The Integration Feature No One Thinks About Until They Need It
Most enterprise environments have external IDs coming in from ERP systems, third-party platforms, or legacy on-premises applications. Without an alternate key defined on that external ID column, every integration sync must do a retrieve-then-upsert: two API calls where one should be enough.
Multiply across thousands of daily sync operations, and you're generating unnecessary API load that has a real cost both in performance and, depending on your licensing tier, potentially in consumption limits.
Alternate keys also enforce uniqueness, which matters more than most people appreciate until they're debugging a duplicate data problem that's three months old and spread across 80,000 records.
Define alternate keys for every table that participates in external system integration. Keep their narrow composite keys beyond two columns; start creating their own index overhead, and you've traded one problem for another.
The Deeper Issue: Treating Dataverse Like a Relational Database
Underneath everything above is a conceptual mistake that architects trained in traditional RDBMS environments make understandable and repeated.
Dataverse is not SQL Server. It abstracts the data layer on purpose. When you design for it the way you'd design for a relational database deep table hierarchies, junction tables for every many-to-many, highly normalised structures that require multi-hop queries to surface a single piece of information, you end up with a schema that is technically coherent in theory and genuinely painful in practice.
Power Apps and Power Automate are not built for five-join query chains. The platform is built for lookup-friendly, relatively flat structures where the consumer the form, the flow, the report can get what it needs in as few hops as possible.
"Strategic denormalization" is not a dirty word in Dataverse. Storing a frequently accessed value redundantly, so a Canvas App doesn't have to traverse three relationships to display it is not a failure of design discipline. It's an appropriate design for the platform you're actually building on.
The Longer You Wait, the More It Costs
A schema that's running at 80% efficiency at 100,000 records tends to become visibly broken somewhere between 1 and 3 million. The organisations that scale Dataverse well are rarely the ones with the most sophisticated initial architecture. They're the ones that review schema decisions regularly, have governance gates before new customisations go through, and are willing to have the uncomfortable conversation with stakeholders when a quick fix is going to create a structural problem twelve months down the line.
If reading any section of this made you think of a specific table, a specific integration, or a specific relationship in your current environment, that's your starting point.
A schema audit doesn't have to be a six-week engagement. Map your top 10 tables by record volume, column count, and API call frequency. That data alone will surface the majority of where your risk lives.
We've helped enterprise teams across the UK, Europe, and Australia do exactly that before the 2 AM failure, not after it.
If your Dataverse environment is growing and you want to make sure the foundation holds, let's have a conversation.
