How dynamic tables work¶
When creating a dynamic table, you specify the query used to transform the data from one or more base objects or dynamic tables. An automated refresh process executes this query regularly and updates the dynamic table with the changes made to the base objects.
This automated process computes the changes that were made to the base objects and merges those changes into the dynamic table. To perform this work, the process uses compute resources that you associate with the dynamic table. For more information on resources refer to Understanding cost for dynamic tables.
When creating a dynamic table, you specify a target “freshness” for the data (a target lag). For example, you can specify that the data should be at most five minutes behind the updates to the base table. Based on this target freshness, the automated process sets up refreshes so that the data in the dynamic table is kept up to date within this target (that is, within five minutes of updates to the base table).
If the data does not need to be as fresh, you can specify a longer target freshness time to reduce costs. For example, if the data in the target table just needs to be at most one hour behind the updates to the base tables, you can specify a target freshness of one hour (instead of five minutes) to reduce costs.
When to use dynamic tables¶
There are several methods of transforming data in your pipeline (for example, streams and tasks, CTAS, your own custom solution). Dynamic tables are one option for transforming your data.
Dynamic tables are best used when:
You don’t want to write code to track data dependencies and manage data refresh.
You don’t need, or want to avoid, the complexity of streams and tasks.
You do need to materialize the results of a query of multiple base tables.
You need to build multiple tables to transform data via an ETL pipeline.
You don’t need fine-grained refresh schedule control and you just want to specify the target data freshness for your pipelines.
You don’t need to use unsupported dynamic query constructs such as stored procedures, non-deterministic functions not listed in Supported non-deterministic functions in full refresh, or external functions, or need to use sources for dynamic tables that are external tables, streams, or materialized views.
Note
Dynamic tables can be used as the source of a stream. When used together, a stream based on a dynamic table works like any other stream. For additional information and examples see Dynamic tables compared to streams and tasks and to materialized views.