Supported queries for dynamic tables¶

DISTINCT

Supported

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

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

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" } ] } ] }');

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;

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.

[{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

PIVOT and UNPIVOT

Not supported

Not supported

Scalar Aggregates

Supported

Supported

Set operators (UNION, MINUS, EXCEPT, INTERSECT)

Not supported

Supported

All subquery operators.

Not 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

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.

WITH

Common table expressions (CTEs) that use incremental refresh supported features in the subquery are supported.

WITH RECURSIVE is not supported.

Supported

ANY_VALUE

Not supported

Not supported

COMPLETE (SNOWFLAKE.CORTEX)

Not supported

Supported

CURRENT_DATE (and aliases)

Supported

Supported

CURRENT_ROLE

Not supported

Supported

CURRENT_TIMESTAMP (and aliases)

Supported

Supported

CURRENT_WAREHOUSE

Not supported

Supported

EMBED_TEXT_768 (SNOWFLAKE.CORTEX)

Not supported

Supported

EXTRACT_ANSWER (SNOWFLAKE.CORTEX)

Not supported

Supported

FIRST_VALUE

Supported

Supported

NTH_VALUE

Supported

Supported

ROW_NUMBER

Not supported

Supported

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

Not supported

Supported

VOLATILE user-defined functions

Not supported

Supported