Introduction

DuckDB Labs has introduced DuckLake 1.0, a data lake format that revolutionizes metadata management by storing table metadata in a SQL database rather than scattering it across numerous files in object storage. This approach drastically reduces small-file overhead and simplifies updates. Available as a DuckDB extension, DuckLake 1.0 brings catalog-stored incremental updates, improved sorting and partitioning options, and compatibility with Iceberg-style features. In this guide, you will learn how to set up and use DuckLake 1.0 step by step, from installation to querying a fully managed data lake.

What You Need

• DuckDB (version 0.9.0 or later) installed on your machine. Download DuckDB
• Access to a SQL database for metadata storage (e.g., SQLite, PostgreSQL, or DuckDB itself). DuckLake uses this as its catalog.
• Object storage (like Amazon S3, Google Cloud Storage, or local filesystem) for actual data files.
• Basic familiarity with SQL and DuckDB commands.
• DuckLake extension files (can be installed via DuckDB's extension mechanism).

Step-by-Step Guide

Step 1: Install the DuckLake Extension

Open your DuckDB command-line interface or client. Run the following SQL command to install and load the DuckLake extension:

INSTALL ducklake;
LOAD ducklake;

This adds new functions and data types needed for DuckLake operations. Verify the installation with:

SELECT * FROM duckdb_extensions();

Look for 'ducklake' in the list.

Step 2: Create a Catalog Database

DuckLake stores table metadata in a SQL database of your choice. For simplicity, we'll use an SQLite file as the catalog. Create a new database and attach it:

ATTACH 'file::memory:?cache=shared' AS ducklake_catalog (TYPE sqlite);

Alternatively, use a persistent file: ATTACH 'metadata.db' AS ducklake_catalog (TYPE sqlite);. This will hold all table schemas, partitions, and versioning information.

Step 3: Define Your Data Lake Schema

Using DuckLake, you define tables as you normally would in DuckDB, but with DuckLake-specific options. For example, create a partitioned and sorted table:

CREATE OR REPLACE TABLE my_lake_table (
    event_date DATE,
    user_id BIGINT,
    event_type VARCHAR,
    value DOUBLE
) WITH (
    format = 'parquet',
    location = 's3://my-bucket/lake/',
    partition_by = ['event_date'],
    sort_by = ['user_id', 'event_type'],
    catalog = 'ducklake_catalog'
);

The catalog option tells DuckLake where to store metadata. The location points to your object store. DuckLake will manage files under that path.

Step 4: Load Initial Data

Insert data into your DuckLake table. DuckLake automatically writes data files (e.g., Parquet) to the object store and records metadata in the catalog:

INSERT INTO my_lake_table VALUES 
    ('2024-01-01', 1001, 'click', 2.5),
    ('2024-01-01', 1002, 'view', 1.2),
    ('2024-01-02', 1001, 'purchase', 20.0);

Because of the partition_by and sort_by options, DuckLake will create optimized file structures, similar to Iceberg's approach. You can monitor the catalog tables (e.g., SELECT * FROM ducklake_catalog.snapshots) to see versions.

Step 5: Perform Catalog-Stored Small Updates

One of DuckLake's key benefits is efficient small updates without rewriting whole files. Use UPDATE or DELETE commands normally:

UPDATE my_lake_table SET value = 3.0 WHERE user_id = 1001 AND event_type = 'click';
DELETE FROM my_lake_table WHERE event_date = '2024-01-02';

Instead of rewriting Parquet files, DuckLake records these changes as small delta files in the catalog, drastically improving write throughput for point updates.

Step 6: Query and Analyze Data

Query the lake table just like any other DuckDB table. DuckLake transparently merges metadata and data files:

SELECT event_date, COUNT(*) AS events
FROM my_lake_table
WHERE value > 1.0
GROUP BY event_date
ORDER BY event_date;

You can also inspect the catalog directly for advanced debugging:

SELECT * FROM ducklake_catalog.manifests;

Step 7: Add Partition Evolution and Sorting Changes

With DuckLake 1.0, you can later modify partitioning or sorting without rewriting all data—another advantage over traditional data lakes. Use the ALTER TABLE command:

ALTER TABLE my_lake_table SET (
    partition_by = ['event_type', 'event_date'],
    sort_by = ['user_id']
);

New data will follow the new layout while old data remains accessible via the catalog. This flexibility is part of the Iceberg-compatible feature set.

Tips and Best Practices

• Optimize Catalog Performance: Use a persistent catalog database (SQLite file or PostgreSQL) for production to avoid memory-only limitations.
• Monitor File Sizes: DuckLake's small updates create delta files. Periodically run OPTIMIZE or VACUUM on the catalog to compact metadata and reduce overhead.
• Leverage Iceberg Interoperability: If you already use Apache Iceberg, DuckLake can read Iceberg manifests and vice versa, thanks to format compatibility. Test with existing Iceberg tables using CREATE EXTERNAL TABLE ... USING ducklake.
• Use Appropriate Partition Granularity: For time-series data, partition by day or month. Over‑partitioning (e.g., by hour) can lead to many small files. DuckLake mitigates this with metadata, but still consider cardinality.
• Secure Object Storage Credentials: When using S3 or GCS, set environment variables (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY) or use DuckDB's SET commands. Example: SET s3_region='us-east-1';
• Keep DuckDB Updated: DuckLake 1.0 is a first release. New versions will bring performance improvements and bug fixes. Stay current via UPDATE extension ducklake;.
• Test on Small Data First: Before migrating large volumes, prototype with a small dataset to understand DuckLake's behavior with your specific data patterns.

By following these steps, you can harness the power of DuckLake 1.0 to build a modern, efficient data lake that leverages SQL-based metadata management, drastically simplifying updates and improving query performance. For more details, refer to the official DuckLake documentation.