Lakehouse vs data warehouse: what a small team actually needs

Lakehouse vs data warehouse is usually framed as old against new. For a small team that is the wrong frame. Both store tables and run SQL. The real difference is who owns the parts: in a warehouse, the vendor owns storage, compute and the format. In a lakehouse, you own the files and pick the engine.

So the useful question is not which is better. It is how much of that ownership your team wants, and what the smallest setup is that does the job. Below is each option in plain terms, the parts of a lakehouse, and the smallest one I could build, with timings.

What a data warehouse gives you

A warehouse, such as Snowflake, BigQuery or Redshift, is one product. You load data in. It stores the data in its own format, runs your queries on its own compute, and handles the rest: users and roles, concurrency, backups, tuning.

What you get is a short list of things to operate, which for most teams is close to none. What you give up:

• The data is in their format. Getting it out means an export.
• Compute is billed by the vendor's meter, whether the query is big or small. The DuckDB vs Snowflake post covers when that meter is worth paying.
• One engine. Anything else that wants the data, such as a Python job or another tool, gets a copy.

What a lakehouse is

A lakehouse takes the warehouse apart into pieces you can choose separately. The data lakehouse architecture has four parts:

  engines     DuckDB · Spark · Trino · a warehouse that reads the format
     │
  catalog     which files make up each table, at each snapshot
     │
  table format   Iceberg · Delta Lake · DuckLake: snapshots, deletes, schema changes
     │
  storage     Parquet files in a bucket or on disk

The files are open. Any engine that speaks the table format can read them, so you keep one copy of the data and point several tools at it. The cost is that you now run the catalog and the maintenance jobs that a warehouse runs out of sight.

Bronze, silver and gold layers show up in most lakehouse diagrams. They are a way to organise data (raw, cleaned, ready to use), and they work just as well in a warehouse or in plain folders. They are not what makes something a lakehouse.

Two of these layers deserve their own posts, so I won't repeat them here. Whether you need a table format at all is in Iceberg vs Parquet. Which catalog to run is in Iceberg catalogs explained.

Lakehouse vs data warehouse, side by side

	Data warehouse	Lakehouse
Who stores the data	The vendor, in its format	You, as Parquet in your bucket
Who runs queries	The vendor's compute	Any engine that reads the format
Several engines on one copy	No, each one needs a copy	Yes
Users, roles, row-level access	Built in	Depends on catalog and engine; often yours to build
Many analysts at once	What it is built for	Depends on the engine
Things to operate	Close to none	Catalog, compaction, cleanup
Lock-in	Format and compute	Lower; files are open

Read the table from the bottom. A lakehouse removes lock-in and gives you one copy of the data. It adds things to operate. A small team should only take on that trade if it pays for itself.

The minimum DuckDB lakehouse: a laptop and a bucket

To find out how small a lakehouse can be, I built one from three parts:

1. Engine: DuckDB 1.5.6.
2. Table format and catalog: the DuckLake extension, with a SQLite file as the catalog.
3. Storage: a local folder standing in for a bucket. On S3 the DATA_PATH becomes s3://bucket/prefix/.

INSTALL ducklake; LOAD ducklake;
ATTACH 'ducklake:sqlite:catalog.sqlite' AS lake (DATA_PATH 'bucket/');
USE lake;

CREATE TABLE orders AS
SELECT i AS order_id,
       TIMESTAMP '2025-10-01' + (i % 31536000) * INTERVAL 1 SECOND AS placed_at,
       'C' || (i % 200000) AS customer_id,
       ['IN','GB','US','DE','SG'][i % 5 + 1] AS country,
       round((i % 9973) / 7.0, 2) AS amount
FROM range(10000000) t(i);

On one machine (20 threads, 31 GB RAM), with warm caches:

Step	Result
Load 10 million rows	2.14 s
Files written	1 Parquet file, 166.6 MB
Catalog size	148 KB
Revenue by country	11 ms
Orders by month	50 ms
Top 10 of 200,000 customers	117 ms

Then the two things a plain DuckDB file cannot do:

• Time travel. I set one order's amount to 0. The current table returned 0.0. SELECT amount FROM orders AT (VERSION => 1) WHERE order_id = 42 returned the old 6.0.
• A second process while a writer is attached. With the writer still connected, a separate process attached the lake read-only and counted 10,001,000 rows in 20 ms. Against a plain DuckDB file, the second process failed with IO Error: Could not set lock on file.

That is a working lakehouse: open files, snapshots, several processes, SQL. It has no cluster and no service except a SQLite file. Swap SQLite for Postgres when writers run on more than one machine. The data stays where it is.

What it does not have is just as important. There are no users or roles beyond file and bucket permissions. No one is serving fifty analysts at once. Only engines that speak DuckLake can read it. Those gaps are why the warehouse still exists.

What a small team actually needs

Most teams I talk to fall into one of four shapes.

One writer, a few readers. A nightly job writes, and a handful of people and dashboards read. You need neither. Parquet in a bucket and DuckDB are enough, and do you actually need a data warehouse? walks through that scorecard.

Several writers, cheap storage, deletes. Several jobs write the same tables. Or you must delete a customer's rows on request, or keep history. This is the case for a small lakehouse. With DuckDB as the engine, DuckLake is the shortest path.

Several engines on one copy. Spark for one job, Trino for another, a warehouse for BI, all on the same tables. You need a lakehouse with a format they all read, which today usually means Iceberg.

Many analysts, governance, nobody to run it. Twenty people writing ad hoc SQL at nine in the morning, with roles, masking and an audit trail. Buy the warehouse. Running that yourself is a job, not a side task.

A team can also sit in two shapes at once. Keep the raw and cleaned layers as files you own, and load only the final tables into a warehouse for BI. The DuckDB vs Snowflake post calls that "both, in different places". It is often the cheapest stable answer.

Questions to answer before you choose

1. How many processes write the same table? One means plain files. More than one means a table format.
2. How many people query at once, at peak? A handful works on one engine. Dozens is warehouse territory.
3. Do you need roles, row-level access or an audit trail inside the database? If yes, a warehouse gives you that today. A lakehouse makes you build it.
4. Who runs the catalog backups and compaction? Name the person. If there isn't one, that settles it.
5. What else must read the data? If the answer is "only DuckDB", the minimum stack above is enough. If it is "Spark and Snowflake too", pick Iceberg.

So: lakehouse or data warehouse?

For a small team, start with the smallest thing that answers question 1. Plain Parquet if one process writes. A DuckDB lakehouse if several do. Add a warehouse when the number of people querying at once, or the need for governance, outgrows what you want to run.

The mistake I see most is buying the end state first. That means a warehouse sized for fifty analysts when there are three, or a multi-engine lakehouse when one engine does all the work.

Local-First Analytics covers the smallest shape in depth: the three-zone file layout (Chapter 4), DuckDB as the warehouse over those files (Chapter 5), and the warehouse bill this setup avoids (Chapter 1). It doesn't cover DuckLake or Iceberg. Chapter 1 is free to read; the rest is on Amazon.

If you are weighing a warehouse against a lakehouse for a real system, a Data Platform Audit is a week and a written roadmap you keep. If plain files and DuckDB are enough, that is what it will say.

More in this series: DuckLake vs Iceberg, DuckLake with Postgres and S3, Iceberg vs Parquet, Postgres and Iceberg and Iceberg catalogs explained.