Apache Iceberg

Attach an Iceberg REST catalog as a MotherDuck database to read and write Iceberg tables, or scan individual tables by path.

MotherDuck supports the Apache Iceberg format through the DuckDB Iceberg extension.

There are two ways to work with Iceberg in MotherDuck:

Persisted Iceberg catalogs — attach an Iceberg REST catalog as a MotherDuck database with CREATE DATABASE. The attachment lives in your workspace, so it survives across sessions, and reads and writes run on MotherDuck's compute.
Scanning individual tables — query a single Iceberg table by path with iceberg_scan, without attaching a catalog.

Set up the Iceberg extension

In a fresh local DuckDB environment, install and load the Iceberg extension before connecting to MotherDuck. Do this once per environment, such as a local machine, container, or VM.

INSTALL iceberg;
LOAD iceberg;
ATTACH 'md:';

In Python, install and load the extension before opening the MotherDuck connection:

import duckdb

duckdb.sql("INSTALL iceberg")
duckdb.sql("LOAD iceberg")
conn = duckdb.connect("md:")

Persisted Iceberg catalogs

Attach an Iceberg REST catalog as a MotherDuck database. The database persists in your workspace: you attach it once, it appears alongside your other databases, and you don't re-attach it in each new session. Reads and writes run on MotherDuck's cloud execution engine.

MotherDuck works with any Iceberg REST catalog endpoint, but your mileage may vary by provider. Amazon S3 Tables and Apache Polaris are tested and supported.

note

Persisted Iceberg catalogs require DuckDB 1.5.2 or later. The MotherDuck extension updates automatically when you restart DuckDB.

warning

Iceberg REST catalog reads and writes run on MotherDuck's cloud compute. Attaching an Iceberg REST catalog directly in a local DuckDB session (without MotherDuck) is unreliable and not supported here. Attach the catalog as a MotherDuck database instead.

Authentication

Store your catalog credentials in a MotherDuck secret. Credentials must live in a secret — the database options accept catalog settings only, not credentials.

-- OAuth2 client credentials
CREATE SECRET my_iceberg_secret IN MOTHERDUCK (
    TYPE ICEBERG,
    CLIENT_ID 'my_client_id',
    CLIENT_SECRET 'my_client_secret',
    OAUTH2_SERVER_URI 'https://my-catalog.example.com/v1/oauth/tokens'
);

-- Bearer token
CREATE SECRET my_iceberg_secret IN MOTHERDUCK (
    TYPE ICEBERG,
    TOKEN 'my_bearer_token'
);

See CREATE SECRET for the full list of Iceberg secret parameters.

Creating the database

note

CREATE DATABASE ... TYPE ICEBERG does not create a new Iceberg catalog. It connects to an existing REST catalog and registers it as a MotherDuck database, behaving like an attach. The catalog must already exist at the endpoint you point to.

Create the database with TYPE ICEBERG, referencing the secret and the catalog endpoint. A default_schema that exists in the catalog is required:

CREATE DATABASE my_datalake (
    TYPE ICEBERG,
    "secret" my_iceberg_secret,
    endpoint 'https://my-catalog.example.com',
    warehouse 'my_warehouse',
    default_schema 'default'
);

Once attached, browse and query the catalog with standard SQL:

-- List schemas
SELECT schema_name FROM information_schema.schemata
WHERE catalog_name = 'my_datalake';

-- List tables in a schema
SHOW TABLES FROM my_datalake.my_schema;
SHOW SCHEMAS IN my_datalake;

-- Inspect a table's columns (duckdb_columns() does not list them)
DESCRIBE my_datalake.default.my_table;

-- Query a table
SELECT * FROM my_datalake.default.my_table;

Set the database as the active catalog to use unqualified names:

USE my_datalake;
SELECT * FROM my_table;

Database options

Pass these options in the CREATE DATABASE options list. Credentials (CLIENT_ID, CLIENT_SECRET, OAUTH2_*, TOKEN) belong in the secret, not here.

Option	Description
`secret`	Name of the MotherDuck Iceberg or S3 secret holding catalog credentials. Quote as `"secret"`.
`endpoint`	URL of the Iceberg REST catalog. Required unless the endpoint is set in the secret or derived from `endpoint_type`.
`warehouse`	Catalog warehouse identifier. For S3 Tables, this is the bucket ARN.
`default_schema`	Required. Schema used to resolve unqualified table names. Must exist in the catalog.
`endpoint_type`	Selects a well-known catalog flavor, for example `'s3_tables'` or `'glue'`.
`default_region`	Per-catalog region override. Defaults to your MotherDuck org region.
`read_only`	Attach the catalog as read-only.
`access_delegation_mode`	Controls vended-credential delegation, for example `'none'`.

For the full set of catalog options, see the DuckDB Iceberg REST catalog documentation.

Amazon S3 Tables

For Amazon S3 Tables, authenticate with an S3 secret (SigV4) and set endpoint_type to 's3_tables'. The warehouse is the table bucket ARN, and the endpoint is derived from it.

CREATE SECRET s3_tables_secret IN MOTHERDUCK (
    TYPE S3,
    KEY_ID '<aws_access_key_id>',
    SECRET '<aws_secret_access_key>',
    REGION 'us-east-1'
);

CREATE DATABASE my_s3_tables (
    TYPE ICEBERG,
    endpoint_type 's3_tables',
    warehouse 'arn:aws:s3tables:us-east-1:<account_id>:bucket/<bucket_name>',
    "secret" s3_tables_secret,
    default_schema 'default'
);

Databricks

Databricks Unity Catalog provides an Iceberg REST catalog endpoint at https://<workspace-url>/api/2.1/unity-catalog/iceberg-rest. You can use that endpoint to attach Unity Catalog as an Iceberg catalog in MotherDuck. This can include Unity Catalog Iceberg tables and Delta tables that are configured for Iceberg reads.

CREATE SECRET databricks_uc_secret IN MOTHERDUCK (
    TYPE ICEBERG,
    TOKEN '<databricks_personal_access_token>'
);

CREATE DATABASE databricks_uc (
    TYPE ICEBERG,
    "secret" databricks_uc_secret,
    endpoint 'https://<workspace-url>/api/2.1/unity-catalog/iceberg-rest',
    warehouse '<uc_catalog_name>',
    default_schema '<uc_schema_name>',
    read_only false
);

Delta tables exposed through Unity Catalog's Iceberg REST catalog have three limitations:

They are read-only.
They need to be stored in an external location, they cannot use default storage.
Iceberg reads need to be enabled, which means using IcebergCompatV2 and disabling deletion vectors.

CREATE OR REPLACE TABLE <table_name> (<table_schema>) TBLPROPERTIES
  (
    'delta.columnMapping.mode' = 'name',
    'delta.enableDeletionVectors' = 'false',
    'delta.enableIcebergCompatV2' = 'true',
    'delta.universalFormat.enabledFormats' = 'iceberg'
  );

For a complete overview of setup requirements see the Databricks Iceberg client access documentation.

Reading and writing

A persisted Iceberg catalog supports standard DDL and DML, executed on MotherDuck's compute: creating schemas and tables, inserting data, partitioned writes, MERGE INTO, and ALTER TABLE.

CREATE SCHEMA my_datalake.analytics;

CREATE TABLE my_datalake.analytics.events (
    event_id INTEGER,
    event_type VARCHAR,
    created_at TIMESTAMP
);

INSERT INTO my_datalake.analytics.events
VALUES (1, 'page_view', '2025-01-15 10:30:00');

ALTER TABLE my_datalake.analytics.events
SET PARTITIONED BY (year(created_at));

Refer to the DuckDB Iceberg documentation for the current support matrix for write operations and time travel.

Time travel

Query a historical snapshot of a catalog table with the AT clause, by snapshot ID or timestamp:

-- Query a specific snapshot by ID
SELECT * FROM my_datalake.default.my_table
    AT (VERSION => 1234567890);

-- Query as of a timestamp
SELECT * FROM my_datalake.default.my_table
    AT (TIMESTAMP => TIMESTAMP '2025-01-15 10:30:00');

Limitations

UPDATE, DELETE, and MERGE INTO use merge-on-read semantics and write positional delete files; copy-on-write is not supported. If a table sets write.update.mode or write.delete.mode to anything other than merge-on-read, the operation fails
Iceberg catalogs can't be shared. To give another account access to the same catalog, create the same Iceberg database in that account.
ALTER DATABASE is not supported on Iceberg databases. To change catalog options, drop and recreate the database.
INSERT and UPDATE are not supported on tables that have a sort order.
Table columns are not populated in duckdb_columns(). Run DESCRIBE <table> to see a table's columns.
Reading from REST catalogs is limited to S3, S3 Tables, and GCS storage backends.
Converting an Iceberg catalog to DuckLake with iceberg_to_ducklake is not supported.

For more details, see the DuckDB Iceberg REST catalog documentation.

Scanning individual Iceberg tables

Use iceberg_scan to query individual Iceberg tables directly by path, without attaching a catalog:

SELECT count(*)
FROM iceberg_scan('s3://my-bucket/my-iceberg-table',
    allow_moved_paths = true);

note

To query data in a secure Amazon S3 bucket, you will need to configure your Amazon S3 credentials. If credentials are missing, expired, or lack permission, iceberg_scan fails with No version was provided and no version-hint could be found — check your S3 secret before anything else. Enabling unsafe_enable_version_guessing does not fix a credentials problem.

The allow_moved_paths option is only needed for tables whose files were copied or moved to a different location after they were written (metadata then contains absolute paths that no longer match). Freshly written tables read fine without it.

`iceberg_scan` parameters

Parameter	Type	Default	Description
`allow_moved_paths`	`BOOLEAN`	`false`	Allow scanning Iceberg tables that have been moved or relocated
`metadata_compression_codec`	`VARCHAR`	`''`	Set to `'gzip'` to read gzip-compressed metadata files
`snapshot_from_id`	`UBIGINT`	`NULL`	Query a specific snapshot by ID
`snapshot_from_timestamp`	`TIMESTAMP`	`NULL`	Query the latest snapshot as of a given timestamp
`version`	`VARCHAR`	`'?'`	Explicit version string, hint file path, or `'?'` for auto-detection
`version_name_format`	`VARCHAR`	`'v%s%s.metadata.json,%s%s.metadata.json'`	Custom metadata filename pattern

Time travel with `iceberg_scan`

-- Query a specific snapshot
SELECT *
FROM iceberg_scan('s3://my-bucket/my-iceberg-table',
    allow_moved_paths = true,
    snapshot_from_id = 1234567890);

-- Query as of a timestamp
SELECT *
FROM iceberg_scan('s3://my-bucket/my-iceberg-table',
    allow_moved_paths = true,
    snapshot_from_timestamp = TIMESTAMP '2025-01-15 10:30:00');

Metadata and snapshot functions

Use iceberg_metadata to inspect manifest entries (file paths, formats, record counts):

SELECT *
FROM iceberg_metadata('s3://my-bucket/my-iceberg-table',
    allow_moved_paths = true);

Use iceberg_snapshots to list available snapshots:

SELECT *
FROM iceberg_snapshots('s3://my-bucket/my-iceberg-table');

Example with sample dataset

The sample dataset was relocated after it was written, so allow_moved_paths is required here:

SELECT count(*)
FROM iceberg_scan('s3://us-prd-motherduck-open-datasets/iceberg/lineitem_iceberg',
    allow_moved_paths = true);

Writing individual Iceberg tables

COPY ... TO with FORMAT iceberg writes a query result as a standalone Iceberg table at an object-store path, without a catalog:

COPY (SELECT * FROM my_table)
TO 's3://my-bucket/my-iceberg-table' (FORMAT iceberg);

The result can be read back with iceberg_scan (no allow_moved_paths needed) and by other Iceberg readers.

warning

This write path has important caveats:

Each COPY creates a brand-new table. Writing to a path that already contains an Iceberg table replaces it: the previous snapshot history is lost and the previous data files are left orphaned in the data/ prefix. It is not an append or an Iceberg-transactional overwrite.
PARTITION_BY is not applied. The table is written with an empty partition spec regardless of any PARTITION_BY clause.

For transactional writes with snapshot history, appends, and partitioning, write through an attached Iceberg REST catalog instead.

Set up the Iceberg extension​

Persisted Iceberg catalogs​

Authentication​

Creating the database​

Database options​

Amazon S3 Tables​

Databricks​

Reading and writing​

Time travel​

Limitations​

Scanning individual Iceberg tables​

iceberg_scan parameters​

Time travel with iceberg_scan​

Metadata and snapshot functions​

Example with sample dataset​

Writing individual Iceberg tables​

Set up the Iceberg extension

Persisted Iceberg catalogs

Authentication

Creating the database

Database options

Amazon S3 Tables

Databricks

Reading and writing

Time travel

Limitations

Scanning individual Iceberg tables

`iceberg_scan` parameters

Time travel with `iceberg_scan`

Metadata and snapshot functions

Example with sample dataset

Writing individual Iceberg tables