Differences between Data Warehouse and Lakehouse

Question

Hi,I noticed some differences between the lakehouse and the data warehouse and I'm not much sure why these differences are there.&nbsp;The lakehouse:The files are compacted (snappy format).The files are created based on a maximum size of 1GB, avoiding the small files problemThe files use the VORDER formatThe folder with the table contains an _delta_log folder and the files&nbsp;The Data Warehouse:&nbsp;The files are not compactedThe files are prone to the small files problem (the sample fact_sale table has 26 files in a warehouse and only 1 in a lakehouse).The files are not in the VORDER format (or at least, this is not defined in the metadata).The folder with the table contains an _delta_log folder and another folder, the files are inside the 2nd folder&nbsp;The questions about these differences:How the differences are positive for each one, lakehouse and data warehouse, although they are the opposite? For example, why the warehouse is not affected by small files (or it is?) ? Why the warehouse doesn't need the VORDER ?After an update in the data warehouse table, new parquet files were created, as expected. But the delta_log files were not updated. Why?After creating a shortcut from the Data Warehouse to a lakehouse, the VACUUM didn't work. I imagine it doesn't because the new parquet files were not updated in the delta_log, am I correct? How to fix this?The Tables format are different, I mean, there is a sub-folder in the data warehouse, while this sub-folder doesn't exist in the lakehouse. Is this why the VACUUM doesn't work?Should the VACUUM work, or is there some other method in place to replace its need ?&nbsp;Thank you in advance !

anonymous · Answer

Hi&nbsp;DennesTorres&nbsp;,Thanks for using Fabric Community.
The differences between the lakehouse and the data warehouse are due to the fact that they are different types of data storage systems with different design goals and trade-offs.The lakehouse is designed to provide a unified data platform that combines the benefits of data lakes and data warehouses. It uses a combination of Delta Lake and Azure Synapse Link to provide fast and efficient querying of large datasets, while also supporting real-time data ingestion and processing.
The data warehouse, on the other hand, is designed to provide a scalable and highly available data warehousing solution that can handle large volumes of structured data. It uses a columnar storage format and a massively parallel processing architecture to provide fast query performance and high scalability.
1) How the differences are positive for each one, lakehouse and data warehouse, although they are the opposite? For example, why the warehouse is not affected by small files (or it is?) ? Why the warehouse doesn't need the VORDER ?
The data warehouse is not as sensitive to the small files problem as the lakehouse because it uses a different storage mechanism. The data warehouse stores files in blobs, which are large containers that can hold multiple files. This means that the number of files in the data warehouse does not have as big of an impact on performance as it does in the lakehouse.You cannot use VORDER for tables in Warehouse. VORDER is a file format that is optimized for columnar storage, but it is not supported by Warehouse.Warehouse uses a different storage mechanism for structured data, which is optimized for performance for complex queries on structured data.
2) After an update in the data warehouse table, new parquet files were created, as expected. But the delta_log files were not updated. Why?
Regarding this issue there is already an ongoing issue in Fabric and there is a bug created for the same. You can check the issues in this link:https://support.fabric.microsoft.com/en-IN/known-issues//
3) After creating a shortcut from the Data Warehouse to a lakehouse, the VACUUM didn't work. I imagine it doesn't because the new parquet files were not updated in the delta_log, am I correct? How to fix this?
Yes you are correct.&nbsp;As I already mentioned there is an ongoing bug regarding delta_log,&nbsp;the VACUUM command may not work because the new parquet files were not updated in the delta_log.
Conclusion
The lakehouse and data warehouse formats have different strengths and weaknesses. The lakehouse format is designed for a wide range of workloads, including large data queries, incremental updates, and machine learning. The data warehouse format is designed for workloads that require high performanceHope this helps. Please do let us know if you have any further queries.

anonymous · Answer

Hi&nbsp;DennesTorres&nbsp;,We haven’t heard from you on the last response and was just checking back to see if your query has been resolved. Please let us know if you have any further queries.

anonymous · Answer

Hi&nbsp;DennesTorres&nbsp;,Following up to check if your query has been resolved. Please do let us know if you have any further queries.&nbsp;

dennestorres · Answer

Hi,I'm missing something about your answer, or you are revealing something extremely new, almost opening the pandora box.&nbsp;In order to explain, I need to remind two main points:- The concept broadly accepted about onelake is that it's a single storage for all the different tools we will use. It doesn't mind if it's a lakehouse or data warehouse, the data will be stored in delta.- It goes to the level I watched a speaker mentioning in one official microsoft session "The choice depends only on the kind of tool you like to use, because both are stored in delta on onelake"But on your answer you are revealing:"The data warehouse is not as sensitive to the small files problem as the lakehouse because it uses a different storage mechanism. The data warehouse stores files in blobs, which are large containers that can hold multiple files."This makes a huge difference, changes the base concept of onelake about having everything in a single place and format.The fact the warehouse uses a different storage format is critical because we need to understand this format. Different formats leads to different results and this affects the architectural changes we should make. It's much more than the basic technical differences we already know about.The Kusto database I already know from another answer here and other sources as well it uses a custom format and put a shortcut in the onelake to be acessible. But the warehouse keep the files in the onelake, we can see the files in the onelake explorer. If the files are kept in the onelake, it's not clear how in one moment or another it doesn't endup suffering the same problems related to this kind of storage - Delta.Is there a deeper documentation about this format?Am I misunderstanding something ?Kind Regards,Dennes

anonymous · Answer

Hi&nbsp;DennesTorres&nbsp;,
Apologies for the delay in reply from our side.I have taken help from the internal team and got the information as below:
Firstly, the Data Warehouse files will be compacted shortly and they are in fact VOrdered (this was a recent change we introduced a few weeks ago). The reason you're observing no updates to the delta log when a warehouse table is updated is because we do not currently reflect updates and deletes in the delta log, but we will very soon. We do not support VACUUM but we will do our own garbage collection shortly as well. So, this concludes that Warehouse uses VORDER.To understand better about the differences between Lakehouse and Warehouse please refer this document:&nbsp;&nbsp;Choosing between Lakehouse and Warehouse in Microsoft Fabric | LinkedInHope this helps. Please let us know if you have any further queries.

Forum Discussion

Differences between Data Warehouse and Lakehouse

9 Replies

Related content

warehouse vs lakehouse

Difference between Lakehouse and Warehouse in Microsoft Fabric?

Lakehouse vs Warehouse

Lakehouse/Warehouse Backups ?

Connect warehouse lakehouse to Fabric pipeline.