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 may change. Only DuckLake catalogs hosted on PostgreSQL are supported.

Syntax

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:

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

The following example uses a role and includes a recommended external ID:

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'
    AWS_ROLE_EXTERNAL_ID = 'my-external-id'
  );

For role-based AWS access you can additionally set an external ID. An external ID is a value you choose and control that AWS checks when Firebolt assumes your role, adding a second condition on top of your account’s unique IAM principal. Configuring one is a recommended best practice. See IAM roles.

AWS_ROLE_EXTERNAL_ID is optional. To assume the role without an external ID, omit it:

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.

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

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