Suspend or resume dynamic tables¶

This topic discusses why dynamic tables automatically suspend and how to manually suspend or resume your dynamic tables.

Suspended dynamic tables aren’t automatically refreshed; you can manually refresh them.

Automatic dynamic table suspension¶

Dynamic tables are automatically suspended after five consecutive scheduled refresh errors. A successful refresh, including a manual refresh, resets the error count to zero. For example, if a table fails two consecutive scheduled refreshes, then succeeds on the next, the error count resets to zero.

Errors from manually triggered refreshes don’t count toward this limit.

Any dynamic tables dependent on a suspended table are also suspended.

You can view the current state (ACTIVE or SUSPENDED) of your dynamic tables using one of the following options:

SELECT name, scheduling_state
  FROM TABLE (INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY());

+-------------------+---------------------------------------------------------------------------------+
  | NAME              | SCHEDULING_STATE                                                                |
  |-------------------+---------------------------------------------------------------------------------|
  | DTSIMPLE          | {                                                                               |
  |                   |   "reason_code": "SUSPENDED_DUE_TO_ERRORS",                                     |
  |                   |   "reason_message": "The DT was suspended due to 5 consecutive refresh errors", |
  |                   |   "state": "SUSPENDED",                                                         |
  |                   |   "suspended_on": "2023-06-06 19:27:29.142 -0700"                               |
  |                   | }                                                                               |
  | DT_TEST           | {                                                                               |
  |                   |   "state": "ACTIVE"                                                             |
  |                   | }                                                                               |
  +-------------------+---------------------------------------------------------------------------------+

Manually suspend dynamic tables¶

Manually suspend a dynamic table when you don’t need it now but want to avoid refresh costs without dropping it, keeping it available for future use. Suspension can also give you better control over refresh frequency, for example, if skips occur and you need time for troubleshooting.

If you want to ensure refreshes at a specific time or occurrence, you can use a task or script that runs regularly to execute a manual refresh because dynamic tables don’t guarantee exact refresh timing. This allows precise control over when your table refreshes.

You can use either the ALTER DYNAMIC TABLE … SUSPEND command or Snowsight to manually suspend dynamic tables, with the following limitations:

• Suspending a dynamic table also suspends the dynamic tables that are downstream from it.
• Suspending a dynamic table with incremental refresh beyond the Time Travel retention period of its base tables will cause it to fail on the next refresh after the dynamic table resumes.

ALTER DYNAMIC TABLE my_dynamic_table SUSPEND;

Resume dynamic tables¶

To resume your dynamic tables, use either the ALTER DYNAMIC TABLE … RESUME command or Snowsight.

ALTER DYNAMIC TABLE my_dynamic_table RESUME;

Drop or undrop dynamic tables¶

This topic describes dropping existing dynamic tables and restoring them.

You might no longer need a dynamic table if it’s no longer relevant to your data pipeline. Dropping it helps clean up your environment and reduces unnecessary storage and compute usage. Because dynamic tables consume resources, especially with frequent refreshes, dropping unused tables can help manage costs by preventing further resource consumption.

You can undrop or, in other words, restore a dropped dynamic table using the UNDROP DYNAMIC TABLE command. This allows you to recover the dynamic table and its data without needing to recreate it, whether it’s due to accidental deletion or if a previously dropped table becomes relevant again, such as with changing project priorities or data needs.

To drop or undrop a dynamic table, you must use a role that has the OWNERSHIP privilege on that dynamic table.

Drop existing dynamic tables¶

To drop a dynamic table, you can use either the DROP DYNAMIC TABLE command or Snowsight, as long as you have the OWNERSHIP privilege on that dynamic table.

DROP DYNAMIC TABLE my_dynamic_table;

Restore dropped dynamic tables¶

To undrop a dynamic table, you can use the UNDROP DYNAMIC TABLE command, as long as you have the OWNERSHIP privilege on that dynamic table. Note that you can only undrop dynamic tables within the retention period (default is 24 hours). If a dynamic table with the same name already exists, an error will be returned.

The following example uses the UNDROP DYNAMIC TABLE command to undrop my_dynamic_table.

UNDROP DYNAMIC TABLE my_dynamic_table;

Alter the warehouse or target lag for dynamic tables¶

Adjust your dynamic tables’ warehouse for cost efficiency or performance boost. For more information, see Compute costs and Understand warehouse usage for dynamic tables.

Adjust your dynamic table’s target lag to get fresher data in the following situations:

• You need fresher data: Reduce target lag to trigger more frequent refreshes.
• You want to reduce cost: Data that doesn’t need near real-time freshness can use a longer target lag. For example, a dynamic table that refreshes every 20 minutes but only needs to be within one hour of the source tables can use a one-hour target lag to reduce compute costs.
• Your pipeline has misaligned schedules: When your dynamic table depends on other tables with longer refresh intervals, align the target lag with those dependencies to avoid unnecessary refreshes.
• You’re seeing skipped refreshes: When refreshes take longer than your target lag, Snowflake skips some refreshes. Increase the target lag to match realistic refresh durations.

For more information, see Understanding dynamic table target lag.

To change the warehouse or target lag for a dynamic table, use the ALTER DYNAMIC TABLE command. For example:

-- Change the warehouse for my_dynamic_table to my_other_wh:
ALTER DYNAMIC TABLE my_dynamic_table SET
  WAREHOUSE = my_other_wh;

-- Specify the downstream target lag for a dynamic table:
ALTER DYNAMIC TABLE my_dynamic_table SET
  TARGET_LAG = DOWNSTREAM;