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.

  • You can’t set the DATA_RETENTION_TIME_IN_DAYS object parameter in your source tables to zero.

  • You can’t use dynamic SQL (for example, session variables or unbound variables of anonymous blocks) in the dynamic table’s definition.

  • In a dynamic table definition, SELECT blocks that read from user-defined table functions (UDTF) must explicitly specify columns and can’t use *.

  • Dynamic tables can become stale if they are not refreshed within the MAX_DATA_EXTENSION_TIME_IN_DAYS period of the input tables. Once stale, dynamic tables must be recreated resume refreshes.

  • Dynamic tables do not currently support tracking in the ACCESS_HISTORY view. This means that queries and operations performed on dynamic tables are not captured in Snowflake’s ACCESS_HISTORY for auditing or monitoring purposes.

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

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 table functions (UDTF) written in SQL.

  • User-defined functions (UDF) written in SQL that contain a subquery (for example, a SELECT statement).

  • PIVOT and UNPIVOT constructs are not supported in incremental or full refresh.

  • SAMPLE / TABLESAMPLE 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.

  • Masking policies with database roles on shared tables.

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.

When you create a dynamic table, the default value for the 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 determine the most suitable refresh mode for your use case, experiment with refresh modes and automatic recommendations. For consistent behavior across Snowflake releases, explicitly set the refresh mode on all dynamic tables. For example, if you want your dynamic tables to refresh only incrementally, you must explicitly set the refresh mode to INCREMENTAL when creating them. For more information, see Set the refresh mode for all production dynamic tables.

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:

  • Set operators:

    • UNION, MINUS, EXCEPT, INTERSECT.

  • The following uses of UNION [ ALL ]:

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

    • UNION ALL between a GROUP BY or DISTINCT and another GROUP BY or DISTINCT.

  • All subquery operators.

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

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

    • SQL UDFs that contain subqueries.

  • LATERAL joins, except for using LATERAL with FLATTEN().

  • ML or LLM functions.

Additional limitations with incremental refresh

Masking and row access policies

Masking or row access policies on a dynamic table don’t affect its refresh mode. However, policies applied on source tables might affect the refresh mode:

  • Incremental refresh is supported if the policies on source tables use the CURRENT_ROLE function.

  • Incremental refresh isn’t supported if the policies on source tables use any other functions, INFORMATION_SCHEMA views, or query a table (for example, a mapping table lookup).

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

Cloned incremental dynamic tables might need to reinitialize during their first refresh after being created.

If a dynamic table is cloned from another dynamic table with dropped base tables, the clone will be suspended and can’t be resumed or refreshed.

Language: English