About managing dynamic tables¶

Change the warehouse or target lag for dynamic tables¶

You might want to adjust the warehouse for a dynamic table 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 the target lag for a dynamic table for any of the following reasons:

• You need more up-to-date data. Reducing the target lag ensures the dynamic table refreshes more frequently, providing fresher data closer to real time.

• You want to optimize resources. Increasing the target lag can reduce the frequency of refreshes, saving compute resources, which is useful for workloads that don’t need constant updates or where data changes infrequently. For example, if a dynamic table refreshes every 20 minutes but only needs to be within one hour of the source tables, setting a one-hour target lag can lower costs.

• You want the target lag to align with the data pipeline. If your dynamic table depends on other tables that have longer refresh intervals, adjusting the target lag ensures it aligns with dependencies.

For more information, see Understanding 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;

For additional details and examples of using downstream target lag, see Understanding target lag.

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;