Supported queries for dynamic tables¶

Dynamic tables support standard SQL expressions and Snowflake-supported functions, including mathematical operations, string functions, date functions, etc. This topic describes the expressions, constructs, functions, operators, and clauses that dynamic tables support in incremental and full refresh modes.

If a query uses expressions, keywords, operators, or clauses that are not supported for incremental refresh, the automated refresh process uses a full refresh instead, which might incur an additional cost.

For guidance on how different operators 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, 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).

Supported queries in incremental and full refresh modes¶

CREATE 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 DYNAMIC TABLE my_dynamic_table
 TARGET_LAG = DOWNSTREAM
 WAREHOUSE = mywh
 AS
  SELECT p.id, f.value, f.path
  FROM persons p,
  LATERAL FLATTEN(input => p.entity) f;

Supported non-deterministic functions in incremental and full refresh modes¶

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 updates. For example, it can classify customer reviews, support tickets, or survey responses as positive/negative or assign categories.

In the following example, review_sentiment uses AI_FILTER to evaluate each review with an LLM. Cortex AI Functions combine the prompt The reviewer enjoyed the restaurant with the actual review text. The output column enjoyed is the classification generated using Cortex AI Functions based on the prompt, indicating whether the reviewer enjoyed the restaurant.

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 review_sentiment
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
  AS
    SELECT review, AI_FILTER(CONCAT('The reviewer enjoyed the restaurant', review), {'model': 'llama3.1-70b'}) AS enjoyed FROM reviews;

Support for incremental refresh¶

Dynamic tables support two refresh modes: incremental and full. You can either set the refresh mode to AUTO or set it explicitly. For more information, see Dynamic table refresh modes and Choose a refresh mode.

Incremental refresh on full-refresh dynamic tables¶

Dynamic tables in incremental refresh mode can’t consume an upstream dynamic table with full refresh mode unless the upstream full-refresh dynamic table has a system-derived reliable unique key. When such a reliable unique key exists, Snowflake can compute row-level changes across full refreshes, enabling downstream incremental processing.

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

For more information, see Understanding primary keys in 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 first refresh after being created.

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