Create dynamic tables¶

This topic outlines the key concepts for creating dynamic tables.

Before you begin, ensure you have the privileges for creating dynamic tables, and all objects used by the dynamic table query have change tracking enabled.

Some limitations might apply to creating dynamic tables. For a complete list, see Dynamic table limitations.

Enable change tracking¶

When creating a dynamic table with incremental refresh mode, if change tracking is not already enabled on the tables that it queries, Snowflake automatically attempts to enable change tracking on them. In order to support incremental refreshes, change tracking must be enabled with non-zero time travel retention on all underlying objects used by a dynamic table.

As base objects change, so does the dynamic table. If you recreate a base object, you must re-enable change tracking.

Note

Snowflake doesn’t automatically attempt to enable change tracking on dynamic tables created with full refresh mode.

To enable change tracking on a specific database object, use ALTER TABLE, ALTER VIEW, and similar commands on that object. The user creating the dynamic table must have the OWNERSHIP privilege to enable change tracking on all underlying objects.

To check if change tracking is enabled, use SHOW VIEWS, SHOW TABLES, and similar commands on the underlying objects, and inspect the change_tracking column.

Syntax for creating dynamic tables¶

Suppose that you want to create a dynamic table named product that contains the product_id and product_name columns from the table named staging_table, and you decide:

You want the data in the product table to be at most 20 minutes behind the data in staging_table.
You want to use the warehouse mywh for the compute resources needed for the refresh.
You want the refresh mode to be automatically chosen.
- Snowflake recommends using the automatic refresh mode only during development. For more information, see Best practices for choosing dynamic table refresh modes.
You want the dynamic table to refresh synchronously at creation.
You want the refresh mode to be automatically chosen, and you want the dynamic table to refresh synchronously at creation.

To create this dynamic table, you would execute the following CREATE DYNAMIC TABLE SQL statement:

CREATE OR REPLACE DYNAMIC TABLE product
  TARGET_LAG = '20 minutes'
  WAREHOUSE = mywh
  REFRESH_MODE = auto
  INITIALIZE = on_create
  AS
    SELECT product_id, product_name FROM staging_table;

Copy

For a complete list of parameters and variant syntax, see the CREATE DYNAMIC TABLE reference.

Best practices for creating dynamic tables¶

Chain together pipelines of dynamic tables¶

When defining a new dynamic table, rather than defining a large dynamic table with many nested statements, use small dynamic tables with pipelines instead.

You can set up a dynamic table to query other dynamic tables. For instance, imagine a scenario where your data pipeline extracts data from a staging table to update various dimension tables (e.g., customer, product, date and time). Additionally, your pipeline updates an aggregate sales table based on the information from these dimension tables. By configuring the dimension tables to query the staging table and the aggregate sales table to query the dimension tables, you create a cascade effect similar to a task graph.

In this setup, the refresh for the aggregate sales table executes only after the refreshes for the dimension tables have successfully completed. This ensures data consistency and meets lag targets. Through an automated refresh process, any changes in the source tables trigger refreshes in all dependent tables at the appropriate times.

Comparison between task graphs and dynamic tables DAGs

Use a “controller” dynamic table for complex task graphs¶

When you have a complex graph of dynamic tables with many roots and leaves and you want to perform operations (e.g. changing lag, manual refresh, suspension) on the full task graph with a single command, do the following:

Set the value for the TARGET_LAG of all of your dynamic tables to DOWNSTREAM.

Create a “controller” dynamic table that reads from all of the leaves in your task graph. To ensure this controller doesn’t consume resources, do the following:

CREATE DYNAMIC TABLE controller
  TARGET_LAG = <target_lag>
  WAREHOUSE = <warehouse>
  AS
    SELECT 1 A FROM <leaf1>, …, <leafN> LIMIT 0;

Copy

Use the controller to control the whole graph. For example:

Set a new target lag for the task graph.
ALTER DYNAMIC TABLE controller SET
  TARGET_LAG = <new_target_lag>;
Copy
Manually refresh the task graph.
ALTER DYNAMIC TABLE controller REFRESH;
Copy

About cloning pipelines of dynamic tables¶

Clone all elements of the dynamic table pipeline in the same clone command to avoid reinitializations of your pipeline. You can do this by consolidating all elements of the pipeline (e.g. base tables, view, and dynamic tables) in the same schema or database.

Use transient dynamic tables to reduce storage cost¶

Transient dynamic tables maintain data reliably over time and support Time Travel within the data retention period, but don’t retain data beyond the fail-safe period. By default, dynamic table data is retained for seven days in fail-safe storage.

For dynamic tables with high refresh throughput, this can significantly increase storage consumption. Therefore, you should make a dynamic table transient only if its data doesn’t need the same level of data protection and recovery provided by permanent tables.

You can create a transient dynamic table or clone existing dynamic tables to transient dynamic tables using the CREATE DYNAMIC TABLE statement.

Troubleshoot dynamic table creation¶

When you create a dynamic table, the initial refresh happens either on a schedule (ON_SCHEDULE) or immediately at creation (ON_CREATE). The initial data population, or initialization, depends on when this initial refresh occurs. For example, for ON_CREATE, initialization might take longer if it triggers refreshes of upstream dynamic tables.

Initialization can take some time, depending on how much data is scanned. To view progress, do the following:

Sign in to Snowsight.
In the navigation menu, select Monitoring » Query History.
In the Filters dropdown, enter CREATE DYNAMIC TABLE in the SQL Text filter and enter your warehouse name in the Warehouse filter.
Select the query with your dynamic table under SQL text and use the Query Details and Query Profile tabs to track progress.