About managing dynamic tables¶

Alter the warehouse or target lag for dynamic tables¶

You might want to adjust your dynamic tables’ warehouse for cost efficiency or performance boost. For more information, see Compute cost and How warehouse configurations affect dynamic table performance.

You might want to adjust your dynamic table’s target lag to get fresher data, reduce compute costs, or better align with the refresh intervals of upstream dependencies. 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;

Rename dynamic tables¶

Renaming a dynamic table can be useful in scenarios where you have scripts or applications that rely on a specific table name, and you want to update the dynamic table without changing your existing script. For example, if you have a script that references a specific dynamic table name, renaming the table allows you to swap out the underlying table while keeping the script unchanged. This ensures continuity and avoids the hassle of updating multiple references across scripts or processes.

To rename a dynamic table, use the ALTER DYNAMIC TABLE … RENAME TO command. For example:

ALTER DYNAMIC TABLE my_dynamic_table RENAME TO my_new_dynamic_table;

Swap dynamic tables¶

Swapping dynamic tables allows for a seamless transition between datasets or table versions without disrupting workflows or modifying dependent scripts. For example, if you’re developing a new version of a table but want to keep the same name for ongoing processes, swapping lets you replace the old table with the new one. This approach ensures continuity while enabling updates, testing, or upgrades with minimal downtime or disruption.

To swap a dynamic table, use the ALTER DYNAMIC TABLE … SWAP WITH command. Note that you can only swap a dynamic table with another dynamic table.

For example:

-- Swap my_dynamic_table with the my_new_dynamic_table:
ALTER DYNAMIC TABLE my_dynamic_table SWAP WITH my_new_dynamic_table;

Cluster dynamic tables¶

Clustering dynamic tables can enhance performance by improving query efficiency and refresh operations:

• Query efficiency: Clustering dynamic tables can help speed up queries, just like with regular tables, by clustering on common join keys or filter columns.

• Refresh operations: Clustering can also help make refreshes faster if the clustering keys align with frequent change patterns; for example, clustering by user ID can be effective when you have updates where a handful of users change.

Clustering keys can be specified for a dynamic table with incremental or full refresh mode. In full refresh, the clustering is performed during the refresh and background reclustering isn’t needed.

To cluster a dynamic table, use the ALTER DYNAMIC TABLE … CLUSTER BY command:

ALTER DYNAMIC TABLE my_dynamic_table CLUSTER BY (date);

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 drop my_dynamic_table.

UNDROP DYNAMIC TABLE my_dynamic_table;

Understanding the effects of changes to columns in base tables¶

The columns in the base tables, views, and underlying dynamic tables can change over time. Some changes can affect the dynamic table itself; others may have limited or no impact.

When the underlying objects associated with a dynamic table change, the following behaviors apply:

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;