Change Lakehouse Pinned in Explorer

[michelsilva]

I use Fabric with version control enabled.

This way, I have a main workspace and derived workspaces, for which I've adopted a naming convention for features under development...

Since the workspace names vary (because I use version control, branching into other workspaces), I can't have the workspace name "hardcoded" into the code.

For notebooks using PySpark, I managed to work around this by obtaining the "workspace" variable:

import sempy.fabric as fabric

workspace = fabric.resolve_workspace_name(workspace=fabric.get_notebook_workspace_id())

However, when using SparkSQL I can't do this!!! I'm trying to use SparkSQL to create Materialized Lake Views, and Fabric requires me to have a lakehouse "pinned" in the Explorer. When I do this and version to another branch, the "pinned" lakehouse remains the same, causing conflicts!

Honestly, I find it bizarre that a code application requires adjustments that depend on a graphical interface and that cannot be altered/parameterized with code!

I've already tried using `USE`...

%%pyspark

workspace = fabric.resolve_workspace_name(workspace=fabric.get_notebook_workspace_id())
lakehouse = 'mp_silver'
schema = 'abc'

stmt = f'USE `{workspace}`.{lakehouse}.{schema};'
print(stmt)
spark.sql(stmt)

which is used in DataBricks... but it doesn't work...

I know it's possible to use PySpark, transforming the query into a parameterized variable...

But I lose the autocomplete functionality of SparkSQL...

stmt = f'USE `{workspace}`.{lakehouse}.{schema};'
print(stmt)
spark.sql(stmt)

Please, does anyone know of a function that allows me to use SparkSQL with the variable name of the workspace?

I'm thinking of using a previous script with Sempy to modify the pinned lakehouse (since I can't edit the pinned lakehouse in an active Spark session)...
... it seems like using a cannon to solve something that should already exist by default.

[michelsilva · Accepted solution]

Thank you so much.

After posting on the forum, I looked for an alternative.

As I said before, it seemed like using a cannon to solve something that should be simple. Haha...

But it worked.

It's a script to modify the pinned lakehouse. I need to insert it into the pipeline as a step before the notebook that creates the MLVs.
https://gist.github.com/michelmetran/aeb87d53040942710a1422ddd0259630

Tks.

[oussamahaimoud]

Hi @michelsilva,

great that it worked for you! if it solves your problem, don't forget to mark it as the solution so others facing the same issue can find it quickly. your gist is a real contribution to the community, thanks for sharing it!

Best regards,

Oussama (Data Consultant & Fabric's Expert)

[oussamahaimoud]

Hi @michelsilva,

Hope you're doing well!

This is indeed a real pain point in Fabric when working with version control and branching strategies. Let me walk you through what's actually possible today and what your realistic options are.

--> PROBLEM :

Fabric's SparkSQL engine resolves the default lakehouse context from the one pinned in the notebook's explorer, and this binding is stored at the artifact level, not in the code. When you branch into a new workspace, that pinned reference doesn't follow the branch context, which creates exactly the conflict you're describing.

--> What actually works today :

the closest thing to a proper solution is to use the three-part fully qualified name directly inside your SparkSQL cells, without relying on USE. Fabric supports this syntax:

sql

%%sql
select * from `<workspace_name>`.`<lakehouse_name>`.`<schema_name>`.`<table_name>`

but of course that puts you back to hardcoding the workspace name, which defeats the purpose.

--> The hybrid approach that avoids hardcoding :

the trick most teams land on is to define your context once in a pyspark cell at the top of the notebook, then inject it into subsequent spark sql cells via spark.conf.set, which is readable inside sql through the ${...} substitution syntax:

python

import sempy.fabric as fabric
workspace = fabric.resolve_workspace_name(workspace=fabric.get_notebook_workspace_id())
spark.conf.set("notebook.workspace", workspace)
spark.conf.set("notebook.lakehouse", "mp_silver")
spark.conf.set("notebook.schema", "abc")

then in your sparksql cells:

sql

%%sql
select * from `${notebook.workspace}`.`${notebook.lakehouse}`.`${notebook.schema}`.your_table

this preserves the %%sql magic, so you keep syntax highlighting, though you do lose some autocomplete on table names since the context isn't resolved at edit time.

--> Regarding materialized lake views specifically :

for the create materialized lake view statement, the same fully qualified reference pattern applies. you can write it like:

sql

%%sql
create materialized lake view `${notebook.workspace}`.`${notebook.lakehouse}`.`${notebook.schema}`.your_view_nameas select ...

this avoids touching the pinned lakehouse at all.

--> The sempy approach you mentioned :

you can indeed use sempy to swap the default lakehouse programmatically before your session, using FabricRestClient to call the notebook update api, but as you said yourself it's using a cannon for something that should be native. it also doesn't work mid-session since Fabric locks the spark context's default lakehouse at session start.

--> Remember that :

the spark.conf + ${variable} pattern is currently the least painful way to stay in sparksql while keeping your workspace dynamic. it's not perfect since autocomplete on table names won't resolve correctly at edit time, but the sql syntax itself remains valid and the cells remain readable as sql. the lack of a native USE <dynamic_workspace> equivalent is a genuine gap in Fabric today, and you're not wrong to find it frustrating given how well git branching otherwise works in the platform.

Here are some useful official references and docs to check for deep and more informations :

- Spark sql in fabric notebooks and the %%sql magic: https://learn.microsoft.com/en-us/fabric/data-engineering/notebook-code-cell-types

- Querying across lakehouses with three-part names: https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-notebook-explore

- Materialized lake views documentation: https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-materialized-view

- Fabric git integration and branching workspaces: https://learn.microsoft.com/en-us/fabric/cicd/git-integration/intro-to-git-integration

- Sempy fabric api reference: https://learn.microsoft.com/en-us/python/api/semantic-link-sempy/sempy.fabric

Hope this helps. Feel free to ask me questions if needed, and don’t forget to give kudos (likes) & Accept as Solution if this guidance worked for you. That's motivate me to keep helping.

Best regards,

Oussama (Data Consultant & Fabric's Expert)