Alter existing 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;

Add clustering keys to dynamic tables¶

Adding clustering keys to dynamic tables can enhance performance by improving query efficiency and refresh operations:

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

• Refresh operations: Clustering keys can help speed up refreshes 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);