Create a dynamic Apache Iceberg™ table

A dynamic Iceberg table stores its output in Apache Iceberg format on Snowflake-managed external storage. It supports the same refresh modes and scheduling as a regular dynamic table, but external engines such as Spark and Trino can also read the data directly.

For the full syntax reference, see CREATE DYNAMIC ICEBERG TABLE.

Prerequisites

Before you create a dynamic Iceberg table, set up an external volume. The external volume defines the cloud storage location where Snowflake writes Iceberg data and metadata files. For setup instructions, see Configure an external volume.

Create a dynamic Iceberg table with Snowflake Managed Storage

The CREATE statement requires three Iceberg-specific parameters: EXTERNAL_VOLUME, CATALOG, and BASE_LOCATION. This example creates a dynamic Iceberg table that reads from the raw_orders base table:

CREATE OR REPLACE DYNAMIC ICEBERG TABLE dt_orders_iceberg
    TARGET_LAG = '10 minutes'
    WAREHOUSE = transform_wh
    EXTERNAL_VOLUME = 'my_external_volume'
    CATALOG = 'SNOWFLAKE'
    BASE_LOCATION = 'dt_orders_iceberg'
    REFRESH_MODE = INCREMENTAL
AS
    SELECT
        order_id,
        customer_id,
        order_date,
        TRIM(UPPER(product_name)) AS product_name,
        quantity,
        unit_price,
        quantity * unit_price AS line_total,
        order_status
    FROM raw_orders
    WHERE order_status != 'returned';

If Snowflake storage for Iceberg tables is enabled for your account, you can omit EXTERNAL_VOLUME, CATALOG, and BASE_LOCATION:

Note

Snowflake storage for Iceberg tables is in public preview and is available only on AWS and Azure. It is not available in government regions.

-- Same definition as above, but without explicit storage parameters
CREATE OR REPLACE DYNAMIC ICEBERG TABLE dt_orders_iceberg_managed
    TARGET_LAG = '10 minutes'
    WAREHOUSE = transform_wh
    -- No EXTERNAL_VOLUME, CATALOG, or BASE_LOCATION needed
    REFRESH_MODE = INCREMENTAL
AS
    SELECT ... FROM raw_orders ...;  -- same columns as explicit storage example above

For full parameter details including PARTITION_BY, TARGET_FILE_SIZE, and PATH_LAYOUT, see CREATE DYNAMIC ICEBERG TABLE.

Verify the dynamic Iceberg table

Confirm the table exists and check the is_iceberg flag:

SHOW DYNAMIC TABLES LIKE 'dt_orders_iceberg';
+--------------------+-----+--------------+------+------------------+------------+
| name               | ... | refresh_mode | rows | scheduling_state | is_iceberg |
|--------------------+-----+--------------+------+------------------+------------+
| DT_ORDERS_ICEBERG | ... | INCREMENTAL  |    4 | RUNNING          | true       |
+--------------------+-----+--------------+------+------------------+------------+

The is_iceberg column reads true for dynamic Iceberg tables and false for regular dynamic tables.

Grant access with future grants

Future grants for dynamic Iceberg tables use ICEBERG TABLES, not DYNAMIC TABLES. The following syntax grants privileges on any dynamic Iceberg tables created in the schema:

GRANT <privilege> ON FUTURE ICEBERG TABLES IN SCHEMA mydb.myschema TO ROLE transform_role;

Using ON FUTURE DYNAMIC TABLES does not apply to dynamic Iceberg tables:

-- This does NOT grant access to dynamic Iceberg tables:
GRANT <privilege> ON FUTURE DYNAMIC TABLES IN SCHEMA mydb.myschema TO ROLE transform_role;

Limitations

Dynamic Iceberg tables have additional limitations:

LimitationDetail
IF NOT EXISTS not supportedThe IF NOT EXISTS clause is not supported. Attempting to use it results in a syntax error. Use CREATE OR REPLACE instead.
No ALTER DYNAMIC ICEBERG TABLEALTER DYNAMIC ICEBERG TABLE does not exist as a command. Use ALTER DYNAMIC TABLE for refresh properties and ALTER ICEBERG TABLE for storage properties.
Cloning not supportedYou can’t clone a dynamic Iceberg table. Cloning a database or schema that contains one skips the table.
SNOWFLAKE catalog onlyThe CATALOG parameter must be 'SNOWFLAKE'. External catalogs (Snowflake Open Catalog, AWS Glue) are not supported.
Backup exclusionDynamic Iceberg tables are not included in database or schema backups.
Replication not supportedDynamic Iceberg tables can’t be replicated. Replication of databases or schemas containing them skips these tables.
Create-or-Alter not supportedThe CREATE OR ALTER syntax is not supported for dynamic Iceberg tables. Use CREATE OR REPLACE instead.
Backfill not supportedUsing backfill on dynamic Iceberg tables is not supported.

Dynamic Iceberg tables support the same data types as regular Iceberg tables. For the full mapping, see Supported data types.

What’s next