Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
teolachev
Helper II
Helper II

DW vs Lakehouse

From a storage perspective, both DW and Lakehouse save data as parquet files in delta. So, what's the difference? I set up a DW and I see I have stored procedures and views. I tried a stored procedure with a TRUNCATE statement but it's not recognized. What SQL flavor is supported in DW and what are the limitations?

6 REPLIES 6
JoshAsh
Frequent Visitor

I think it's the same engine... 
I say that because i've hit an assertion error bug that i can reproduce on both endpoints identically.

cmaneu
Microsoft Employee
Microsoft Employee

Hello @teolachev,
To complement @ce87 answer, here is a quick comparaison of Lake vs Warehouse (from https://aka.ms/fabric-notes)

cmaneu_0-1685353615029.png

 

Today, Microsoft Fabric is an analytics platform. While you can read and insert data in a Warehouse table (and thus, using it from you web app for example), it might lack several features you would want from your OLTP database (backups, response time consistency, etc...).

Thanks Christopher.  We need to add Azure SQL Database and Dedicated Pools to this comparison. We need also OLTP and OLAP benchmarks. We keep on adding tools but we need to know when to use which. Based on what I know about delta performance, OLTP should be out of consideration and therefore the guidance there is should be to use Azure SQL DB or Managed Instance. It appears that Dedicated Pools will be deprecated.  So, we should clearly state that while the new Synapse Warehouse is our long-term warehousing solution, it's not for OLTP (not sure about streaming, which will probably benefit from Lakehouse).

 

Also, do have a Synapse Warehouse roadmap detailing when it will support the missing T-SQL features?

ce87
Advocate I
Advocate I

Here is a good article that help with the use cases for the Lake vs Warehouse decision. There is another Learn article that has a table showing the difference between Datamart, Lake, and Warehouse, but I can't seem to find it.

 

As for OLTP, I'm not sure that the DW in Fabric would be the best choice. The tables in the DW are just Delta tables, exactly like the lake house. I'm not sure how well a delta table would handle transactional workload. Maybe if it's a highly batched workload it could be an option, but I think a traditional DB would still be the better option for OLTP workloads.

 

Better together: the lakehouse and warehouse 

 

 

ce87
Advocate I
Advocate I

Hi, 

There is an article in the Fabric documentation that lists the current limitations of t-SQL in Fabric. Link below. 

 

Documentation 

Thank you for pointing this out. Plenty of T-SQL limitations that bar practical implementations as the new Synapse data warehouse stands today. Speaking of which:

1. Do we have a Synapse warehouse in Fabric roadmap somewhere?

2. Back to the "transactional" nature of the new delta warehouse, are we suggesting that Synapse data warehouse can be used for OLTP workloads? Do we have any OLAP and OLTP benchmarks?

Helpful resources

Announcements
Sept Fabric Carousel

Fabric Monthly Update - September 2024

Check out the September 2024 Fabric update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.