DuckLake, explained: a lakehouse you can hold in your head

Pondside's warehouse has been a single DuckDB database since part two, and for a coffee roaster that is likely to stay the right answer for years. But data has a way of growing past the machine it started on, and businesses have a way of wanting to know what a number was last Tuesday. Those two needs are what the word “lakehouse” is about.

This part explains lakehouses without the vendor fog: what problem they solve, how the established formats solve it, and why DuckLake, a newer format from the people behind DuckDB, solves it in a way you can hold in your head. Then we will put Pondside into a lake, change something, and travel back in time to before the change.

Lakes, warehouses and lakehouses

Three words get used loosely. Here is what each one means when used carefully.

Data lake

Files, usually Parquet or CSV, in object storage such as S3. Cheap, nearly unlimited and readable by many tools, but with no transactions: two writers can trample each other, and a reader can see half a change.

Data warehouse

A database built for analysis, which owns its storage and offers tables, transactions and SQL. Reliable and fast, but the data lives inside one engine’s format.

Lakehouse

Tables with a warehouse’s guarantees, stored as open files in a lake. Any engine that speaks the format can read them, and the storage costs what object storage costs.

The trick that turns a lake into a lakehouse is metadata: a record of which files make up each table right now, which made it up yesterday, and what the columns are. Data files are never changed once written. Instead, a change writes new files and then updates the metadata, in one atomic step, to say which files count. Readers follow the metadata, so they always see a complete version of the table, never half of one.

Where that metadata lives is the whole difference between the formats.

How Iceberg and Delta keep track

The two established open table formats, Apache Iceberg and Delta Lake, keep their metadata as more files in the same object storage as the data.

• Iceberg writes a metadata file for each version of a table, which points to a manifest list, which points to manifest files, which point to the data files. A commit writes the new metadata files and then atomically swaps a pointer to say which metadata file is current. That pointer normally lives in a separate catalog service.
• Delta Lake keeps a transaction log, a folder of numbered JSON files with periodic checkpoints, next to the data. Each commit adds the next file to the log. A catalog is optional.

Both work, at very large scale, and both are widely supported. But keeping a database's bookkeeping in files has costs. Every commit writes several small files. Every reader has to fetch and walk a chain of files before it can read any data. Coordinating writers takes care, and in practice usually a catalog service as well, which is to say a database, holding the pointer.

DuckLake's idea: put the metadata in a database

DuckLake was announced on May 27, 2025, by DuckDB's creators, Mark Raasveldt and Hannes Mühleisen, and reached version 1.0 on April 13, 2026, with a promise of backward compatibility from then on. Its starting point is the observation in that last paragraph. If a lakehouse needs a database for its catalog anyway, why not keep all of the metadata there, as ordinary tables, and stop writing metadata files altogether?

So a DuckLake has exactly two parts. The catalog is a SQL database, which can be a DuckDB file, SQLite or Postgres, holding tables of snapshots, schemas, columns and the list of data files with their statistics. The data is plain Parquet files, in a local folder or in object storage. Finding the files for a query is a single indexed lookup. Committing a change is a single database transaction. And because databases have spent fifty years getting transactions right, DuckLake inherits that work instead of reinventing it on top of files.

A lakehouse needs a database to keep its books. DuckLake simply lets the database keep all of them.

What you get

Snapshots and time travel

Every committed change creates a snapshot, a numbered version of the whole lake. Old data files are not deleted when rows change, so any snapshot still on record can be queried as it was, by version or by time:

-- The table as of snapshot 3.
SELECT * FROM orders AT (VERSION => 3);

-- The table as it was a week ago.
SELECT * FROM orders AT (TIMESTAMP => now() - INTERVAL '1 week');

For a business this is the answer to “what did this number say on Monday, before someone fixed the data?”, and to the more anxious version of the same question after an accidental DELETE.

Schema changes without rewriting data

Adding, dropping or renaming a column, or widening a type, such as an INTEGER to a BIGINT, is a change to the catalog. The Parquet files already written are left as they are and read through the new schema. On a large table, that is the difference between an instant change and an hour of rewriting.

Transactions across tables

Because a commit is one transaction in the catalog database, it can cover many tables at once. A sync that updates orders and order_items together either lands completely or not at all, and no reader ever sees new orders without their items.

Small writes that do not litter

Writing a new Parquet file for every three-row change would fill a lake with tiny files. DuckLake 1.0 instead stores small changes, up to 10 rows by default, directly in the catalog database, a feature called data inlining. They are moved out to Parquet in bulk later, when the lake is tidied.

Encryption

Attached with ENCRYPTED, DuckLake encrypts every Parquet file it writes with a fresh key per write, and keeps the keys in the catalog. Whoever can read the bucket but not the catalog sees only ciphertext, which makes it reasonable to keep data files somewhere you trust less than your catalog.

Try it on Pondside

You can make a DuckLake on your laptop, with a DuckDB file as the catalog and a local folder for the data, in a couple of minutes. Open the Pondside sample from part one and follow along. The ducklake extension needs DuckDB 1.5.2 or newer and downloads itself the first time.

1. Create the lake

   Attach a new DuckLake named lake. Its catalog is the file pondside.ducklake; its data goes into the lake_files folder.

   DuckDB

   INSTALL ducklake;
   ATTACH 'ducklake:pondside.ducklake' AS lake (DATA_PATH 'lake_files/');

2. Copy something in

   Pond Club's subscriptions make a good test: they change over time, and their history matters.

   DuckDB

   CREATE SCHEMA lake.stripe;
   CREATE TABLE lake.stripe.subscriptions AS FROM stripe.subscriptions;

   Look in lake_files and you will find ordinary Parquet files, which any Parquet reader can open.

3. Change it

   A subscriber cancels, and we add a column the business has started to care about.

   DuckDB

   UPDATE lake.stripe.subscriptions
   SET status = 'canceled'
   WHERE id = 'sub_P00001';
   
   ALTER TABLE lake.stripe.subscriptions ADD COLUMN gift BOOLEAN DEFAULT false;

   A one-row change is small enough to be inlined: DuckLake keeps the new row in the catalog instead of writing a Parquet file for it.

4. See the history

   Every change so far is a snapshot.

   DuckDB

   FROM lake.snapshots();

5. Travel back

   Pick the snapshot that created the table from the list, and ask what the subscription looked like then, and now.

   DuckDB

   -- Use the snapshot_id of the CREATE TABLE from the list above.
   SELECT id, status
   FROM lake.stripe.subscriptions AT (VERSION => 2)
   WHERE id = 'sub_P00001';
   
   SELECT id, status, gift
   FROM lake.stripe.subscriptions
   WHERE id = 'sub_P00001';

   The first query reads the table as it was before the cancellation and before gift existed. The second reads it as it is. Both come from the same files.

Keeping a lake tidy

A lake that keeps every version of everything grows without limit, and a lake written to in many small changes ends up as thousands of small files that every query has to open. DuckLake leaves the clean-up to you, which is the right call for a format, and gives you the tools:

Each can be called on its own. Or CHECKPOINT on a DuckLake runs them all, in that order, using options you can store in the lake itself:

-- Keep a month of history, and tidy everything else.
CALL lake.set_option('expire_older_than', '1 month');
USE lake;
CHECKPOINT;

-- Or one step at a time.
CALL ducklake_expire_snapshots('lake', older_than => now() - INTERVAL '1 week');
CALL ducklake_cleanup_old_files('lake', older_than => now() - INTERVAL '1 day');

Choosing a catalog

The catalog database decides who can use the lake at once. The DuckLake documentation is direct about the choices:

ATTACH 'ducklake:postgres:dbname=lake_catalog host=catalog.internal' AS lake
  (DATA_PATH 's3://pondside-lake/');

DuckLake on DuckHouse

A DuckHouse database can be built on either engine, chosen when you create it and fixed afterwards. The DuckLake engine is a lakehouse in exactly the shape above: Parquet data in an object storage bucket that belongs to that database alone, and DuckDB serving it over Quack, so you attach it from any DuckDB client the same way as any other DuckHouse database. Its catalog is a DuckDB file. That is the one-process choice in the table above, and one process is all it needs: the server is the lake's only client, everyone else reaches the lake through it, and it saves the catalog to the same bucket within seconds of every change. The two engines trade off like this, in the words the dashboard uses when you choose:

The maintenance in the previous section is handled for you. Every 12 hours, if anything may have written to the lake since the last pass, DuckHouse flushes inlined rows to Parquet, compacts small files and files that are mostly deleted rows, expires snapshots older than 7 days and removes the files nothing refers to any more. The practical consequence is that time travel on a DuckHouse lake reaches back 7 days.

Which should Pondside choose?

Not DuckLake, yet. Pondside's whole history is a few hundred thousand rows, its questions are interactive, and someone at a farmers' market wants answers in a second, not a second plus a round trip to object storage for each file. A single DuckDB database on a local disk is the faster, simpler fit, and it is backed up by snapshots anyway.

The signs that it is time to move are specific:

• The data will not stop growing: event streams, logs, years of line items, anything heading for more than a machine's disk comfortably holds.
• You need history as a feature: auditors, finance, or anyone who needs to see what the data said at a point in time, not just now.
• More than one engine should read the data. Parquet in a bucket is readable by nearly everything, and a DuckLake catalog in Postgres is readable by any DuckDB.
• The tables are large and the latency is acceptable: scheduled reports and heavy batch work, rather than a dashboard that people click through.

If none of those is true today, start with DuckDB. Moving later is a copy, not a rewrite: the SQL is the same DuckDB SQL on both.

That completes the tour of the parts: the engine, the storage underneath, the syncs, the model, the SQL, the AI on top, and now the lake for when it all keeps growing. Part ten (part 10, out November 10) puts them together into one afternoon's work, from an empty account to a warehouse you can question from your phone, and covers what it takes to keep it healthy for years.