Skip to main content

Apache Iceberg

MotherDuck supports the Apache Iceberg format through the DuckDB Iceberg extension. 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 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:

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

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:

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

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.

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.

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.

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​

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