DYNAMIC_TABLE

Supported DBMS products: Snowflake

Physical options related to DYNAMIC_TABLE physical type

• REFRESH.DYNAMIC_TABLE_TARGET_LAG

• REFRESH.DYNAMIC_TABLE_WAREHOUSE

• REFRESH.DYNAMIC_TABLE_REFRESH_MODE

• STORAGE.DYNAMIC_TABLE_INITIALIZE_BEHAVIOR

• STORAGE.DYNAMIC_TABLE_IMMUTABLE_WHERE

• STORAGE.DYNAMIC_TABLE_BACKFILL_FROM

• STORAGE.DYNAMIC_TABLE_SIMPLE_ENTITY_RECREATE

• STORAGE.TIME_TRAVEL_RETENTION_TIME_IN_DAYS

• STORAGE.AUTO_CLUSTERING_SUSPEND

• STORAGE.SEARCH_OPTIMIZATION

• STORAGE.SEARCH_OPTIMIZATION_OPTIONS

Environment variables related to DYNAMIC_TABLE

• system.deployment.entity_recreate.dependent_dynamic_table.refresh

Known limitations

• https://docs.snowflake.com/en/user-guide/dynamic-tables-limitations

  • For example defining Row Access Policy to any of the sources might prevent the creation of the dynamic table with reasoning:

    CODE

    Change tracking is not supported on queries using policies that contain subqueries.

Good to know

• Change tracking will be tried to be activated on all sources of the Dynamic table by Snowflake, Change Tracking behavior with Dynamic tables | Snowflake Documentation

  • Agile Data Engine will expect STORAGE.CHANGE_TRACKING on for any parent entities of the DYNAMIC_TABLE.

  • By default STORAGE.CHANGE_TRACKING prevents any ENTITY_RECREATE action for any entity, but this can be overridden with Force Entity Recreate setting in Entity properties.

• In case the parent entities (tables/views) for a dynamic table get breaking chances for the dynamic table SQL, the dynamic table will remain accessible and only its refresh history in Snowflake will indicate whether it is in error state.

  • It is recommended to get familiar with the dynamic table refresh history information and the current refresh logic available in Agile Data Engine, which is adjustable with the environment variable per runtime system.deployment.entity_recreate.dependent_dynamic_table.refresh.

See also

• https://docs.snowflake.com/en/sql-reference/sql/create-dynamic-table