Forum Discussion
High volume Dataverse dataflow times out avoid NESTED FROMs and/or JOINs best approach to resolve?
Background
We have a Gen1 Dataverse dataflow that calculates aggregate totals per record (yearly, prior-year, lifetime, and household rollups) and writes them to two output entities, Merge Account and Merge Contact. Each output entity is built from a chain of ~11 Table.NestedJoin (JoinKind.FullOuter) joins over ~12 upstream aggregate queries.
The problem
The dataflow refreshes without issue on smaller environments but times out on high-volume ones with:
Microsoft SQL: "The updated two minutes (2) timeout period elapsed prior to completion of the operation. Please avoid using SELECT *, or NESTED FROMs and/or JOINs to execute the query with default five minutes (5) timeout." ErrorCode = 10478; Number = 40000
(Some environments show error 40197 instead.) It stops on the Merge step.
What we've implemented so far
- We've built a monitoring solution: a cloud flow that detects dataflow refresh failures and logs the details (dataflow name, ID, and error message) into a custom Refresh Audit table, surfaced in a model-driven app so the team has visibility into failures.
- We've analyzed the failure across multiple environments and confirmed it correlates with data volume — the same dataflow completes on smaller datasets and times out on larger ones, with the filtered row volume and per-record density appearing to be the main drivers.
- We've identified that the full-outer joins don't appear to fold, so a large nested query is sent to the Dataverse TDS endpoint and hits the query timeout — i.e. it's a timeout, not a data/row error.
Approaches we're evaluating — would these be effective?
- Increasing the query/command timeout for a Gen1 Dataverse dataflow — is that configurable, and where?
- Using Table.Buffer on the join inputs to force local evaluation and avoid pushing the nested query to the TDS endpoint — would this reliably help, or just shift the bottleneck?
- Staging the upstream queries into computed/staged entities (or a separate staging dataflow) — is this the recommended pattern, and does it need enhanced compute / premium?
- For very large datasets (1M+ rows), pushing the aggregation source-side (FetchXML aggregate / SQL view) — is this the more reliable long-term approach?
- Is there a supported way to get per-step timing / query diagnostics for a dataflow refresh, to pinpoint the heaviest step and enrich our monitoring?
Any guidance on which of these works best for high-volume Dataverse dataflows with heavy full-outer join chains would be much appreciated. Thanks!
Hi Tejakm123
Thanks for the update the fact that it always fails at Step 8 is actually a helpful clue. It usually means Steps 1–7 fit under the TDS endpoint's query-size limit, and Step 8 is simply the point where the accumulated nested-join plan gets too big to accept. It's rarely Step 8's own logic that's wrong.
A few troubleshooting steps to have a try in order:- Stage right before Step 8. Load the output of Steps 1–7 into its own entity (ideally a separate staging dataflow), then have Step 8 onward read from that staged entity. This resets the query plan and, in most cases like this, is enough on its own to get the refresh through.
- Check the join key at Step 8. Full-outer joins blow up quickly if the key has duplicates or nulls on either side. Compare distinct-key vs. total-row counts on both inputs — if there are duplicates, deduplicate/aggregate before joining.
- Swap the full-outer chain for a "keys + left joins" pattern. Build one base table containing all distinct keys (union from every source), then do only left joins onto it. Same result, much lighter SQL, folds cleanly.
- Skip Table.Buffer here it breaks folding and usually just changes which error you get. Staging (option 1) does what you'd hope Buffer would do, but properly.
- Long-term: for Annual Giving–style data that will only grow, the sustainable fix is to move the aggregation out of Power Query via Link to Microsoft Fabric / Synapse Link, or a FetchXML aggregate / Dataverse view. Gen1 dataflows just aren't built to carry 11 chained full-outer joins at scale.
- Start with #1 it's the smallest change and usually does the trick. If you can share the M for Step 8, I'm happy to point out exactly where to cut the chain.
Thanks,
Srikanth Cheri
Community Support Team
5 Replies
- Zanqueta
Super User
Hi Tejakm123. I am not completely certain, but based on what you described this looks more like a Dataverse Gen1 limitation rather than just a timeout setting issue.
What can work in this case is to simplify the design. Chains of Full Outer Joins usually create very heavy queries, and in Dataverse these are pushed to the TDS endpoint, which then times out as the data volume grows. It may work with small datasets, but it does not scale.
My suggestion is to consider two main changes. First, try to avoid chained Full Outer Joins. Instead, you can create a base table of keys and use only left joins, or use an append (union) followed by a pivot. These approaches are much lighter and tend to perform better.
Second, split the process. Rather than calculating everything in a single entity, stage each aggregation separately and materialise the results before the final step. Then the final entity only performs simple joins on already prepared data. This avoids generating one very large query.
For larger datasets, the most reliable approach is to move the aggregation closer to the source. That can be done using Dataverse rollups or by moving the logic to an analytical layer such as a lakehouse or warehouse.
Regarding the other options, increasing the timeout does not fix the root issue, and Table.Buffer often makes things worse because it breaks query folding and increases memory usage.
In short, the problem is query complexity rather than configuration. Simplifying joins, adding staging, or moving the logic upstream is usually the best way to resolve it. - v-csrikanth
Community Support
Hi Tejakm123
We would like to inquire whether have you got the chance to check the solutions provided by Zanqueta in commiunity to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.
Thanks,
Srikanth Cheri
Community Support Team.- Hi v-csrikanth
Thank you for following up. I reviewed the solutions shared by @Zanqueta, but the issue is still occurring.
This is a large Annual Giving query with 11 processing steps, and it consistently fails at Step 8. Since the earlier steps complete successfully, any additional guidance on troubleshooting that specific step, improving the query, or identifying possible capacity/performance limitations would be very helpful.
I would be grateful for any further suggestions from the Microsoft Fabric community.
Thank you!
- v-csrikanth
Hi Tejakm123
Thanks for the update the fact that it always fails at Step 8 is actually a helpful clue. It usually means Steps 1–7 fit under the TDS endpoint's query-size limit, and Step 8 is simply the point where the accumulated nested-join plan gets too big to accept. It's rarely Step 8's own logic that's wrong.
A few troubleshooting steps to have a try in order:- Stage right before Step 8. Load the output of Steps 1–7 into its own entity (ideally a separate staging dataflow), then have Step 8 onward read from that staged entity. This resets the query plan and, in most cases like this, is enough on its own to get the refresh through.
- Check the join key at Step 8. Full-outer joins blow up quickly if the key has duplicates or nulls on either side. Compare distinct-key vs. total-row counts on both inputs — if there are duplicates, deduplicate/aggregate before joining.
- Swap the full-outer chain for a "keys + left joins" pattern. Build one base table containing all distinct keys (union from every source), then do only left joins onto it. Same result, much lighter SQL, folds cleanly.
- Skip Table.Buffer here it breaks folding and usually just changes which error you get. Staging (option 1) does what you'd hope Buffer would do, but properly.
- Long-term: for Annual Giving–style data that will only grow, the sustainable fix is to move the aggregation out of Power Query via Link to Microsoft Fabric / Synapse Link, or a FetchXML aggregate / Dataverse view. Gen1 dataflows just aren't built to carry 11 chained full-outer joins at scale.
- Start with #1 it's the smallest change and usually does the trick. If you can share the M for Step 8, I'm happy to point out exactly where to cut the chain.
Thanks,
Srikanth Cheri
Community Support Team - v-csrikanth
Hi Tejakm123
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Thank you.
