Create dynamic tables

This topic outlines the key concepts for creating dynamic tables and what occurs during the initialization process.

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 Known limitations for 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.

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.

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

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 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, it is initialized immediately. However, if a dynamic table is set to refresh at a scheduled time, it is 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.

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:

Change

Impact

  • New column added.

  • Existing unused column removed.

None. If a new column is added to the base table or an unused column is deleted, no action occurs and refreshes continue as before.

  • Underlying base table is recreated with identical column names and types.

  • Underlying base table column is recreated with the same name and type.

Full refresh/reinitialize: During the next refresh cycle, a full refresh is done to ensure that no incorrect or stale data is in the dynamic table.

  • An underlying column or other element used by a dynamic table changes in name or in some other way.

The state of the dynamic table changes to FAILING. The dynamic table must be recreated to respond to the change. For more information about dynamic table states, see Dynamic table states.