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

Not supported

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

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:

  • Multiple window functions in the same dynamic table definition, where PARTITION BY clauses are either non-identical or they appear in separate query blocks are not supported.

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

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)

Not supported

Supported

COMPLETE (SNOWFLAKE.CORTEX)

Not supported

Supported

CURRENT_ACCOUNT

Not supported

Supported

CURRENT_DATE (and aliases)

Supported

Supported

CURRENT_REGION

Not supported

Supported

CURRENT_ROLE

Not supported

Supported

CURRENT_TIME (and aliases)

Supported

Supported

CURRENT_TIMESTAMP (and aliases)

Supported

Supported

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

Not supported

Supported

EMBED_TEXT_768 (SNOWFLAKE.CORTEX)

Not supported

Supported

EMBED_TEXT_1024 (SNOWFLAKE.CORTEX)

Not supported

Supported

EXTRACT_ANSWER (SNOWFLAKE.CORTEX)

Not supported

Supported

FINETUNE (SNOWFLAKE.CORTEX)

Not supported

Supported

FIRST_VALUE

Supported

Supported

LAST_VALUE

Supported

Supported

NTH_VALUE

Supported

Supported

RANK

Not supported

Supported

ROW_NUMBER

Not supported

Supported

SENTIMENT (SNOWFLAKE.CORTEX)

Not supported

Supported

Sequence functions (e.g., SEQ1, SEQ2)

Not supported

Supported

TRANSLATE (SNOWFLAKE.CORTEX)

Not supported

Supported

VOLATILE user-defined functions

Not supported

Supported