Building a Serverless Lakehouse with DuckLake

What is DuckLake?

DuckLake is an open table format built for DuckDB that combines the catalog and metadata store into a single component. Key benefits:

• No Java required: Pure SQL interface for all operations
• Flexible metadata storage: Supports PostgreSQL, MySQL, DuckDB, or SQLite as the catalog backend
• Full SQL access to metadata: Query snapshots, file information, and table history directly
• Built-in time travel: Every operation creates a snapshot for historical access

Getting Started: Creating a DuckLake

Copy code
-- Attach a DuckLake with DuckDB as the metadata store
ATTACH 'ducklake:lake.db' AS lake;

-- Set the data path for parquet files
SET ducklake_data_path = 'data/lake';

Adding Data: Two Approaches

Copy code
-- Register existing parquet files without copying
CALL add_data_files('lake.orders_raw', 'tpch/orders/*.parquet');

Copy code
-- Create a partitioned table
CREATE TABLE lake.orders (...);
ALTER TABLE lake.orders SET PARTITION BY (year);

-- Insert with partition columns
INSERT INTO lake.orders 
SELECT *, YEAR(order_date) as year FROM lake.orders_raw;

Time Travel and Snapshots

Every operation creates a new snapshot:

Copy code
-- View all snapshots
SELECT * FROM lake.ducklake_snapshots();

-- Query historical data
SELECT COUNT(*) FROM lake.orders AT VERSION 4;

-- Compare versions
SELECT * FROM lake.ducklake_table_changes('orders', 5, 6);

Pro tip: Wrap multiple operations in a transaction to create a single snapshot:

Copy code
BEGIN TRANSACTION;
-- multiple inserts/updates
COMMIT;

File Compaction and Maintenance

Small files accumulate from incremental inserts. DuckLake provides maintenance operations:

Copy code
-- Merge small files together
CALL merge_adjacent_files('lake.line_item');

-- Mark old snapshots for deletion (keep only recent)
CALL expire_snapshots('lake', INTERVAL '1 day');

-- Delete unreferenced files
CALL cleanup_old_files('lake');

-- Remove files not tracked in metadata
CALL vacuum_orphan_files('lake');

-- Or run everything at once
CALL checkpoint('lake');

Inspecting Metadata

All metadata is queryable via SQL:

Copy code
-- File information
SELECT path, record_count, file_size_bytes 
FROM lake.ducklake_table_files('orders');

-- Table partitions
SELECT * FROM lake.ducklake_table_partitions('line_item');

DuckLake with MotherDuck

Copy code
-- Create a MotherDuck-managed DuckLake (uses managed S3)
CREATE DATABASE my_lake TYPE ducklake;

-- Or use your own S3 bucket
CREATE DATABASE my_lake TYPE ducklake 
  DATA_PATH 's3://my-bucket/lake';

When to Choose DuckLake vs Iceberg

What's Coming

• DuckLake 1.0: February 2025
• Spark connector: Write to DuckLake from Spark
• Optimize operations: Intelligent file rewriting based on sort order and zone maps
• Inline storage: Small writes stay in the relational catalog until flushed