ALTER ICEBERG TABLE¶

Syntax¶

ALTER ICEBERG TABLE [ IF EXISTS ] <table_name> clusteringAction

ALTER ICEBERG TABLE [ IF EXISTS ] <table_name> SET
  [ REPLACE_INVALID_CHARACTERS = { TRUE | FALSE } ]
  [ CATALOG_SYNC = '<open_catalog_integration_name>']
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ AUTO_REFRESH = { TRUE | FALSE } ]

ALTER ICEBERG TABLE [ IF EXISTS ] <table_name> UNSET REPLACE_INVALID_CHARACTERS

ALTER ICEBERG TABLE [ IF EXISTS ] dataGovnPolicyTagAction

Where:

clusteringAction ::=
  {
     CLUSTER BY ( <expr> [ , <expr> , ... ] )
     /* { SUSPEND | RESUME } RECLUSTER is valid action */
   | { SUSPEND | RESUME } RECLUSTER
   | DROP CLUSTERING KEY
  }

Copy

dataGovnPolicyTagAction ::=
  {
      SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]
    | UNSET TAG <tag_name> [ , <tag_name> ... ]
  }
  |
  {
      ADD ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , ... ] )
    | DROP ROW ACCESS POLICY <policy_name>
    | DROP ROW ACCESS POLICY <policy_name> ,
        ADD ROW ACCESS POLICY <policy_name> ON ( <col_name> [ , ... ] )
    | DROP ALL ROW ACCESS POLICIES
  }
  |
  {
      SET AGGREGATION POLICY <policy_name> [ FORCE ]
      | UNSET AGGREGATION POLICY
  }
  |
  {
    { ALTER | MODIFY } [ COLUMN ] <col1_name>
        SET MASKING POLICY <policy_name>
          [ USING ( <col1_name> , <cond_col_1> , ... ) ] [ FORCE ]
      | UNSET MASKING POLICY
  }
  |
  {
    { ALTER | MODIFY } [ COLUMN ] <col1_name>
        SET PROJECTION POLICY <policy_name>
          [ FORCE ]
      | UNSET PROJECTION POLICY
  }
  |
  { ALTER | MODIFY } [ COLUMN ] <col1_name> SET TAG
      <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]
      , [ COLUMN ] <col2_name> SET TAG
          <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]
  |
  { ALTER | MODIFY } [ COLUMN ] <col1_name> UNSET TAG <tag_name> [ , <tag_name> ... ]
                  , [ COLUMN ] <col2_name> UNSET TAG <tag_name> [ , <tag_name> ... ]

Copy

Parameters¶

table_name

Identifier for the table to modify.

If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

For more information, see Identifier requirements.

SET ...

Specifies one or more properties/parameters to set for the external table (separated by blank spaces, commas, or new lines):

REPLACE_INVALID_CHARACTERS = { TRUE | FALSE }

Specifies whether to replace invalid UTF-8 characters with the Unicode replacement character (�) in query results. You can only set this parameter for tables that use an external Iceberg catalog.

• TRUE replaces invalid UTF-8 characters with the Unicode replacement character.

• FALSE leaves invalid UTF-8 characters unchanged. Snowflake returns a user error message when it encounters invalid UTF-8 characters in a Parquet data file.

If not specified, the Iceberg table defaults to the parameter value for the schema, database, or account. The schema takes precedence over the database, and the database takes precedence over the account.

Default: FALSE

CATALOG_SYNC = 'snowflake_open_catalog_integration_name'

Specifies the name of a catalog integration configured for Snowflake Open Catalog. Snowflake syncs the table with an external catalog in your Snowflake Open Catalog account. For more information, see Sync a Snowflake-managed table with Snowflake Open Catalog.

DATA_RETENTION_TIME_IN_DAYS = integer

Specifies the retention period for a Snowflake-managed table so that Time Travel actions (SELECT, CLONE, UNDROP) can be performed on historical data in the table. For more information, see Understanding & using Time Travel.

For a detailed description of this object-level parameter, as well as more information about object parameters, see Parameters.

Values:

• Standard Edition: 0 or 1

• Enterprise Edition: 0 to 90 for permanent tables

Default:

• Standard Edition: 1

• Enterprise Edition (or higher): 1 (unless a different default value was specified at the schema, database, or account level)

Note

A value of 0 effectively disables Time Travel for the table.

AUTO_REFRESH = { TRUE | FALSE }

Specifies whether Snowflake should automatically poll the external Iceberg catalog associated with the table for metadata updates when you use automated refresh. If no value is specified for the REFRESH_INTERVAL_SECONDS parameter on the catalog integration, Snowflake uses a default refresh interval of 30 seconds.

Default: FALSE

Preview feature

Available to all accounts.

UNSET

Currently, you can only unset the following parameter with this command:

• REPLACE_INVALID_CHARACTERS

Clustering actions (clusteringAction)¶

CLUSTER BY ( expr [ , expr , ... ] )

Specifies (or modifies) one or more table columns or column expressions as the clustering key for the table. These are the columns/expressions for which clustering is maintained by Automatic Clustering.

To learn more about clustering, see Clustering Keys & Clustered Tables.

SUSPEND | RESUME RECLUSTER

Enables or disables Automatic Clustering for the table.

DROP CLUSTERING KEY

Drops the clustering key for the table.

For more information about clustering keys and reclustering, see Understanding Snowflake Table Structures.

Data Governance policy and tag actions (dataGovnPolicyTagAction)¶

TAG tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ]

Specifies the tag name and the tag string value.

The tag value is always a string, and the maximum number of characters for the tag value is 256.

For information about specifying tags in a statement, see Tag quotas for objects and columns.

policy_name

Identifier for the policy; must be unique for your schema.

The following clauses apply to all table kinds that support row access policies, such as but not limited to tables, views, and event tables. To simplify, the clauses just refer to “table.”

ADD ROW ACCESS POLICY policy_name ON (col_name [ , ... ])

Adds a row access policy to the table.

At least one column name must be specified. Additional columns can be specified with a comma separating each column name. Use this expression to add a row access policy to both an event table and an external table.

DROP ROW ACCESS POLICY policy_name

Drops a row access policy from the table.

Use this clause to drop the policy from the table.

DROP ROW ACCESS POLICY policy_name, ADD ROW ACCESS POLICY policy_name ON ( col_name [ , ... ] )

Drops the row access policy that is set on the table and adds a row access policy to the same table in a single SQL statement.

DROP ALL ROW ACCESS POLICIES

Drops all row access policy associations from the table.

This expression is helpful when a row access policy is dropped from a schema before dropping the policy from an event table. Use this expression to drop row access policy associations from the table.

SET AGGREGATION POLICY policy_name

[ ENTITY KEY (col_name [ , ... ]) ] [ FORCE ]

Assigns an aggregation policy to the table.

Use the optional ENTITY KEY parameter to define which columns uniquely identity an entity within the table. For more information, see Implementing entity-level privacy with aggregation policies.

Use the optional FORCE parameter to atomically replace an existing aggregation policy with the new aggregation policy.

UNSET AGGREGATION POLICY

Detaches an aggregation policy from the table.

{ ALTER | MODIFY } [ COLUMN ] ...

USING ( col_name , cond_col_1 ... )

Specifies the arguments to pass into the conditional masking policy SQL expression.

The first column in the list specifies the column for the policy conditions to mask or tokenize the data and must match the column to which the masking policy is set.

The additional columns specify the columns to evaluate to determine whether to mask or tokenize the data in each row of the query result when a query is made on the first column.

If the USING clause is omitted, Snowflake treats the conditional masking policy as a normal masking policy.

FORCE

Replaces a masking or projection policy that is currently set on a column with a different policy in a single statement.

Note that using the FORCE keyword with a masking policy requires the data type of the policy in the ALTER TABLE statement (i.e. STRING) to match the data type of the masking policy currently set on the column (i.e. STRING).

If a masking policy is not currently set on the column, specifying this keyword has no effect.

For details, see: Replace a masking policy on a column or Replace a projection policy.

Access control requirements¶

A role used to execute this SQL command must have the following privileges at a minimum:

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes¶

• Only the table owner (that is, the role with the OWNERSHIP privilege on the table) or higher can execute this command.

• Clustering is only supported for tables that use Snowflake as the Iceberg catalog. To add clustering to an Iceberg table, you must also have the USAGE or OWNERSHIP privileges on the schema and database that contain the table.

• You can use data metric functions with Iceberg tables by executing an ALTER TABLE command. For more information, see Working with data metric functions.

• Regarding metadata:

  Attention

  Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

• To troubleshooting issues with altering the CATALOG_SYNC parameter, see You can’t alter an Iceberg table when specifying the CATALOG_SYNC parameter

Examples¶

The following example sets a tag (my_tag) with a value of customer on an Iceberg table.

ALTER ICEBERG TABLE my_iceberg_table SET TAG my_tag = 'customer';