ALTER DYNAMIC TABLE¶

Snowflake logo in black (no text) Preview Feature — Open

Available to accounts.

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 [ <name> ] { SUSPEND | RESUME }

ALTER DYNAMIC TABLE [ <name> ] REFRESH

ALTER DYNAMIC TABLE <name> SET
  [ TARGET_LAG = { '<num> { seconds | minutes | hours | days }'  | DOWNSTREAM } ]
  [ WAREHOUSE = <warehouse_name> ]
Copy

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';
Copy
Language: English