Create dynamic tables¶

Limitations and considerations for using Iceberg tables¶

All limitations for regular dynamic tables and dynamic Iceberg tables still apply.

Additionally:

• All limitations for Iceberg base tables apply. For more information, see Considerations and limitations.

• You can create a dynamic table that reads from Snowflake native tables, Snowflake-managed Iceberg tables, and externally managed Iceberg tables.

• Dynamic tables track changes at the file level for externally managed Iceberg base tables, unlike other base tables that track changes at the row level. Frequent copy-on-write operations (for example, updates or deletes) on externally managed Iceberg tables may impact the performance of incremental refreshes.

Examples of using immutability constraints¶

The following example creates a dynamic table and sets it as immutable by using the IMMUTABLE WHERE parameter.

CREATE DYNAMIC TABLE my_dynamic_table (id1 INT)
  TARGET_LAG = '20 minutes'
  WAREHOUSE = mywh
  IMMUTABLE WHERE ( <expr> )
  AS
    SELECT id AS id1 FROM staging_table;

Columns referenced in the IMMUTABLE WHERE condition must be columns in the dynamic table, not columns from the base table. For example, the IMMUTABLE WHERE expression in the example above can only use id1, not id.

You can only set a single IMMUTABLE WHERE condition on a dynamic table. To replace an existing predicate, you use the ALTER DYNAMIC TABLE command as shown in the following example:

-- Set or replace an existing predicate:
ALTER DYNAMIC TABLE my_dynamic_table SET IMMUTABLE WHERE ( <expr> );

-- Remove an existing predicate:
ALTER DYNAMIC TABLE my_dynamic_table UNSET IMMUTABLE;

To view the immutability constraints on your dynamic tables, run the SHOW DYNAMIC TABLES command. The immutable_where column shows the IMMUTABLE WHERE constraint set on the table, or NULL if none is set.

For information about compute cost, see Compute cost for immutability constraints.

CREATE DYNAMIC TABLE joined_data
  TARGET_LAG = '1 minute'
  WAREHOUSE = mywh
  IMMUTABLE WHERE (timestamp_col < CURRENT_TIMESTAMP() - INTERVAL '1 day')
  AS
    SELECT F.primary_key primary_key, F.timestamp_col timestamp_col, D.value dim_value
    FROM fact_table F
    LEFT OUTER JOIN dimension_table D USING (primary_key);

CREATE TABLE staging_data (raw TEXT, ts TIMESTAMP);

CREATE DYNAMIC TABLE parsed_data
  TARGET_LAG = '1 minute'
  WAREHOUSE = mywh
  IMMUTABLE WHERE (ts < CURRENT_TIMESTAMP() - INTERVAL '7 days')
  AS
    SELECT parse_json(raw):event_id::string event_id, parse_json(raw):name::string name, parse_json(raw):region::string region, ts
    FROM staging_data WHERE region = 'US';

-- Delete old staging data using Task
CREATE TASK delete_old_staging_data
  WAREHOUSE = mywh
  SCHEDULE = '24 hours'
  AS
    DELETE FROM staging_data WHERE ts < CURRENT_TIMESTAMP() - INTERVAL '30 days';

CREATE FUNCTION my_udtf(x varchar)
  RETURNS TABLE (output VARCHAR)
  LANGUAGE PYTHON
  AS $$ ... $$;

CREATE DYNAMIC TABLE udtf_dt
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = FULL
  IMMUTABLE WHERE (ts < current_timestamp() - interval '1 day')
  AS
    SELECT ts, data, output, join_key FROM input_table, TABLE(my_udtf(data));

CREATE DYNAMIC TABLE incremental_join_dt
  TARGET_LAG = '1 hour'
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
  IMMUTABLE WHERE (ts < current_timestamp() - interval '1 day')
  AS
    SELECT * FROM udtf_dt JOIN dim_table USING (join_key);

Limitations and considerations when setting immutability constraints¶

All limitations of regular dynamic tables still apply.

Additionally:

• There can only be a single IMMUTABLE WHERE predicate on a dynamic table. Setting another one with an ALTER DYNAMIC TABLE … SET IMMUTABLE WHERE command replaces any existing predicate.

• IMMUTABLE WHERE constraints can’t contain the following items:

  • Subqueries.

  • Nondeterministic functions, except for timestamp functions like CURRENT_TIMESTAMP()` or CURRENT_DATE(). If you use timestamp functions, you must use them so that the immutable region grows over time. For example, TIMESTAMP_COL < CURRENT_TIMESTAMP() is allowed but TIMESTAMP_COL > CURRENT_TIMESTAMP() isn’t.

  • User-defined or external functions.

  • Metadata columns; for example, those starting with METADATA$.

  • Columns that are the result of aggregates, window functions or non-deterministic functions, or columns that are passed through a window function operator. In the following dynamic tables, only col3 can be used in IMMUTABLE WHERE predicates:

    CREATE DYNAMIC TABLE aggregates TARGET_LAG = '1 minute' WAREHOUSE = mywh
      AS SELECT col1, SUM(col2) AS col2 FROM input_table
      GROUP BY col3;
    
    CREATE DYNAMIC TABLE window_fns TARGET_LAG = '1 minute' WAREHOUSE = mywh
      AS SELECT col3, SUM(col4) OVER (PARTITION BY col3 ORDER BY col4) AS col2, col5 FROM input_table;
    

    Copy

IMMUTABLE WHERE constraints are copied during cloning and replication with no limitations.

Examples of using backfill¶

The following examples show how to create new dynamic tables from tables with backfilled data.

Each column name must be found in the backfill table with compatible data types and must appear in the same order as the backfill table. Table properties and privileges from the backfill table aren’t copied.

If Time Travel parameters AT | BEFORE are specified, data from the backfill table is copied at the specified time.

CREATE DYNAMIC TABLE my_dynamic_table (day TIMESTAMP, totalSales NUMBER)
  IMMUTABLE WHERE (day < '2025-01-01')
  BACKFILL FROM my_backfill_table
  TARGET_LAG = '20 minutes'
  WAREHOUSE = 'mywh'
  AS SELECT DATE_TRUNC('day', ts) AS day, sum(price)
    FROM my_base_table
    GROUP BY day;

CREATE OR REPLACE TABLE sales(item_id INT, ts TIMESTAMP, sales_price FLOAT);

INSERT INTO sales VALUES (1, '2025-05-01 01:00:00', 10.0), (1, '2025-05-01 02:00:00', 15.0), (1, '2025-05-01 03:00:00', 11.0);
INSERT INTO sales VALUES (1, '2025-05-02 00:00:00', 11.0), (1, '2025-05-02 05:00:00', 13.0);


CREATE DYNAMIC TABLE my_dynamic_table
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = mywh
  INITIALIZE = on_create
  IMMUTABLE WHERE (day <= '2025-05-01')
  AS
    SELECT item_id, date_trunc('DAY', ts) day, count(sales_price) AS sales_count FROM sales
    GROUP BY item_id, day;

SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dynamic_table;

+---------+------------+-------------+
| ITEM_ID | DAY        | SALES_COUNT |
|---------+------------+-------------|
| 1       | 2025-05-01 | 3           |
| 1       | 2025-05-02 | 2           |
|---------+-------------+------------|

DELETE FROM sales WHERE ts < '2025-05-02';

ALTER DYNAMIC TABLE my_dynamic_table REFRESH;

SELECT item_id, to_char(day, 'YYYY-MM-DD') AS day, sales_count FROM my_dynamic_table;

Limitations and considerations for using backfill¶

All limitations of regular dynamic tables and the limitations of immutability constraints still apply.

Additionally, the following limitations and considerations apply:

• Only regular tables are currently supported as backfill tables.

• You can’t specify policies or tags in the new dynamic table because they are copied from the backfill table.

• Clustering keys in the new dynamic table and backfill table must be the same.

• If you create a dynamic table with BACKFILL FROM, you can’t remove its IMMUTABLE WHERE constraint later.

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.

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:

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

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

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

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.