Manage Apache Iceberg™ tables¶

Example: Update a table¶

You can use INSERT and UPDATE statements to modify Snowflake-managed Iceberg tables.

The following example inserts a new value into an Iceberg table named store_sales, then updates the cola column to 1 if the value is currently -99.

INSERT INTO store_sales VALUES (-99);

UPDATE store_sales
  SET cola = 1
  WHERE cola = -99;

Query tables¶

Snowflake supports querying externally managed Iceberg tables when you’ve configured row-level deletes for update, delete, and merge operations.

To configure row-level deletes, see Write properties in the Apache Iceberg documentation.

Write to tables by using positional delete files¶

Snowflake supports position row-level deletes for writing to externally managed Iceberg tables stored on Amazon S3, Azure, or Google Cloud. To turn off position deletes, which enable running the DML operations in copy-on-write mode, set the ENABLE_ICEBERG_MERGE_ON_READ parameter to FALSE at the table, schema, or database level.

Write to tables by using deletion vectors¶

To optimize row-level data modifications, Snowflake supports deletion vectors for writing to externally managed and Snowflake-managed Iceberg tables stored on Amazon S3, Azure, or Google Cloud. With deletion vectors, Snowflake can perform “merge-on-read” (MOR) operations, which improve write performance for the following DML statements:

• DELETE

• UPDATE

• MERGE

Snowflake achieves this performance by writing small vector files instead of rewriting large data files. For more information, see Deletion vectors in the Apache Iceberg specification.

Copy-on-write vs. merge-on-read¶

Iceberg provides two modes for configuring how compute engines handle row-level operations for externally managed tables. Snowflake supports both of these modes.

The following table describes when you might want to use each mode:

For more information about row-level changes for Iceberg, see Row-level deletes in the Apache Iceberg documentation.

Considerations and limitations¶

Consider the following information when you use row-level deletes with Iceberg tables:

• Snowflake supports position deletes only for v2 Iceberg tables, and deletion vectors for v3 Iceberg tables.

• Snowflake only supports position deletes with externally managed Iceberg tables.

• For the best read performance when you use row-level deletes, perform regular compaction and table maintenance to remove old delete files. For information, see Maintain tables that use an external catalog.

• Excessive position deletes, especially dangling position deletes, might prevent table creation and refresh operations. To avoid this issue, perform table maintenance to remove extra position deletes.

  The table maintenance method to use depends on your external Iceberg engine. For example, you can use the rewrite_data_files method for Spark with the delete-file-threshold or rewrite-all options. For more information, see rewrite_data_files in the Apache Iceberg™ documentation.

Automatic Clustering¶

Automatic Clustering reorganizes data within files or partitions based on frequently queried columns. The file size for Iceberg tables is based on your clustering configuration, unless you set a target file size. If you do, the file size is the specific size you set. For more information, see Set a target file size.

To set Automatic Clustering, specify the CLUSTER BY parameter when you create a Snowflake-managed Iceberg table or modify an existing table. For more information, see:

• CREATE ICEBERG TABLE (Snowflake as the Iceberg catalog)

• ALTER ICEBERG TABLE

For more information about Automatic Clustering, see Automatic Clustering.

Data compaction¶

Data compaction combines small files into larger, more efficient files to manage storage, maintain an optimal file size, and improve query performance.

In most cases, data compaction doesn’t have a significant effect on compute costs, but if these costs are a concern, you can disable compaction. For example, you might want to disable compaction on a table if you rarely query it. To disable or enable data compaction, see Set data compaction.

Manifest compaction¶

Manifest compaction optimizes the metadata layer by reorganizing and combining smaller manifest files. This compaction reduces metadata overhead and improves query performance.

This feature is enabled automatically and you can’t disable it.

Snapshot expiry¶

Snapshot expiry systematically deletes old snapshots and their unique data and metadata files from the table’s history. This deletion is based on predefined retention policies.

This feature is enabled automatically and you can’t disable it.

Refresh the metadata for a table¶

The following example manually refreshes the metadata for a table that uses an external catalog (for example, AWS Glue or Delta). Refreshing the table keeps the table in sync with any changes that have occurred in the remote catalog.

With this type of Iceberg table, you don’t specify a metadata file path in the command.

ALTER ICEBERG TABLE my_iceberg_table REFRESH;

To keep a table updated automatically, you can set up automated refresh. Use the ALTER ICEBERG TABLE command.

For example:

ALTER ICEBERG TABLE my_iceberg_table SET AUTO_REFRESH = TRUE;

Refresh the metadata for a table created from Iceberg files¶

The following example manually refreshes a table created from Iceberg metadata files in an external cloud storage location, specifying the relative path to a metadata file without the leading forward slash (/). The metadata file defines the data in the table after refreshing.

ALTER ICEBERG TABLE my_iceberg_table REFRESH 'metadata/v1.metadata.json';

Example: Create a table with a default value¶

To create an Iceberg table with default values, use the CREATE ICEBERG TABLE command.

In the following example, you first set a default value for a column when you create a Snowflake-managed Iceberg table. Next, you insert a record into the table without specifying a value for the column with the default value.

1. Create a user_events table, which includes an event_version column with a default value of 2:

   CopyExpand

   CREATE ICEBERG TABLE user_events (
       event_id INT,
       user_id INT,
       event_type STRING,
       event_time TIMESTAMP,
       event_version INT DEFAULT 2
     )
     CATALOG = 'SNOWFLAKE'
     EXTERNAL_VOLUME = 'my_external_volume'
     BASE_LOCATION = 'database/schema/user_event'
     ICEBERG_VERSION = 3;
   

   Show lessSee more

   Scroll to top

   Setting a default value in the table definition sets an initial default and a write default. Because the column has a write default, the value 2 will be used for new records if the event_version isn’t specified during writes.

2. Add a login event with event_version specified:

   CopyExpand

   INSERT INTO user_events VALUES
     (1, 101, 'login', '2025-11-01 10:00:00', 1);
   

   Show lessSee more

   Scroll to top

3. Add a purchase event, but don’t specify an event_version:

   CopyExpand

   INSERT INTO user_events VALUES
   (1, 101, 'purchase', '2025-11-01 10:01:00');
   

   Show lessSee more

   Scroll to top

   As a result, Snowflake inputs the value for event_version into the table as 2.

4. Query the table:

   CopyExpand

   SELECT * FROM user_events;
   

   Show lessSee more

   Scroll to top

   Output:

   Expand

   +-----------+----------+-------------+---------------------+----------------+
   | event_id  | user_id  | event_type  | event_time          | event_version  |
   +-----------+----------+-------------+---------------------+----------------+
   | 1         | 101      | login       | 2025-11-01 10:00:00 | 1              |
   | 1         | 101      | purchase    | 2025-11-01 10:01:00 | 2              |
   +-----------+----------+-------------+---------------------+----------------+
   

   Show lessSee more

   Scroll to top

Example: Add a column with a default value to an existing table¶

To add a new column with a default value to an Iceberg table, use the ALTER ICEBERG TABLE command.

In the following example, you modify the user_events table by adding an event_version column, which has a default value of 2:

ALTER ICEBERG TABLE user_events ADD COLUMN event_version INT DEFAULT 2;

In addition to setting a write default, adding a column with a default value also sets an initial default for the column. As a result, the default value for existing records for the event_version column is 2.

Example: Change the write default for a column¶

The following example changes the write default for the event_version column of the user_events table to 3:

ALTER ICEBERG TABLE user_events ALTER COLUMN event_version SET WRITE DEFAULT 3;

View the default values defined for a table¶

To view the default value for a table column in a Snowflake-managed or externally managed Iceberg table, run the DESCRIBE ICEBERG TABLE command, and then view the DEFAULT column and WRITE DEFAULT column in the output:

• The DEFAULT column maps to the initial-default value in the Apache Iceberg specification.

• The WRITE DEFAULT column maps to the write-default value in the Apache Iceberg specification.

These columns return in the output, regardless of whether the table is a v2 Iceberg table or a v3 Iceberg table.

The following example describes the columns for the user_events table. This table has an initial default and write default specified for the event_version column:

DESC ICEBERG TABLE user_events
  ->> SELECT
    "name",
    "kind",
    "default",
    "write default"
      FROM $1;

Output:

+-----------------+---------+---------+---------------+
| name            | kind    | default | write default |
+-----------------+---------+-------------------------+
| EVENT_ID        | COLUMN  |         |               |
| USER_ID         | COLUMN  |         |               |
| EVENT_TYPE      | COLUMN  |         |               |
| EVENT_TIME      | COLUMN  |         |               |
| EVENT_VERSION   | COLUMN  | 2       | 3             |
+-----------------+---------+---------+---------------+

Drop the write default¶

To drop the write default for a column, use the DROP WRITE DEFAULT keywords with the ALTER ICEBERG TABLE command.

The following example drops the default write value for the event_version column:

ALTER ICEBERG TABLE user_events ALTER COLUMN event_version DROP WRITE DEFAULT;

Considerations and limitations for default values¶

Consider the following items when you use default values with Snowflake-managed and externally managed Iceberg tables:

Considerations and limitations for row lineage¶

Row lineage is supported in streams with the following considerations:

• Append-only streams and standard streams are supported on Snowflake-managed Iceberg v3 tables.

• Insert-only streams and standard streams are supported on externally managed Iceberg v3 tables.

  • To have standard streams produce the correct results, the external engine must write to Iceberg v3 tables with respect to the Iceberg v3 specification. Specifically, newly inserted rows should have _row_id=NULL. Rows that are copied during copy-on-write should maintain the _row_id.

  • MAX_DATA_EXTENSION_TIME_IN_DAYS doesn’t work on externally managed Iceberg v3 tables.

• When DMLs are committed over multi-statement transactions, append-only streams on Iceberg v3 tables have different semantics compared to Iceberg v2 tables:

  • On Iceberg v2, for append-only streams, if a row is added and then deleted in a multi-statement transaction, this row is considered an insertion.

  • On Iceberg v3, for append-only streams, this row isn’t treated as an insertion.

These considerations and limitations apply to row lineage, which is a feature from Iceberg v3. For a list of considerations and limitations that apply to all Iceberg v3 tables, see Considerations and limitations for Iceberg v3 features.