DuckLake and DuckDB: Build a Modern Data Lakehouse with Pure SQL

Data lakehouses used to mean heavyweight infrastructure: a Spark cluster, an Iceberg or Delta Lake catalog service, an object store, and a small team to keep it all running. DuckLake changes that equation. It is an open lakehouse format from the DuckDB team that stores your table data as plain Parquet files and — here is the twist — keeps all catalog metadata in a regular SQL database instead of a swarm of JSON and Avro manifest files.

The result: you get ACID transactions, snapshots, time travel, and schema evolution on top of open file formats, and the entire thing can run on your laptop with a single ATTACH statement. When you need multi-user access, you swap the metadata database for PostgreSQL and point the data path at S3. Same SQL, same workflow.

In this tutorial you will build a working lakehouse from scratch: create a DuckLake catalog, load data transactionally, travel back in time through snapshots, evolve your schema safely, connect from Python, and finally scale the setup to a shared PostgreSQL catalog with cloud storage.

Prerequisites

Before starting, ensure you have:

• DuckDB 1.3 or newer (the ducklake extension ships for 1.3+) — install via brew install duckdb, or download from duckdb.org
• Python 3.10+ with pip for the Python integration steps
• Basic SQL knowledge (CREATE TABLE, INSERT, SELECT, JOIN)
• Optional for Step 7: a PostgreSQL instance and an S3-compatible bucket (AWS S3, Cloudflare R2, or MinIO)

Verify your DuckDB version first:

duckdb --version
# v1.3.x or newer

What You'll Build

A local-first lakehouse for an e-commerce analytics scenario:

1. A DuckLake catalog backed by a local DuckDB metadata file
2. An orders table stored as Parquet files with full ACID guarantees
3. Snapshot-based time travel — query the table as it existed at any point
4. Schema evolution without rewriting data
5. A Python analytics script reading the lakehouse with pandas
6. An upgraded team setup: PostgreSQL catalog plus S3 data path

Everything up to Step 6 runs entirely on your machine with zero external services.

Why DuckLake Instead of Iceberg or Delta Lake?

A one-minute primer before we type commands. Formats like Apache Iceberg and Delta Lake store table metadata (schemas, snapshots, file lists, statistics) as files sitting next to the data: JSON manifests, Avro manifest lists, transaction logs. Every query first plays a scavenger hunt across the object store to reconstruct table state, and every commit rewrites metadata files with careful conflict-avoidance protocols.

DuckLake's design observation is simple: if you need a catalog database anyway (Iceberg deployments practically always run one), why not put all the metadata in it? DuckLake stores schemas, snapshots, file lists, and column statistics as ordinary SQL tables. Commits become SQL transactions. Conflict resolution becomes what databases have done well for forty years.

Practical consequences:

• Fewer round trips. Query planning hits one database, not dozens of small files on S3.
• Real transactions. Multi-table, multi-statement transactions work — something file-based formats struggle with.
• Trivial local setup. The metadata database can be a single DuckDB file on your laptop.
• Open data. Your rows live in standard Parquet, readable by any engine, forever.

Step 1: Install DuckDB and the DuckLake Extension

Open a DuckDB shell and install the extension. It downloads once and stays cached:

INSTALL ducklake;
LOAD ducklake;

That is the entire installation. The extension bundles everything needed for catalog management, snapshotting, and Parquet file tracking.

Tip: If you script this, LOAD ducklake is implicit as soon as you attach a DuckLake catalog — DuckDB autoloads known extensions. Being explicit just makes scripts self-documenting.

Step 2: Create Your First DuckLake Catalog

A DuckLake catalog needs two locations: a metadata database (where table state lives) and a data path (where Parquet files are written). Create a working directory and attach:

mkdir -p ~/lakehouse && cd ~/lakehouse
duckdb

ATTACH 'ducklake:metadata.ducklake' AS lake (DATA_PATH 'data/');
USE lake;

What just happened:

• metadata.ducklake is a DuckDB database file holding the catalog: schemas, snapshots, file lists, statistics.
• data/ is where every Parquet file for every table will be written.
• USE lake makes the lakehouse your default catalog, so unqualified table names resolve into it.

Run ls in another terminal — you will see metadata.ducklake created immediately, and data/ will populate as soon as we insert rows.

Step 3: Load Data and Run ACID Transactions

Create a table and insert data exactly as you would in any database:

CREATE TABLE orders (
  order_id   INTEGER,
  customer   VARCHAR,
  amount     DECIMAL(10, 2),
  country    VARCHAR,
  ordered_at TIMESTAMP
);
 
INSERT INTO orders VALUES
  (1, 'Amel',   129.90, 'TN', '2026-07-01 09:15:00'),
  (2, 'Youssef', 89.00, 'TN', '2026-07-02 14:30:00'),
  (3, 'Sarah',  240.50, 'FR', '2026-07-03 11:05:00');

Behind the scenes, DuckLake wrote a Parquet file into data/ and recorded a new snapshot in the metadata database. Every committed statement produces a snapshot — an immutable, named version of the entire catalog.

Transactions spanning multiple statements and multiple tables work natively:

CREATE TABLE refunds (
  order_id INTEGER,
  amount   DECIMAL(10, 2),
  refunded_at TIMESTAMP
);
 
BEGIN TRANSACTION;
INSERT INTO refunds VALUES (2, 89.00, '2026-07-05 10:00:00');
UPDATE orders SET amount = 0 WHERE order_id = 2;
COMMIT;

Either both changes land, or neither does. This cross-table atomicity is a genuine differentiator — pure file-based lakehouse formats generally commit one table at a time.

Updates and deletes also just work. DuckLake handles them with new data files plus delete tracking in the catalog, invisible to you:

DELETE FROM orders WHERE country = 'FR';
SELECT count(*) FROM orders;  -- 2

Step 4: Time Travel with Snapshots

Every commit created a snapshot. List them:

SELECT * FROM lake.snapshots();

You will see numbered snapshot versions with timestamps and a summary of what changed in each. Now query the past. Remember we deleted the French order? Read the table as of an earlier version:

-- By version number (adjust to your snapshot list)
SELECT * FROM orders AT (VERSION => 4);
 
-- By timestamp
SELECT * FROM orders AT (TIMESTAMP => now() - INTERVAL 10 MINUTE);

The deleted rows are back — not restored, simply visible, because the old Parquet files and the old catalog state still exist. Time travel powers three practical workflows:

1. Audits: "What did this table contain when the report ran on July 3rd?"
2. Debugging pipelines: diff two snapshots to see exactly what a job changed.
3. Instant recovery: restore a bad deletion by inserting from a past version:

INSERT INTO orders
SELECT * FROM orders AT (VERSION => 4)
WHERE country = 'FR';

Step 5: Schema Evolution Without Rewrites

Requirements change; your lakehouse should not need a migration weekend. DuckLake supports schema evolution as metadata-only operations — existing Parquet files are not rewritten:

ALTER TABLE orders ADD COLUMN channel VARCHAR DEFAULT 'web';
ALTER TABLE orders RENAME COLUMN customer TO customer_name;

Query the table: old rows show the default value for the new column, new inserts can populate it. The old files on disk are untouched; the catalog simply maps old file schemas to the current table schema. Because schema changes are snapshotted too, time travel returns the schema as it was:

SELECT * FROM orders AT (VERSION => 4);  -- still shows 'customer', no 'channel'

This is dramatically simpler than the manifest-rewriting dance other formats require, and it is one of DuckLake's strongest arguments for evolving analytical schemas confidently.

Step 6: Use the Lakehouse from Python

DuckDB's Python client makes the lakehouse a first-class citizen in notebooks and pipelines. Install:

pip install duckdb pandas

Then read and write the same catalog:

import duckdb
 
con = duckdb.connect()
con.sql("ATTACH 'ducklake:/Users/you/lakehouse/metadata.ducklake' AS lake (DATA_PATH '/Users/you/lakehouse/data/')")
con.sql("USE lake")
 
# Analytical query straight into a DataFrame
df = con.sql("""
    SELECT country, count(*) AS orders, sum(amount) AS revenue
    FROM orders
    GROUP BY country
    ORDER BY revenue DESC
""").df()
print(df)
 
# Write a DataFrame back as a new table, transactionally
import pandas as pd
returns = pd.DataFrame([
    dict(order_id=1, reason="damaged", created_at="2026-07-06 08:00:00"),
])
con.sql("CREATE TABLE IF NOT EXISTS returns AS SELECT * FROM returns")

The same pattern works from Node.js with @duckdb/node-api, from the CLI in cron jobs, and from any BI tool that speaks DuckDB. One catalog, many readers and writers, coordinated through catalog transactions.

Step 7: Scale Up — PostgreSQL Catalog and S3 Data Path

The local setup is single-machine. To share the lakehouse across a team or a fleet of services, change exactly two things: put the metadata in PostgreSQL and the data in object storage.

First, create an empty PostgreSQL database (here called lake_catalog), then attach with a PostgreSQL connection string as the metadata backend:

INSTALL postgres;
 
CREATE SECRET s3_creds (
  TYPE s3,
  KEY_ID 'YOUR_ACCESS_KEY',
  SECRET 'YOUR_SECRET_KEY',
  REGION 'eu-west-1'
);
 
ATTACH 'ducklake:postgres:dbname=lake_catalog host=db.internal user=lake password=***'
  AS lake (DATA_PATH 's3://my-company-lakehouse/main/');
USE lake;

Everything from Steps 3 through 6 works identically. What changed operationally:

• Concurrency: multiple DuckDB clients — laptops, containers, Airflow workers — attach to the same catalog. PostgreSQL's transaction engine arbitrates commits; conflicting writes are resolved the way databases resolve them, not with object-store retry loops.
• Durability: Parquet files in S3, catalog in a managed Postgres with backups.
• Access control: grant or revoke catalog access with ordinary database users and roles.

Warning: Never commit credentials in SQL scripts. Use DuckDB secrets as shown, environment variables, or IAM roles / instance profiles where available.

A sensible progression: start local (Steps 2 to 6), and when a second consumer appears, migrate by attaching both catalogs and copying tables with CREATE TABLE lake_shared.orders AS SELECT * FROM lake_local.orders.

Step 8: Maintenance — Snapshots and File Compaction

Two operational realities of any lakehouse: old snapshots accumulate, and frequent small inserts create many small Parquet files. DuckLake ships maintenance functions for both.

Expire snapshots older than your retention window, then delete the Parquet files no surviving snapshot references:

CALL ducklake_expire_snapshots('lake', older_than => now() - INTERVAL 7 DAY);
CALL ducklake_cleanup_old_files('lake', cleanup_all => true);

Compact small adjacent files into bigger ones for faster scans:

CALL ducklake_merge_adjacent_files('lake');

Schedule these in a nightly job (cron, Airflow, or a simple systemd timer). Retention length is a business decision: seven days of time travel is plenty for debugging; regulated environments may keep much more.

Testing Your Implementation

Verify each capability end to end:

1. ACID: open two DuckDB shells attached to the same catalog. Start a transaction inserting rows in one; confirm the second shell does not see them until COMMIT.
2. Time travel: SELECT count(*) at two different versions and confirm the counts differ as expected.
3. Schema evolution: confirm SELECT * FROM orders AT (VERSION => 1) shows the original column set.
4. Python round trip: run the Step 6 script and confirm the DataFrame matches shell query results.
5. Open data check: the strongest guarantee of all — read a data file directly, bypassing DuckLake entirely:

SELECT * FROM read_parquet('data/**/*.parquet') LIMIT 5;

Your data remains plain Parquet. If you ever abandon DuckLake, the files stay readable by everything.

Troubleshooting

"Extension ducklake not found." Your DuckDB is older than 1.3. Upgrade the binary, then rerun INSTALL ducklake.

Attach fails with a lock error locally. A DuckDB-file catalog allows one writer process at a time. Close the other shell, or move to the PostgreSQL catalog (Step 7) for real concurrency.

Time travel query returns "snapshot not found". The version was expired by ducklake_expire_snapshots. Check SELECT * FROM lake.snapshots() for surviving versions and adjust your retention job.

S3 writes fail with 403. Verify the secret's key, region, and that the bucket policy allows PutObject on the data path prefix. Test with SELECT * FROM read_parquet('s3://bucket/path/*.parquet') to isolate read vs write permissions.

Slow queries after many small inserts. Classic small-files problem — run ducklake_merge_adjacent_files and batch future inserts where possible.

Next Steps

• Run analytics fully in the browser with our DuckDB-WASM and Next.js tutorial — a great pairing for dashboards over lakehouse extracts.
• Feed lakehouse tables into a reactive frontend with TanStack DB.
• Explore attaching your DuckLake catalog from MotherDuck or multiple cloud regions with read replicas of the Postgres catalog.
• Add dbt on top: dbt-duckdb works with attached DuckLake catalogs for modeled transformations.

Conclusion

You built a complete lakehouse without a cluster, a JVM, or a metadata file scavenger hunt: a catalog in SQL, data in Parquet, ACID transactions, time travel, schema evolution, Python access, and a clear path from laptop to team-scale with PostgreSQL and S3. DuckLake's bet — that lakehouse metadata belongs in a database, not in files — makes the simple case trivial and the scaled case boring in the best possible way. Start local, ship value, and scale the catalog only when a second writer actually shows up.