# 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.

## 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. 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.

| 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)* |
| Snowflake Horizon | ✅ Yes | ✅ Yes *[(tables must be backed by external storage)](https://docs.snowflake.com/en/user-guide/tables-iceberg-access-using-external-query-engine-snowflake-horizon#configure-read-access-to-your-iceberg-tables)|

- 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 the AWS Glue tab under [Catalog-specific setup](#catalog-specific-setup) 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 the Databricks tab under [Catalog-specific setup](#catalog-specific-setup) 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 the Cloudflare R2 tab under [Catalog-specific setup](#catalog-specific-setup) for details.
- **Snowflake Horizon:** Only for tables stored on external volumes. See the Snowflake tab under [Catalog-specific setup](#catalog-specific-setup) 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;

-- Inspect a table's columns
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`                 | Required. 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 `<account_id>_<bucket_name>`, 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. |

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

### Changing database options

Use [`ALTER DATABASE`](/sql-reference/motherduck-sql-reference/alter-database#iceberg-databases) to update an attached catalog's configuration. MotherDuck reattaches the catalog right away, so the next query uses the new settings:

```sql
-- 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`](/sql-reference/motherduck-sql-reference/alter-database#iceberg-databases) for the full list.

### Catalog-specific setup \{#catalog-specific-setup}

### 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 '<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](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 '<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](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:::<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:

   ```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

<details>
<summary>Error reference: Lake Formation and Glue REST errors</summary>

| 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 <table>` | 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.

</details>

### 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.

```sql
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.

```sql
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](https://docs.databricks.com/aws/en/external-access/iceberg).

#### Troubleshooting Databricks Iceberg reads

<details>
<summary>Error reference: Databricks Unity Catalog Iceberg reads</summary>

| Error message | Cause | Fix |
| :--- | :--- | :--- |
| `HTTP 404` / `NoSuchKey` naming a specific `.parquet` file | UniForm 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`](https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-repair-table) 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 credentials` | The 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. |

</details>

### Snowflake

```sql
CREATE SECRET my_catalog_secret IN MOTHERDUCK (
    TYPE ICEBERG,
    CLIENT_ID '<client_id>',
    CLIENT_SECRET '<client_secret>',
    OAUTH2_SERVER_URI '<oauth_token_endpoint>',
    OAUTH2_SCOPE 'session:role:<supported_role>'
);

CREATE DATABASE my_lakehouse (
    TYPE ICEBERG,
    "secret" my_catalog_secret,
    endpoint '<catalog_endpoint>',
    warehouse '<warehouse_identifier>',
    default_schema '<schema_name>',
    disable_multi_table_commit true,
    stage_create_tables false,
    skip_create_table_metadata_updates true,
    remove_files_on_delete false
);
```

- Use the DuckDB CLI to run the queries above if you run into Cross-Origin Resource Sharing (CORS) issues in the MotherDuck UI
- The `disable_multi_table_commit true` option is required for compatibility to make writes like `INSERT`, `DELETE`, `MERGE INTO` work in DuckDB versions 1.5.4 and higher.
- An [external volume](https://docs.snowflake.com/en/user-guide/tables-iceberg-configure-external-volume) must be configured before creating Iceberg tables through the Horizon endpoint. The external volume must be set as a default in Snowflake at the account, database, or schema level.
- Creating Iceberg tables requires [USAGE privilege](https://docs.snowflake.com/en/user-guide/tables-iceberg-access-using-external-query-engine-snowflake-horizon#configure-write-access-to-your-iceberg-tables) on the external volume granted to the role.
- `stage_create_tables false` and `skip_create_table_metadata_updates true` both need to be set together for Iceberg table creation to work.
- `remove_files_on_delete false` is set because Snowflake controls the lifecycle of the underlying data and metadata files for Iceberg tables that use it as the catalog.

### 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 '<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](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, `UPDATE`, `DELETE`, `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.<glue_database_name>.<table_name> (
    id BIGINT
) WITH (
    'location' = 's3://<bucket_name>/<path>/<table_name>'
);
```

`UPDATE`, `DELETE`, and `MERGE INTO` work on both partitioned and unpartitioned tables. Rather than rewriting data files, they write a small delete file that marks individual rows as removed, and readers apply those marks at query time. Iceberg calls this merge-on-read.

:::note Two cases where a write is refused

Most tables are unaffected by both. You only hit them on tables created by another engine, because MotherDuck can't produce either kind.

- **Tables with a sort order.** `INSERT` and `UPDATE` fail. `DELETE` still works. See [Sorted tables](#sorted-tables).
- **Tables set to copy-on-write.** `DELETE` fails. See [Copy-on-write tables](#copy-on-write-tables).

:::

Refer to the [DuckDB Iceberg writing documentation](https://duckdb.org/docs/current/core_extensions/iceberg/writing_to_iceberg) for the full set of supported DDL and DML statements.

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

Use the per-query `AT` clause for historical queries. `SNAPSHOT_VERSION` and `SNAPSHOT_TIME` do not pin a native `ATTACH 'md:...'` connection.

### Sorted tables

An Iceberg table can declare that its rows are stored in a particular order. Writing to one fails:

```console
Not implemented Error: INSERT into a sorted iceberg table is not supported yet.
```

MotherDuck can't write rows in a declared order, so it refuses instead of writing them in the wrong order and leaving the table claiming otherwise. `DELETE` is unaffected and works normally.

You won't create such a table from MotherDuck. `SORTED BY` on `CREATE TABLE` and `ALTER TABLE ... SET SORTED BY` are both rejected. You only meet one if another engine, such as Spark or Snowflake, created it.

#### Writing to one anyway

Some catalogs re-sort the data for you when they compact in the background. Amazon S3 Tables does, and picks a sorting compaction strategy automatically when a table declares a sort order. If yours does too, you can write unsorted and let compaction fix the layout. Turn off the check with `unsafe_iceberg_ignore_sort_order`:

```sql
SET unsafe_iceberg_ignore_sort_order = true;
INSERT INTO my_datalake.default.my_sorted_table VALUES (3, 'c'), (1, 'a');
```

:::warning

Between your write and the next compaction, the table declares a sort order that its files no longer follow. A query engine that trusts the declaration to skip files can return wrong results in that window.

If your catalog doesn't compact automatically, nothing fixes the layout and the table stays that way until you rewrite it. Check before you set this.

:::

### Copy-on-write tables

Iceberg tables can ask for row changes to be handled by rewriting whole data files, called copy-on-write, instead of marking rows in a delete file. MotherDuck only does the second, whatever `write.delete.mode` and `write.update.mode` ask for. A `DELETE` against a table set to copy-on-write fails:

```console
Not implemented Error: DuckDB-Iceberg only supports merge-on-read
for updates/deletes. Table Property 'write.delete.mode' is set
to 'copy-on-write' for table my_table
```

Set both properties to `merge-on-read` so the table describes what MotherDuck actually writes. Inspect and change them with the Iceberg property functions:

```sql
-- See the current settings
SELECT * FROM iceberg_table_properties(my_datalake.default.my_table);

-- Switch the table to merge-on-read
CALL set_iceberg_table_properties(
    my_datalake.default.my_table,
    MAP{
        'write.delete.mode': 'merge-on-read',
        'write.update.mode': 'merge-on-read'
    }
);
```

Changing the properties affects how future writes are recorded. It doesn't rewrite anything that already exists.

### What a delete removes

A `DELETE` hides rows. It doesn't erase them.

The delete file records "in this data file, skip row 3". The data file itself is never touched, so the deleted row is still on disk in full, and still readable through [time travel](#time-travel). An `UPDATE` behaves the same way: it marks the old row and appends the new one, so every previous version of a row stays on disk too.

Two more things have to happen before the bytes are actually gone, and MotherDuck does neither of them:

1. **Compaction** rewrites the data files without the deleted rows.
2. **Snapshot expiry and unreferenced-file cleanup** delete the files that are left behind.

Both are your catalog's job. Amazon S3 Tables runs them automatically: compaction applies row-level deletes as it goes, and snapshot management expires snapshots older than 120 hours by default, keeping a minimum of one. Other catalogs differ, and self-hosted ones do nothing until you run maintenance yourself.

:::warning

If you are deleting rows to remove the data rather than to hide it, a successful `DELETE` is not the end of the job. Check your catalog's compaction and snapshot-expiry settings, because some leave snapshot expiry off by default. Until it runs, the data is still there and still queryable at an earlier snapshot.

Switching the table to copy-on-write doesn't shortcut this. It keeps the deleted row out of the new files, but the old file survives as long as a snapshot still references it.

:::

### Limitations

- `UPDATE`, `DELETE`, and `MERGE INTO` always write delete files rather than rewriting data files. Copy-on-write is not supported, whatever `write.delete.mode` and `write.update.mode` ask for, and a `DELETE` against a copy-on-write table fails outright. See [Copy-on-write tables](#copy-on-write-tables).
- `INSERT` and `UPDATE` fail on a table that declares a sort order. `DELETE` works. See [Sorted tables](#sorted-tables).
- MotherDuck can't create a sorted table. `SORTED BY` on `CREATE TABLE` and `ALTER TABLE ... SET SORTED BY` are both rejected.
- No maintenance functions. MotherDuck can't compact a table, expire snapshots, or clean up unreferenced files. See [What a delete removes](#what-a-delete-removes).
- 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](#changing-database-options).
- 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 on write support, see the [DuckDB Iceberg writing documentation](https://duckdb.org/docs/current/core_extensions/iceberg/writing_to_iceberg).

## 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:

```sql
INSTALL iceberg;
LOAD iceberg;
```

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=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.