Data warehouse, data lake, lakehouse: Which one is best for which use case?

Introduction

In two of our previous articles, we explored howthe Medallion architecture technicallystructures data into Bronze, Silver, and Gold layers, and then how Data Democracy, theData Product, and theData Mesh enable teams to be organized around this data. One question, however, has remained in the background, even though it determines everything else:where, exactly, do we store analytical data, and in what format?

Three solutions dominate the market: thedata warehouse, thedata lake, and thelakehouse—a portmanteau of the two. We often present them as successive generations, with the latest rendering the others obsolete, but the reality is more nuanced. Let’s explore where these three models come from, what truly sets them apart, and how to choose between them.

1. Why do we need storage dedicated to analytics?

Every company already has databases: those for its ERP, CRM, or e-commerce site. These databases are known astransactional databases(or OLTP,Online Transaction Processing): they are optimized to quickly record thousands of small transactions, such as an order, a payment, or an update to a customer record.

The problem arises as soon as business units want to analyze this data: “What is our revenue by region over the past three years?” These queries scan through millions of rows, and when run against production databases, they slow down the applications themselves. And since each analyst processes the raw data on their own, the figures end up differing from one department to another.
Hence the idea of copying the data into ananalyticalspace (OLAP,Online Analytical Processing), separate from the applications and designed for large-scale querying. Our three models represent three ways of designing this space, each arising from the limitations of the previous one.

 

2. The Data Warehouse: A Well-Organized Repository

Thedata warehouseis the historical model formalized byBill Inmon—often referred to as its “father”—who defines it as asubject-oriented, integrated, historical, and non-volatilecollection of data designed to support decision-making. Ralph Kimball subsequentlypopularized dimensional modeling with the famousstar schema: a fact table (e.g., sales) surrounded by dimension tables (customer, product, date, etc.).

Its fundamental principle is the "schema-on-write " approach: the structure of the tables is definedbeforethe data is loaded. The data therefore goes through anETL ( Extract, Transform, Load) process that cleans it before it is stored.

The result: clean data that can be immediately used in SQL by BI tools such as Power BI. Today, data warehouses have largely migrated to the cloud: Snowflake, Google BigQuery, andAmazon Redshift.

Strengths

• BI Performance → Fast SQL queries, even with many concurrent users;
• Reliability → ACID transactions (the guarantee that a write operation is either fully committed or not at all), enforced schema, a single version of the counters
• Accessibility → Most analysts are proficient in SQL, and the data arrives “ready to use.”

Limitations

• Structured data only → images, PDFs, or IoT feeds do not integrate well with it;
• Rigidity → Every new source or requirement requires changes to the model and pipelines 
• Not well suited for data science → the data has been transformed and aggregated for reporting purposes, whereas a data scientist often needs the raw details;
• Dependence on the editor → the data is stored in a proprietary format that can only be read by the editor's engine.

A common misconception about cost:It is often claimed that storing a data warehouse

is “expensive.” That was true for “on-premises” deployments. In the cloud, Snowflake charges around $23 per TB per month for storage, which is in line with standard object storage rates. The bulk of the cost comes fromcomputing (typically around 80% of expenses) and therelianceon a proprietary format.

3. The data lake: the large repository

In the 2010s, the explosion in data volumes (social media) and the diversity of formats posed challenges for the data warehouse. James Dixon, then CTO of Pentaho, offered a now-famous analogy: “If a data mart is likebottled water—purified and packaged for consumption—adata lakeis a naturallake, fed by many sources, where anyone can come to draw water, observe, or dive in.”

In practical terms, a data lake is a storage space (nowadays almost always cloud object storage such as Amazon S3, Azure Data Lake Storage, or Google Cloud Storage) where data is storedin its original format: table exports, CSV or JSON files, logs, images, and videos. It operates ona “schema-on-read” model: the schema is applied only when we query the data. This shifts us from an ETL (Extract, Transform, Load) approach to anELT(Extract, Load, Transform) approach: we load the data first, then transform it as needed.

Strengths

• Very low storage cost → and virtually unlimited capacity;
• All types of data → structured, semi-structured (JSON, XML), and unstructured (images, text, audio) 
• Ideal for data science and machine learning → the raw, complete data is preserved, with no loss of information
• Open formats→ Parquet, ORC, and Avro can be read by many tools.

Limitations

• No transaction guarantees → two transactions that write at the same time can corrupt a dataset
• Poor BI performance → queries are slow when processing large volumes of unoptimized files
• The risk of a “data swamp” → without a catalog or governance, no one knows what’s in the lake or which data is reliable.

This last point is by no means theoretical. In 2018, Gartner placed data lakes in the “trough of disillusionment” on itsHype Cycle, with its analysts explaining that projects fail when the lake is built without governance, metadata, or transactions.

As a result, most companies have ended up using both: a data lake for storage and exploration, and a data warehouse fed by the lake for reporting. This “two-tier” architecture works, but itduplicates data, increases the number of pipelines, and creates discrepancies between what data scientists and analysts see.

4. The lakehouse: the best of both worlds?

It is precisely this dual system that thelakehouse seeks to eliminate. The concept was formalized by the founders of Databricks (Michael Armbrust, Ali Ghodsi, Reynold Xin, and Matei Zaharia) in a research paper presented at the CIDR conference in 2021. Their thesis: we can achieve the guarantees and performance of a data warehouse directly on the open, low-cost storage of a data lake, using a single copy of the data.

It all hinges on one key component: theopen tableformat. The data remains as Parquet files in object storage, but ametadata layer accompanies them and keeps track of each table: which files make it up, which version is valid, and which schema applies. To borrow Dixon’s analogy, it’s like a lake to which we’ve added a land registry and a log of entries and exits.

Three formats share this role:

• Delta Lake → created by Databricks, open source since 2019, and hosted by the Linux Foundation
• Apache Iceberg → originated at Netflix in 2017; today, it is supported by virtually all data engines (Snowflake, BigQuery, Databricks, AWS services, etc.);
• Apache Hudi → originated at Uber in 2016, with a strong focus on incremental ingestion and streaming.

These formats provide the data lake with what it was missing:ACID transactions, row-by-rowupdates and deletes,schema control, and time travel (the ability to query a table as it existed on a past date, which is particularly useful for auditing).

The lakehouse is also the natural environment forthe Medallion architecture discussed in a previous article: the Bronze layer plays the historical role of the lake, the Gold layer that of the warehouse, and the Silver layer serves as the link between them. In practice, these layers are often built using dbt (Data Build Tool), the flagship tool of the Modern Data Stack. Every transformation is a simple SQL query, automatically versioned and tested (for uniqueness, missing values, etc.). Along the way, dbt generates documentation and lineage—that is, a map showing what feeds into what. Each profile works on the same platform, at the level of maturity that suits them.

Unified Governance

A table schema alone is not enough to avoid the “data swamp”: you still need to know what’s in the lake, who is responsible for it, and who has access to it. That’s the role ofa unified catalog ( Unity Catalog, Snowflake Horizon, Apache Polaris, etc.), which centralizes documentation, lineage, ownership, and access rights in a single location. Two control models coexist:

• RBAC(Role-Based Access Control) → permissions depend on the role: a financial analyst can view the "Gold" tables in the finance department, and nothing else 
• ABAC(Attribute-Based Access Control) → Access rights depend on attributes, often labels applied to the data: any column marked as “personal data” is automatically hidden, regardless of the table in which it appears.

ABAC scales better: we no longer manage hundreds of permissions on a table-by-table basis, but rather a few rules that apply across the board. This is the practical implementation of the federated governance discussed in our article on the Data Mesh.

But not everything is perfect

• Complexity → A lakehouse combines several components (storage, table format, catalog, computing engines, orchestration) that must be configured and maintained;

• Skills → Spark, Python, file optimization (compaction, partitioning): the requirements are higher than for a purely SQL data warehouse;

• Computing costs → storage is inexpensive, but poorly sized clusters billed on a pay-as-you-go basis can quickly drive up the bill;

5. Comparison: The Three Models Side by Side

• To make things clearer, here is a summary of the main differences in a single table.