ALTER DYNAMIC TABLE¶

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> SET
  [ TARGET_LAG = { '<num> { seconds | minutes | hours | days }'  | DOWNSTREAM } ]
  [ WAREHOUSE = <warehouse_name> ]

Where:

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

Copy

For more information, see Clustering Keys & Clustered Tables.

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 the dynamic table(s).

The following restrictions apply:

• You can’t swap a permanent or transient dynamic table with a temporary dynamic table. This restriction prevents a naming conflict that could occur when a temporary dynamic table is swapped with a permanent or transient dynamic table, and an existing permanent or transient dynamic table has the same name as the temporary dynamic table.

• 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 may be manually refreshed. Manually refreshed dynamic tables return MANUAL as the output for refresh_trigger in the DYNAMIC_TABLE_REFRESH_HISTORY function.

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.

You must have the USAGE privilege on this warehouse.

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.

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.

• 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