Spark SQL Temporary Views on Fabric Schema-Enabled Lakehouses

Gerhard Brueckl wants to create a temporary view:

Some time ago my friend Christian Henrik Reich blogged about how to handle schema-enabled lakehouses in Spark temporary views. We already found a good solution leveraging SQL USE keyword to set the schema once and reference tables by name only in our views. However, after some tests, in particular with notebookutils.notebook.runMultiple, I realized that there are some more things to consider as suddenly my SQL and notebooks stopped working when executed in parallel!

But let set the scope for this blogpost first. We recently built a data platform on Microsoft Fabric where we integrated data from different source systems which were combined into a single schema-enabled lakehouse where each source system had it own schema. Naturally, when querying those schemas, we used temporary views to express our business logic in SQL and then continue working with PySpark. We relied heavily on USE to work around the issue describe by Henrik until we realized that this approach does not work in combination with runMultiple. So we had to find another solution which I will describe here.

Read on for that solution.