Apache Iceberg™ tables: Support for Apache Iceberg™ v3 (Preview)¶

Data types¶

The following v3 data types are supported in the public preview:

• geography

• geometry

• nanosecond

• variant

For more information, see Iceberg v3 data types.

Default values¶

See Default values.

Deletion vectors¶

See Deletion vectors.

Row lineage¶

See Row lineage.

Usage notes¶

• To modify the ICEBERG_VERSION_DEFAULT parameter at the account level, you must be an account administrator; that is, you must be a user with the ACCOUNTADMIN role.

• To modify the ICEBERG_VERSION_DEFAULT parameter at the database or schema level, the role used to perform the operation must have the OWNERSHIP privilege on the respective database or schema.

Examples¶

Specify that new Iceberg tables in the my_db database should be created using v3:

ALTER DATABASE my_db SET ICEBERG_VERSION_DEFAULT=3;

Create a new externally managed Iceberg table with v3. The column definitions included with the command indicate that a new table will be created, or an existing table will be replaced, in the remote catalog. The table is successfully created because this is a new table that doesn’t have an existing version.

CREATE OR REPLACE ICEBERG TABLE my_iceberg_v3_table (
    boolean_col boolean,
    int_col int,
    long_col long,
  )
  CATALOG='my_catalog_integration'
  ICEBERG_VERSION=3;

Create an externally managed Iceberg table with v3 from an existing table with Iceberg metadata. The lack of a column definitions or format version in this example indicates that this table already exists and the column specification and format version will be inferred from Iceberg metadata from the remote catalog. This example uses catalog-vended credentials, so the EXTERNAL_VOLUME parameter is excluded from the CREATE ICEBERG TABLE statement:

CREATE OR REPLACE ICEBERG TABLE my_iceberg_v3_table
  CATALOG = 'my_catalog_integration'
  CATALOG_TABLE_NAME = 'my_table'
  AUTO_REFRESH = TRUE;

Unsupported Snowflake features¶

The following Snowflake features aren’t supported in this preview for Iceberg v3:

• Append-only streams on externally managed Iceberg tables

• dbt Projects on Snowflake

• Schema inference

• Snowpipe Streaming classic architecture

• SnowGov Regions

• For tables that use an external catalog, you can’t create Iceberg v3 tables with structured type columns, which includes OBJECT, ARRAY, or MAP. For example, you can’t use CREATE ICEBERG TABLE … AS SELECT (CTAS) to create an externally managed Iceberg v3 table with structured type columns.

  You can create Snowflake-managed Iceberg v3 tables with structured type columns.

• An in-place upgrade of a Snowflake-managed Iceberg table from v2 to v3, which includes cloning a v2 table, and then upgrading the clone to v3

  Important

  If you use Apache Spark to upgrade an externally managed Iceberg table from v2 to v3, you must use a commit that creates a new snapshot, such as DML operations. Otherwise, if the format-version is updated in table properties without a new snapshot, Snowflake’s manual and automated refresh for the table will fail until a new snapshot is created.

  The following example uses Apache Spark to upgrade an externally managed Iceberg table from v2 to v3:

  ALTER TABLE table_name SET TBLPROPERTIES('format-version'='3');
  

  Copy

Supported Snowflake features¶

Features that aren’t listed in the Unsupported Snowflake features section are supported. Supported features include those in the following list:

Unsupported Iceberg v3 features¶

The following features from the Iceberg v3 specification aren’t supported:

• Nested variant

• Multi-argument transforms for partitioning and sorting

• Table encryption keys

• UNKNOWN data type

Create a v3 Iceberg table¶

The following example creates a Snowflake-managed Apache Iceberg™ table that conforms to v3 of the Apache Iceberg™ specification:

CREATE ICEBERG TABLE my_v3_iceberg_table (
  record VARIANT,
  event_timestamp TIMESTAMP_LTZ(6)
)
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'my_external_volume'
  BASE_LOCATION = 'my_iceberg_table'
  ICEBERG_VERSION = 3;

The following example creates an Apache Iceberg™ table that uses a remote Iceberg REST catalog and conforms to v3 of the Apache Iceberg™ specification:

CREATE ICEBERG TABLE my_v3_iceberg_table
  EXTERNAL_VOLUME = 'my_external_volume'
  CATALOG = 'my_rest_catalog_integration'
  CATALOG_TABLE_NAME = 'my_remote_table'
  AUTO_REFRESH = TRUE;

The following example creates a writable Iceberg table in a catalog-linked database with column definitions and conforms to v3 of the Apache Iceberg™ specification:

USE DATABASE my_catalog_linked_db;

USE SCHEMA 'my_namespace';

CREATE OR REPLACE ICEBERG TABLE my_iceberg_table (
  first_name string,
  last_name string,
  amount int,
  create_date date
)
  ICEBERG_VERSION = 3;

Write to a v3 Iceberg table¶

DML commands INSERT, UPDATE, DELETE, MERGE, TRUNCATE TABLE, and COPY INTO are supported for writing to Snowflake-managed and externally managed Iceberg v3 tables:

The following example inserts a row into an Apache Iceberg™ table that conforms to v3 of the Apache Iceberg™ table specification:

INSERT INTO my_v3_iceberg_table (id, payload) VALUES (1, PARSE_JSON('{"name": "Alice", "age": 30}'));

The following example loads files into an Apache Iceberg™ table that conforms to v3 of the Apache Iceberg™ table specification:

COPY INTO my_v3_iceberg_table
  FROM @my_json_stage
  FILE_FORMAT = 'my_json_format'
  MATCH_BY_COLUMN_NAME = CASE_SENSITIVE;

Load data by using Snowpipe¶

The following example loads data from files for Iceberg v3 tables, for both Snowflake-managed and externally managed tables:

CREATE PIPE mypipe
  AUTO_INGEST = TRUE
  INTEGRATION = 'MYINT'
  AS
  COPY INTO snowpipe_db.public.my_v3_iceberg_table
  FROM @snowpipe_db.public.mystage
  FILE_FORMAT = (TYPE = 'JSON');

Create a v3 dynamic Iceberg table¶

The following example writes a v3 Snowflake-managed Iceberg table as the output of a dynamic table:

CREATE DYNAMIC ICEBERG TABLE my_dynamic_iceberg_v3_table (
    num_orders NUMBER(10,0),
    order_day
  )
  TARGET_LAG = '20 minutes'
  WAREHOUSE = my_warehouse
  EXTERNAL_VOLUME = 'my_external_volume'
  CATALOG = 'SNOWFLAKE'
  BASE_LOCATION = 'my_dynamic_iceberg_v3_table'
  ICEBERG_VERSION = 3
  AS
    SELECT
        COUNT(DISTINCT order_id)
        DATE_TRUNC('DAY', order_timestamp_ns) AS order_day
      FROM staging_v3_iceberg_table;

Query a v3 Iceberg table¶

The following example queries a Snowflake-managed or externally managed Iceberg v3 table:

SELECT * FROM MY_DB.MY_SCHEMA.MY_ICEBERG_V3_TABLE;

Copy