cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft

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
Helper I
Helper 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
Helper I
Helper 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
MPPC 2023 Fabric Carousel

Power Platform Conference-Fabric and Power BI Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Fabric August Update Carousel

Fabric August 2023 Update

Check out the August 2023 Fabric update to learn about new features.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Get Help with Synapse in the General Discussion Forum

General Discussion Forum

Ask your questions about Synapse here!