ALTER DYNAMIC TABLE¶

Modifies the properties of a dynamic table.

See also:: CREATE DYNAMIC TABLE, DESCRIBE DYNAMIC TABLE, DROP DYNAMIC TABLE, SHOW DYNAMIC TABLES

Syntax¶

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> { SUSPEND | RESUME }

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> SWAP WITH <target_dynamic_table_name>

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> REFRESH

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> { clusteringAction }

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> { tableColumnCommentAction }

ALTER DYNAMIC TABLE <name> { SET | UNSET } COMMENT = '<string_literal>'

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> dataGovnPolicyTagAction

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> searchOptimizationAction

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> SET
  [ TARGET_LAG = { '<num> { seconds | minutes | hours | days }'  | DOWNSTREAM } ]
  [ WAREHOUSE = <warehouse_name> ]
  [ DATA_RETENTION_TIME_IN_DAYS = <integer> ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer> ]
  [ DEFAULT_DDL_COLLATION = '<collation_specification>' ]

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> UNSET
  [ DATA_RETENTION_TIME_IN_DAYS ],
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS ],
  [ DEFAULT_DDL_COLLATION ]

Copy

Where:

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

Copy

For more information, see Clustering Keys & Clustered Tables.

tableCommentAction ::=
  {
    ALTER | MODIFY [ ( ]
                           [ COLUMN ] <col1_name> COMMENT '<string>'
                         , [ COLUMN ] <col1_name> UNSET COMMENT
                       [ , ... ]
                   [ ) ]
  }

Copy

dataGovnPolicyTagAction ::=
  {
      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
  }
  |
  {
    { 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 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> ... ]
  }
  |
  {
      SET TAG <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' ... ]
    | UNSET TAG <tag_name> [ , <tag_name> ... ]
  }

Copy

searchOptimizationAction ::=
  {
    ADD SEARCH OPTIMIZATION [
      ON <search_method_with_target> [ , <search_method_with_target> ... ]
        [ EQUALITY ]
      ]

    | DROP SEARCH OPTIMIZATION [
      ON { <search_method_with_target> | <column_name> | <expression_id> }
        [ EQUALITY ]
        [ , ... ]
      ]

    | SUSPEND SEARCH OPTIMIZATION [
       ON { <search_method_with_target> | <column_name> | <expression_id> }
          [ , ... ]
     ]

    | RESUME SEARCH OPTIMIZATION [
       ON { <search_method_with_target> | <column_name> | <expression_id> }
          [ , ... ]
     ]
  }

Copy

For details, see Search optimization actions (searchOptimizationAction).

Parameters¶

name

Identifier for the dynamic table to alter.

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.

SUSPEND | RESUME

Specifies the action to perform on the dynamic table:

SUSPEND suspends refreshes on the dynamic table. If the dynamic table is used by other dynamic tables, they are also suspended.
RESUME resumes refreshes on the dynamic table. Resume operations cascade downstream to all downstream dynamic tables not manually suspended.

RENAME TO new_name

Renames the specified dynamic table with a new identifier that is not currently used by any other dynamic tables in the schema.

Renaming a dynamic table requires the CREATE DYNAMIC TABLE privilege on the schema for the dynamic table.

You can also move the dynamic table to a different database and/or schema while optionally renaming the dynamic table. To do so, specify a qualified new_name value that includes the new database and/or schema name in the form db_name.schema_name.new_name or schema_name.new_name, respectively.

The following restrictions apply:

The destination database and/or schema must already exist. In addition, an object with the same name cannot already exist in the new location; otherwise, the statement returns an error.
You can’t move an object to a managed access schema unless the object owner (that is, the role that has the OWNERSHIP privilege on the object) also owns the target schema.
When an object (table, column, etc.) is renamed, other objects that reference it must be updated with the new name.

SWAP WITH target_dynamic_table_name

Swaps two dynamic tables in a single transaction. The role used to perform this operation must have OWNERSHIP privileges on both dynamic tables.

The following restrictions apply:

You can only swap a dynamic table with another dynamic table.

REFRESH

Specifies that the dynamic table should be manually refreshed.

Both user-suspended and auto-suspended dynamic tables can be manually refreshed. Manually refreshed dynamic tables return MANUAL as the output for refresh_trigger in the DYNAMIC_TABLE_REFRESH_HISTORY function.

Note that refreshing a dynamic table also refreshes all upstream dynamic tables as of the same data timestamp. For more information, see Alter the warehouse or target lag for a dynamic table.

For information on dynamic table refresh status, see DYNAMIC_TABLE_REFRESH_HISTORY.

SET ...

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

TARGET_LAG = { num { seconds | minutes | hours | days } | DOWNSTREAM }

Specifies the target lag for the dynamic table:

'num seconds | minutes | hours | days'

Specifies the maximum amount of time that the dynamic table’s content should lag behind updates to the base tables.

For example:

If the data in the dynamic table should lag by no more than 5 minutes, specify 5 minutes.
If the data in the dynamic table should lag by no more than 5 hours, specify 5 hours.

The minimum value is 1 minute. If a dynamic table A depends on another dynamic table B, the minimum lag for A must be greater than or equal to the lag for B.

DOWNSTREAM

Specifies that the dynamic table should be refreshed if any dynamic table downstream of it is refreshed.

WAREHOUSE = warehouse_name

Specifies the name of the warehouse that provides the compute resources for refreshing the dynamic table.

The owner role of the dynamic table must have the USAGE privilege on this warehouse.

DATA_RETENTION_TIME_IN_DAYS = integer

Object-level parameter that modifies the retention period for the dynamic table for Time Travel. For more details, see Understanding & using Time Travel and Working with Temporary and Transient Tables.

For a detailed description of this parameter and more information about object parameters, see Parameters.

Values:

Standard Edition: 0 or 1

Enterprise Edition:

0 to 90 for permanent dynamic tables

0 or 1 for transient dynamic tables

Note

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

MAX_DATA_EXTENSION_TIME_IN_DAYS = integer

Object parameter that specifies the maximum number of days Snowflake can extend the data retention period to prevent streams on the dynamic table from becoming stale.

For a detailed description of this parameter, see MAX_DATA_EXTENSION_TIME_IN_DAYS.

DEFAULT_DDL_COLLATION = 'collation_specification'

Specifies a default collation specification for any new columns added to the dynamic table.

Setting this parameter does not change the collation specification for any existing columns.

For more information, see DEFAULT_DDL_COLLATION.

UNSET ...

Specifies one or more properties/parameters to unset for the dynamic table, which resets them back to their defaults:

DATA_RETENTION_TIME_IN_DAYS
MAX_DATA_EXTENSION_TIME_IN_DAYS
DEFAULT_DDL_COLLATION

Clustering actions (`clusteringAction`)¶

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

Specifies (or modifies) one or more table columns or column expressions as the clustering key for the dynamic table. These are the columns/expressions for which clustering is maintained by Automatic Clustering. Before you specify a clustering key for a dynamic table, you should understand micro-partitions. For more information, see Understanding Snowflake Table Structures.

Note the following when using clustering keys with dynamic tables:

Column definitions are required and must be explicitly specified in the statement.
Clustering keys are not intended or recommended for all tables; they typically benefit very large (for example, multi-terabyte) tables.

SUSPEND | RESUME RECLUSTER

Enables or disables Automatic Clustering for the dynamic table.

DROP CLUSTERING KEY

Drops the clustering key for the dynamic table.

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

Table comment actions (`tableCommentAction`)¶

ALTER | MODIFY [ ( ]` . [ COLUMN ] <col1_name> COMMENT '<string>' . , [ COLUMN ] <col1_name> UNSET COMMENT . [ , ... ] . [ ) ]: Alters a comment or overwrites the existing comment for a column in the dynamic table.
SET | UNSET COMMENT = '<string_literal>': Adds a comment or overwrites the existing comment for the dynamic table.

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.

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

Adds a row access policy to the dynamic table.

At least one column name must be specified. Additional columns can be specified with a comma separating each column name.

DROP ROW ACCESS POLICY policy_name

Drops a row access policy from the dynamic 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 dynamic table and adds a row access policy to the same dynamic table in a single SQL statement.

DROP ALL ROW ACCESS POLICIES

Drops all row access policy associations from the dynamic table.

{ ALTER | MODIFY } [ COLUMN ] ...

USING ( col_name , cond_col_1 ... )

Specifies the arguments to pass into the conditional masking policy.

The first column in the list specifies the data to be masked or tokenized based on policy conditions and must match the column to which the masking policy is applied.

The additional columns specify which data to evaluate for masking or tokenization in each row of the query result when selecting from 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 DYNAMIC 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.

Search optimization actions (`searchOptimizationAction`)¶

ADD SEARCH OPTIMIZATION

Adds search optimization for the entire dynamic table or, if you specify the optional ON clause, for specific columns.

Search optimization can be expensive to maintain, especially if the data in the table changes frequently. For more information, see Search optimization cost estimation and management.

ON search_method_with_target [, search_method_with_target ... ]

Specifies that you want to configure search optimization for specific columns or VARIANT fields (rather than the entire dynamic table).

For search_method_with_target, use an expression with the following syntax:

<search_method>(<target> [, ...])

Copy

Where:

search_method specifies one of the following methods that optimizes queries for a particular type of predicate:
- GEO: Predicates that use GEOGRAPHY types.
- SUBSTRING: Predicates that match substrings and regular expressions (for example, [ NOT ] LIKE, [ NOT ] ILIKE, [ NOT ] RLIKE, REGEXP_LIKE, etc.)
- EQUALITY: Equality and IN predicates.
target specifies the column, VARIANT field, or an asterisk (*).

Depending on the value of search_method, you can specify a column or VARIANT field of one of the following types:
- GEO: Columns of the GEOGRAPHY data type.
- SUBSTRING: Columns of string or VARIANT data types, including paths to fields in VARIANTs. Specify paths to fields as described under EQUALITY; searches on nested fields are improved in the same way.
- EQUALITY: Columns of numeric, string, binary, and VARIANT data types, including paths to fields in VARIANT columns.
  
  To specify a VARIANT field, use dot or bracket notation. For example:
  - my_column:my_field_name.my_nested_field_name
  - my_column['my_field_name']['my_nested_field_name']
  You may also use a colon-delimited path to the field. For example:
  - my_column:my_field_name:my_nested_field_name
  When you specify a VARIANT field, the configuration applies to all nested fields under that field.
  
  For example, if you specify ON EQUALITY(src:a.b):
  - This configuration can improve queries on src:a.b and on any nested fields (for example, src:a.b.c, src:a.b.c.d, etc.).
  - This configuration only affects queries that use the src:a.b prefix (for example, src:a, src:z, etc.).

To specify all applicable columns in the table as targets, use an asterisk (*).

Note that you can’t specify both an asterisk and specific column names for a given search method. However, you can specify an asterisk in different search methods.

For example, you can specify the following expressions:

ON SUBSTRING(*)
ON EQUALITY(*), SUBSTRING(*), GEO(*)

Copy

You can’t specify the following expressions:

ON EQUALITY(*, c1)
ON EQUALITY(c1, *)
ON EQUALITY(v1:path, *)
ON EQUALITY(c1), EQUALITY(*)

Copy

To specify more than one search method on a target, use a comma to separate each subsequent method and target:

ALTER DYNAMIC TABLE product ADD SEARCH OPTIMIZATION ON EQUALITY(c1), EQUALITY(c2, c3);

Copy

If you run the ALTER DYNAMIC TABLE … ADD SEARCH OPTIMIZATION ON … command multiple times on the same table, each subsequent command adds to the existing configuration for the table. For instance, suppose that you run the following commands:

ALTER DYNAMIC TABLE product ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2);
ALTER DYNAMIC TABLE product ADD SEARCH OPTIMIZATION ON EQUALITY(c3, c4);

Copy

This adds equality predicates for the columns c1, c2, c3, and c4 to the configuration for the table. This is equivalent to running the command:

ALTER DYNAMIC TABLE product ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2, c3, c4);

Copy

For examples, see Enabling search optimization for specific columns.

DROP SEARCH OPTIMIZATION

Removes search optimization for the entire dynamic table or, if you specify the optional ON clause, from specific columns.

The following restrictions apply:

If a dynamic table has the search optimization property, then dropping the dynamic table and undropping it preserves the search optimization property.
Removing the search optimization property from a dynamic table and then adding it back incurs the same cost as adding it the first time.

ON search_method_with_target | column_name | expression_id [, ... ]

Specifies that you want to drop the search optimization configuration for specific columns or VARIANT fields (rather than dropping search optimization for the entire dynamic table).

To identify the column configuration to drop, specify one of the following:

For search_method_with_target, specify a method for optimizing queries for one or more specific targets, which can be columns or VARIANT fields. Use the syntax described earlier.
For column_name, specify the name of the column configured for search optimization. Specifying the column name drops all expressions for that column, including expressions that use VARIANT fields in the column.
For expression_id, specify the ID for an expression listed in the output of the DESCRIBE SEARCH OPTIMIZATION command.

You can specify any combination of search methods with targets, column names, and expression IDs using a comma between items.

For examples, see Dropping search optimization for specific columns.

Usage notes¶

To alter a dynamic table, you must be using a role that has OPERATE privilege on that dynamic table. For general information, see Privileges to view a dynamic table’s metadata.
You cannot make changes to the masking policy after you’ve created the dynamic table.
If you want to update an existing dynamic table and need to see its current definition, call the GET_DDL function.
You can use data metric functions with dynamic tables by executing an ALTER TABLE command. For more information, see Working with data metric functions.
You cannot use IDENTIFIER() to specify the name of the dynamic table to alter. For example, the following statement isn’t supported:
```
ALTER DYNAMIC TABLE IDENTIFIER(product) SUSPEND;
```
Copy
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.

Examples¶

Change the target lag time of a dynamic table named product to 1 hour:

ALTER DYNAMIC TABLE product SET
  TARGET_LAG = '1 hour';

Copy

Specify downstream target lag for a dynamic table for a dynamic table named product:

ALTER DYNAMIC TABLE product SET TARGET_LAG = DOWNSTREAM;

Copy

Suspend the product dynamic table:

ALTER DYNAMIC TABLE product SUSPEND;

Copy

Resume the product dynamic table:

ALTER DYNAMIC TABLE product RESUME;

Copy

Rename the product dynamic table:

ALTER DYNAMIC TABLE product RENAME TO updated_product;

Copy

Swap the product dynamic table with new-product:

ALTER DYNAMIC TABLE product SWAP WITH new_product;

Copy

Change the clustering key for a table:

ALTER DYNAMIC TABLE product CLUSTER BY (date);

Copy

Remove clustering from a table:

ALTER DYNAMIC TABLE product DROP CLUSTERING KEY;

Copy

ALTER DYNAMIC TABLE¶

Syntax¶

Parameters¶

Clustering actions (clusteringAction)¶

Table comment actions (tableCommentAction)¶

Data Governance policy and tag actions (dataGovnPolicyTagAction)¶

Search optimization actions (searchOptimizationAction)¶

Usage notes¶

Examples¶

Clustering actions (`clusteringAction`)¶

Table comment actions (`tableCommentAction`)¶

Data Governance policy and tag actions (`dataGovnPolicyTagAction`)¶

Search optimization actions (`searchOptimizationAction`)¶