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.

Supported data types¶

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

Structured data types.
Geospatial data types (full refresh only).

Supported queries in incremental and full refresh modes¶

Keyword	Incremental Refresh Mode	Full Refresh Mode
DISTINCT	Supported	Supported
External functions	Not supported	Not supported
FROM	Source tables, views, Snowflake-managed Apache Iceberg™ tables, and other dynamic tables. Subqueries outside of FROM clauses (for example, WHERE EXISTS) are not supported.	Supported
GROUP BY	Supported	Supported
CROSS JOIN	Supported. You can specify any number of tables in the join, and updates to all tables in the join are reflected in the results of the query.	Supported
INNER JOIN	Supported. You can specify any number of tables in the join, and updates to all tables in the join are reflected in the results of the query.	Supported
LATERAL JOIN	Not supported. However, you can use LATERAL with FLATTEN(). For example: 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" } ] } ] }'); Copy 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; Copy Note the following behavior for using lateral flatten with incremental refresh: Selecting the flatten SEQ column from a lateral flatten join is not supported. When using the AUTO parameter, Snowflake typically chooses incremental refresh for queries with lateral flatten joins, unless prevented by other limitations.	Supported.
OUTER-EQUI JOIN.	Supported. You can specify any number of tables in the join, and updates to all tables in the join are reflected in the results of the query.	Supported
[{LEFT \| RIGHT \| FULL }] OUTER JOIN	The following is not supported: Outer joins where both sides are the same table. Outer joins where both sides are a subquery with GROUP BY clauses. Outer joins with non-equality predicates. Otherwise, you can specify any number of tables in an outer join, and updates to all tables in the join are reflected in the results of the query.	Supported
ML or LLM functions	Supported in the SELECT clause.	Supported
PIVOT and UNPIVOT	Not supported	Not supported
SAMPLE / TABLESAMPLE	Not supported	Not supported
Scalar Aggregates	Supported	Supported
SELECT	Expressions including those using deterministic built-in functions and immutable user-defined functions.	Supported
Set operators (UNION, MINUS, EXCEPT, INTERSECT)	Not supported, except for UNION. In incremental refresh, the UNION set operator works like the combination of the UNION ALL and SELECT DISTINCT operators.	Supported
Sequences.	Not supported	Not supported
All subquery operators.	Not supported	Supported
UNION ALL	Supported	Supported
User-defined functions (UDFs)	Supported, except for the following limitations: UDFs written in Python, Java, Scala, or Javascript that specify the VOLATILE parameter are not supported. UDFs written in SQL that contain subqueries are not supported (for example, a SELECT statement). Replacing an IMMUTABLE UDF while it’s in use by a dynamic table that uses incremental refresh results in failed refreshes. Importing UDFs from an external stage is not supported.	Supported
User-defined table functions (UDTFs)	Supported, except for the following limitations: UDTFs written in SQL are not supported. SELECT blocks that read from UDTFs must explicitly specify columns and can’t use `*`.	Supported
WHERE / HAVING / QUALIFY	Filters with the same expressions that are valid in SELECT are supported. Filters with the CURRENT_TIMESTAMP, CURRENT_TIME, and CURRENT_DATE functions and their aliases are supported.	Supported. Filters with the CURRENT_TIMESTAMP, CURRENT_TIME, and CURRENT_DATE functions and their aliases are supported.
Window functions	Supported, except for the following limitations: Using the window functions PERCENT_RANK, DENSE_RANK, RANK with sliding window frames is not supported. Using ANY_VALUE is not supported since it’s a non-deterministic function.	Supported
WITH	Common table expressions (CTEs) that use incremental refresh supported features in the subquery are supported. WITH RECURSIVE is not supported.	Supported

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

Non-deterministic Function	Incremental Refresh Mode	Full Refresh Mode
ANY_VALUE	Not supported	Not supported
CLASSIFY_TEXT (SNOWFLAKE.CORTEX)	Supported in the SELECT clause	Supported
COMPLETE (SNOWFLAKE.CORTEX)	Supported in the SELECT clause	Supported
CURRENT_ACCOUNT	Not supported	Supported
CURRENT_DATE (and aliases)	Supported only as a part of a WHERE/HAVING/QUALIFY clause.	Supported only as a part of a WHERE/HAVING/QUALIFY clause.
CURRENT_REGION	Not supported	Supported
CURRENT_ROLE	Not supported	Supported
CURRENT_TIME (and aliases)	Supported only as a part of a WHERE/HAVING/QUALIFY clause.	Supported only as a part of a WHERE/HAVING/QUALIFY clause.
CURRENT_TIMESTAMP (and aliases)	Supported only as a part of a WHERE/HAVING/QUALIFY clause.	Supported only as a part of a WHERE/HAVING/QUALIFY clause.
Functions that rely on CURRENT_USER.	Not supported. Dynamic table refreshes act as their owner role with a special SYSTEM user.	Not supported. Dynamic table refreshes act as their owner role with a special SYSTEM user.
CURRENT_WAREHOUSE	Not supported	Supported
DENSE_RANK	Supported	Supported
EMBED_TEXT_768 (SNOWFLAKE.CORTEX)	Supported in the SELECT clause	Supported
EMBED_TEXT_1024 (SNOWFLAKE.CORTEX)	Supported in the SELECT clause	Supported
EXTRACT_ANSWER (SNOWFLAKE.CORTEX)	Supported in the SELECT clause	Supported
FINETUNE (SNOWFLAKE.CORTEX)	Supported in the SELECT clause	Supported
FIRST_VALUE	Supported	Supported
LAST_VALUE	Supported	Supported
NTH_VALUE	Supported	Supported
RANK	Supported	Supported
ROW_NUMBER	Supported	Supported
SENTIMENT (SNOWFLAKE.CORTEX)	Supported in the SELECT clause	Supported
Sequence functions (e.g., `SEQ1`, `SEQ2`)	Not supported	Supported
TRANSLATE (SNOWFLAKE.CORTEX)	Supported in the SELECT clause	Supported
VOLATILE user-defined functions	Not supported	Supported

Supported Snowflake Cortex AISQL functions¶

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

Cortex AISQL lets 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 AISQL combines the prompt The reviewer enjoyed the restaurant with the actual review text. The output column enjoyed is the classification generated using Cortex AISQL 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;

Copy