Known limitations for dynamic tables

This topic describes limitations on the following dynamic table features:

General limitations

The following general limitations apply to using dynamic tables:

  • A single account can hold a maximum of 4000 dynamic tables.

  • In the definition of a dynamic table:

    • You can’t query more than 100 tables.

    • You can’t query more than 100 dynamic tables.

  • You can’t truncate data from a dynamic table.

  • You can’t create a temporary dynamic table.

  • When you use a dynamic table to ingest shared data, the query can’t select from a shared dynamic table or a shared secure view that references an upstream dynamic table.

  • You can’t use secondary roles with dynamic tables because dynamic table refreshes act as their owner role. For more information, see Enforcement model with primary role and secondary roles.

Limitations on query constructs

The following constructs are not currently supported in the query for a dynamic table. If you specify these in the query, an error occurs:

  • External functions.

  • Sequences.

  • Functions that rely on CURRENT_USER. Dynamic table refreshes act as their owner role with a special SYSTEM user.

  • Sources that include directory tables, external tables, streams, and materialized views.

  • Views on dynamic tables or other unsupported objects.

  • User-defined functions (UDFs and UDTFs) written in SQL and containing a subquery.

  • UNPIVOT constructs are not supported in dynamic table incremental or full refresh.

Support for cross-feature interactions

The following cross-feature interactions are not supported:

  • Using the query acceleration service (QAS) for dynamic table refreshes.

The following limitations apply for cross-feature interactions:

  • Dynamic tables and base tables that are in different failover groups cause replication to fail.

Support for incremental refresh

This section describes the expressions, clauses, and functions that are currently not supported for dynamic table incremental refresh. If a query uses these, the automated refresh process uses a full refresh, which might consume more credits. See Determine whether an incremental or full refresh is used.

Non-deterministic functions are not supported with incremental refreshes, but some non-deterministic functions are supported with full refreshes.

If you want your dynamic tables to refresh only incrementally, you should explicitly set the desired refresh type when creating your dynamic tables. For more information, see Set the refresh mode for all production dynamic tables.

Note

When the 2024_04 behavior change bundle is enabled, Snowflake chooses the refresh mode that’s likely to perform best depending on your query definition.

To determine the best mode for your use case, experiment with refresh modes and automatic recommendations. For consistent behavior across Snowflake releases, you should explicitly set the refresh mode on all dynamic tables.

When you create a dynamic table, the default value for REFRESH_MODE is AUTO, which selects an incremental refresh of the dynamic table. If the CREATE DYNAMIC TABLE statement doesn’t support incremental refresh, full refresh is automatically chosen as the refresh mode. To verify the refresh mode for your dynamic tables, see View dynamic table refresh mode.

Unsupported constructs, operators, and functions

Dynamic tables don’t currently support incremental refreshes on some constructs, operators, and functions. If you specify the following in the query, the dynamic table is updated through a full refresh:

  • PIVOT.

  • Set operators:

    • UNION, MINUS, EXCEPT, INTERSECT.

  • The following use of UNION [ ALL ]:

    • UNION ALL of a table and itself or a clone of itself.

    • UNION ALL of two GROUP BYs.

  • Lateral join.

  • The following outer join (left, right, or full) patterns:

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

  • The following uses of window functions:

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

    • Using the window functions PERCENT_RANK, DENSE_RANK, RANK with sliding windows.

    • Using ANY since it’s a non-deterministic function.

  • The following uses of user-defined functions (UDF):

    • UDFs and UDTFs written in Python, Java, Scala, or Javascript, and specifying the VOLATILE parameter.

    • UDFs written in SQL and containing subqueries or any unsupported constructs.

Additional limitations with incremental refresh

Masking and row access policies:

Masking or row access policies that are applied on a dynamic table don’t affect that dynamic table’s refresh mode. Policies applied on source tables affect the refresh mode of a dynamic table if:

  • The masking or row access policies applied on the source tables use the CURRENT_ROLE function, in which case the dynamic table uses incremental refresh mode.

  • The masking or row access policies applied on the source tables use any other function or query a table (for example, a mapping table lookup), in which case the dynamic table uses full refresh mode.

Replication:

Replicated dynamic tables with incremental refresh reinitialize after failover before they can resume incremental refresh.

For more information, see Replication and dynamic tables.

Cloning:

In certain cases, cloned incremental dynamic tables might need to reinitialize on their first refresh after being created.