> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

> Set up a DuckLake catalog on PostgreSQL with DuckDB, then read its Parquet data from Firebolt using READ_DUCKLAKE and LIST_DUCKLAKE_FILES.

# Query DuckLake tables with Firebolt

<Warning>
  DuckLake support is **experimental** and may change. Only DuckLake catalogs hosted on **PostgreSQL** are supported.
</Warning>

This guide walks you through setting up a [DuckLake](https://ducklake.select/) catalog on PostgreSQL with Parquet data files on local disk, and then reading that data from Firebolt. You'll create a table with DuckDB and query it from Firebolt using [`READ_DUCKLAKE`](/reference-sql/functions-reference/ducklake/read_ducklake) and [`LIST_DUCKLAKE_FILES`](/reference-sql/functions-reference/ducklake/list_ducklake_files).

## Prerequisites

* Firebolt
* PostgreSQL — this guide runs it as a Docker container
* [DuckDB](https://duckdb.org/) — tested with v1.5.2

## Step 1: Start PostgreSQL

DuckLake stores its catalog metadata in a SQL database. Start a PostgreSQL container, setting the user, password, and database name:

```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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:

   ```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
   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:

   ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
   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:

   ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
   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:

   ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
   -- 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:

   ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
   .timer on   -- optional: show query latency
   SELECT * FROM my_first_ducklake_table ORDER BY r DESC LIMIT 5;
   ```

## Step 3: Start Firebolt

Start the Firebolt container. Two options matter for DuckLake:

* `--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.

```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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

The container from Step 3 runs in the foreground, so open a **new terminal** to connect to Firebolt. Any [supported client](/self-managed/connecting-over-http) works — connect to the query endpoint on port `3473`, then run the SQL below.

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:

   ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
   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)](/reference-sql/commands/data-definition/create-location-ducklake).

2. List the Parquet files that make up your table:

   ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
   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:

   ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
   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:

   ```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
   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](/reference-sql/functions-reference/ducklake/read_ducklake) — Full reference, including reading from S3-compatible object storage, pinning a snapshot, and the supported data types.
* [LIST\_DUCKLAKE\_FILES](/reference-sql/functions-reference/ducklake/list_ducklake_files) — Inspect a table's data files and per-file statistics.
* [CREATE LOCATION (DuckLake)](/reference-sql/commands/data-definition/create-location-ducklake) — Store catalog connection details and credentials in a reusable location object.