# Apache Iceberg > Attach an Iceberg REST catalog as a MotherDuck database to read from and write back to Iceberg tables, or scan individual tables by path. MotherDuck supports the Apache Iceberg format through the [DuckDB Iceberg extension](https://duckdb.org/docs/current/core_extensions/iceberg/overview). There are two ways to work with Iceberg in MotherDuck: - **[Persisted Iceberg catalogs](#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](#scanning-individual-iceberg-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. ```sql INSTALL iceberg; LOAD iceberg; ATTACH 'md:'; ``` In Python, install and load the extension before opening the MotherDuck connection: ```python import duckdb duckdb.sql("INSTALL iceberg") duckdb.sql("LOAD iceberg") conn = duckdb.connect("md:") ``` ## Persisted Iceberg catalogs Attach an [Iceberg REST catalog](https://duckdb.org/docs/stable/core_extensions/iceberg/iceberg_rest_catalogs) 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](/concepts/architecture-and-capabilities#dual-execution). :::note Persisted Iceberg catalogs require DuckDB 1.5.2 or later. ::: MotherDuck works with any Iceberg REST catalog endpoint. | Catalog | Read | Write | | :-- | :-- | :-- | | 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)](https://docs.databricks.com/aws/en/iceberg/#access-iceberg-tables-using-external-systems)* | - Write operations use Iceberg's merge-on-read model and are subject to the [Limitations](#limitations) below. - **AWS Glue:** `CREATE TABLE` requires an explicit `location`. See [AWS Glue](#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](#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](#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](#cloudflare-r2-data-catalog) 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. ```sql -- 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`](/sql-reference/motherduck-sql-reference/create-secret#iceberg-secrets) 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: ```sql 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: ```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: ```sql 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](#authentication), 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. For Cloudflare R2, this is `_`, a *mandatory input*. | | `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` | Whether 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. | | `max_table_staleness` | Caches table metadata for the given interval, for example `'1 minute'`, instead of fetching fresh metadata on every query (the default). | For the full set of catalog options, see the [DuckDB Iceberg REST catalog documentation](https://duckdb.org/docs/stable/core_extensions/iceberg/iceberg_rest_catalogs). ### Amazon S3 Tables For [Amazon S3 Tables](https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables.html), 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. ```sql CREATE SECRET s3_tables_secret IN MOTHERDUCK ( TYPE S3, KEY_ID '', SECRET '', REGION 'us-east-1' ); CREATE DATABASE my_s3_tables ( TYPE ICEBERG, endpoint_type 's3_tables', warehouse 'arn:aws:s3tables:us-east-1::bucket/', "secret" s3_tables_secret, default_schema 'default' ); ``` ### AWS Glue For the [AWS Glue Data Catalog](https://docs.aws.amazon.com/glue/latest/dg/connect-glu-iceberg-rest.html), 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`. ```sql CREATE SECRET glue_secret IN MOTHERDUCK ( TYPE S3, KEY_ID '', SECRET '', REGION '' ); CREATE DATABASE my_glue_catalog ( TYPE ICEBERG, endpoint_type 'glue', warehouse '', "secret" glue_secret, default_schema '' ); ``` 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](https://docs.aws.amazon.com/lake-formation/latest/dg/what-is-lake-formation.html), 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: ```bash 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: ```bash aws lakeformation register-resource \ --resource-arn arn:aws:s3::: \ --role-arn arn:aws:iam:::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: ```json { "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](https://docs.aws.amazon.com/lake-formation/latest/dg/tag-based-access-control.html). 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](https://docs.aws.amazon.com/lake-formation/latest/dg/full-table-credential-vending.html) 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 message | Cause | Fix | | :--- | :--- | :--- | | `Insufficient Lake Formation permissions. Verify the data lake settings for account` | Application integration isn't enabled | Enable `AllowFullTableExternalDataAccess` (prerequisite 1) | | `Access is not allowed.` | The S3 location is registered with the service-linked role, which doesn't support credential vending | Re-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 filter | Grant `SELECT` on the whole table with no filters (see [Lake Formation permissions](#lake-formation-permissions)) | | `Insufficient Lake Formation permission(s): Required Describe on ` | The principal has no Lake Formation grant on that table | Grant `DESCRIBE` and `SELECT` if the principal should have access | | `not authorized to perform: s3:GetObject` | The location isn't registered with Lake Formation, so no credentials are vended | Register 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:///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. ```sql CREATE SECRET databricks_uc_secret IN MOTHERDUCK ( TYPE ICEBERG, TOKEN '' ); CREATE DATABASE databricks_uc ( TYPE ICEBERG, "secret" databricks_uc_secret, endpoint 'https:///api/2.1/unity-catalog/iceberg-rest', warehouse '', default_schema '', 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. ```sql CREATE OR REPLACE TABLE () 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](https://docs.databricks.com/aws/en/external-access/iceberg). ### Cloudflare R2 Data Catalog [Cloudflare R2 Data Catalog](https://developers.cloudflare.com/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. ```sql CREATE SECRET r2_iceberg IN MOTHERDUCK ( TYPE ICEBERG, TOKEN '' ); CREATE DATABASE my_r2_catalog ( TYPE ICEBERG, "secret" r2_iceberg, endpoint 'https://catalog.cloudflarestorage.com//', warehouse '_', default_schema '' ); ``` The `endpoint` and `warehouse` are shown in your bucket's R2 Data Catalog settings. The `warehouse` (`_`) field is required; without it, the attach cannot address the catalog. :::note Enable the catalog on the bucket (`npx wrangler r2 bucket catalog enable `) 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](https://developers.cloudflare.com/r2/data-catalog/) 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`. ```sql 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: ```sql CREATE TABLE my_glue_catalog.. ( id BIGINT ) WITH ( 'location' = 's3:////' ); ``` Refer to the [DuckDB Iceberg documentation](https://duckdb.org/docs/stable/core_extensions/iceberg/iceberg_rest_catalogs) 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: ```sql -- 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
` 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](https://duckdb.org/docs/stable/core_extensions/iceberg/iceberg_rest_catalogs). ## Scanning individual Iceberg tables Use `iceberg_scan` to query individual Iceberg tables directly by path, without attaching a catalog: ```sql 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](../../cloud-storage/amazon-s3). 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` ```sql -- 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): ```sql SELECT * FROM iceberg_metadata('s3://my-bucket/my-iceberg-table', allow_moved_paths = true); ``` Use `iceberg_snapshots` to list available snapshots: ```sql 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: ```sql 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: ```sql 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](#persisted-iceberg-catalogs) instead. ::: --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fintegrations%2Ffile-formats%2Fapache-iceberg%2F&page_title=Apache%20Iceberg&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.