Evolve dynamic table pipelines¶

This topic describes structural changes to dynamic tables in a pipeline, including renaming, swapping, and adding clustering keys.

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);