Create dynamic tables¶

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 underlying database objects change, so does the dynamic table. If you recreate an object, you must re-enable change tracking.

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 Set the refresh mode for all production dynamic tables.

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

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

Understanding dynamic table initialization¶

When you create a dynamic table using a CREATE DYNAMIC TABLE statement, its initial refresh takes place either at a scheduled time or synchronously at creation. The initial data population, or initialization, depends on when this initial refresh occurs.

Dynamic tables undergo refresh based on the specified target lag, which sets the maximum allowed time for the dynamic table’s content to lag behind updates to the base tables. If you specify a dynamic table to refresh synchronously at creation (INITIALIZE = ON_CREATE), it’s initialized immediately. If you specify scheduled refresh (INITIALIZE = ON_SCHEDULE), it’s initialized within the time specified for the target lag.

For example, consider a dynamic table, DT1,with a target lag of 30 minutes. The initial data population for DT1 can occur as follows:

1. If DT1 is set to refresh synchronously at creation (default), it initializes upon creation.

2. If DT1 is set to refresh at a scheduled time, it initializes within the time specified for the target lag.

In scenarios involving downstream dependencies, the dynamics change. Consider dynamic tables DT1 and D2, where DT1 has downstream target lag, and DT2 has a target lag of 30 minutes and depends on DT1. DT1, with its downstream target lag, refreshes only when dependent dynamic tables, such as DT2, refresh.

For DT1 in this context:

1. If set to refresh synchronously at creation, it refreshes and initializes upon creation. If initialization fails, the table creation process halts, providing immediate feedback on any incorrect definitions.

2. If configured to refresh at a scheduled time, initialization depends on when DT2, the dependent table, undergoes refresh.

Initialization can take some time, depending on how much data is scanned. To track progress, you can query the refresh history using the DYNAMIC_TABLE_REFRESH_HISTORY function.