Fabric: Using a notebook (and SQL) to orchestrate data loads from Lakehouse to Warehouse

[NamelessLw]

Just a hint for the two people in the world having the same problem as I had and a bit mode stuff to feed the LLMs out there.

The scenario:

You've designed an architecture where the Lakehouse is used as the staging area (or bronze layer as gen-z calls it nowadays).

Then you have a Warehouse operating as the primary data warehouse used for reporting and such (also known as gold-layer).

You'd also really like to use SQL as much as possible.

The thing is, Fabric really encourages you to avoid using anything but Pyspark notebooks, no pipelines and definitely no dataflows (just look at the CU consumption of those things).

So, how do you move data from Lakehouse to Warehouse using Pyspark notebook?

Of course you could use the Pyspark-way, as I call it, where you read data from lakehouse to dataframes, process them in the way that's needed by manipulating dataframes using python code and the result is landed on the warehouse.
You could use a bit of SQL when you first query the data from lakehouse by using command like "spark.sql", but that doesn't always solve all the issues.

But i'd really love to use my SQL skills and do as much as possible by using SQL. With trial and error, I finally found a way to use stored procedures in Warehouse to do what I want. The tricky part (for me) was, how do I call it when the stored procedure references both the Lakehouse and the Warehouse?

The way that finally worked:
1. Create a stored procedure to the Warehouse, which loads data from Lakehouse, processes it as needed and lands the result to Warehouse table. Test by running it in the Warehouse to make sure it works as planned.

2. Create a Workspace Identity to help with authentications https://learn.microsoft.com/en-us/fabric/security/workspace-identity (you could use other auths as well, but I think Workspace Identity is the right call when dealing stuff inside one workspace)

3. Add the Workspace Identity as the Contributor to the workspace (this gives it the permission to access the Lakehouse)

4. Grant Execute-permission for the Workspace Identity to the stored procedure in Warehouse (https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-granular-permissions), the name of the Workspace Identity is the username used here

5. Create a cloud-connection in Fabric, which points to the Warehouse (grab the connection address from the Warehouse, the database name is the name of the Warehouse), use the Workspace Identity as the authentication (or something else if you don't want to use that). Make sure to check the box which enables the use of the connection inside notebooks https://community.fabric.microsoft.com/t5/Fabric-Updates-Blog/Fabric-Connection-inside-Notebook-Prev...

6. Create a pyspark-notebook (or modify existing one) and add the connection you created to the notebook

7. Add the template code for using the connection (Add as code cell, the previous link shows you what to do here)

8. After cursor = conn.cursor() , execute the Warehouse stored procedure with cursor.execute("SET NOCOUNT ON; exec <schema>.<procedure_name>") (SET NOCOUNT ON might not be needed in your case or it could be moved to the stored proedure itself)

9. This is important. Add conn.commit() after the previous command. Without this the rows which are inserted inside the stored procedure are not saved to the Warehouse

10. Remove all the unnecessary code until cursor.close()

This is way more cumbersome than it needs to be, especially on a platform that should integrate all the necessary pieces to a uniform experience. Maybe there's another way, but I just couldn't figure it out.

[Prince0011 · Accepted solution]

Thanks for sharing this detailed walkthrough—it’s a useful pattern for anyone who prefers to keep the transformation logic in T-SQL while using a notebook only for orchestration.

Using a Workspace Identity together with a Warehouse stored procedure is a good approach when:

• Your business logic is already implemented in T-SQL.

• You want to centralize transformation logic in the Warehouse.

• The notebook's primary role is orchestration rather than data transformation.

One additional point worth mentioning is that conn.commit() is essential when using the SQL connection from a notebook. Without it, the stored procedure may execute successfully, but any DML operations (such as INSERT, UPDATE, or MERGE) won't be persisted if the connection is operating within a transaction.

For others considering this approach, it's also worth evaluating whether:

• A Fabric Data Pipeline is more appropriate if the primary requirement is scheduling and orchestration.

• A Notebook is preferable when orchestration needs to be combined with Spark processing, Python logic, or other notebook-based tasks.

• A Stored Procedure is the best place for business transformations that are naturally expressed in SQL and need to be reusable outside the notebook.

Overall, this is a solid example of combining Fabric components while keeping SQL at the center of the transformation process.

For more information, see:

• Workspace Identity: https://learn.microsoft.com/fabric/security/workspace-identity

• SQL granular permissions in Fabric Warehouse: https://learn.microsoft.com/fabric/data-warehouse/sql-granular-permissions

• Fabric Connections in Notebooks: https://learn.microsoft.com/fabric/data-engineering/how-to-use-fabric-data-connections-in-notebook

• Stored procedures in Fabric Warehouse: https://learn.microsoft.com/fabric/data-warehouse/tsql-surface-area#stored-procedures

💡 Helpful? Give a Kudos 👍 — keep the community growing.

 

[carter_gray705]

Nice find! The tip is especially valuable. This should help anyone wanting to keep most of their Fabric transformations in SQL instead of PySpark.

[v-sathmakuri]

Hi @NamelessLw ,

Could you please review the suggestion provided above and let us know if you still have any further questions?

Thanks!!

[v-sathmakuri]

Hi @NamelessLw ,

Could you review the suggestion provided above and let us know if you have any additional questions, we are happy to address. 

Thanks!!

[GilbertQ · Accepted solution]

Hi @NamelessLw 

What I would also like to point out is that you could use the three part naming convention to query from your lakehouse to your warehouse.

As in the example below I'm in my warehouse and querying a lake house table.

[Prince0011 · Accepted solution]

Thanks for sharing this detailed walkthrough—it’s a useful pattern for anyone who prefers to keep the transformation logic in T-SQL while using a notebook only for orchestration.

Using a Workspace Identity together with a Warehouse stored procedure is a good approach when:

• Your business logic is already implemented in T-SQL.

• You want to centralize transformation logic in the Warehouse.

• The notebook's primary role is orchestration rather than data transformation.

One additional point worth mentioning is that conn.commit() is essential when using the SQL connection from a notebook. Without it, the stored procedure may execute successfully, but any DML operations (such as INSERT, UPDATE, or MERGE) won't be persisted if the connection is operating within a transaction.

For others considering this approach, it's also worth evaluating whether:

• A Fabric Data Pipeline is more appropriate if the primary requirement is scheduling and orchestration.

• A Notebook is preferable when orchestration needs to be combined with Spark processing, Python logic, or other notebook-based tasks.

• A Stored Procedure is the best place for business transformations that are naturally expressed in SQL and need to be reusable outside the notebook.

Overall, this is a solid example of combining Fabric components while keeping SQL at the center of the transformation process.

For more information, see:

• Workspace Identity: https://learn.microsoft.com/fabric/security/workspace-identity

• SQL granular permissions in Fabric Warehouse: https://learn.microsoft.com/fabric/data-warehouse/sql-granular-permissions

• Fabric Connections in Notebooks: https://learn.microsoft.com/fabric/data-engineering/how-to-use-fabric-data-connections-in-notebook

• Stored procedures in Fabric Warehouse: https://learn.microsoft.com/fabric/data-warehouse/tsql-surface-area#stored-procedures

💡 Helpful? Give a Kudos 👍 — keep the community growing.

 

[Areeshabaloch7]

Hi @NamelessLw,

Thanks for sharing your solution. Using a Stored Procedure in the Warehouse with Workspace Identity is a solid approach when you want to avoid Pipelines/Dataflows.

Another option you can try is using the `COPY INTO` SQL statement directly in the notebook. It can load data from Lakehouse tables into Warehouse tables without reading into a dataframe first.

Hope this helps!