Suspend or resume dynamic tables¶

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;