Building a Data Lakehouse with DuckDB and DuckLake

Many years ago, if you wanted to store large amounts of data that could be sensibly queried, a database like Oracle or Postgres was your main choice. Sure, there were other options like the mainframe systems from companies such as ICL and IBM, but they were very costly and locked you in to a specific manufacturer.

The next big advance in data storage was the data warehouse. This brought information from separate operational systems into a central repository designed specifically for reporting and historical analysis. Its main advantages were faster analytical queries and consistent business definitions, while its disadvantages included expensive infrastructure, complex ETL pipelines, and the need to model data before loading it.

The most recent advance in data storage is the emergence of the data lake. Data lakes allowed organisations to store much larger volumes of raw structured, semi-structured, and unstructured data cheaply. Companies like Databricks, Snowflake, and the major cloud providers like AWS are competing to extract as much value as possible from customers in exchange for making the management, running, and development of data lakes as smooth as possible.

But you can go a long way toward developing an effective data lake for almost zero cost with DuckDB and the DuckLake extension for DuckDB.

Both DuckDB and DuckLake are MIT-licensed, open-source, and free to use.

A Quick Recap on Parquet Format Files, DuckDB, and DuckLake

Data lakes of almost all types rely on Parquet files to store their underlying data. Parquet is a columnar file format designed for analytical data. It stores values from the same column together, which allows query engines to read only the columns needed by a query. Parquet files tend to be immutable and typically need additional metadata files to be useful in data lakes. The metadata records which Parquet files belong to a data table, their locations, partitions and statistics, as well as which files were added or removed during each table version. Additionally, all changes to the data made via SQL — such as inserts, updates, deletes, and schema changes — are tracked. This metadata allows a lakehouse system to support efficient queries, transactions, schema evolution, and time travel without modifying the underlying Parquet files directly.

DuckDB is a super-fast, in-memory analytical database suitable for small to medium datasets — roughly up to a couple of hundred GBs of data.

DuckLake is an extension for DuckDB, developed by the team behind DuckDB. It turns the traditional idea of how a data lake file system should be structured on its head, managing the metadata in a relational database instead of in files co-located with the underlying Parquet data files. The database used to store DuckLake metadata doesn’t need to be DuckDB — Postgres, SQLite, and MySQL are also supported.

Several competing table formats manage data in modern data lakes, including Delta Lake, Apache Iceberg, and Apache Hudi. They all store the underlying data in Parquet format files and record table state and change history in metadata files stored with, or close to, the Parquet data.

DuckLake can store petabytes of data, but processing it is the bottleneck. Single-node DuckDB is well suited to selective queries that scan only a manageable portion of the lake, but multi-user workloads that repeatedly process tens or hundreds of terabytes will require a more powerful database like Postgres and likely a distributed query engine such as Spark.

DuckDB has released V1.0 of DuckLake, signalling it is ready for production use.

What We’ll Build

In this article, we’ll build an example data lake in two stages, beginning with a single customers Parquet file on our local computer and using it to explore the main features of DuckDB and DuckLake. Once the local lakehouse is working, we will add an orders Parquet file stored in Amazon S3 and join it to the local customer data.

Note: although the purpose of data lakes is the storage and processing of large data volumes, the goal here is to demonstrate how to build a data lake, so the data files used will be very small.

By the end, we will have demonstrated how to:

Use DuckDB to query local and remote Parquet files
Create a DuckLake to store local data
Use DuckDB to query our DuckLake
Use SQL to update table data and evolve a table’s schema
Use DuckLake snapshots to examine earlier versions
Perform a join between a DuckLake table and an external S3 file
Create a DuckLake table from an external S3 file

Prerequisites

You will need:

Windows, macOS, or a recent Linux distribution
A terminal or PowerShell
An internet connection to install the DuckDB CLI and its DuckLake extension
An AWS account for the S3 portion of the article
Permission to create or use an S3 bucket
The AWS CLI if you want to follow the command-line upload steps

The example creates a very small S3 object, but AWS storage and request charges may still apply. Please delete it when you’re done to avoid unexpected costs. If you don’t want to use cloud storage for the second part of the example, local storage works fine as a substitute.

Creating Our Project Structure

Our folder structure for the project looks like this:

customers.parquet — the original local source file
orders.parquet — a staging file to be optionally uploaded to S3
metadata.ducklake — contains the DuckLake catalogue
lake/ — contains Parquet files managed by DuckLake
duckdb.dev — holds the DuckDB session database

On Windows PowerShell, create the project directory structure before proceeding.

Installing DuckDB

DuckDB is available as a command-line program for Windows. All installation methods are documented on the official DuckDB installation page. Choose your preferred method and follow the instructions.

The simplest Windows installation uses winget. After installing, close and reopen PowerShell, then verify the installation by checking the DuckDB version.

Creating Our DuckDB Database and Installing DuckLake

Start DuckDB and create a persistent working database. You should see the DuckDB prompt once the database is created.

DuckLake is distributed as a DuckDB extension — there is no separate desktop application or server to install. From the DuckDB prompt, install and load the DuckLake extension.

Create the Local “Customers” Parquet File

We will begin with a small customer dataset. After entering the appropriate SQL statements, DuckDB creates the file data/customers.parquet. At this point, the data exists as an ordinary file, not a DuckDB or DuckLake table, and we can query it directly.

This is useful for ad hoc queries, but it doesn’t turn the file into a transactional table. Parquet is an immutable file format from the point of view of normal SQL operations — you can’t update a single row in place, for example. The file would need to be replaced or rewritten entirely. That’s where DuckLake comes into its own.

Create the Local DuckLake

Attach a new DuckLake catalogue using an ATTACH statement that identifies two storage locations: the catalogue file and the data path. If the catalogue doesn’t already exist, DuckLake creates it. The data path is recorded in the catalogue, so it doesn’t need to be supplied again when reconnecting later.

You can view all databases attached to the current DuckDB session with the appropriate SHOW DATABASES command.

Once attached, import the customers file into DuckLake to create a managed table. After the import, there are two copies of the data:

The original data/customers.parquet source file
The managed DuckLake table stored under data/lake/

The original file is unchanged. The new table can be queried just like any other database table, and DuckLake maintains the relationship between the logical customers table and the Parquet files used to store it. You can list the physical files used by the table using DuckLake’s built-in metadata functions.

Examining the DuckDB DuckLake Metadata

Behind the scenes, DuckDB stores metadata that tracks the state of your data lake. This metadata is accessible via a set of internal tables. You can query any of these tables as you would a regular database table — for example, to inspect snapshot history or file listings. When finished inspecting the metadata database, switch back to your main DuckLake attachment before continuing.

Updating a DuckLake Table

To update data in a DuckLake table, use standard SQL. For example, to replace “London” with “Greater London” for customer 1001, issue a regular UPDATE statement against the DuckLake table.

The original source file is not affected. Querying data/customers.parquet directly will still return the original value. DuckLake doesn’t make the original file mutable — it creates and manages a separate representation of the data, writing new Parquet files to the data/lake/ directory to reflect each change while leaving the source file intact.

Inspecting the Snapshot History

Every change to a DuckLake table creates a new snapshot. You can query the snapshot history to see a record of all changes made to the lake, including inserts, updates, deletes, and schema changes. Each snapshot is assigned a unique ID and timestamp, making it straightforward to audit the history of your data.

Time-Travel Queries

DuckLake supports time-travel queries, allowing you to query a table as it existed at a specific snapshot or point in time. This is useful for auditing, debugging, or recovering from accidental changes, and it works without modifying or restoring any underlying files.

Adding Commit Messages

You can attach a descriptive commit message to any DuckLake transaction to make snapshot history easier to read and audit. This is done by setting a session-level configuration option before executing your SQL statement.

Evolving a Table Schema

DuckLake supports schema evolution — you can add, rename, or drop columns from a managed table using standard ALTER TABLE SQL statements. Each schema change is recorded as a new snapshot, and time-travel queries respect the schema that was in effect at the time of each snapshot.

Dealing with Cloud-Based Data Files

So far, all the data has been local. DuckDB can also query Parquet files stored in Amazon S3 directly, without downloading them first.

Create the Orders Parquet File

Create a small orders dataset and write it to data/orders.parquet. This file can then be uploaded to an S3 bucket using the AWS CLI. Once uploaded, DuckDB can query it directly using the s3:// URI scheme, provided your AWS credentials are configured.

Using the S3 Data with Our DuckLake

With the orders file in S3, you can query it directly from DuckDB and join it against the local DuckLake customers table in a single SQL statement — no data movement required. You can also import the S3 file into DuckLake as a managed table, after which it behaves identically to the locally sourced customers table, with full support for updates, snapshots, time travel, and schema evolution.

Summary

DuckDB and DuckLake together provide a surprisingly capable data lakehouse stack that costs nothing to license. We’ve seen how to ingest local and S3-hosted Parquet files into a managed DuckLake catalogue, perform standard SQL operations including updates and schema changes, explore the full snapshot history, and run time-travel queries against earlier table versions. For teams working with selective analytical queries on datasets up to a few hundred gigabytes, this open-source stack represents a practical and low-overhead alternative to proprietary lakehouse platforms.