Use immutability constraints on dynamic tables¶

Example: Use IMMUTABLE WHERE to prevent recomputation of old data if a dimension table changes¶

In the following example, updating a row in the dimension table causes facts from the last day that join with it to be reprocessed, rather than all historical facts:

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

Example: Unlimited retention dynamic table and limited retention base table¶

The following example creates a staging table with a limited window of recently added data and a dynamic table that stores all parsed and filtered data:

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

Example: Use IMMUTABLE WHERE to enable incremental downstream refreshes when the upstream dynamic table runs in full refresh mode¶

Some query constructs, such as Python user-defined table functions (UDTFs) require a dynamic table to run in full-refresh mode. Typically, this limitation prevents incremental processing. To let downstream tables remain incremental, you can define an immutable region using the IMMUTABLE WHERE clause as shown in the following example. This way, downstream tables still benefit from performance optimizations, even when the upstream table requires full refreshes.

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¶

• The IMMUTABLE WHERE predicate is ignored during the initial refresh but applies to all subsequent refreshes. In full refresh mode, the predicate limits recomputation to only the rows that don’t match the condition. Streams and dynamic tables in incremental refresh mode can read from these full refresh tables.

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

• All limitations of regular dynamic tables still apply. Additionally, the following limitations apply:

  • There can only be a single IMMUTABLE WHERE predicate on a dynamic table. Setting another predicate 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 can’t shrink 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 col3, 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

  • You can only set a single IMMUTABLE WHERE condition on a dynamic table. To replace an existing predicate, 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> );
    

    Copy

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

    Copy