{"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":"2025-06-27T14:55:49","modified_gmt":"2025-06-27T14:55:49","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":"Choosing Between the Lakehouse and Warehouse in Microsoft Fabric"},"content":{"rendered":"

Microsoft Fabric<\/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. 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

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

\"A<\/p>\n

Figure 1: high-level overview of the services in Microsoft Fabric<\/p>\n

ALT: a diagram displaying all available services in Fabric, with the Onelake storage layer beneath them<\/em><\/p>\n

For an overview of all the available services\/experiences in Fabric, check out the article Microsoft Fabric Personas<\/a>.<\/p>\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

Microsoft Fabric behind the Scenes<\/h2>\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>. 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>. 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. 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

Another interesting fact about storage is that all tables you create in the warehouse and in the lakehouse are delta tables<\/strong>. Delta<\/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). 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. 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.<\/p>\n

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

\"A<\/p>\n

Figure 2: Delta table with transaction log and Parquet files<\/p>\n

ALT: delta table in the storage layer. One folder for the transaction log, one folder for the Parquet files<\/em><\/p>\n

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

\"A<\/p>\n

Figure 3: Transaction Log of the Delta Table<\/p>\n

ALT: transaction log of a delta table, showing 8 different transactions<\/em><\/p>\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

\"A<\/p>\n

Figure 4: The different Parquet files of a Delta Table<\/p>\n

ALT: a folder with a bunch of Parquet files. Displayed is also a deletion vector file.<\/em><\/p>\n

The folder also contains a deletion vector<\/a>, which is an optimization technique to handle deletes more efficiently.<\/p>\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>. A capacity is a compute resource and is designated with a scale. The lowest capacity is an F2, the next one is F4, then F8 all the way up to F2048. 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.<\/p>\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

\"A<\/p>\n

Figure 5: Assigning a Fabric capacity to a workspace<\/p>\n

ALT: the list of options when assigning a capacity to a workspace<\/em><\/p>\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