Database vs data warehouse vs data lake: what each one is for and where your data belongs

What is a data warehouse?

A data warehouse is a database built for analysis: it collects structured data from many sources, keeps its history, and is designed to answer questions that scan many rows but few columns, such as revenue by store by month. Data is cleaned and given a fixed schema when it is loaded (schema on write), so every query reads the same definitions.

Warehouses store tables by column rather than by row, and that one design choice explains most of their behaviour. The measurement below shows what it buys and what it costs. For the design work that follows the choice, see the data warehouse implementation guide; for picking a vendor, how to choose a data warehouse.

What is a data lake?

A data lake is storage that keeps data in its original format – CSV, JSON, Parquet, logs, images – usually as files in cloud object storage, with structure applied only when the data is read (schema on read). It is cheap to fill and accepts anything, which is both its use and its risk.

A lake has no query engine of its own. To ask it a question you point an engine at the files, for example AWS Athena, which “makes it easy to analyze data directly in Amazon Simple Storage Service (Amazon S3) using standard SQL” (AWS Athena documentation, October 2026). Without a catalog of what each folder holds, a lake becomes a pile of files nobody trusts, the state usually called a data swamp.

“Data repository” is the umbrella term: a database, a warehouse and a lake are all repositories. “Big data” describes volume, not a system; at large volumes the choice is still between these three.

Database vs data warehouse vs data lake: the comparison

Transactional database vs data warehouse is the comparison most people actually need. Both hold tables and both speak SQL, so a relational database looks like it could serve reports. Whether it should is a measurable question, so we measured it.

One table, three stores, measured

We built one synthetic sales table – 2,000,000 rows, 17 columns, one year, 6 stores, the same columns as the point-of-sale table in our demo project – and stored it three ways:

• in PostgreSQL 16.14, a row store, with a B-tree index on sale_id (the database);
• as one Parquet file, a columnar format and the layout warehouses use (the warehouse side);
• as one CSV file, the way data usually lands in a lake (the lake side).

The files were queried with DuckDB 1.1.3. Everything ran on one Apple M4 Pro laptop on 15 October 2026. PostgreSQL was timed inside the server (EXPLAIN ANALYZE execution time, repeated runs with the table in the operating system’s file cache); DuckDB was timed from Python, best of three runs, so its times include client overhead. The Parquet byte figures are the column-chunk sizes the reader needs according to the file’s own metadata, not measured disk reads. Compare bytes and orders of magnitude, not exact milliseconds.

The queries, exactly as run. PostgreSQL:

EXPLAIN (ANALYZE, BUFFERS)
SELECT store_id, SUM(net_sales)
FROM sales
GROUP BY 1;

EXPLAIN (ANALYZE, BUFFERS)
SELECT store_id, SUM(net_sales)
FROM sales
WHERE sale_date = '2026-09-30'
GROUP BY 1;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM sales
WHERE sale_id = 'S001234567';

EXPLAIN (ANALYZE, BUFFERS)
UPDATE sales SET discount = 0.5
WHERE sale_id = 'S001234567';

DuckDB, over the two files:

SELECT store_id, SUM(net_sales)
FROM 'sales.parquet'
GROUP BY 1;

SELECT store_id, SUM(net_sales)
FROM read_csv('sales.csv')
GROUP BY 1;

SELECT * FROM 'sales.parquet'
WHERE sale_id = 'S001234567';

-- bytes stored per column, from the file's footer
SELECT path_in_schema,
  SUM(total_compressed_size) AS bytes
FROM parquet_metadata('sales.parquet')
GROUP BY 1;

What the four rows mean:

The dashboard query goes to the columnar store. To total sales by store, PostgreSQL read every page of the table, because each row is stored whole and the two columns it needed sit inside all of them. From the Parquet file the reader needs only the two column chunks, per the file’s metadata. The row store read 29x those bytes and took 12.6x longer. On a year of history, this is the report that slows the shop down when it runs on the production database.

The application query goes to the database. To fetch one sale, PostgreSQL followed its index to 4 pages and answered in 0.04 ms. Parquet has no index; DuckDB used the row groups’ min/max statistics to open one row group, which still meant 5.2 MB of column chunks. That is Parquet’s best case: our ids were written in order, so only one row group could hold the id; on a file with ids in random order it would open more. The database was about two orders of magnitude faster at the job an application does thousands of times a minute.

The raw file is the slowest to read and the cheapest to land. CSV lost both contests: every query parsed the whole file. But writing it needs no schema, no index and no conversion – it is the export as it arrived. That is what a lake is for: keep the file now, convert it when a question needs it.

Files cannot be updated in place. PostgreSQL changed one sale in 0.15 ms. A Parquet or CSV file is immutable: changing one value means rewriting the file, or at least a row group. Apache Iceberg and Delta Lake exist to manage exactly that – tracking which files make up a table, so updates, deletes and history work on top of files. That is the step from a lake to a lakehouse.

The same columnar effect inside a cloud warehouse, on 11.5 million rows in BigQuery, is measured in the data warehouse implementation guide, including a one-day filter that read more than the whole year.

Why reports don’t run on the production database

A report on the application’s database does more than run slowly itself: it slows the application. We measured that on the same PostgreSQL table and laptop with pgbench (PostgreSQL 16.14). Four clients ran random one-sale lookups for 20 seconds, alone and then beside four clients running the store-totals report. Three runs each:

With four report sessions running, the average shop lookup became 2.7–3.1x slower and lookup throughput fell 63–68%. The size of the slowdown depends on the machine and on what else it is doing – an independent re-run on the same laptop saw lookups 7.2x slower – so read it as a direction, not a constant. The reports suffered too: about 410 ms each, against 123 ms when run alone. Four report sessions running back to back – a heavier load than people refreshing a dashboard – took roughly two thirds of the lookup capacity the shop’s own pages rely on.

The two pgbench scripts. lookup.sql:

\set i random(0, 1999999)
SELECT * FROM sales
WHERE sale_id = 'S' || lpad(:i::text, 9, '0');

agg.sql, the report load:

SELECT store_id, SUM(net_sales) FROM sales GROUP BY store_id;

The lookup run, alone and then alongside four clients running agg.sql in a loop:

pgbench -n -f lookup.sql -c 4 -j 4 -T 20

Which one does my data belong in?

Sort by dataset, not by company. The measurements above give the test: what does the work on this dataset look like – one record at a time, a few columns over many rows, or a file nobody has parsed yet?

1. Reads fetch one record by key, or records change after they are written (an order, a customer, a stock level) → a database. In our run that is a 0.04 ms index lookup and a 0.15 ms update; a columnar file took about two orders of magnitude longer to find one record and cannot update it in place.
2. Questions total a few columns over many rows, across time or across sources → a warehouse, or columnar tables. That is the 29x-fewer-bytes side. The moment reports start competing with the application for the same database is the moment to copy the data to a warehouse – in our test, four report sessions made every lookup about three times slower.
3. The data arrives as files that are not tables yet, or you do not know the question yet (images, PDFs, server logs, raw event dumps, CSV exports) → a lake. Landing costs nothing but storage; every read pays for parsing until someone converts it.
4. You need table-style updates and queries on files in your own storage, on one copy → a lakehouse (next section).

Many analytics estates have nothing to put in a lake at all: our synthetic demo project holds 14 datasets, all of them tables, as data lakehouse use cases shows. Inside the warehouse, raw connector tables are modelled into one definition of each metric – the data model – and the data lakehouse architecture guide traces those layers on demo data. Checks belong there too: data quality checks at the raw layer catch a source that stopped loading, and the implementation guide shows one that did.

Where the data lakehouse fits

A lakehouse keeps data as open-format files in object storage, like a lake, and adds the table features of a warehouse on top: transactions, schema enforcement, SQL. Databricks defines it as “a data management system that combines the benefits of data lakes and data warehouses” (Databricks documentation, October 2026), and its native format, Delta Lake, as “an optimized storage layer that supports ACID transactions and schema enforcement.”

That answers the last row of our measurement: the files stay immutable, and the table format records which files make up the current table. Google BigQuery, Snowflake and AWS Redshift can all write Apache Iceberg tables to your own storage, so the line between warehouse and lakehouse is now a table format rather than a product; the data lakehouse architecture guide quotes each vendor and builds a real Iceberg table.

Choose a lakehouse when the same data has to serve both reports and file-based work, such as model training on raw events, and you want one copy.

Is Snowflake, S3 or Databricks a data lake?

Each answer below uses the vendor’s own documentation.

Is Snowflake a data lake? Not by default. Its compute units are called virtual warehouses, which “consume credits as they execute queries, load data and perform other DML operations” (Snowflake documentation, October 2026), and its native tables are warehouse tables. With Iceberg tables it reads and writes files in storage you manage, which is the lakehouse pattern. Snowflake itself describes a lakehouse as “an evolution of a traditional data lake.” More in what is Snowflake.

Is Amazon S3 a data lake? S3 is where a lake’s files usually sit; the lake is those files plus a catalog and an engine to query them. AWS Athena queries data “directly in Amazon Simple Storage Service (Amazon S3) using standard SQL” (AWS Athena documentation, October 2026), and Redshift can write Iceberg tables there. See AWS Athena explained.

Is Databricks a data warehouse or a lakehouse? A lakehouse by its own definition, with SQL warehouses as its query compute: “Databricks SQL supports serverless, pro, and classic types” (Databricks documentation, October 2026). See what is Databricks.

Is BigQuery a data lake? It is a warehouse that can also manage Iceberg tables in your own bucket: Google says Iceberg managed tables “store data in customer-owned storage buckets” (BigQuery documentation, October 2026). Is BigQuery a data warehouse or a data lake works through it for marketing data.

What are the disadvantages of a data warehouse? It is the wrong tool for the application’s own work – in our run, the columnar file took about two orders of magnitude longer than the indexed row store to find one record – and its cost depends on how queries are written. Minimums add to it: BigQuery on-demand applies “a minimum 10 MB data processed per table referenced by the query” (Google’s BigQuery pricing page, October 2026), and how to choose a data warehouse shows a small report billed many times what it read because of it.

Where OWOX Data Marts sits

OWOX Data Marts runs on your warehouse, not beside it: Google BigQuery, Snowflake, AWS Redshift, AWS Athena or Databricks. Connectors land ad platform data in your data storage; each Data Mart is a SQL query, table or view there, with a description, an owner and its relationships to other Data Marts; and reports deliver it to Google Sheets, Excel, Data Studio or an AI assistant. The data never leaves the warehouse you chose, so the decision this page is about comes first. Business reporting with Data Marts explains the model.

Google BigQueryBigQuery for Marketing Analytics: What Changed in 2026September 24, 2026→Google BigQueryIs BigQuery a data warehouse or a data lake? A marketing guideOctober 6, 2026→Google BigQueryBigQuery DML Commands: A Complete Guide for Data AnalystsMarch 14, 2024→

See all articles →