Data Connectors

DuckLake Data Connector

Query DuckLake tables stored as Parquet files with SQLite or PostgreSQL metadata, and write rows with INSERT INTO.

DuckLake is an open lakehouse format that stores metadata in a SQLite-compatible database (or PostgreSQL) and data in Parquet files. This connector enables querying individual DuckLake tables as datasets in Spice.

For automatic discovery of all schemas and tables in a DuckLake catalog, use the DuckLake Catalog Connector instead.

datasets:
  - from: ducklake:my_table
    name: my_table
    params:
      ducklake_connection_string: s3://my-bucket/path/metadata.ducklake

Configuration

from

The from field specifies the DuckLake table to connect to. Use ducklake:<table_path>, where table_path is the table name or a schema-qualified table name.

fromDescription
ducklake:my_tableRead from my_table in the default main schema
ducklake:my_schema.my_tableRead from my_table in the my_schema schema

name

The dataset name. This will be used as the table name within Spice.

datasets:
  - from: ducklake:customer
    name: tpch_customer
    params:
      ducklake_connection_string: s3://my-bucket/metadata.ducklake
SELECT COUNT(*) FROM tpch_customer;

The dataset name cannot be a reserved keyword.

params

Parameter NameDescription
ducklake_connection_stringRequired. The DuckLake metadata location (e.g., s3://bucket/path/metadata.ducklake).
ducklake_nameThe name to attach the DuckLake catalog as in DuckDB. Default: ducklake.
ducklake_openPath to an existing DuckDB file for persistent storage. If not provided, an in-memory DuckDB instance is used.
ducklake_aws_regionOptional. The AWS region for S3 storage. Default: us-east-1 when explicit credentials are provided.
ducklake_aws_access_key_idOptional. The AWS access key ID for S3 storage. Must be set together with ducklake_aws_secret_access_key.
ducklake_aws_secret_access_keyOptional. The AWS secret access key for S3 storage. Must be set together with ducklake_aws_access_key_id.
ducklake_aws_session_tokenOptional. The AWS session token for S3 storage. Required with temporary (STS) credentials. Ignored, with a warning, unless ducklake_aws_access_key_id is also set.
ducklake_aws_endpointOptional. Custom S3-compatible endpoint URL (e.g., for MinIO).
ducklake_aws_allow_httpOptional. Set to true to allow HTTP (non-TLS) connections to S3. Default: false.
ducklake_automatic_migrationOptional. Set to true to automatically migrate an older DuckLake catalog schema to the version required by the DuckLake extension on attach. Default: false. Migration rewrites catalog metadata and cannot be undone.

Connection string formats

BackendExample
Local file/path/to/metadata.ducklake
AWS S3s3://bucket/path/metadata.ducklake
PostgreSQLpostgres:dbname=mydb host=localhost user=postgres password=secret

Authentication

AWS S3

When no explicit S3 credentials are configured, DuckDB falls back to its built-in credential chain provider:

  1. Environment variables (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_SESSION_TOKEN)
  2. Shared credentials file (~/.aws/credentials)
  3. IAM instance profiles (on EC2/ECS)

To provide explicit S3 credentials, use the ducklake_aws_* parameters:

datasets:
  - from: ducklake:customer
    name: customer
    params:
      ducklake_connection_string: s3://my-bucket/metadata.ducklake
      ducklake_aws_region: us-west-2
      ducklake_aws_access_key_id: ${secrets:AWS_ACCESS_KEY_ID}
      ducklake_aws_secret_access_key: ${secrets:AWS_SECRET_ACCESS_KEY}

For S3-compatible storage (e.g., MinIO), use ducklake_aws_endpoint:

datasets:
  - from: ducklake:customer
    name: customer
    params:
      ducklake_connection_string: s3://my-bucket/metadata.ducklake
      ducklake_aws_endpoint: http://minio:9000
      ducklake_aws_access_key_id: ${secrets:MINIO_ACCESS_KEY}
      ducklake_aws_secret_access_key: ${secrets:MINIO_SECRET_KEY}
      ducklake_aws_allow_http: true

Write Support

This connector supports writing data to DuckLake tables using SQL INSERT INTO statements when access is set to read_write:

datasets:
  - from: ducklake:customer
    name: customer
    access: read_write
    params:
      ducklake_connection_string: s3://my-bucket/metadata.ducklake
INSERT INTO customer (c_custkey, c_name) VALUES (1, 'Acme Corp');

UPDATE and DELETE FROM are not supported. For DDL operations (CREATE TABLE, DROP TABLE), use the DuckLake Catalog Connector with access: read_write_create.

Examples

Reading from a local DuckLake catalog

datasets:
  - from: ducklake:customer
    name: customer
    params:
      ducklake_connection_string: /path/to/metadata.ducklake

Reading from S3

datasets:
  - from: ducklake:customer
    name: customer
    params:
      ducklake_connection_string: s3://my-bucket/lakehouse/metadata.ducklake

Reading from a specific schema

datasets:
  - from: ducklake:analytics.events
    name: events
    params:
      ducklake_connection_string: s3://my-bucket/metadata.ducklake

PostgreSQL metadata backend

datasets:
  - from: ducklake:customer
    name: customer
    params:
      ducklake_connection_string: "postgres:dbname=ducklake_catalog host=localhost user=postgres password=postgres"

Multiple tables with YAML anchors

datasets:
  - from: ducklake:customer
    name: customer
    params: &ducklake_params
      ducklake_connection_string: s3://my-bucket/metadata.ducklake
  - from: ducklake:orders
    name: orders
    params: *ducklake_params
  - from: ducklake:lineitem
    name: lineitem
    params: *ducklake_params

With data acceleration

datasets:
  - from: ducklake:customer
    name: customer
    params:
      ducklake_connection_string: s3://my-bucket/metadata.ducklake
    acceleration:
      enabled: true
      engine: cayenne
      mode: file
      refresh_check_interval: 1h