Skip to content

ducklake.md

Latest commit

 

History

History
289 lines (207 loc) · 14.2 KB

ducklake.md

File metadata and controls

289 lines (207 loc) · 14.2 KB
Raw

DuckLake Platform

DuckLake is an open lakehouse table format shipped as a DuckDB extension: table data is stored as Parquet files, while table metadata (the catalog) lives in a SQL database. BenchBox runs DuckLake through the same DuckDB engine used by the duckdb platform, inheriting DuckDB's SQL dialect and benchmark compatibility unchanged.

The DuckLake platform is **beta**. BenchBox has tested four catalog/storage deployment modes with TPC-H; other backend-by-storage combinations have not been tested separately. See [Catalog Backends](#catalog-backends) below and [ADR: DuckLake Maturity, Publishability, and Compaction Bias](../development/adr/adr-ducklake-maturity-and-publishability.md).

Features

  • Lakehouse format - Parquet table data, SQL-database catalog metadata
  • DuckDB dialect - Inherits SQL dialect and query translation from DuckDB
  • Reused engine - Schema creation, data loading, query execution, plan capture, and tuning are all inherited from DuckDBAdapter unchanged
  • Catalog reuse - Re-attaches an existing catalog by default; --force wipes catalog + data for a clean rebuild
  • Pluggable catalog backend - DuckDB-file (default), SQLite, or self-hosted PostgreSQL metadata catalog via --platform-option catalog=...
  • Local or cloud storage - Parquet DATA_PATH may be a local directory or an s3:// URI (via DuckDB's httpfs extension)

Quick Start

uv add "duckdb>=1.3,<2.0"

benchbox run --platform ducklake --benchmark tpch --scale 0.01

The first command installs DuckDB 1.3 or later, which the ducklake extension requires. The second runs the benchmark. Metadata and data paths default under benchmark_runs/databases/.

Requirements

DuckLake requires DuckDB >= 1.3 — the ducklake extension is not available on earlier releases. The adapter checks the version when it connects. If the detected DuckDB version is too old, the adapter raises immediately with the detected version and a remediation hint (e.g. uv add 'duckdb>=1.3,<2.0' or --platform-option driver_version=1.3.2).

The first run also needs network access once, to INSTALL the ducklake extension.

Configuration Options

All options are passed as --platform-option KEY=VALUE.

Option Default Description
metadata_path Generated under benchmark_runs/databases/ Path to the DuckLake catalog metadata file (.ducklake, or .sqlite for the sqlite backend)
data_path Generated under benchmark_runs/databases/.../ducklake_data/ Path to the DuckLake Parquet data directory (local path or s3:// URI)
catalog duckdb Catalog metadata backend: duckdb, sqlite, or postgres
deployment_mode (derived) Deprecated alias of --platform ducklake:<mode>: local, local_catalog_s3, postgres_catalog, or postgres_catalog_s3
pg_host localhost PostgreSQL hostname (catalog=postgres only)
pg_port 5432 PostgreSQL port (catalog=postgres only)
pg_database ducklake_catalog PostgreSQL database name - must already exist (catalog=postgres only)
pg_user postgres PostgreSQL username (catalog=postgres only)
pg_password (none) PostgreSQL password (catalog=postgres only)
s3_key_id (none - uses credential_chain) AWS access key ID for S3 DATA_PATH
s3_secret (none - uses credential_chain) AWS secret access key for S3 DATA_PATH
s3_region (none) AWS region for S3 DATA_PATH

For example, metadata_path and data_path:

benchbox run --platform ducklake --benchmark tpch --scale 0.1 \
    --platform-option metadata_path=/path/to/catalog.ducklake \
    --platform-option data_path=/path/to/parquet_data

Catalog Backends

The catalog metadata backend is selected with --platform-option catalog=<duckdb|sqlite|postgres>. All three backends reuse the same DuckDB query engine and dialect; only the metadata storage (and, for postgres, the extension INSTALL/LOAD and connection setup) changes.

Backend Required DuckDB Extensions Metadata Storage Notes
duckdb (default) ducklake Local .ducklake file Best-tested path
sqlite ducklake, sqlite Local .sqlite file metadata_path suffix is swapped to .sqlite automatically when it is left at its generated default
postgres ducklake, postgres Self-hosted PostgreSQL database The target database must already exist - DuckLake's ATTACH does not run CREATE DATABASE 
benchbox run --platform ducklake --benchmark tpch --scale 0.1 \
    --platform-option catalog=sqlite

benchbox run --platform ducklake --benchmark tpch --scale 0.1 \
    --platform-option catalog=postgres \
    --platform-option pg_host=localhost \
    --platform-option pg_database=ducklake_catalog \
    --platform-option pg_user=postgres \
    --platform-option pg_password=postgres

The first command uses a SQLite catalog with local Parquet data. The second uses a self-hosted PostgreSQL catalog; the ducklake_catalog database must already exist on the target server.

MySQL is deliberately not supported as a catalog backend - DuckLake's own documentation flags it as not recommended (compatibility issues).

Cloud Storage (S3 DATA_PATH)

Any catalog backend can be combined with an s3:// data_path; BenchBox installs DuckDB's httpfs extension and creates an S3 secret before the ATTACH. No credentials are required for local data_path.

By default the secret uses the credential_chain provider, so ambient AWS credentials (environment variables, shared profile, or IMDS) are picked up the same way the AWS CLI would - no BenchBox-level configuration is needed beyond a reachable bucket:

benchbox run --platform ducklake --benchmark tpch --scale 0.1 \
    --platform-option data_path=s3://my-bucket/ducklake-data

To use explicit static credentials instead of the credential chain, pass both s3_key_id and s3_secret (and, optionally, s3_region):

benchbox run --platform ducklake --benchmark tpch --scale 0.1 \
    --platform-option data_path=s3://my-bucket/ducklake-data \
    --platform-option s3_key_id=AKIA... \
    --platform-option s3_secret=... \
    --platform-option s3_region=us-east-1

Usage Examples

Basic Benchmark

benchbox run --platform ducklake --benchmark tpch --scale 0.01

benchbox run --platform ducklake --benchmark tpcds --scale 1.0

The first command runs TPC-H at scale factor 0.01 with the default (generated) paths. The second runs TPC-DS at scale factor 1.

Explicit Catalog and Data Paths

benchbox run --platform ducklake --benchmark tpch --scale 0.1 \
    --platform-option metadata_path=./ducklake_catalogs/tpch_sf0.1.ducklake \
    --platform-option data_path=./ducklake_catalogs/tpch_sf0.1_data

Clean Rebuild

Without --force, an existing catalog is reused: schema creation and data loading are skipped and queries run directly against the already-populated catalog. For catalog=duckdb/sqlite this is detected from the metadata_path file; for catalog=postgres the catalog lives server-side, so it is detected after the ATTACH by inspecting the attached catalog itself.

benchbox run --platform ducklake --benchmark tpch --scale 0.1 --force

This wipes the existing catalog metadata file and Parquet data, then rebuilds them.

What --force clears depends on where the catalog and the data live:

Location Cleared by --force?
catalog=duckdb / sqlite metadata file (+ sidecars) Yes - deleted
catalog=postgres catalog tables Yes - dropped server-side
Local data_path directory Yes - cleared recursively
Cloud data_path (s3://...) No - see below 
`--force` never deletes objects from a cloud `DATA_PATH`. Recursively deleting an
object-store prefix is destructive in a way that clearing a local run directory is
not, and the configured prefix may hold data this run did not write. The rebuilt
catalog references only newly-written files, so **results stay correct** - but the
Parquet from earlier runs is left unreferenced and keeps accruing storage cost. The
adapter logs a warning naming the prefix; clear it yourself, e.g.
`aws s3 rm --recursive s3://bucket/prefix/`.

Python API

from benchbox import TPCH
from benchbox.platforms.ducklake import DuckLakeAdapter

adapter = DuckLakeAdapter(
    metadata_path="benchmark_runs/databases/tpch_sf1.ducklake",
    data_path="benchmark_runs/databases/tpch_sf1_data",
)

benchmark = TPCH(scale_factor=1.0)
benchmark.generate_data()
adapter.load_benchmark(benchmark)
results = adapter.run_benchmark(benchmark)

Architecture

DuckLake extends DuckDBAdapter, which means:

  • SQL Dialect: Uses DuckDB's SQL dialect for query translation
  • Data Types: Same data type mappings as DuckDB
  • Benchmark Compatibility: Supports all benchmarks that DuckDB supports
  • Connection setup: create_connection() runs INSTALL ducklake; LOAD ducklake; ATTACH 'ducklake:<metadata_path>' AS lake (DATA_PATH '<data_path>'); USE lake; before any benchmark DDL/DML, so subsequent unqualified CREATE TABLE/INSERT INTO statements resolve against the attached lake catalog
from benchbox.core.platform_registry import PlatformRegistry

family = PlatformRegistry.get_platform_family("ducklake")

parent = PlatformRegistry.get_inherited_platform("ducklake")

get_platform_family returns "duckdb", and get_inherited_platform also returns "duckdb".

Comparison: DuckLake vs DuckDB

Feature DuckLake DuckDB
Table data Parquet files under DATA_PATH DuckDB native storage format
Catalog Separate SQL-database metadata file Embedded in the database file
Deployment Local (default), self-hosted PostgreSQL catalog, or S3-backed storage - see Catalog Backends Local embedded
Requirements DuckDB >= 1.3 + ducklake extension DuckDB (any supported version)
Support status Beta Stable
Best For Evaluating lakehouse table-format overhead on DuckDB General local benchmarking

When to Use DuckLake

Use DuckLake when:

  • You want to benchmark lakehouse table-format overhead (separate catalog + Parquet data) against plain DuckDB storage
  • You are prototyping against DuckLake's Parquet-data/SQL-catalog split before adopting a non-DuckDB catalog backend
  • You need results that stay directly comparable to duckdb because the underlying engine and dialect are identical

Use DuckDB instead when:

  • You do not need a separate catalog/data-file split
  • You are on a DuckDB runtime older than 1.3
  • You want the most stable, generally-available local platform

Interpreting DuckLake numbers: no compaction is performed

BenchBox never invokes DuckLake's maintenance operations (compaction / ducklake_merge_adjacent_files, inlining). DuckLake writes a new Parquet file per insert transaction — five separate INSERTs produce five files — and nothing merges them afterwards.

The resulting bias runs against DuckLake: queries scan more, smaller files than a compacted production deployment would, so BenchBox's DuckLake timings are a floor rather than a ceiling. How much this matters depends on the load path — bulk loading via few large inserts produces few large files and little bias, while row-wise loading produces many small ones.

Treat a DuckLake-vs-other-engine gap accordingly, and do not read it as the best DuckLake can do. See ADR: DuckLake Maturity, Publishability, and Compaction Bias for why this is documented rather than instrumented.

Lakehouse-Feature Benchmarks (Time Travel, Schema Evolution, Snapshots)

DuckLake runs the same TPC-H and TPC-DS workloads as DuckDB, including the standard maintenance operations; BenchBox has no dedicated time-travel, schema-evolution, or snapshot benchmark yet.

Troubleshooting

DuckDB Version Too Old

RuntimeError: DuckLake requires DuckDB >= 1.3 (the 'ducklake' extension is not
available on earlier releases). Detected DuckDB version: 1.2.x. ...

Solution: Upgrade the duckdb package, e.g. uv add 'duckdb>=1.3,<2.0', or pin a compatible driver with --platform-option driver_version=1.3.2 --platform-option driver_auto_install=true.

Catalog Attach Failure

RuntimeError: Failed to initialize the DuckLake catalog (INSTALL/LOAD/ATTACH 'ducklake' extension). ...

Solutions:

  1. Confirm network access is available for the first INSTALL ducklake (extension download)
  2. Verify metadata_path's parent directory and data_path are writable
  3. Re-run with --force if a previous run left a partially-initialized catalog

Table Already Exists

If a benchmark run is interrupted mid-load, a subsequent run without --force reuses the existing (partially loaded) catalog. Pass --force to wipe the catalog metadata file and Parquet data directory and rebuild from scratch.

For catalog=postgres, the adapter inspects the attached catalog, verifies the benchmark/scale/tuning identity before reuse, and drops its tables when --force is passed.

PostgreSQL Catalog: Database Does Not Exist

... database "ducklake_catalog" does not exist ...

Solution: DuckLake's postgres catalog ATTACH does not run CREATE DATABASE - create the target database on the PostgreSQL server first (e.g. createdb ducklake_catalog), then re-run with --platform-option pg_database=ducklake_catalog. Re-running against an already-populated PostgreSQL catalog is supported: the adapter inspects the attached catalog after ATTACH, reuses it by default, and drops its tables when --force is passed.

Related Documentation