Supported queries for dynamic tables¶

Source tables, views, Snowflake-managed Apache Iceberg™ tables, and other dynamic tables.

Subqueries outside of FROM clauses (for example, WHERE EXISTS) are not supported.

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.

The following is not supported:

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

Expressions including those using deterministic built-in functions and immutable user-defined functions.

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, 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 *.

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.

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.

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

WITH RECURSIVE is not supported.