DW vs Lakehouse

[teolachev]

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?

[JoshAsh]

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]

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

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...).

[teolachev]

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]

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]

Hi, 

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

Documentation 

[teolachev]

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?