CREATE LOCATION (DuckLake)

Creates a new location object that stores the connection details and credentials for a DuckLake data source. Instead of passing the catalog connection string and storage credentials to READ_DUCKLAKE and LIST_DUCKLAKE_FILES on every call, you reference the location by name. A DuckLake location captures:

The DuckLake catalog connection string — a PostgreSQL connection string that points to the catalog database. This is encrypted at rest because it usually contains a password.
An optional S3 endpoint and storage credentials for the Parquet data files. Omit these when the data files live on a local filesystem.
An optional default schema and table, so callers can reference the location without repeating them.

This document captures the syntax and examples for DuckLake locations. For a comprehensive guide to location objects, see LOCATION objects. For location syntax in general, see CREATE LOCATION.

DuckLake support is experimental and disabled by default. Set enable_ducklake = true to use it, and expect the behavior to change. Only DuckLake catalogs hosted on PostgreSQL are supported.

Topics:

Syntax
Parameters
Catalog-level and table-level locations
Examples
Related

Syntax

SET enable_ducklake = true;

CREATE LOCATION [ IF NOT EXISTS ] <location_name> WITH
  SOURCE = DUCKLAKE
  CATALOG = '<postgres_connection_string>'
  [ ENDPOINT = '<s3_endpoint>' ]
  [ SCHEMA = '<schema_name>' ]
  [ TABLE = '<table_name>' ]
  [ CREDENTIALS = {
      -- AWS key-based authentication
      ( AWS_ACCESS_KEY_ID = '<aws_access_key_id>'
        AWS_SECRET_ACCESS_KEY = '<aws_secret_access_key>'
        [ AWS_SESSION_TOKEN = '<aws_session_token>' ] )
      |
      -- AWS role-based authentication
      ( AWS_ROLE_ARN = '<aws_role_arn>'
        [ AWS_ROLE_EXTERNAL_ID = '<aws_role_external_id>' ] )
    } ]
  [ DESCRIPTION = '<description>' ]

Parameters

Common parameters

Parameter	Description
`<location_name>`	A unique identifier for the location within your account.
`SOURCE`	The external data source type. Use `DUCKLAKE` for DuckLake locations.
`DESCRIPTION`	Optional metadata describing the location’s purpose.

DuckLake parameters

Parameter	Required	Description
`CATALOG`	Yes	A PostgreSQL connection string pointing to the DuckLake catalog database, for example `postgresql://user:password@host:5432/dbname`. Stored encrypted.
`ENDPOINT`	No	The S3-compatible endpoint that serves the Parquet data files, for example `http://minio:9000` or `https://s3.us-east-1.amazonaws.com`. Omit it for data files on a local filesystem.
`SCHEMA`	No	A default catalog schema for callers of `READ_DUCKLAKE` and `LIST_DUCKLAKE_FILES`. If omitted here and at the call site, `main` is used.
`TABLE`	No	A default table for callers. If set, callers can read this table without passing a `TABLE` argument.
`CREDENTIALS`	No	Storage credentials for the data files. Omit it for data files on a local filesystem where no object-storage access is needed.

Credentials

CREDENTIALS accepts AWS authentication fields in one of two combinations: key-based (AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY, optionally with AWS_SESSION_TOKEN) or role-based (AWS_ROLE_ARN, optionally with AWS_ROLE_EXTERNAL_ID). You must provide either the access-key pair or AWS_ROLE_ARN, and AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY must be supplied together.

Parameter	Description
`AWS_ACCESS_KEY_ID`	AWS access key ID. Must be provided together with `AWS_SECRET_ACCESS_KEY`.
`AWS_SECRET_ACCESS_KEY`	AWS secret access key. Must be provided together with `AWS_ACCESS_KEY_ID`.
`AWS_SESSION_TOKEN`	Optional temporary session token, used with key-based authentication.
`AWS_ROLE_ARN`	The ARN of the IAM role to assume for role-based authentication.
`AWS_ROLE_EXTERNAL_ID`	Optional external ID for role assumption.

Catalog-level and table-level locations

A DuckLake location can describe a whole catalog or a single table, depending on how much you embed in it.

Catalog-level location. Set only CATALOG (plus ENDPOINT and CREDENTIALS if the data is in object storage). Callers supply SCHEMA and TABLE at the call site, so one location serves every table in the catalog.

CREATE LOCATION my_catalog WITH
  SOURCE = DUCKLAKE
  CATALOG = 'postgresql://dl_user:dl_pw@127.0.0.1:5432/dl_db';

SELECT * FROM READ_DUCKLAKE(LOCATION => 'my_catalog', SCHEMA => 'main', TABLE => 'orders') LIMIT 5;
SELECT * FROM READ_DUCKLAKE(LOCATION => 'my_catalog', SCHEMA => 'main', TABLE => 'customers') LIMIT 5;

Table-level location. Also set SCHEMA and TABLE. Callers can then reference the location with no further arguments.

CREATE LOCATION my_orders WITH
  SOURCE = DUCKLAKE
  CATALOG = 'postgresql://dl_user:dl_pw@127.0.0.1:5432/dl_db'
  SCHEMA = 'main'
  TABLE = 'orders';

SELECT * FROM READ_DUCKLAKE(LOCATION => 'my_orders') LIMIT 5;

When a location defines a SCHEMA or TABLE and the caller also passes one, the two values must match. Otherwise the query fails with a mismatch error.

Examples

Local catalog with on-disk data files

When the catalog is a local PostgreSQL instance and the Parquet files are on a local filesystem (for example, a Firebolt Core deployment), CATALOG is the only required field — no endpoint or credentials are needed:

SET enable_ducklake = true;

CREATE LOCATION my_ducklake_loc WITH
  SOURCE = DUCKLAKE
  CATALOG = 'postgresql://dl_user:dl_pw@127.0.0.1:5432/dl_db';

SELECT * FROM READ_DUCKLAKE(LOCATION => 'my_ducklake_loc', TABLE => 'my_first_ducklake_table') LIMIT 5;

Object storage with access key and secret

CREATE LOCATION my_ducklake_loc WITH
  SOURCE = DUCKLAKE
  CATALOG = 'postgresql://dl_user:dl_pw@pg-host:5432/dl_db'
  ENDPOINT = 'https://s3.us-east-1.amazonaws.com'
  SCHEMA = 'main'
  TABLE = 'orders'
  CREDENTIALS = (
    AWS_ACCESS_KEY_ID = '1231'
    AWS_SECRET_ACCESS_KEY = '567'
  );

SELECT * FROM READ_DUCKLAKE(LOCATION => 'my_ducklake_loc') LIMIT 5;

Object storage with a session token

CREATE LOCATION my_ducklake_loc WITH
  SOURCE = DUCKLAKE
  CATALOG = 'postgresql://dl_user:dl_pw@pg-host:5432/dl_db'
  ENDPOINT = 'https://s3.us-east-1.amazonaws.com'
  CREDENTIALS = (
    AWS_ACCESS_KEY_ID = '1231'
    AWS_SECRET_ACCESS_KEY = '567'
    AWS_SESSION_TOKEN = 'session-token'
  );

Object storage with a role

CREATE LOCATION my_ducklake_loc WITH
  SOURCE = DUCKLAKE
  CATALOG = 'postgresql://dl_user:dl_pw@pg-host:5432/dl_db'
  ENDPOINT = 'https://s3.us-east-1.amazonaws.com'
  CREDENTIALS = (
    AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/MyRole'
  );

MinIO endpoint

DuckLake data files can be served from any S3-compatible store, such as MinIO:

CREATE LOCATION my_ducklake_loc WITH
  SOURCE = DUCKLAKE
  CATALOG = 'postgresql://dl_user:dl_pw@pg-host:5432/dl_db'
  ENDPOINT = 'http://minio:9000'
  CREDENTIALS = (
    AWS_ACCESS_KEY_ID = 'firebolt'
    AWS_SECRET_ACCESS_KEY = 'firebolt'
  );

READ_DUCKLAKE — Read a DuckLake table using a location object.
LIST_DUCKLAKE_FILES — List the data files behind a DuckLake table.
DuckLake functions overview — All DuckLake functions in one place.
Query DuckLake tables with Firebolt Core — A step-by-step guide to setting up a DuckLake catalog and reading it from Firebolt.
DROP LOCATION — Remove a location object.

What is Firebolt?

Overview

Self-Managed

Performance and Observability

Guides

SQL reference

General reference

API reference

Legal

Syntax

Parameters

Common parameters

DuckLake parameters

Credentials

Catalog-level and table-level locations

Examples

Local catalog with on-disk data files

Object storage with access key and secret

Object storage with a session token

Object storage with a role

MinIO endpoint

​Syntax

​Parameters

​Common parameters

​DuckLake parameters

​Credentials

​Catalog-level and table-level locations

​Examples

​Local catalog with on-disk data files

​Object storage with access key and secret

​Object storage with a session token

​Object storage with a role

​MinIO endpoint

​Related

Syntax

Parameters

Common parameters

DuckLake parameters

Credentials

Catalog-level and table-level locations

Examples

Local catalog with on-disk data files

Object storage with access key and secret

Object storage with a session token

Object storage with a role

MinIO endpoint

Related