warehouse vs lakehouse

[DiKi-I]

can someone help me with the limitation of using warehouse over lakehouse? Bascially I would need a detailed comparsion of both?

[v-nmadadi-msft]

Hi @DiKi-I 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @DiKi-I 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-nmadadi-msft]

Hi @DiKi-I ,
Thanks for reaching out to the Microsoft fabric community forum.

Please refer this article which contains information comparing both warehouse and lakehouse.
Based on the criterion which seems more important to your business needs, you can come to a decision on what to use.
Microsoft Fabric Decision Guide: Choose between Warehouse and Lakehouse - Microsoft Fabric | Microso...

Additionally, A decision can be made based on your Engineering needs not just based on the limitations
Lakehouse is best for data engineers and data scientists working with raw, semi-structured, or unstructured data. It is often used with Spark to perform ETL, large-scale data transformations, or machine learning.

Warehouse is best for BI developers and analysts who need high-performance, structured data models and robust SQL capabilities for reporting and dashboarding.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[suparnababu8]

Hello @DiKi-I 

I know you already refered MS documentation. Let me tell you the limitations of both as mentioned below

Warehouse:

• It supports only structured data, if you want to process JSOn or xml file it's not possible in warehouse.
• If you are going to chnage any schema, you will have careful planning otherwise it would distrucb your downstream users.
• In warehouise you are not supposted to interact with file level access (raw file access)
• Here no native support for spark, streaning data ingestion. 
• Not suitable for raw layer ingestion.

Lakehouse:

• Here you can't implement the DML operations using SQL analytics end point.
• It become a complexity for BI users, Spark need to be used to DML opertions.
• Performance will be slow down bcz, spark jobs may require tuning.

Finally, if you are going to built mediallian archetcecure (bronze,silver and gold) you may go with lakehouse. If you are going tow ork with structured data with heavy BI solutions you can go wioth warhouse.

You can use both also deopending on the project requirement.

Please let me know if it helps you

Thank you!!

Please join in my community at https://www.meetup.com/nextgen-data-aspirants-community/

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

[DiKi-I]

Thanks I have already gone through the ms learn but would need to know more about the limitation which has been encountered practically while working with lakehouse and warehouse.

[KarinSzilagyi · Accepted solution]

So you're mostly looking for personal experiences rather than just a technical comparison as far as I understand,  @DiKi-I ?

For full transparency: I haven't set up a new platform project since early summer, so I might be slightly behind the news on Warehouse capabilities as I was also biased towards Lakehouses so far, because the Warehouse just didn't support many of the things I loved in Fabric when I last had to make this choice.

I personally chose depending on what kind of data I'm working with (unstructured, structured, mirrored, shortcuts?), how fast and frequent I need to get it processed and displayed (e.g. in a report) and what the pre-existing skill-set of the people that will work with it later is.

If you and your team mostly want to process structured data from a SQL Server into Fabric and are primarily used to working with T-SQL (and have limited or no Spark experience), I would recommend a Warehouse. It's optimized for T-SQL, feels pretty familiar and works great as a serving layer for reporting. Last I checked (see disclaimer above) Warehouses still don't support Shortcuts and while T-SQL notebooks are nice, you still couldn't fully use Spark notebooks with Warehouses.
There's a Spark connector, but it only lets you read and write data between Spark and a Warehouse, it doesn't let you run Spark jobs inside the Warehouse itself, which means that every transformation requires the data to be staged and copied across compute boundaries. That adds I/O overhead and increases latency since Spark can't operate directly on the Warehouse's storage layer. 
You can write back to a Warehouse via the SQL Endpoint though, which could be interesting for some projects (wasn't a use-case for any of my projects yet though).

If you really want to have fun and get the most of your Fabric experience, I personally lean towards a Lakehouse in 9.5 out of 10 cases, since it offers full flexibility (structured and unstructured data + easy file handling via the OneLake Explorer - though I really don't recommend to try and do a bulk upload of thousands of files via the explorer, as it kept crashing on us when we last tried that in a project).

With a Lakehouse you also have the full freedom on how to get the data into Fabric and how to move/process it - e.g. using Shortcuts, Spark Notebooks, Materialized Lake Views (preview) or Mirroring. You can also use schemas on your Lakehouse to organize it - for this one I definitely recommend to fully stick to schema or schemaless Lakehouses though. Last time when I tried to write between a schema and a schemaless Lakehouse, I kept running into all kinds of issues and errors. 

So quick summary: 

• Warehouse if you want a pure SQL experience with managed compute and schema enforcement and/or need writeback via the SQL Endpoint for whatever reason
• Lakehouse if you want to play with all the newest features, work with a mix of data and want maximum flexibility

But remember that you can also mix and match as needed. Last I heard many ppl used one or multiple lakehouses for their Bronze + Silver Layer and a Warehouse for their Gold-Layer.

[KarinSzilagyi]

Hi @DiKi-I, just so we know what you already know or don't know, did you see this comparison on the Learn Platform already? https://learn.microsoft.com/en-us/fabric/fundamentals/decision-guide-lakehouse-warehouse 

Guy in a Cube also posted a nice, quick video on how to decide between different storage options in Fabric just a 4 days ago: https://www.youtube.com/watch?v=xTXscW3PjeE