Query DuckLake tables with Firebolt

​

Prerequisites

• Firebolt
• PostgreSQL — this guide runs it as a Docker container
• DuckDB — tested with v1.5.2

​

Step 1: Start PostgreSQL

docker rm -f ducklake-postgres

docker run -d \
  --name ducklake-postgres \
  -e POSTGRES_USER=dl_user \
  -e POSTGRES_PASSWORD=dl_pw \
  -e POSTGRES_DB=dl_db \
  -p 5432:5432 \
  postgres:16

​

Step 2: Create a DuckLake table with DuckDB

1. Install DuckDB and start the DuckDB shell:
   curl https://install.duckdb.org | sh
   duckdb
   
   The remaining commands in this step run inside the DuckDB shell.
2. Install and load the DuckLake extension:
   INSTALL ducklake;
   LOAD ducklake;
   
3. Create the DuckLake catalog in PostgreSQL and attach it. Use the same credentials you set for the PostgreSQL container. The DATA_PATH option determines where the Parquet data files are written on local disk:
   ATTACH 'ducklake:postgres:host=localhost port=5432 user=dl_user password=dl_pw dbname=dl_db'
     AS pg_ducklake (DATA_PATH '/tmp/ducklake/', OVERRIDE_DATA_PATH true);
   
4. Create a table and insert some data:
   -- Use the DuckLake catalog
   USE pg_ducklake;
   
   -- Create a table in the DuckLake catalog
   CREATE TABLE my_first_ducklake_table (a INT, r FLOAT);
   
   -- Insert sample data
   INSERT INTO my_first_ducklake_table SELECT x, random() FROM generate_series(1, 10000) g(x);
   
5. Confirm the table exists, with its Parquet files written to local disk:
   .timer on   -- optional: show query latency
   SELECT * FROM my_first_ducklake_table ORDER BY r DESC LIMIT 5;
   

​

Step 3: Start Firebolt

• --network host lets the Firebolt binary inside the container reach the PostgreSQL container.
• -v /tmp/ducklake:/tmp/ducklake mounts the local directory where DuckDB wrote the Parquet files into the Firebolt container at the same path.

docker run -it \
  --name firebolt \
  --rm \
  -e FIREBOLT_CORE_MODE=1 \
  --ulimit memlock=8589934592:8589934592 \
  --security-opt seccomp=unconfined \
  --network host \
  -v /tmp/ducklake:/tmp/ducklake \
  ghcr.io/firebolt-db/engine:dev

​

Step 4: Query the DuckLake table from Firebolt

1. Create a location object that points to the DuckLake catalog. Use the same credentials you set for the PostgreSQL container. Because the data files are on local disk, no endpoint or storage credentials are needed:
   CREATE LOCATION my_ducklake_loc WITH
     SOURCE = DUCKLAKE
     CATALOG = 'postgresql://dl_user:dl_pw@127.0.0.1:5432/dl_db';
   
   For the full syntax, see CREATE LOCATION (DuckLake).
2. List the Parquet files that make up your table:
   SELECT *
   FROM LIST_DUCKLAKE_FILES(
     LOCATION => 'my_ducklake_loc',
     SCHEMA => 'main',    -- optional: 'main' is the default
     TABLE => 'my_first_ducklake_table'
   );
   
3. Read the data, the same query you ran in DuckDB:
   SELECT *
   FROM READ_DUCKLAKE(
     LOCATION => 'my_ducklake_loc',
     TABLE => 'my_first_ducklake_table'
   )
   ORDER BY r DESC
   LIMIT 5;
   
4. Inspect the query plan to see how caching behaves across repeated runs. Run it twice and compare the two plans, the second run reads cached metadata and data:
   EXPLAIN (ANALYZE)
   SELECT *
   FROM READ_DUCKLAKE(
     LOCATION => 'my_ducklake_loc',
     TABLE => 'my_first_ducklake_table'
   )
   ORDER BY r DESC
   LIMIT 5;
   

​

Next steps

• READ_DUCKLAKE — Full reference, including reading from S3-compatible object storage, pinning a snapshot, and the supported data types.
• LIST_DUCKLAKE_FILES — Inspect a table’s data files and per-file statistics.
• CREATE LOCATION (DuckLake) — Store catalog connection details and credentials in a reusable location object.