T-SQL magic cell with parameters in for loop Fabric Python Notebook

Hi austworks19,

Thanks for reaching out to the Microsoft fabric community forum. You're absolutely right Fabric notebooks using spark.sql() are scoped to the default lakehouse of the Spark session, and unfortunately, you can't dynamically switch workspaces or lakehouses mid-session using Spark commands.

Your workaround of constructing the full abfss path and replacing table references with backtick-enclosed paths is spot on and probably the most flexible approach for your use case.

As for using the %%sql magic function from the blog you linked, you're also correct that it requires separate cells for each query, which doesn’t play well with loops or dynamic execution.

If you're looking to keep everything inside a loop, here’s a suggestion that continue using spark.sql() but dynamically rewrite each query to use the full abfss path. You can store the rewritten queries in a list and iterate through them, executing each with spark.sql().

This way, you avoid the cell-per-query limitation and still get access to the correct lakehouse data.

I would also take a moment to thank tackytechtom , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.