Externally managed Dynamic Apache Iceberg™ tables (private preview)

An externally managed dynamic Iceberg table (DEIT) is a dynamic table whose underlying storage resides in an external Iceberg catalog. You create and manage DEITs using the same CREATE DYNAMIC ICEBERG TABLE syntax as Snowflake-managed dynamic Iceberg tables (this topic uses the abbreviation DEIT to distinguish the externally managed variant). DEITs bring the pipeline refresh scheduling of dynamic tables to externally managed Iceberg storage inside a catalog-linked database (CLD).

This feature is available only to selected accounts. Contact your Snowflake account team to request enrollment and report feedback.

Overview

Snowflake supports dynamic tables on Snowflake-managed Iceberg storage through CREATE DYNAMIC ICEBERG TABLE. DEITs expand this functionality: when you run CREATE DYNAMIC ICEBERG TABLE inside a catalog-linked database, Snowflake creates a dynamic table where the resulting Iceberg data and metadata are written to the external catalog rather than to Snowflake-managed storage.

DEITs support the same SQL syntax as Snowflake-managed dynamic Iceberg tables. The key distinction is the execution context: DEITs can only be created inside a catalog-linked database. Mixed-mode scenarios (outside a CLD) are not supported.

Prerequisites

Before you can create a DEIT, you must:

Create an externally managed dynamic Iceberg table

Use the standard CREATE DYNAMIC ICEBERG TABLE syntax inside a CLD schema. When you run this command inside a CLD schema, Snowflake creates the table as an externally managed dynamic Iceberg table.

CREATE [ OR REPLACE ] DYNAMIC ICEBERG TABLE <name> (
  <col_name> <col_type> [, ...]
)
TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM }
WAREHOUSE = <warehouse_name>
[ BASE_LOCATION = '<optional_directory_for_table_files>' ]
[ TARGET_FILE_SIZE = '{ AUTO | 16MB | 32MB | 64MB | 128MB }' ]
[ PARTITION BY ( <partitionExpression> [, <partitionExpression> , ...] ) ]
[ PATH_LAYOUT = { FLAT | HIERARCHICAL } ]
[ ICEBERG_VERSION = <integer> ]
[ REFRESH_MODE = { AUTO | FULL | INCREMENTAL | ADAPTIVE | CUSTOM_INCREMENTAL } ]
[ FROZEN WHERE ( <expr> ) ]
[ INITIALIZE = ON_SCHEDULE ]
[ COMMENT = '<string_literal>' ]
[ [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , <col_name> ... ] ) ]
[ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
[ REQUIRE USER ]
[ EXECUTE AS USER <user_name>
    [ USE SECONDARY ROLES { ALL | NONE | <role> [ , ... ] } ] ]
AS <query>

Example

The following example creates a DEIT inside a CLD named my_linked_db:

USE DATABASE my_linked_db;
USE SCHEMA my_schema;

CREATE OR REPLACE DYNAMIC ICEBERG TABLE order_summary (
  customer_id   INT,
  total_orders  INT,
  total_spent   DECIMAL(12,2)
)
TARGET_LAG = '1 hour'
WAREHOUSE = my_warehouse
BASE_LOCATION = 'order_summary/'
REFRESH_MODE = ADAPTIVE
AS
  SELECT
    customer_id,
    COUNT(*)             AS total_orders,
    SUM(order_amount)    AS total_spent
  FROM raw_orders
  GROUP BY customer_id;

Modify an externally managed dynamic Iceberg table

Because DEITs combine dynamic table and Iceberg table semantics, use the appropriate ALTER command depending on which property you are changing:

Property typeCommand to useSupported properties
Dynamic table propertiesALTER DYNAMIC TABLETARGET_LAG, WAREHOUSE, REFRESH_MODE, and others
Iceberg table propertiesALTER ICEBERG TABLEPATH_LAYOUT, TARGET_FILE_SIZE

Example — update TARGET_LAG:

ALTER DYNAMIC TABLE order_summary SET TARGET_LAG = '30 minutes';

Example — update PATH_LAYOUT:

ALTER ICEBERG TABLE order_summary SET PATH_LAYOUT = HIERARCHICAL;

DEIT-specific behavior

External writes cause suspension

DEITs do not support external writes to the Iceberg catalog. If an external engine writes to the underlying Iceberg table while a DEIT is active, Snowflake detects the change during the next refresh and suspends the DEIT with the reason EXTERNAL_WRITE_ON_DYNAMIC_EXTERNAL_MANAGED_ICEBERG.

A suspended DEIT cannot be resumed. You must drop and re-create it:

CREATE OR REPLACE DYNAMIC ICEBERG TABLE order_summary (...)
TARGET_LAG = '1 hour'
WAREHOUSE = my_warehouse
AS <query>;

Iceberg manual refresh and Iceberg AUTO_REFRESH are not supported

Because DEITs do not support external writes to the Iceberg catalog, Iceberg-level manual refresh and AUTO_REFRESH are not supported. Refreshes follow the dynamic table refresh schedule.

Limitations

LimitationDetails
CLD onlyCreation outside a catalog-linked database is not supported.
INITIALIZE = ON_CREATE not supportedUse INITIALIZE = ON_SCHEDULE (the default). The table is populated on the initial refresh.
CREATE OR ALTER not supportedCREATE or ALTER DYNAMIC ICEBERG TABLE … is not supported for DEITs.
Service failure during creation may leave artifactsIf a service failure occurs during CREATE DYNAMIC ICEBERG TABLE, the external catalog may contain leftover artifacts. Use CREATE OR REPLACE when re-creating the table. If the CLD’s automatic table discovery picks up the leftover artifact before you can re-create, it appears as a plain externally managed Iceberg table; drop that object before running CREATE DYNAMIC ICEBERG TABLE again.
Multiple CLDs on the same external catalogIf two CLDs target the same external catalog and you create a DEIT in one, the other CLD detects the table as a writable unmanaged Iceberg table. Writing to that table from the second CLD can corrupt the DEIT. Avoid this configuration.

Limitations and expectations for this private preview

  • This topic is not in the public navigation. Use the direct link provided by your Snowflake account team.
  • Feature behavior, SQL syntax, and parameter names can change during the preview.
  • Preview features are not committed to a specific region, cloud, edition, or GA date.
  • Non-production environments are recommended unless your organization has accepted the risk of preview features.