Supported queries for dynamic tables¶

This page lists which SQL constructs, functions, and data types are supported in each refresh mode. Use it before creating dynamic tables to predict whether a definition qualifies for incremental refresh.

ADAPTIVE has the same query construct requirements as incremental refresh: if a construct is listed as unsupported for incremental refresh, it is also unsupported for ADAPTIVE refresh.

If your definition contains a construct that is not supported for incremental refresh, Snowflake selects a full refresh instead, which reprocesses all data on every refresh. For cost implications, see Understanding costs for dynamic tables.

For guidance on how query patterns affect incremental refresh performance, see Optimize queries for incremental refresh.

Supported data types¶

Dynamic tables support all Snowflake SQL data types for both incremental and full refresh, except:

• Structured data types (structured OBJECT, structured ARRAY, and MAP). This limitation applies to both incremental and full refresh. Semi-structured types (VARIANT, OBJECT, ARRAY without a defined schema) are fully supported.
• Geospatial data types (full refresh only).

Incremental and full refresh support matrix¶

The following table shows which SQL constructs are supported in each refresh mode. Where a construct is supported for incremental refresh with restrictions, the table describes the specific conditions.

Examples for common constructs¶

The following examples demonstrate common SQL patterns that qualify for incremental refresh.

Example: LEFT JOIN with incremental refresh¶

The following example creates a dynamic table that LEFT JOINs order data with customer data. Because the LEFT JOIN uses an equality predicate, this definition qualifies for incremental refresh.

CREATE OR REPLACE DYNAMIC TABLE dt_orders_with_customers
    TARGET_LAG = '10 minutes'
    WAREHOUSE = transform_wh
    REFRESH_MODE = INCREMENTAL
AS
    SELECT
        o.order_id,
        o.order_date,
        o.product_name,
        o.quantity * o.unit_price AS line_total,
        c.customer_name,
        c.region
    FROM raw_orders o
    LEFT JOIN dim_customers c
        ON o.customer_id = c.customer_id;

Orders without a matching customer appear in the results with NULL values for customer_name and region. Snowflake tracks changes to both raw_orders and dim_customers.

Example: UNION ALL with incremental refresh¶

The following example combines rows from two base tables using UNION ALL. Both branches must produce compatible column lists.

-- Base tables (simplified setup)
CREATE OR REPLACE TABLE raw_orders_us (
    order_id INT, customer_id INT, order_date TIMESTAMP_NTZ,
    product_name VARCHAR, quantity INT, unit_price DECIMAL(10,2),
    region VARCHAR DEFAULT 'US'
);

CREATE OR REPLACE TABLE raw_orders_eu (
    order_id INT, customer_id INT, order_date TIMESTAMP_NTZ,
    product_name VARCHAR, quantity INT, unit_price DECIMAL(10,2),
    region VARCHAR DEFAULT 'EU'
);

CREATE OR REPLACE DYNAMIC TABLE dt_combined_orders
    TARGET_LAG = '10 minutes'
    WAREHOUSE = transform_wh
    REFRESH_MODE = INCREMENTAL
AS
    SELECT order_id, customer_id, order_date, product_name, quantity, unit_price, region
    FROM raw_orders_us
    UNION ALL
    SELECT order_id, customer_id, order_date, product_name, quantity, unit_price, region
    FROM raw_orders_eu;

Snowflake tracks changes to both raw_orders_us and raw_orders_eu independently. New rows inserted into either table are processed incrementally.

Example: LATERAL FLATTEN with incremental refresh¶

The following example flattens a semi-structured VARIANT column using LATERAL FLATTEN, which is supported for incremental refresh.

First, create a base table with nested JSON data:

CREATE OR REPLACE TABLE persons
 AS
  SELECT column1 AS id, parse_json(column2) AS entity
  FROM values
   (12712555,
   '{ name:  { first: "John", last: "Smith"},
     contact: [
     { business:[
       { type: "phone", content:"555-1234" },
       { type: "email", content:"j.smith@example.com" } ] } ] }'),
   (98127771,
    '{ name:  { first: "Jane", last: "Doe"},
     contact: [
     { business:[
       { type: "phone", content:"555-1236" },
       { type: "email", content:"j.doe@example.com" } ] } ] }');

CREATE OR REPLACE DYNAMIC TABLE dt_flattened_contacts
 TARGET_LAG = DOWNSTREAM
 WAREHOUSE = transform_wh
 AS
  SELECT p.id, f.value, f.path
  FROM persons p,
  LATERAL FLATTEN(input => p.entity) f;

Supported non-deterministic functions¶

Many non-deterministic functions are supported for incremental refresh. Timestamp functions (CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME) work in WHERE, HAVING, and QUALIFY clauses. Session-context functions (CURRENT_USER, CURRENT_ROLE, CURRENT_WAREHOUSE) and sequence functions are restricted. The following table shows the full matrix.

User-defined functions¶

UDFs are supported in dynamic table definitions. Whether a UDF supports incremental refresh depends on the function type and volatility:

Scalar UDFs:

• IMMUTABLE: compatible with incremental refresh
• VOLATILE (the default): force full refresh
• SQL UDFs containing subqueries: dynamic table creation fails

Table functions (UDTFs):

• Supported in lateral joins only
• UDTFs always force full refresh regardless of volatility

UDFs default to VOLATILE. To enable incremental refresh, create the function with the IMMUTABLE property. Only use IMMUTABLE if the function always returns the same output for the same input. Snowflake doesn’t validate this, and incorrect use can produce wrong results.

If you replace an IMMUTABLE UDF that an existing dynamic table uses, subsequent incremental refreshes fail. Recreate the dynamic table after replacing the UDF.

For details, see User-defined functions overview.

Supported Snowflake Cortex AI functions¶

You can use Snowflake Cortex AI Functions (including LLM functions) in the SELECT clause for dynamic tables in incremental refresh mode. The same availability restrictions as described in Cortex AI functions apply.

Cortex AI functions let you add AI-powered insights directly to your dynamic tables, automatically analyzing data as it refreshes. For example, you can classify customer reviews, support tickets, or survey responses.

In the following example, dt_review_sentiment uses AI_FILTER to evaluate each review with an LLM. The output column enjoyed indicates whether the reviewer enjoyed the restaurant, based on the prompt.

CREATE OR REPLACE TABLE reviews AS
  SELECT 'Wow... Loved this place.' AS review
  UNION ALL
  SELECT 'The pizza is not good.' AS review;

CREATE OR REPLACE DYNAMIC TABLE dt_review_sentiment
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = transform_wh
  REFRESH_MODE = INCREMENTAL
  AS
    SELECT review, AI_FILTER(CONCAT('The reviewer enjoyed the restaurant', review), {'model': 'llama3.1-70b'}) AS enjoyed FROM reviews;

What causes fallback to full refresh¶

When you set REFRESH_MODE = INCREMENTAL or REFRESH_MODE = AUTO, Snowflake evaluates whether the definition supports incremental refresh at creation time. With INCREMENTAL, creation fails if unsupported. With AUTO, creation succeeds but resolves to FULL. The following conditions cause Snowflake to select full refresh instead:

To identify why a dynamic table resolved to full refresh, query the refresh_mode_reason column:

SHOW DYNAMIC TABLES LIKE 'dt_orders';

+-------------------+-------------------------------------------+
| refresh_mode      | refresh_mode_reason                       |
|-------------------+-------------------------------------------|
| FULL              | QUERY_NOT_SUPPORTED_FOR_INCREMENTAL       |
+-------------------+-------------------------------------------+

Common refresh_mode_reason values include:

Additional values may appear in future releases.

What happens when you use an unsupported construct with INCREMENTAL¶

If you explicitly set REFRESH_MODE = INCREMENTAL and the definition contains a construct that is not supported for incremental refresh, the CREATE DYNAMIC TABLE statement fails with an error:

CREATE OR REPLACE DYNAMIC TABLE dt_unsupported_example
    TARGET_LAG = '10 minutes'
    WAREHOUSE = transform_wh
    REFRESH_MODE = INCREMENTAL
AS
    SELECT order_id, order_date
    FROM raw_orders
    EXCEPT
    SELECT order_id, order_date
    FROM excluded_orders;

002711 (0A000): SQL compilation error:
  Dynamic Table 'DT_UNSUPPORTED_EXAMPLE' is defined with REFRESH_MODE = INCREMENTAL,
  but the query is not supported for incremental refresh.

When you use REFRESH_MODE = AUTO instead, the same definition succeeds but resolves to FULL.

Incremental refresh constraints¶

The following constraints apply specifically to dynamic tables that use incremental refresh.

Incremental refresh on upstream full-refresh dynamic tables¶

Dynamic tables in incremental refresh mode can’t consume an upstream dynamic table that uses full refresh unless the upstream full-refresh dynamic table has a system-derived unique key or a frozen region. When either condition is met, Snowflake computes row-level changes across full refreshes, enabling downstream incremental processing.

To use this capability, set REFRESH_MODE = INCREMENTAL explicitly on the downstream dynamic table. AUTO doesn’t resolve to incremental in this scenario.

For more information, see Optimize input data for dynamic tables.

Masking and row access policies¶

Masking or row access policies on a dynamic table don’t affect its refresh mode. However, policies applied on base tables might affect the refresh mode:

• Incremental refresh is supported if the policies on base tables use the CURRENT_ROLE or IS_ROLE_IN_SESSION function.
• Incremental refresh isn’t supported if the policies on base tables use any other functions, INFORMATION_SCHEMA views, or query a table (for example, a mapping table lookup).
• Changes to the policies on base objects of dynamic tables with incremental refresh trigger reinitialization.

Replication¶

Replicated dynamic tables with incremental refresh reinitialize after failover before they can resume incremental refresh.

For more information, see Replication and dynamic tables.

Cloning¶

Cloned incremental dynamic tables might need to reinitialize during their initial refresh after being created.

If a dynamic table is cloned from another dynamic table with dropped base tables, the clone is suspended and can’t be resumed or refreshed.

What’s next¶

• To optimize query patterns for incremental refresh performance, see Optimize queries for incremental refresh.
• To take full control of refresh logic with custom MERGE or INSERT statements, see Custom incrementalization.
• To understand the cost differences between incremental and full refresh, see Understanding costs for dynamic tables.
• To troubleshoot refresh issues, see Troubleshoot dynamic table refresh issues.
• For the full CREATE DYNAMIC TABLE syntax, see CREATE DYNAMIC TABLE.