Forum Discussion
Power ParticipantDifferences between Data Warehouse and Lakehouse
Hi DennesTorres ,
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. As I already mentioned there is an ongoing bug regarding delta_log, 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 performance
Hope this helps. Please do let us know if you have any further queries.
Power ParticipantHi,
I'm missing something about your answer, or you are revealing something extremely new, almost opening the pandora box.
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
Hi DennesTorres ,
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: Choosing between Lakehouse and Warehouse in Microsoft Fabric | LinkedIn
Hope this helps. Please let us know if you have any further queries.- DennesTorres
Hi,
There are some additional welcome information, there are some promisses of features to come, but it's still very confusing.
I understand the fact the updates and deletes are not reflected on delta logs is about to change, ok. But the fact it's happening now means there is another internal storage supporting the data warehouse.
A delta table with the delta logs not reflecting updates and deletes would be showing wrong results. But the results are correct, so these updates and deletes are reflected somewhere else, in an internal structure which the lakehouse doesn't have.
The article you are pointing to me is very basic and exactly the kind of dangerous article I mentioned. One of the items for the choice is "Language preference for data management".
In this way, the article is considering the underlying data has the same storage, delta. However, as I pointed on previous paragraph, delta is not the only and main storage of the warehouse, otherwise it would not work on the conditions it is today (not updating the delta log).
I understand probably the information we are touching is under NDA. The problem is: if the data warehouse has an additional layer of storage and management, as all the clues and your own information suggest, this means it will perform differently than the lakehouse, in different scenarios. It's important for data engineers to understand the technical details of the differences otherwise we may endup on the wrong choice and only discovering when it's too late (and expensive) to change.
I hope my suggestions could help to expose a bit more of the data warehouse architecture so we don't need to rely on articles like this one you pointed to me, which makes everyone believes both (lakehouse and warehouse) have the same technical internal architecture and will behave in the same way so it's just a matter of the "Language preference for data management"
Thank you!Hi DennesTorres ,
Apologies for the delay in response.
Regarding the Warehouse data log, it is an internal format but is very similar to Delta and we publish logs that are compliant with the Delta Lake Log. Results from Warehouse and Lakehouse will be the same but it is a different engine, so performance and language options could be different. Currently the Delta logs for a Warehouse will only contain inserts that have occurred - updates and deletes will also be reflected shortly.
Appreciate if you could share the feedback on our feedback link , which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.
Feedback Link : Home (microsoft.com)Hope this helps. Please let us know if you have any further queries.