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.

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

  • Sources that include directory tables, Iceberg 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:

  • Setting clustering keys on a dynamic table.

  • Enabling search optimization on a dynamic table.

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

  • Using dynamic tables in managed access schemas.

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 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. Using a role with the necessary privileges, you can verify the refresh mode using one of the following methods:

  • Using SQL: Run the SHOW DYNAMIC TABLES statement. In the output, the refresh_mode column shows the actual refresh mode, and the text column shows the user-specified refresh mode.

  • Using Snowsight: In the navigation menu, select Monitoring » Dynamic Tables, and then select your dynamic table. You can view the refresh mode for the dynamic table in the Table Details tab.

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.

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

Language: English