{"id":102229,"date":"2024-06-10T21:25:26","date_gmt":"2024-06-10T21:25:26","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=102229"},"modified":"2026-09-21T11:59:47","modified_gmt":"2026-09-21T11:59:47","slug":"choosing-between-the-lakehouse-and-warehouse-in-microsoft-fabric","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/choosing-between-the-lakehouse-and-warehouse-in-microsoft-fabric\/","title":{"rendered":"Fabric Lakehouse vs Warehouse: How to Choose"},"content":{"rendered":"\n

TL;DR:<\/strong> In Microsoft Fabric, the warehouse and the lakehouse can both store tables and both be queried with SQL, so the choice comes down to how your team works. <\/strong><\/p>\n\n\n\n

Choose the warehouse if your team thinks in T-SQL, is migrating from SQL Server or Synapse, or needs strict transactions and fine-grained security. Choose the lakehouse if your team works in Python or Spark, needs to handle unstructured data, or wants real-time and machine-learning workloads alongside reporting. <\/strong><\/p>\n\n\n\n

Most teams don’t have to pick one: a common pattern is a lakehouse for the bronze and silver layers and a warehouse for gold. The rest of this article compares the two, service by service, so you can make that call for your own platform.<\/strong><\/p>\n\n\n\n

Microsoft Fabri<\/a>c<\/a> is a new centralized, AI-powered cloud data platform hosted by Microsoft. It combines several services of the existing Azure Data Platform \u2013 such as Azure Data Factory \u2013 with Power BI, while also introducing new services. <\/p>\n\n\n\n

It\u2019s tempting to compare Microsoft Fabric with Azure Synapse Analytics<\/a> since Synapse also bundles different services together, but Fabric is so much more. In contract with Synapse, Fabric is a software-as-a-service (SaaS) platform providing you with many low-code to no-code data services.<\/p>\n\n\n\n

This doesn\u2019t mean no code has to be written. On the contrary, in this article we\u2019re going to focus on two services of Fabric: the lakehouse<\/strong> and the warehouse<\/strong>. The first one is part of the Data Engineering experience in Fabric, while the latter is part of the Data Warehousing experience. Both require code to be written to create any sort of artefact. <\/p>\n\n\n\n

In the warehouse we can use T-SQL to create tables, load data into them and do any kind of transformation. In the lakehouse, we use notebooks to work with data, typically in languages such as PySpark or Spark SQL.<\/p>\n\n\n\n

\"a
Figure 1: high-level overview of the services in Microsoft Fabric<\/em><\/figcaption><\/figure>\n\n\n\n

For an overview of all the available services\/experiences in Fabric, check out the article Microsoft Fabric Personas<\/a>.<\/p>\n\n\n\n

If we can create tables and work with data in both the lakehouse or the warehouse, how do we choose between the two? When would you use one or the other? The answer is \u2013 as always \u2013 it depends<\/strong>. In this article, we\u2019re going to present you with the advantages and disadvantages of each service and everything else you might take into consideration to make an informed decision about which service might be best suited for your use case.<\/p>\n\n\n\n

Microsoft Fabric behind the scenes<\/h2>\n\n\n\n

Before we start, let\u2019s take a look first at how Microsoft Fabric works. For example, how is data stored in Microsoft Fabric? The answer is quite simple: everything is stored in the OneLake storage layer<\/strong>. <\/p>\n\n\n\n

This is a logical data lake build on top of Azure Data Lake Storage Gen2<\/em>. It acts as a wrapper around this storage. Microsoft commonly refers to OneLake as \u201cOneLake is the OneDrive for your data\u201d<\/em>. <\/p>\n\n\n\n

This means that, in reality, you might be using one or more Azure Data Lake storage accounts depending on your set-up and which regions you use, but you will perceive it as one single storage. <\/p>\n\n\n\n

As mentioned before, Fabric tries to centralize as much as it can. A benefit of this is that one service can read data stored by another service. For example, if you create a table in a lakehouse, you can query this table from a warehouse. This is known as the OneCopy<\/em> feature.<\/p>\n\n\n\n

Another interesting fact about storage is that all tables you create in the warehouse and in the lakehouse are delta tables<\/strong>. Delta<\/strong><\/a> is an open-source format that applies a transaction log on top of Parquet files. In other words, all table data is stored in Parquet files (these are well-compressed files where data is stored using a columnstore format). <\/p>\n\n\n\n

However, Parquet files are immutable, which means they cannot be updated after they are written to storage. Every time you insert, update or delete a record in a table, a new Parquet file has to be written out. The delta format manages a transaction log alongside all those Parquet files, guaranteeing ACID compliance just like in relational databases. <\/p>\n\n\n\n

In short, thanks to the delta format, we can use Parquet files to store our data but it all works similar to a regular relational database such as SQL Server.<\/strong><\/p>\n\n\n\n

When we look at how a table is stored in OneLake, we can see two folders:<\/p>\n\n\n\n

\"delta
Figure 2: Delta table with transaction log and Parquet files<\/em><\/figcaption><\/figure>\n\n\n\n

In the _delta_log<\/em> folder we can see the whole transaction log of the table:<\/p>\n\n\n\n

\"transaction
Figure 3: Transaction Log of the Delta Table<\/em><\/figcaption><\/figure>\n\n\n\n

In the other folder, we can see a whole bunch of Parquet files. They contain the data of the table through various points in time (new files can be created after each transaction).<\/p>\n\n\n\n

\"a
Figure 4: The different Parquet files of a Delta Table<\/em><\/figcaption><\/figure>\n\n\n\n

The folder also contains a deletion vector<\/a>, which is an optimization technique to handle deletes more efficiently.<\/p>\n\n\n\n

Now we know how data is stored, but how is the compute<\/strong> managed in Fabric? The driving force behind everything in Fabric are the capacities<\/strong>. <\/p>\n\n\n\n

A capacity is a compute resource and is designated with a scale.<\/strong> The lowest capacity is an F2, the next one is F4, then F8 all the way up to F2048. <\/p>\n\n\n\n

Every time something wants to use compute, the capacity is used. It doesn\u2019t matter if it is a warehouse, a data factory pipeline, or a real-time stream, it all uses the same capacity.<\/strong><\/p>\n\n\n\n

In the Microsoft Fabric portal<\/a> (which is the same as the one used for Power BI), you can create workspaces and you can assign a capacity to that workspace.<\/p>\n\n\n\n

\"the
Figure 5: Assigning a Fabric capacity to a workspace<\/em><\/figcaption><\/figure>\n\n\n\n

At the time of writing, there\u2019s also a free trial of Fabric<\/a> available. This trial capacity is equivalent with an F64. There are three methods of purchasing Microsoft Fabric:<\/p>\n\n\n

\n