Forum Discussion

NamelessLw's avatar
NamelessLw
Regular Visitor
26 days ago
Solved

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

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...
Data Engineering
Lakehouse
Notebook
  • Prince0011's avatar
    Prince0011
    26 days ago

    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:

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

     

  • GilbertQ's avatar
    GilbertQ
    26 days ago

    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's avatar
Prince0011
Solution Sage
26 days ago

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:

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