I'm trying to find the reason for this error happening. I have a function in ADX that is triggered when data is ingested into a table. Each night we add about 100k rows. This function has two lookups and is unable to complete.
Any tips on how to troubleshoot this? Is to too many rows or columns? Any error logs I can investigate to figure it out? Is it a scaling issue on the cluster? I would expect ADX to handle 100k rows in a trigger.
The failure is due to a limitation of the lookup operator which is transformed internally into a broadcast join.
Please have a look at the docs of broadcast join here which states that the left side of the join (in broadcast join) which is the right side of lookup (in lookup operator) should be up to a few 10s of MBs.
In the query above, it has 2 lookup operators where each one has a right side of a big result (one is ~135MB and the other is ~400MB) which causes the query to hit that limitation of broadcast join.
In order to estimate the right side size in your case before deciding if to use lookup & broadcast join / regular join operator, you can use this query:
RightSideExpression
| project col1, col2, ..., colN
| summarize sum(estimate_data_size(*))
In order to fix the above query, you need to switch to join operator and make sure to follow query best practices regarding keeping the smaller dataset on the left side of the join.
P.S. We'll update the doc for lookup to mention the limitation.