Understanding dynamic table refresh¶
Dynamic table content is based on the results of a specific query. When the underlying data on which the dynamic table is based on changes, the table is updated to reflect those changes. These updates are referred to as a refresh. This process is automated, and it involves analyzing the query that underlies the table. The following sections explain how dynamic tables are refreshed and how the SELECT statement for a dynamic table can determine the type of refresh used.
Dynamic table initialization¶
When a dynamic table is created using a CREATE DYNAMIC TABLE statement, the dynamic table is initially populated using data from the underlying tables. After creation, the dynamic table goes through a full refresh, including dynamic tables with downstream lag. The refresh ensures snapshot isolation, synchronizing all involved dynamic tables, saving costs with no-data refreshes for upstream dynamic tables.
This initial refresh can take some time, especially if extensive data scanning is
required, and the duration of the dynamic table creation depends on the size of the
scanned data. To track progress, query the refresh history using
information_schema.dynamic_table_refresh_history()
and look for entries with
refresh_trigger = INITIAL
.
If initialization fails, the table creation process stops, providing immediate feedback on any incorrect definitions. During the initialization of a new version of an existing dynamic table, the existing version remains accessible until the new version is available, ensuring that failing to create a new version doesn’t affect the current instance.
Dynamic table refresh types¶
The dynamic table refresh process operates in one of two ways:
Incremental refresh: This automated process analyzes the dynamic table’s query and calculates changes since the last refresh. It then merges these changes into the table. See Types of queries that support incremental refreshes for details on supported queries.
Full refresh: When the automated process can’t perform an incremental refresh, it conducts a full refresh. This involves executing the query for the dynamic table and completely replacing the previous materialized results.
The constructs used in the query determine whether an incremental refresh can be used. After you create a dynamic table, you can monitor the table to determine whether incremental or full refreshes are used to update that table.
Understanding target lag¶
Dynamic table refresh is triggered based on how out of date the data might be, or what is commonly referred to as lag or target lag.
Target lag is specified in one of two ways:
Measure of freshness: Defines the maximum amount of time that the dynamic table’s content should lag behind updates to the base tables.
Specified using the
TARGET_LAG = { num { seconds | ... | days }
parameter when altering or originally defining a dynamic table.DOWNSTREAM: Specifies that the dynamic table should be refreshed on demand when other dynamic tables that depend on it need to refresh. Updates are inferred from upstream database objects. Downstream dynamic tables are only updated when required by upstream consumers.
Consider the following example where Dynamic Table 2 (DT2) is defined based on Dynamic Table 1 (DT1). DT2 must read from DT1 to materialize its contents. In addition, a report consumes DT2 data via a query.

The following results are possible, depending on how each dynamic table specifies its lag:
Dynamic Table 1 (DT1) |
Dynamic Table 2 (DT2) |
Refresh results |
---|---|---|
|
|
DT2 is updated at most every 10 minutes. DT1 gets, or infers, its lag from DT2 and is updated every time DT2 requires updates. |
|
|
This scenario should be avoided. The report query will not receive any data. DT2 is not refreshed as no DT is built on top of DT2. Furthermore, DT1 will be frequently refreshed. |
|
|
DT2 is updated approximately every 10 minutes with data from DT1 that is at most 5 minutes old. |
|
|
DT2 is not refreshed periodically because DT1 has no downstream children with a defined lag. |
Types of queries that support incremental refreshes¶
The following table describes the expressions, keywords, and clauses that currently support incremental refresh.
Note
If the query uses expressions that are not supported for incremental refresh, the automated refresh process uses a full refresh instead. To determine which refresh mode is used, see Determine whether an incremental or full refresh is used.
Keyword/Clause |
Support for Incremental Refreshes |
---|---|
WITH |
Common table expressions (CTE) that use incremental refresh supported features in the subquery. |
Expressions in SELECT |
Expressions including those using deterministic built-in functions and immutable user-defined functions. |
FROM |
Source tables, views, and other dynamic tables. |
OVER |
All window functions. |
WHERE |
Filters with the same expressions that are valid in SELECT. |
JOIN (and other expressions for joining tables) |
Dynamic tables currently only support inner joins, outer joins, and cross joins for incremental refreshes. Note To use incremental refreshes, you must recreate dynamic tables which use other types of joins To determine whether you need to recreate the dynamic table, use one of the following procedures to check if the dynamic table uses a full refresh:
You can specify any number of tables in the join. Updates to all tables in the join are reflected in the results on the query. |
UNION ALL |
Dynamic tables support UNION ALL. |
GROUP BY |
Dynamic tables support GROUP BY. |
Important
Replacing an immutable UDF while it is in use by an incremental refresh dynamic table will result in undefined behavior in the dynamic table using the UDF.
Currently, the following constructs and types of queries are not supported with incremental refreshes. If you specify these in the query, the automated refresh process uses a full refresh (which can result in a higher cost) to update the table.
LATERAL joins
Subqueries outside of FROM clauses (for example, WHERE EXISTS)
VOLATILE user-defined functions
How data is refreshed when dynamic tables depend on other dynamic tables¶
When a dynamic table lag is specified as a measure of time, the automated refresh process determines the schedule for refreshes based on the target lag times of the dynamic tables. The process chooses a schedule that best meets the target lag times of the tables.
Note
Target lag is not a guarantee. Instead, it is a target that Snowflake attempts to meet. Data in dynamic tables is refreshed as closely as possible within the target lag. However, target lag may be exceeded due to factors such as warehouse size, data size, query complexity, and similar factors.
In order to keep data consistent in cases when one dynamic table depends on another, the process refreshes all dynamic tables in an account at compatible times. The timing of less frequent refreshes coincides with the timing of more frequent refreshes.
For example, suppose that dynamic table A has a target lag of two minutes and queries dynamic table B that has a target lag of one minute. The process might determine that A should be refreshed every 96 seconds, and B every 48 seconds. As a result, the process might apply the following schedule:
Specific Point in Time |
Dynamic Tables Refreshed |
---|---|
2022-12-01 00:00:00 |
A, B |
2022-12-01 00:00:48 |
B |
2022-12-01 00:01:36 |
A, B |
2022-12-01 00:02:24 |
B |
This means that at any given time, when you query a set of dynamic tables that depend on each other, you are querying the same “snapshot” of the data across these tables.
Note that the target lag of a dynamic table cannot be shorter than the target lag of the dynamic tables it depends on. For example, suppose that:
Dynamic table A queries the dynamic tables B and C.
Dynamic table B has a target lag of five minutes.
Dynamic table C has a target lag of one minute.
This means that the target lag time for A must not be shorter than five minutes (that is, not shorter than the longer of the lag times for B and C).
If you set the lag for A to five minutes, the process sets up a refresh schedule with these goals:
Refresh C often enough to keep its lag below one minute.
Refresh A and B together and often enough to keep their lags below five minutes.
Ensure that the refresh for A and B coincides with a refresh of C to ensure snapshot isolation.
Note: If refreshes take too long, the scheduler may skip refreshes to try to stay up to date. However, snapshot isolation is preserved.