DuckDB and Apache Iceberg

Fusion only

DuckDB support for catalogs.yml requires the dbt Fusion engine (v2) with the use_catalogs_v2 behavior flag enabled. It isn't available in the legacy Python dbt-duckdb adapter for dbt Core v1.

dbt_project.yml

flags:
  use_catalogs_v2: true

Unlike Snowflake, Databricks, and BigQuery, DuckDB doesn't ship with a single built-in "managed" Iceberg catalog. This means there's no table_format='iceberg'-only shortcut for DuckDB — every Iceberg model requires a catalog_name that points to an entry in catalogs.yml.

dbt supports creating Iceberg tables for two DuckDB materializations:

• Table
• Incremental

How DuckDB attaches catalogs

When you configure a catalog with a duckdb block in catalogs.yml, dbt generates and runs the appropriate DuckDB ATTACH statement on your behalf — you don't need to write ATTACH SQL yourself. dbt then resolves any model with a matching catalog_name to that attached database.

Iceberg REST catalogs

DuckDB can attach to any catalog that implements the Iceberg REST protocol, including self-hosted catalogs (such as Lakekeeper or Nessie), AWS Glue, and AWS S3 Tables.

catalogs.yml

catalogs:
  - name: rest_catalog
    type: iceberg_rest
    table_format: iceberg
    config:
      duckdb:
        endpoint: "https://my-iceberg-rest.example.com"
        secret: my_iceberg_secret

models/my_iceberg_model.sql

{{
    config(
        materialized = 'table',
        catalog_name = 'rest_catalog'
    )
}}

select * from {{ ref('jaffle_shop_customers') }}

Run the model with dbt run -s my_iceberg_model. Instead of endpoint, you can use endpoint_type: GLUE or endpoint_type: S3_TABLES to attach one of these well-known AWS-managed Iceberg REST endpoints without specifying a URL:

catalogs.yml

catalogs:
  - name: s3_tables_catalog
    type: iceberg_rest
    table_format: iceberg
    config:
      duckdb:
        endpoint_type: S3_TABLES
        warehouse: "arn:aws:s3tables:us-east-1:123456789012:bucket/example"

endpoint and endpoint_type are mutually exclusive.

Cross-platform Mesh: reading catalogs managed by other platforms

Because a single catalog entry in catalogs.yml can carry configuration for multiple platforms at once, you can point DuckDB at the same physical catalog that Snowflake or Databricks writes to — enabling cross-platform Mesh without copying data.

Snowflake Horizon

Snowflake Horizon is Snowflake's managed Iceberg catalog. Add a duckdb block alongside the snowflake block to let DuckDB attach to the same catalog:

catalogs.yml

catalogs:
  - name: horizon_catalog
    type: horizon
    table_format: iceberg
    config:
      snowflake:
        external_volume: my_external_volume
      duckdb:
        warehouse: horizon_wh
        endpoint: "https://horizon.example.com/catalog"
        secret: horizon_secret
        default_schema: demo

Databricks Unity Catalog

Similarly, for Databricks Unity Catalog:

catalogs.yml

catalogs:
  - name: unity_catalog
    type: unity
    table_format: iceberg
    config:
      databricks:
        file_format: delta
        use_uniform: true
      duckdb:
        warehouse: unity_wh
        endpoint: "https://dbc-example.cloud.databricks.com/api/2.1/unity-catalog/iceberg"
        default_schema: demo

Read-only vs. read-write

By default, dbt attaches Horizon and Unity catalogs read-write (read_only: false) and applies write-compat ATTACH defaults for each (for example, disabling multi-table commits on Unity). Writing to these catalogs from DuckDB requires DuckDB 1.5.4+ and duckdb-iceberg#1017. If you only need to read Iceberg tables that another platform wrote, set read_only: true:

      duckdb:
        warehouse: horizon_wh
        endpoint: "https://horizon.example.com/catalog"
        read_only: true

DuckLake

DuckLake is a separate open table format (not Apache Iceberg) built for DuckDB, but you configure it the same way, through catalogs.yml. Because DuckLake isn't Iceberg, its catalog entries use table_format: default.

catalogs.yml

catalogs:
  - name: local_lake
    type: ducklake
    table_format: default
    config:
      duckdb:
        metadata_path: "metadata.ducklake"
        data_path: "s3://my-bucket/lake" # optional

models/my_ducklake_model.sql

{{
    config(
        materialized = 'table',
        catalog_name = 'local_lake'
    )
}}

select * from {{ ref('jaffle_shop_customers') }}

dbt installs the DuckLake extension and attaches the catalog before running your model:

INSTALL ducklake
ATTACH IF NOT EXISTS 'ducklake:metadata.ducklake' AS local_lake (DATA_PATH 's3://my-bucket/lake')

Secrets

The secret field in a duckdb catalog block references a named secret defined in profiles.yml, which dbt turns into a DuckDB CREATE SECRET statement:

profiles.yml

my_profile:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: ':memory:'
      secrets:
        - type: iceberg
          name: my_iceberg_secret
          # additional key-value pairs become CREATE SECRET parameters
          # (for example, token, client_id, client_secret) -- see DuckDB's
          # iceberg extension docs for the parameters your catalog needs.

DuckDB-specific configs for Iceberg catalogs

You can supply these configs, nested under config.duckdb, for horizon, unity, and iceberg_rest catalogs:

Field	Required	Description
endpoint	One of endpoint/endpoint_type	Full Iceberg REST catalog URL.
endpoint_type	One of endpoint/endpoint_type	GLUE or S3_TABLES, for well-known AWS-managed endpoints.
warehouse	Required for horizon; required when endpoint_type is S3_TABLES	Warehouse identifier passed as the ATTACH source.
secret	Optional	Name of a DuckDB secret from profiles.yml to use for authentication.
attach_as	Optional	Overrides the DuckDB attach alias. Defaults to the catalog's name.
default_region	Optional	AWS region, when applicable.
default_schema	Optional	Default schema/namespace within the catalog.
max_table_staleness	Optional	How long DuckDB may serve cached metadata before refreshing.
authorization_type	Optional	OAUTH2, SIGV4, or NONE. Can't be combined with endpoint_type.
access_delegation_mode	Optional	VENDED_CREDENTIALS or NONE.
read_only	Optional	Attach the catalog read-only. Defaults to false (read-write).
support_nested_namespaces	Optional	Whether the catalog supports nested namespaces.
stage_create_tables	Optional	Write-compat: stage CREATE TABLE AS SELECT writes. Requires DuckDB 1.5.4+.
disable_multi_table_commit	Optional	Write-compat: disable multi-table commits. Requires DuckDB 1.5.4+.
skip_create_table_metadata_updates	Optional	Write-compat: skip metadata updates on CREATE TABLE. Requires DuckDB 1.5.4+.
remove_files_on_delete	Optional	Write-compat: remove underlying data files when a table is dropped. Requires DuckDB 1.5.4+.
purge_requested	Optional	Purge underlying files when supported by the catalog.
encode_entire_prefix	Optional	Percent-encode the entire object key prefix.

Loading table...

For ducklake catalogs, config.duckdb accepts:

Field	Required	Description
metadata_path	Required	Path to the DuckLake metadata store, for example metadata.ducklake or a database connection string.
data_path	Optional	Where DuckLake writes data files.
attach_as	Optional	Overrides the DuckDB attach alias. Defaults to the catalog's name.
metadata_schema	Optional	Schema within the metadata store to use.
metadata_catalog	Optional	Catalog/database name within the metadata store.
data_inlining_row_limit	Optional	Inline row groups smaller than this many rows into the metadata catalog instead of writing a Parquet file.
create_if_not_exists	Optional	Create the DuckLake catalog if it doesn't already exist.
read_only	Optional	Attach read-only.
encrypted	Optional	Encrypt the DuckLake catalog.
automatic_migration	Optional	Automatically migrate the catalog's DuckLake format version on attach.
override_data_path	Optional	Allow attaching with a data_path that differs from the one recorded in an existing catalog.

Loading table...

Was this page helpful?

Privacy policyCreate a GitHub issue

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.