# Building a Data Lake with DuckDB and DuckLake: A Complete Guide
## The Evolution of Data Storage
For decades, storing and querying large volumes of data meant relying on traditional relational databases. Systems like Oracle, PostgreSQL, and mainframe solutions from IBM and ICL dominated the landscape. While powerful, these solutions came with steep licensing costs and often locked organizations into specific vendors.
The next major leap arrived with data warehouses. These centralized repositories brought together data from multiple operational systems, optimized for reporting and historical analysis. Warehouses offered faster analytical queries and consistent business definitions, but they demanded expensive infrastructure, complex ETL pipelines, and required data modeling before any data could be loaded.
More recently, the concept of the data lake emerged. Lakes allowed organizations to store vast quantities of raw structured, semi-structured, and unstructured data at a fraction of the traditional cost. Companies like Databricks, Snowflake, and the major cloud providers such as AWS have built extensive ecosystems around managing these lakes, offering tools for ingestion, processing, and governance.
However, there is a surprisingly accessible path to building an effective data lake with minimal financial investment. By combining DuckDB — a fast, in-memory analytical database — with the DuckLake extension, teams can create a fully functional lakehouse without spending a penny on licensing or infrastructure.
## Understanding the Core Technologies
### Parquet: The Foundation of Modern Data Lakes
Nearly every modern data lake relies on the Apache Parquet file format under the hood. Parquet is a columnar storage format, meaning it groups values from the same column together rather than storing rows sequentially. This design allows query engines to read only the columns needed for a given query, dramatically improving performance for analytical workloads.
Parquet files are typically immutable. Once written, the data within a file is not modified in place. Instead, any updates, inserts, or deletes result in new files being created. This immutability is by design — it provides reliability and enables efficient compression and encoding strategies.
Because Parquet files cannot be updated directly, they require additional metadata to be useful in a lakehouse context. This metadata tracks which Parquet files belong to a particular table, their physical locations, partitioning schemes, column statistics, and the history of changes made over time. This metadata layer is what enables features like efficient querying, ACID transactions, schema evolution, and time travel.
### DuckDB: The Analytical Query Engine
DuckDB is an open-source, in-process analytical database designed for workloads involving small to medium datasets — typically up to a few hundred gigabytes. It is remarkably fast, thanks to its columnar execution engine and vectorized query processing. DuckDB can read and query Parquet files directly without requiring any ingestion step or database server setup.
DuckDB is distributed as a MIT-licensed, open-source project with no commercial affiliations required for use. It supports a full SQL dialect and integrates seamlessly with popular languages like Python, Rust, and Node.js.
### DuckLake: The Metadata Layer That Transforms Parquet into a Lakehouse
DuckLake is an extension for DuckDB that provides the metadata management layer needed to turn raw Parquet files into a fully managed data lake. Rather than storing metadata in scattered files alongside the Parquet data, DuckLake keeps all catalogue information in a centralized relational database. This database can be DuckDB itself, but it also supports PostgreSQL, SQLite, and MySQL as backends.
DuckLake tracks table schemas, data files, delete files, partition information, column statistics, and snapshot history. It supports ACID transactions, schema evolution, time travel queries, and commit messages — features that are typically associated with much larger and more expensive systems.
### How DuckLake Compares to Other Table Formats
Several other table formats have emerged to manage data in modern data lakes, including Apache Iceberg, Delta Lake, and Apache Hudi. These formats also use Parquet as their underlying storage and maintain metadata to track table state and change history. However, they typically require more complex infrastructure, larger clusters, and often come with significant operational overhead or licensing costs.
DuckLake is particularly well suited for lightweight, SQL-native lakehouse deployments. It shines when the data volume is manageable on a single node and the team values simplicity and cost-effectiveness over distributed processing at massive scale.
## Getting Started: A Hands-On Walkthrough
### Project Setup
Before diving into the technical steps, it helps to understand the folder structure we will be creating:
“`
ducklake-demo/
├── data/
│ ├── customers.parquet
│ ├── orders.parquet
│ ├── metadata.ducklake
│ └── lake/
└── duckdb.dev
“`
Each file serves a distinct purpose:
– **customers.parquet** — the original source data file for customer records.
– **orders.parquet** — a staging file containing order data, which may optionally be uploaded to cloud storage.
– **metadata.ducklake** — the DuckLake catalogue storing all metadata about managed tables.
– **lake/** — the directory where DuckLake manages its Parquet data files.
– **duckdb.dev** — the persistent DuckDB session database.
### Installing DuckDB
DuckDB is available as a command-line tool across all major platforms. On Windows, the simplest installation method uses the `winget` package manager:
“`
winget install DuckDB.cli
“`
After installation, verify it is working by checking the version:
“`
duckdb –version
“`
### Creating a DuckDB Database
Start a DuckDB session and create a persistent working database file:
“`
duckdb duckdb.dev
“`
Once inside the DuckDB prompt, install and load the DuckLake extension:
“`
INSTALL ducklake;
LOAD ducklake;
“`
DuckLake is distributed entirely as a DuckDB extension. There is no separate server process or desktop application to configure.
### Creating a Local Parquet File
Begin by creating a small customer dataset as a Parquet file. The following SQL statement generates sample data and writes it to disk:
“`sql
COPY (
SELECT *
FROM (
VALUES
(1001, ‘Acme Ltd’, ‘London’),
(1002, ‘Northwind’, ‘Leeds’),
(1003, ‘Globex’, ‘Glasgow’),
(1004, ‘Initech’, ‘Manchester’)
) AS customers(
customer_id,
customer_name,
region
)
)
TO ‘data/customers.parquet’
(FORMAT PARQUET);
“`
At this point, the Parquet file is just a plain file — it is not yet a transactional table. Querying it directly works fine for reads:
“`sql
SELECT * FROM read_parquet(‘data/customers.parquet’);
“`
However, because Parquet is immutable from the perspective of standard SQL operations, you cannot update individual rows in place. This is where DuckLake becomes essential.
### Attaching and Creating a DuckLake
Attach a new DuckLake catalogue by pointing to a metadata file and a data directory:
“`sql
ATTACH ‘ducklake:data/metadata.ducklake’ AS customer_lake (
DATA_PATH ‘data/lake/’
);
“`
This single statement tells DuckLake where to store the catalogue metadata and where to manage Parquet files. If the catalogue does not yet exist, DuckLake creates it automatically. The data path is recorded in the catalogue, so you do not need to specify it again on subsequent connections.
You can verify which databases are attached to the current session:
“`sql
SHOW databases;
“`
Now import the customer Parquet file into DuckLake to create a managed table:
“`sql
CREATE TABLE customer_lake.customers AS
SELECT * FROM read_parquet(‘data/customers.parquet’);
“`
The table now behaves like any standard database table. You can query it with full SQL support:
“`sql
SELECT * FROM customer_lake.customers;
“`
Behind the scenes, DuckLake has created a separate managed representation of the data under the `data/lake/` directory, leaving the original Parquet source file untouched.
### Working with DuckLake Metadata
DuckDB stores rich metadata that tracks the state of the data lake. To inspect this metadata, you can detach the lake attachment, re-attach it in read-only mode as a metadata database, and query the internal catalogue tables:
“`sql
DETACH customer_lake;
ATTACH ‘data/metadata.ducklake’ AS customer_metadata (READ_ONLY);
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_catalog = ‘customer_metadata’
ORDER BY table_schema, table_name;
“`
This reveals dozens of system tables covering schemas, columns, data files, delete files, partition information, sort keys, snapshots, and more. You can query any of these tables as you would a regular database table.
When finished, detach the metadata attachment and re-attach the lake in read-write mode to continue working with it.
### Updating Table Data
Updates in DuckLake are performed with standard SQL:
“`sql
UPDATE customer_lake.customers
SET region = ‘Greater London’
WHERE customer_id = 1001;
“`
The original source Parquet file is not modified. DuckLake creates and manages a separate representation of the table that reflects the change. You can verify the original file still contains the old value by querying it directly:
“`sql
SELECT * FROM read_parquet(‘data/customers.parquet’)
WHERE customer_id = 1001;
“`
### Understanding Snapshots and Time Travel
Every committed change to a DuckLake database produces a snapshot — a point-in-time representation of the entire data lake, including its schemas, tables, and underlying data files. Snapshots store metadata about each version rather than creating full copies of the data, making them storage-efficient.
You can list all snapshots like this:
“`sql
SELECT snapshot_id, snapshot_time, schema_version, changes, author, commit_message
FROM customer_lake.snapshots()
ORDER BY snapshot_id;
“`
Time travel queries let you read data as it existed at a specific point in the past. You can reference a snapshot by version number:
“`sql
SELECT * FROM customer_lake.customers
AT (VERSION => 1)
WHERE customer_id = 1001;
“`
Or by timestamp:
“`sql
SELECT * FROM customer_lake.customers
AT (TIMESTAMP => now() – INTERVAL ’25 minutes’);
“`
This capability is invaluable for recovering from accidental deletions or for auditing historical data states.
### Adding Commit Messages
DuckLake supports associating an author and descriptive message with each transaction. Wrap your changes in an explicit transaction block:
“`sql
BEGIN;
UPDATE customer_lake.customers
SET region = ‘Yorkshire’
WHERE customer_id = 1002;
CALL customer_lake.set_commit_message(
‘Article demonstration’,
‘Updated region to Yorkshire for customer 1002’
);
COMMIT;
“`
After committing, the snapshot history will include the author and message for that transaction. DuckLake provides ACID transactions with snapshot isolation — a successful `BEGIN`–`COMMIT` block produces exactly one snapshot containing all changes. If the transaction is rolled back, none of the changes become visible.
### Evolving a Table Schema
DuckLake tracks columns by field identifier and supports compatible schema changes without requiring existing Parquet files to be rewritten. Add a new column with a default value like this:
“`sql
ALTER TABLE customer_lake.customers
ADD COLUMN customer_status VARCHAR DEFAULT ‘active’;
“`
Every existing row automatically receives the default value when queried. The original source Parquet file remains unchanged, and no backfill or data rewrite is necessary.
### Working with Cloud Storage
In real-world scenarios, much of your data will reside in cloud object storage. DuckDB can read Parquet files directly from Amazon S3 (and other cloud providers) using the `httpfs` extension.
First, install and load the extension:
“`sql
INSTALL httpfs;
LOAD httpfs;
“`
Then, create a secret containing your cloud credentials. Using AWS as an example, you can leverage the default credential chain:
“`sql
CREATE OR REPLACE SECRET s3_credentials (
TYPE s3,
PROVIDER credential_chain,
CHAIN ‘config’,
REGION ‘eu-west-2’
);
“`
This secret is scoped to the current DuckDB session and does not expose credentials in the SQL text itself. With the secret in place, you can query remote Parquet files directly:
“`sql
SELECT * FROM read_parquet(‘s3://my-bucket/source/orders.parquet’);
“`
### Joining Local DuckLake Data with Remote S3 Data
There are two approaches for combining DuckLake-managed data with data stored in cloud storage.
**Option 1: Join on the fly without ingesting.** Query the remote Parquet file and join it directly with the DuckLake table:
“`sql
SELECT o.*, c.*
FROM read_parquet(‘s3://my-bucket/source/orders.parquet’) AS o
LEFT JOIN customer_lake.main.customers AS c
ON o.customer_id = c.customer_id;
“`
This approach is useful for ad-hoc analysis where you do not need to persist the remote data locally.
**Option 2: Materialize the remote data into DuckLake.** Create a managed table that tracks the remote data:
“`sql
CREATE TABLE customer_lake.orders AS
SELECT * FROM read_parquet(‘s3://my-bucket/source/orders.parquet’);
“`
Once materialized, the orders table becomes a fully managed DuckLake table, and all subsequent changes, transactions, and snapshot operations apply to it.
## Frequently Asked Questions
### Is DuckDB suitable for large-scale enterprise data lakes?
DuckDB excels at selective queries that scan manageable portions of data. Single-node DuckDB is well suited to small to medium datasets (up to a couple of hundred gigabytes). For multi-user workloads that repeatedly process tens or hundreds of terabytes, a more robust database like PostgreSQL paired with a distributed query engine such as Spark would be more appropriate.
### What makes DuckLake different from Delta Lake, Iceberg, or Hudi?
All of these formats use Parquet as their underlying storage and maintain metadata to track table state and change history. DuckLake distinguishes itself by managing metadata in a relational database rather than in files co-located with the Parquet data. It also integrates directly with DuckDB, offering a lightweight, SQL-native lakehouse experience with minimal infrastructure requirements.
### Can DuckLake metadata be stored in a database other than DuckDB?
Yes. While DuckDB is the default and most common backend for DuckLake metadata, the extension also supports PostgreSQL, SQLite, and MySQL. This flexibility allows teams to integrate DuckLake into existing database ecosystems.
### Do I need cloud infrastructure to use DuckDB and DuckLake?
No. The entire stack can run on a single local machine with no internet connection required (once the software is installed). Cloud storage support is optional and only needed when your source data resides in services like Amazon S3.
### What are the licensing and cost implications?
Both DuckDB and DuckLake are MIT-licensed and completely free to use. There are no commercial affiliations required, and no usage-based pricing. The only costs you might encounter are for cloud storage if you choose to use services like AWS S3, though the example datasets in this guide are small enough that charges would be negligible.
### What happens if I accidentally delete data?
DuckLake’s snapshot feature provides a safety net. Because every committed change is recorded as a snapshot, you can query the table at an earlier snapshot to retrieve accidentally deleted rows and re-insert them into the active table.
### Can I use DuckLake with Python or other programming languages?
Absolutely. DuckDB has first-class support for Python (via the `duckdb` package) as well as Rust, Node.js, and other languages. DuckLake is automatically available whenever DuckDB is loaded with the extension.
## Conclusion
Building a data lake does not require a massive budget or a dedicated data engineering platform. With DuckDB and DuckLake, individuals and small teams can create a fully transactional, version-controlled lakehouse backed by Parquet files — all for essentially zero cost.
The workflow is straightforward: create Parquet source files, attach a DuckLake catalogue, import data into managed tables, and then leverage SQL for all subsequent operations including queries, updates, schema changes, and time travel. When remote data enters the picture, DuckDB reads cloud-based Parquet files directly and joins them with managed tables, optionally materializing results for ongoing tracking.
For teams whose data processing needs are modest and who already work within a SQL-centric analytics stack, DuckLake offers transactions, snapshots, and schema evolution without the complexity and expense of more established but heavier-weight lakehouse formats. If the solution does not scale, the cost of replacing it is simply the time invested.
The barrier to entry for exploring modern data lake architectures has never been lower. With nothing more than a terminal and an internet connection, you can have a working data lake in minutes.
Thank you for reading
