Forum Discussion
High volume Dataverse dataflow times out avoid NESTED FROMs and/or JOINs best approach to resolve?
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
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.