Skip to main content
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

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

ParameterDescription
<location_name>A unique identifier for the location within your account.
SOURCEThe external data source type. Use DUCKLAKE for DuckLake locations.
DESCRIPTIONOptional metadata describing the location’s purpose.

​
DuckLake parameters

ParameterRequiredDescription
CATALOGYesA PostgreSQL connection string pointing to the DuckLake catalog database, for example postgresql://user:password@host:5432/dbname. Stored encrypted.
ENDPOINTNoThe 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.
SCHEMANoA default catalog schema for callers of READ_DUCKLAKE and LIST_DUCKLAKE_FILES. If omitted here and at the call site, main is used.
TABLENoA default table for callers. If set, callers can read this table without passing a TABLE argument.
CREDENTIALSNoStorage 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.
ParameterDescription
AWS_ACCESS_KEY_IDAWS access key ID. Must be provided together with AWS_SECRET_ACCESS_KEY.
AWS_SECRET_ACCESS_KEYAWS secret access key. Must be provided together with AWS_ACCESS_KEY_ID.
AWS_SESSION_TOKENOptional temporary session token, used with key-based authentication.
AWS_ROLE_ARNThe ARN of the IAM role to assume for role-based authentication.
AWS_ROLE_EXTERNAL_IDOptional 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'
  );