Skip to main content

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.

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.

note

Persisted Iceberg catalogs require DuckDB 1.5.2 or later. The catalog is attached, browsed, and queried on MotherDuck's compute, so your client doesn't need the DuckDB Iceberg extension.

MotherDuck works with any Iceberg REST catalog endpoint.

CatalogReadWrite
Amazon S3 Tables✅ Yes✅ Yes
Apache Polaris✅ Yes✅ Yes
AWS Glue✅ Yes✅ Yes
Cloudflare R2✅ Yes✅ Yes
Databricks Unity Catalog✅ Yes✅ Yes (tables must be backed by external storage)
  • Write operations use Iceberg's merge-on-read model and are subject to the Limitations below.
  • AWS Glue: CREATE TABLE requires an explicit location. See AWS Glue for details.
    • AWS Lake Formation: Access is validated for reads. Grants must cover whole tables, so writes through Lake Formation credential vending are not validated. See Lake Formation permissions.
  • Databricks Unity Catalog: Only for tables stored on external locations. Writes apply to Unity Catalog-managed Iceberg tables. Delta tables exposed through the Iceberg REST endpoint are read-only. See Databricks for details.
  • Cloudflare R2 Data Catalog: Table data lives in R2 object storage (S3-compatible). Authenticate with a Cloudflare API token that has R2 Data Catalog permission. See Cloudflare R2 for details.
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 not recommended. 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.

OptionDescription
secretRequired. Name of the MotherDuck Iceberg or S3 secret holding catalog credentials. Quote as "secret".
endpointURL of the Iceberg REST catalog. Required unless the endpoint is set in the secret or derived from endpoint_type.
warehouseCatalog warehouse identifier. For S3 Tables, this is the bucket ARN. For Cloudflare R2, this is <account_id>_<bucket_name>, a mandatory input.
default_schemaRequired. Schema used to resolve unqualified table names. Must exist in the catalog.
endpoint_typeSelects a well-known catalog flavor, for example 's3_tables' or 'glue'.
default_regionPer-catalog region override. Defaults to your MotherDuck org region.
read_onlyAttach the catalog as read-only.
access_delegation_modeWhether to request vended credentials from the catalog. 'vended_credentials' (default) requests short-lived, table-scoped credentials when the catalog supports them; 'none' uses the secret's credentials directly.

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

Changing database options​

Use ALTER DATABASE to update an attached catalog's configuration. MotherDuck reattaches the catalog right away, so the next query uses the new settings:

-- Resolve unqualified table names against a different namespace
ALTER DATABASE my_datalake SET default_schema = 'analytics';

-- Point the database at a rotated secret
ALTER DATABASE my_datalake SET secret = 'my_new_iceberg_secret';

secret, default_schema, default_region, access_delegation_mode, and the catalog behavior toggles can be altered; secret and default_schema can't be cleared once set.

The options that identify the catalog itself - endpoint, warehouse, endpoint_type, and read_only - can't be altered, because changing them points the database at a different catalog: that's a different database, not a reconfigured one. To change one of those, drop the database and create it again. Refer to ALTER DATABASE for the full list.

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'
);

AWS Glue​

For the AWS Glue Data Catalog, authenticate with an S3 secret (SigV4) and set endpoint_type to 'glue'. The warehouse is your AWS account ID, and the endpoint is derived from the secret's REGION. Each Glue database becomes a schema; pass one that exists as default_schema.

CREATE SECRET glue_secret IN MOTHERDUCK (
    TYPE S3,
    KEY_ID '<aws_access_key_id>',
    SECRET '<aws_secret_access_key>',
    REGION '<aws_region>'
);

CREATE DATABASE my_glue_catalog (
    TYPE ICEBERG,
    endpoint_type 'glue',
    warehouse '<aws_account_id>',
    "secret" glue_secret,
    default_schema '<glue_database_name>'
);

If your lake doesn't use AWS Lake Formation, this is the complete setup: the IAM principal in the secret needs the Glue catalog read actions (glue:GetCatalog, glue:GetDatabase, glue:GetDatabases, glue:GetTable, glue:GetTables) plus s3:GetObject on the table locations, and kms:Decrypt if the bucket uses SSE-KMS. The rest of this section covers Lake Formation–governed lakes.

Lake Formation prerequisites​

When your S3 locations are registered with AWS Lake Formation, data access is handled by Lake Formation's credential vending: at query time, AWS issues short-lived, table-scoped S3 credentials to MotherDuck as an external engine. MotherDuck does not vend credentials itself; it presents the secret's IAM principal, and Lake Formation decides what it can read. For tables in registered locations, that principal needs no S3 permissions: access is granted per table by your existing Lake Formation permissions, including tag-based access control.

Four one-time settings enable credential vending for external engines:

  1. Allow full table access for external engines. In the Lake Formation console under Administration → Application integration settings, enable Allow external engines to access data in Amazon S3 locations with full table access. From the CLI, put-data-lake-settings replaces the entire settings object, so retrieve the current settings first:

    aws lakeformation get-data-lake-settings --query DataLakeSettings > settings.json
    # add "AllowFullTableExternalDataAccess": true to settings.json
    aws lakeformation put-data-lake-settings --data-lake-settings file://settings.json

  2. Register the S3 location with a custom IAM role. Credential vending doesn't work for locations registered with the service-linked role. Register (or re-register) the location with a role that Lake Formation can assume and that has S3 access to the bucket, plus kms:Decrypt if the bucket uses SSE-KMS:

    aws lakeformation register-resource \
      --resource-arn arn:aws:s3:::<bucket_name> \
      --role-arn arn:aws:iam::<aws_account_id>:role/<registration_role>

  3. Create the IAM principal for MotherDuck. Its policy contains only the Glue catalog read actions listed above and lakeformation:GetDataAccess. Leave S3 permissions out — Lake Formation vends data access per query:

    {
      "Version": "2012-10-17",
      "Statement": [
        {
          "Effect": "Allow",
          "Action": [
            "glue:GetCatalog",
            "glue:GetDatabase",
            "glue:GetDatabases",
            "glue:GetTable",
            "glue:GetTables"
          ],
          "Resource": "*"
        },
        {
          "Effect": "Allow",
          "Action": ["lakeformation:GetDataAccess"],
          "Resource": "*"
        }
      ]
    }

  4. Remove the IAMAllowedPrincipals defaults. By default, Lake Formation grants IAMAllowedPrincipals — every IAM principal in the account — on new databases and tables. That default satisfies Lake Formation for any principal that can request vended credentials, bypassing your per-principal grants. In the console under Data Catalog settings, clear the Use only IAM access control defaults for new databases and tables, and revoke existing IAMAllowedPrincipals grants on the databases and tables you serve.

Lake Formation permissions​

In Lake Formation, grant the principal DESCRIBE on the Glue database and SELECT on the tables it should read, either directly or through LF-tags. These grants are managed entirely on the AWS side; MotherDuck presents the principal's identity and Lake Formation decides what it can access. Read-only grants are sufficient. With the IAMAllowedPrincipals defaults removed (prerequisite 4), tables the principal isn't granted don't appear in the attached catalog.

Lake Formation grants must apply to whole tables: Lake Formation can't vend credentials for grants that carry column-level permissions or row and cell filters, so queries against tables with such grants fail instead of returning unfiltered data. This AWS constraint applies to all external engines. To serve filtered data, grant access to a pre-filtered table or view instead.

Troubleshooting Lake Formation errors​

Error reference: Lake Formation and Glue REST errors
Error messageCauseFix
Insufficient Lake Formation permissions. Verify the data lake settings for accountApplication integration isn't enabledEnable AllowFullTableExternalDataAccess (prerequisite 1)
Access is not allowed.The S3 location is registered with the service-linked role, which doesn't support credential vendingRe-register the location with a custom role (prerequisite 2)
FULL SELECT or SUPER privileges required on the table.The principal's SELECT grant is missing, limited to specific columns, or has a row filterGrant SELECT on the whole table with no filters (see Lake Formation permissions)
Insufficient Lake Formation permission(s): Required Describe on <table>The principal has no Lake Formation grant on that tableGrant DESCRIBE and SELECT if the principal should have access
not authorized to perform: s3:GetObjectThe location isn't registered with Lake Formation, so no credentials are vendedRegister the location (prerequisite 2), or for lakes without Lake Formation, grant the principal s3:GetObject

Newly created IAM users, roles, and access keys can take a minute to propagate. If you get a 403 Forbidden right after creating one, retry before changing any settings.

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. Databricks only supports credential vending for tables stored on external locations.

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 two limitations:

  • They are read-only.
  • 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.

Troubleshooting Databricks Iceberg reads​

Error reference: Databricks Unity Catalog Iceberg reads
Error messageCauseFix
HTTP 404 / NoSuchKey naming a specific .parquet fileUniForm Iceberg metadata fell behind the Delta log. Attach and listing can still succeed, and a Delta client may still read the table, because those paths use catalog metadata or the Delta log — MotherDuck reads the UniForm Iceberg snapshot through the Iceberg REST endpoint. OPTIMIZE or VACUUM can delete data files that a stale snapshot still references.In Databricks, run MSCK REPAIR TABLE <catalog>.<schema>.<table> SYNC METADATA and retry the query. Re-attaching the catalog in MotherDuck does not fix this — Databricks must regenerate the Iceberg metadata.
Permission error: Missing or invalid credentialsThe table is on Unity Catalog managed storage (Databricks vends storage credentials only for external locations), or the secret is wrong. The error points at your token even when the cause is the storage location.Move the table to an external location. Check the table's storage location before rotating your token.

Cloudflare R2 Data Catalog​

Cloudflare R2 Data Catalog exposes an Iceberg REST catalog on top of an R2 bucket. The table data is stored in R2 object storage, which is S3-compatible, and reads and writes run on MotherDuck's compute.

Authenticate with a Cloudflare API token that has R2 Data Catalog permission (for example an Admin Read & Write R2 API token), stored in a TYPE ICEBERG secret as a bearer TOKEN. An R2 object-only token, or an S3 access key and secret, is not sufficient: the catalog rejects it with 401 Unauthorized (wrong token type) or 403 Forbidden (missing Data Catalog permission).

R2 supports credential vending, so with the default access_delegation_mode the same catalog token also authorizes reading and writing the underlying data files. You do not need a separate S3 secret.

CREATE SECRET r2_iceberg IN MOTHERDUCK (
    TYPE ICEBERG,
    TOKEN '<cloudflare_r2_api_token>'
);

CREATE DATABASE my_r2_catalog (
    TYPE ICEBERG,
    "secret" r2_iceberg,
    endpoint  'https://catalog.cloudflarestorage.com/<account_id>/<bucket_name>',
    warehouse '<account_id>_<bucket_name>',
    default_schema '<namespace>'
);

The endpoint and warehouse are shown in your bucket's R2 Data Catalog settings. The warehouse (<account_id>_<bucket_name>) field is required; without it, the attach cannot address the catalog.

note

Enable the catalog on the bucket (npx wrangler r2 bucket catalog enable <bucket_name>) and make sure it contains at least one namespace before attaching. default_schema must reference a namespace that already exists, and a brand-new R2 catalog is empty. Create the first namespace with PyIceberg or the catalog REST API before running CREATE DATABASE. Once attached, you can create tables within existing namespaces from MotherDuck.

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));

On AWS Glue, CREATE TABLE requires an explicit location, because Glue doesn't assign table locations:

CREATE TABLE my_glue_catalog.<glue_database_name>.<table_name> (
    id BIGINT
) WITH (
    'location' = 's3://<bucket_name>/<path>/<table_name>'
);

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

warning

Never modify Parquet data files or Iceberg metadata files by hand after they've been written. Iceberg treats these files as immutable, and MotherDuck relies on that: snapshots, manifests, and statistics all assume the underlying files never change. Editing, overwriting, or replacing a file in place breaks that assumption and leads to data corruption and incorrect query results.

Writing to the same table from multiple Iceberg writers is supported - the catalog coordinates those writes into new immutable files and snapshots. What's unsafe is mutating a file that has already been written.

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 can't change the options that identify the catalog (endpoint, warehouse, endpoint_type, and read_only). To change one of those, drop and recreate the database. See Changing database options.
  • 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-compatible object storage (including Cloudflare R2), 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​

Unlike a persisted catalog, iceberg_scan and COPY ... TO ... (FORMAT iceberg) are bound by your DuckDB client, so the client needs the Iceberg extension. DuckDB autoloads it on first use. If your environment can't reach extensions.duckdb.org, or you've turned off autoload_known_extensions, install and load it once per environment:

INSTALL iceberg;
LOAD iceberg;

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​

ParameterTypeDefaultDescription
allow_moved_pathsBOOLEANfalseAllow scanning Iceberg tables that have been moved or relocated
metadata_compression_codecVARCHAR''Set to 'gzip' to read gzip-compressed metadata files
snapshot_from_idUBIGINTNULLQuery a specific snapshot by ID
snapshot_from_timestampTIMESTAMPNULLQuery the latest snapshot as of a given timestamp
versionVARCHAR'?'Explicit version string, hint file path, or '?' for auto-detection
version_name_formatVARCHAR'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.