Max iterations (100) reached for batch Resolution, please set 'spark.sql.analyzer.maxIterations' to

[PraveenVeli]

Hi,

I'm encountering the error 'Max iterations (100) reached for batch Resolution, please set 'spark.sql.analyzer.maxIterations' to a larger value.' while executing a Spark SQL script from the notebook. The script is not complex; it queries 1K records from a delta table in Lakehouse A in workspace A and compares them with a delta table in Lakehouse B in workspace B, writing the differences to the delta table in Lakehouse B.

[nilendraFabric · Accepted solution]

Hello @PraveenVeli 

In Spark SQL’s context, “iterations” refer to the number of passes the query analyzer makes through the logical query plan to resolve references, infer types, and apply optimizations

Why This Applies to Your Fabric Scenario
1. Workspace Boundary Resolution
3. Fabric treats Lakehouses in different workspaces as separate catalogs, forcing Spark to:
• Verify table existence in both environments
• Reconcile schemas across workspaces
• Handle potential credential handoffs

Even for 1k rows comparison 

-- Implicitly creates nested plans for:
1) Data fetch from Lakehouse A
2) Data fetch from Lakehouse B
3) Join operation
4) Delta transaction log checks
5) Insert operation

Try

spark.conf.set("spark.sql.analyzer.maxIterations", "200")

And do df.explain(mode="extended")

Look for Cartesian products or complex subquery patterns

Try

OPTIMIZE delta_table ZORDER BY primary_key;

please give a try and let me know if this works 

[v-nmadadi-msft]

Hi @PraveenVeli 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[nilendraFabric]

@PraveenVeli , if this answered your question please accept this solution 

[PraveenVeli]

Thank you @nilendraFabric , information you provided greatly helps. In my case (It's in the same lines of what you said), the issue was that I provided incorrect column names in my CTE, which is causing this error. I'm a bit surprised it's not generating the relevant error message. I have a CTE (that retrieves data from Lake-house A in Workspace B) and then use it within the Merge statement to integrate data into Lake-house B in Warehouse B. If the column name does not match the destination (Lake-house B), it throws an actual error indicating that it can't find the field name. I'm running my notebook in Workspace B.