Evolve dynamic table pipelines¶

As requirements change, you need to modify dynamic table pipelines: updating the definition, adding columns, or changing the refresh mode. This page explains how to make those changes safely, what triggers reinitialization, and how changes cascade through a pipeline.

For the full syntax reference, see ALTER DYNAMIC TABLE and CREATE DYNAMIC TABLE.

The most common modification, changing the definition, requires CREATE OR REPLACE because ALTER DYNAMIC TABLE can’t modify the SELECT query. CREATE OR ALTER DYNAMIC TABLE (public preview) can also modify the definition without DROP semantics. See CREATE DYNAMIC TABLE (preview).

-- Same as the dt_orders definition in create.mdx, with a new column added
CREATE OR REPLACE DYNAMIC TABLE dt_orders
    TARGET_LAG = '10 minutes'
    WAREHOUSE = transform_wh
    REFRESH_MODE = INCREMENTAL
AS
    SELECT
        order_id, customer_id, order_date,
        ...
        order_date::DATE AS order_day  -- new column
    FROM raw_orders
    WHERE order_status != 'returned';

CREATE OR REPLACE DYNAMIC TABLE is atomic. Consumers (downstream dynamic tables and concurrent readers) see either the old definition or the new one, never a partial state. If the statement fails, the original dynamic table remains unchanged.

CREATE OR REPLACE always triggers reinitialization, even if the new definition is identical to the previous one. With INITIALIZE = ON_SCHEDULE, the table is empty until the initial refresh completes. Queries return a ‘not initialized’ error during this period. With the default INITIALIZE = ON_CREATE, the CREATE statement blocks until the initial refresh is done.

ALTER DYNAMIC TABLE vs. CREATE OR REPLACE¶

Some properties are mutable with ALTER DYNAMIC TABLE. Others require CREATE OR REPLACE, which reinitializes the dynamic table. Use the following table to determine which approach you need.

This table shows the most commonly changed properties. For the full list of ALTER-able properties, see ALTER DYNAMIC TABLE.

For property changes that don’t require reinitialization, use ALTER DYNAMIC TABLE:

ALTER DYNAMIC TABLE dt_orders SET
    TARGET_LAG = '5 minutes'
    WAREHOUSE = transform_wh_medium;

Modify the definition¶

To change the definition of a dynamic table, use CREATE OR REPLACE DYNAMIC TABLE. There is no ALTER syntax for modifying the definition.

The following example adds a discount_pct column and changes the filter logic:

CREATE OR REPLACE DYNAMIC TABLE dt_orders
    TARGET_LAG = '10 minutes'
    WAREHOUSE = transform_wh
    REFRESH_MODE = INCREMENTAL
AS
    SELECT
        order_id, customer_id, order_date,
        ...
        COALESCE(discount_pct, 0) AS discount_pct,  -- new column
        order_status
    FROM raw_orders
    WHERE order_status NOT IN ('returned', 'cancelled');  -- updated filter

For the full dt_orders column list, see Create a dynamic table.

What triggers reinitialization¶

Reinitialization is a forced full refresh that reprocesses all data from scratch. The following changes trigger reinitialization:

The following changes do not trigger reinitialization:

How changes cascade downstream¶

When you modify an upstream dynamic table, the effect depends on the type of change.

Property changes (ALTER): Changing TARGET_LAG or WAREHOUSE on an upstream dynamic table does not trigger reinitialization of downstream dynamic tables. Downstream tables continue to read from the upstream table’s output through snapshot-isolated refreshes. However, changing TARGET_LAG can alter how frequently the upstream data is refreshed, which affects freshness for the entire pipeline.

Recreation (CREATE OR REPLACE): Recreating an upstream dynamic table triggers reinitialization of that table. Downstream incremental dynamic tables also reinitialize on their next refresh, because the upstream table is treated as a new object. Downstream full-refresh dynamic tables are unaffected because they already reprocess all data on each refresh.

Refresh mode compatibility: An incremental downstream dynamic table can’t depend on a full-refresh upstream dynamic table unless the upstream table provides row-level change information through a system-derived unique key or an IMMUTABLE WHERE constraint (see Optimize input data for dynamic tables). If you change an upstream table’s refresh mode from INCREMENTAL to FULL, verify that downstream tables are compatible:

SHOW DYNAMIC TABLES LIKE 'dt_orders%';

+-----------------+--------------+
| name            | refresh_mode |
+-----------------+--------------+
| DT_ORDERS_DAILY| INCREMENTAL  |
+-----------------+--------------+

If the downstream table is INCREMENTAL and the upstream is now FULL, recreate the downstream table with REFRESH_MODE = FULL or verify that the upstream table provides a system-derived unique key or has an immutability constraint.

SELECT * for automatic schema evolution¶

Dynamic tables using SELECT * automatically adapt to most base table schema changes. Snowflake handles most schema changes incrementally without reinitialization.

Handled automatically (incremental refresh):

• Adding a column to the base table
• Dropping a column from the base table
• Type widening (for example, NUMBER(10) to NUMBER(38))
• Type narrowing (for example, NUMBER(20) to NUMBER(10)). The dynamic table retains the original wider type.
• Multiple sequential schema changes
• Changes propagate through the full pipeline graph. Each downstream dynamic table picks up the change incrementally on its next refresh: no cascading reinitialization occurs.

Existing rows receive NULL for newly added columns. No backfill occurs.

Handled automatically but triggers reinitialization:

• Dropping and re-adding a column with the same name (regardless of type)
• Adding a column with a DEFAULT value
• Adding a column to a base table when the dynamic table uses SELECT *, expr AS alias (the new column shifts ordinal positions)
• Changing a view definition that the dynamic table reads from (CREATE OR ALTER VIEW)
• Replacing the base table with CREATE OR REPLACE TABLE (creates a new table object)

Requires manual intervention (refresh fails):

• Incompatible type changes (for example, TEXT to INT in-place)
• Dynamic tables with explicit column specs in the CREATE statement
• Dropping a column referenced by CLUSTER BY

Use SELECT * EXCLUDE (col1, col2) to drop specific columns while retaining automatic evolution for everything else:

CREATE OR REPLACE DYNAMIC TABLE dt_orders
    TARGET_LAG = DOWNSTREAM
    WAREHOUSE = transform_wh
    REFRESH_MODE = INCREMENTAL
AS
    SELECT * EXCLUDE (internal_tracking_id, debug_flag)
    FROM raw_orders
    WHERE order_status != 'cancelled';

When a new column is added to raw_orders, dt_orders picks it up automatically on the next refresh. Dropped columns disappear without requiring CREATE OR REPLACE.

When to use explicit column lists instead:

• When you need to transform, rename, or cast specific columns
• When you need to control column order in the output
• When the base table contains sensitive columns you must not propagate

If schema evolution does not work as expected, contact Snowflake Support to verify that the schema evolution feature is enabled for your account.

Safe schema evolution workflow¶

For most schema changes, run CREATE OR REPLACE DYNAMIC TABLE. The operation is atomic: Snowflake creates the replacement as a hidden table, runs the initial refresh, then atomically swaps it in. Downstream tables see either the old or new version, never a partial state. Downstream incremental dynamic tables reinitialize automatically on their next refresh.

To evolve the schema without reinitializing, use IMMUTABLE WHERE to protect stable data while modifying the mutable region.

Advanced: when to suspend downstream¶

In most cases you do not need to suspend downstream tables. Consider suspending only when:

• dbt concurrent replacement races: Multiple dbt threads running CREATE OR REPLACE on related tables can create transient resolution failures. Suspend downstream tables before the batch and resume after.
• Cost control: If a downstream table is expensive to reinitialize (large data volume), suspend it to defer the reinitialization until an off-peak window.
• Streams on downstream dynamic tables: Streams on a dynamic table lose their offset when the table reinitializes. Suspend the downstream table, consume the stream first, then resume.

When you do suspend, complete all steps in the same session. If a suspended table exceeds MAX_DATA_EXTENSION_TIME_IN_DAYS without resuming, it must be fully recreated.

Rename a dynamic table¶

Renaming is useful when you want to replace a dynamic table while keeping existing scripts that reference the original name. Use ALTER DYNAMIC TABLE ... RENAME TO to rename a dynamic table.

Swap dynamic tables¶

Swapping exchanges two dynamic tables’ names and contents atomically. This is useful for blue-green deployments: build and validate a new version, then swap it into place.

You can only swap a dynamic table with another dynamic table. Use ALTER DYNAMIC TABLE <name> SWAP WITH <other_name> to perform the swap.

After the swap, dt_orders contains the data and definition that dt_orders_v2 had, and vice versa. Downstream tables that reference dt_orders by name now read from the swapped-in version.

Add clustering keys¶

You can add or change clustering keys on an existing dynamic table with ALTER DYNAMIC TABLE, and the change takes effect without reinitialization. Clustering keys can improve both query performance and refresh speed for dynamic tables that use incremental or full refresh modes.

For guidance on choosing clustering keys and measuring their effectiveness, see Optimize queries for incremental refresh and Optimize input data for dynamic tables.

What’s next¶

• To understand the differences between INCREMENTAL, FULL, and AUTO refresh modes, see Dynamic table refresh modes.
• To monitor refresh history and diagnose failures, see Monitor dynamic tables.
• To troubleshoot refresh failures after pipeline changes, see Troubleshoot dynamic table refresh issues.