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

Physical options related to ICEBERG_TABLE

• STORAGE.ICEBERG_POINTER_FILE_STAGE

• STORAGE.ICEBERG_EXTERNAL_LOCATION

• STORAGE.ICEBERG_SERIALIZATION_POLICY

• STORAGE.TIME_TRAVEL_RETENTION_TIME_IN_DAYS

• STORAGE.AUTO_CLUSTERING_SUSPEND

• STORAGE.SEARCH_OPTIMIZATION

• STORAGE.SEARCH_OPTIMIZATION_OPTIONS

• STORAGE.CHANGE_TRACKING

• STORAGE.PARTITION_SCHEME

Keys related to ICEBERG_TABLE

• PARTITION_KEY

• SORT_KEY

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

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

See also

• https://docs.snowflake.com/en/sql-reference/sql/create-iceberg-table-snowflake