Dynamic table limitations¶

General limitations¶

The following general limitations apply to using dynamic tables:

• A single account can hold a maximum of 50,000 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 Authorization through 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, they must be recreated to 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.

• When creating a dynamic table that uses a warehouse named DEFAULT, you must use double quotes around the name, following the double-quoted identifier requirements. For example, CREATE DYNAMIC TABLE ... WAREHOUSE = "DEFAULT". For more information on creating dynamic tables, see Create dynamic tables.

• Dynamic tables don’t support sources that include directory tables, external tables, streams, and materialized views.

• You can’t create views on dynamic tables. Dynamic tables are designed to be used as sources for other dynamic tables or for querying data directly, but they do not support the creation of views on top of them.

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.

• Aggregation and projection policies cannot be applied to the base tables of dynamic tables. If a base table has aggregation or projection policies associated with it, the dynamic table will fail to create.

Support for incremental refresh¶

Dynamic tables support two refresh modes: incremental and full. You can either set the refresh mode to AUTO or set it explicitly. For more information, see Dynamic table refresh modes and Best practices for choosing dynamic table refresh modes.