ALTER DYNAMIC TABLE¶
Modifies the properties of a dynamic table.
Syntax¶
ALTER DYNAMIC TABLE [ <name> ] { SUSPEND | RESUME }
ALTER DYNAMIC TABLE [ <name> ] REFRESH
ALTER DYNAMIC TABLE <name> SET
[ TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM } ]
[ WAREHOUSE = <warehouse_name> ]
Parameters¶
name
Identifier for the 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.
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.
Refer to Usage Notes for information on displaying dynamic table state.
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 show MANUAL as the refresh activity.
Refer to Usage Notes for information on displaying dynamic table state. Refer to REFRESH_TRIGGER column of DYNAMIC_TABLE_REFRESH_HISTORY for information on dynamic table refresh status.
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 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 the 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 dynamic table should be refreshed if any downstream ancestor 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 in order to create the dynamic table.
Usage Notes¶
To alter a dynamic table, you must be using a role that has OPERATE privilege on the table.
Refer to Dynamic table states for a list of dynamic table states.
To view the dynamic table information, you can either:
Open Snowsight and select Activity » Dynamic Tables.
Call the DYNAMIC_TABLE_GRAPH_HISTORY table function, and examine the results.
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 lag time of a dynamic table named product
to 1 hour:
ALTER DYNAMIC TABLE product SET TARGET_LAG = '1 hour';