Why use Warehouse instead of Lakehouse? (Wanting to use Notebook)

[frithjof_v]

I see some users are asking how to use Notebook to load data into Warehouse.

I am wondering, why not use Lakehouse instead of Warehouse, if there is a desire to use Notebook?

I thought one of the main points of using a Warehouse, is to work with T-SQL (stored procedures, etc.).

And if there is a desire to use Notebook (with python, pyspark, sparksql, etc.), then Lakehouse is a suitable data store.

Any users have any opinions why they want to use Warehouse instead of Lakehouse, even if they will use Notebook for data load?

Why not just use Lakehouse?

I am interested to hear people's opinions on this 😀

[rrom_dc]

I work as a data analyst and I tend to create many intermediate tables during exploratory analysis that I like to materialize in my own schema inside the warehouse; it's a cleaner environment under my own administration. The only option that I have right now is to write everything that I do directly in the lakehouse and then use a pipeline to transfer it into the warehouse. What if I want to perform aditional transformations using Pyspark over that intermediate result? The option that I have been provided with is to create a shortcut in the lakehouse pointing towards the warehouse table. I feel that these extra steps lower my productivity and I would like to be able to perform read operations pointing towards the warehouse that return Spark dataframes as result and then be able to perform write operations from Spark dataframes to tables in the warehouse. 

Bonus fact in my organization is that I am not allowed to perform write operations in the lakehouse using T-SQL so if I need and intermediate result first I have to open a notebook perform the operation using Spark-SQL, write the result and then read it. Why do you need so many intermediate results you may ask? for me is a combination of order and past experiences with audits. 

I am used to the environment provided by T-SQL, it's easy to keep order and data tidyness and I am not sure of how to translate that into Microsoft Fabric. In my previous environment I would connect using sqlalchemy to the warehouse, perform all the exploration and transformation that I need using python and write back all the intermediate results neccesary with the same alchemy engine. All from the same ipynb notebook in VS Code. Now, to do the same I have to go through many loops including GUIs, notebooks, and endpoints. I am very new to the Fabric environment maybe I am not understanding the philosophy behind notebooks?  

[KevinChant · Accepted solution]

Most popular reasons I tend to get during discussions is the T-SQL support, especially as far as setting permissions are concerned.

However, with the Lakehouse permissions currently in preview the latter could soon be a moot point.

[Anonymous]

Hi @frithjof_v ,

The Warehouse is optimised for structured data and supports T-SQL, a powerful language for querying and managing relational databases. Users who are familiar with T-SQL and need to execute complex queries, stored procedures, and transactions may prefer the Warehouse.

Lakehouse provides flexibility for unstructured and semi-structured data and supports various data processing engines such as PySpark and Spark SQL.

This article may be helpful to you:

Choosing Between the Lakehouse and Warehouse in Microsoft Fabric - Simple Talk (red-gate.com)

Ultimately, the choice between Warehouse and Lakehouse will depend on the specific needs and circumstances of the business.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[frithjof_v]

Thanks @Anonymous 😀

I am hoping to hear other users' opinions on this topic as well 😀

Best regards, Frithjof