---
sidebar_position: 1
title: Apache Iceberg
description: Query Apache Iceberg data and work with Iceberg REST catalogs from MotherDuck sessions using the Iceberg DuckDB extension.
---

MotherDuck supports the [Apache Iceberg format](https://iceberg.apache.org/) through the [DuckDB Iceberg extension](https://duckdb.org/docs/stable/core_extensions/iceberg/overview). The extension is loaded automatically when Iceberg functions or catalogs are used in your current MotherDuck session.

## Iceberg REST catalogs

You can attach an [Iceberg REST catalog](https://duckdb.org/docs/stable/core_extensions/iceberg/iceberg_rest_catalogs) in your current MotherDuck session and query it with standard SQL. The attached catalog is local to the current client session: it does not become a persisted MotherDuck database or workspace attachment.

### Authentication

Create a secret with your catalog credentials:

```sql
-- OAuth2
CREATE SECRET my_iceberg_secret (
    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 (
    TYPE iceberg,
    TOKEN 'my_bearer_token'
);
```

### Attaching a catalog

```sql
ATTACH 'my_warehouse' AS my_iceberg (
    TYPE iceberg,
    SECRET my_iceberg_secret,
    ENDPOINT 'https://my-catalog.example.com'
);
```

:::note

This `ATTACH` adds the Iceberg catalog to your current client session only. Re-attach it in each new session. Use `DETACH my_iceberg;` to remove it from the current session.

:::

Once attached, browse and query tables using standard SQL:

```sql
-- List schemas
SHOW SCHEMAS IN my_iceberg;

-- Query a table
SELECT * FROM my_iceberg.my_schema.my_table;
```

### Session-scoped write operations

Within the attached session, DuckDB's Iceberg REST catalog support includes operations such as creating schemas and tables and inserting data:

```sql
CREATE SCHEMA my_iceberg.analytics;

CREATE TABLE my_iceberg.analytics.events (
    event_id INT,
    event_type VARCHAR,
    created_at TIMESTAMP
);

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

### Additional DuckDB Iceberg catalog features

DuckDB documents additional Iceberg REST catalog capabilities such as time travel for attached catalogs. Refer to the upstream documentation for the current support matrix and syntax details.

```sql
SELECT * FROM my_iceberg.my_schema.my_table
    AT (VERSION => 1234567890);

SELECT * FROM my_iceberg.my_schema.my_table
    AT (TIMESTAMP => TIMESTAMP '2025-01-15 10:30:00');
```

### Limitations

- Attached Iceberg REST catalogs are local to the current client session and are not persisted as MotherDuck workspace attachments
- `UPDATE` and `DELETE` only work on unpartitioned, unsorted tables
- Only merge-on-read semantics (no copy-on-write)
- `MERGE INTO` and `ALTER TABLE` are not supported
- Reading from REST catalogs is limited to S3, S3 Tables, and GCS storage backends

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

:::

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

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