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 |
---|---|---|
Supported |
Supported |
|
Not supported |
Not supported |
|
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 |
|
Supported |
Supported |
|
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 |
|
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:
|
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:
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 |
Not supported |
Supported |
|
Not supported |
Not supported |
|
Not supported |
Not supported |
|
Scalar Aggregates |
Supported |
Supported |
Expressions including those using deterministic built-in functions and immutable user-defined functions. |
Supported |
|
Set operators (UNION, MINUS, EXCEPT, INTERSECT) |
Not supported |
Supported |
Not supported |
Not supported |
|
All subquery operators. |
Not supported |
Supported |
Supported |
Supported |
|
User-defined functions (UDFs) |
Supported, except for the following limitations:
|
Supported |
User-defined table functions (UDTFs) |
Supported, except for the following limitations:
|
Supported |
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:
|
Supported |
|
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 |
---|---|---|
Not supported |
Not supported |
|
Not supported |
Supported |
|
Not supported |
Supported |
|
Not supported |
Supported |
|
CURRENT_DATE (and aliases) |
Supported |
Supported |
Not supported |
Supported |
|
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. |
Not supported |
Supported |
|
Not supported |
Supported |
|
Not supported |
Supported |
|
Not supported |
Supported |
|
Not supported |
Supported |
|
Not supported |
Supported |
|
Supported |
Supported |
|
Supported |
Supported |
|
Supported |
Supported |
|
Not supported |
Supported |
|
Not supported |
Supported |
|
Not supported |
Supported |
|
Sequence functions (e.g., |
Not supported |
Supported |
Not supported |
Supported |
|
VOLATILE user-defined functions |
Not supported |
Supported |