Breadcrumbs

ICEBERG_TABLE

Supported DBMS products: Snowflake

Limitations:

  • Catalog needs to be Snowflake managed

  • ADE Logical datatypes not supported:

    • ARRAY

    • GEOGRAPHY

    • GEOMETRY

    • OBJECT

    • TIMESTAMP_TZ

    • TIMESTAMP_WITH_SCALE

    • VARIANT


Good to know

Iceberg table in Snowflake requires the EXTERNAL_VOLUME to be either defined as default in Snowflake on database or schema level or it needs to be set by using STORAGE.ICEBERG_EXTERNAL_LOCATION physical option.

Only Snowflake is supported Catalog option and therefore it can not be modified at the moment.

Base location for the Iceberg table will be set to value:

<entity_schema>_<entity_name>

Which will result by Snowflake in external volume to a folder name with a random string:

<entity_schema>_<entity_name>.<random_string>


The Iceberg table serialization policy will default to OPTIMIZED, but as it might not be suitable for external readers, it might be better to set STORAGE.ICEBERG_SERIALIZATION_POLICY physical option as COMPATIBLE.

Clustering can be enabled by adding a SORT_KEY for the ICEBERG_TABLE physical type.

Partitioning can be enabled by adding a PARTITION_KEY and optionally STORAGE.PARTITION_SCHEME physical option to achieve the wanted partitioning.

Partition limitations

Snowflake's Iceberg implementation doesn't allow multiple partition expressions on the same source column, even with different temporal transforms.

According to the Apache Iceberg specification that Snowflake follows, partition transforms like YEAR, MONTH, and DAY are hierarchical when applied to the same column.

When partitioning over single timestamp column, choose to the most granular transform you require.

Clustering and partitioning can not be defined on a same Iceberg table

this causes following error:

“Partition by and Cluster by cannot be specified together for Iceberg tables.”

Frequent same Iceberg table creation and deletion can lead to a situation that the Iceberg table will not be visible in Snowflake, yet when trying to create it the error message indicates:

SQL compilation error: Object '<iceberg_schema>.<iceberg_table>' already exists.

This is assumably due to the Snowflake metadata synching not being immediate, but is being executed scheduled in the background.

If this happens, one can add the “Force recreate”-flag on the entity and after this the entity creation DDL will be altered to:

CREATE OR REPLACE ICEBERG TABLE <iceberg_schema>.<iceberg_table>...

It is not wise to keep this flag active, so remove it once the situation has been corrected.

Time travel, search optimization and change tracking are supported also for ICEBERG_TABLE.

See also