Understanding Dynamic Tables Refresh in Snowflake
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.
Executive Summary:
- Understanding the types of Dynamic Table Refresh
- Understanding the Dynamic Table Lag
- Understanding how Snowflake determines best refresh schedule for Lag
What exactly is Dynamic Table Refresh?
As the underlying database objects, defined by the query associated with a dynamic table, change the dynamic table itself must be updated to reflect those changes. Such updates are referred to as refreshes.
To determine how to refresh a dynamic table, an automated refresh process analyzes the query that serves as the basis for the dynamic table. The automated refresh process uses the results of the analysis to determine how best to refresh the data in the dynamic table.
What are the different types of Dynamic Table Refresh?
The dynamic table refresh can be of two types:
- Incremental Refresh
- Full Refresh
Incremental Refresh — With incremental refresh, the automated refresh process analyzes the query for the dynamic table and computes the changes to the query results. The refresh process then merges those changes into the dynamic table.
There are certain types of queries, that can be refreshed. Below is the reference link:
Full Refresh — If the automated process is unable to determine how to perform an incremental refresh, the process performs a full refresh.
With full refresh, the automated refresh process performs the query for the dynamic table and materializes the results, completely replacing the current materialized results of the dynamic table.
What does ‘‘ Target Lag’’ means for Dynamic Tables?
Dynamic table refresh is triggered based on how out-of-date data might be, or what is commonly referred to as lag or target lag.
What could be the different ways to define the Lag?
Lag can be defined in two ways:
- In a measure of Time, i.e second/…/Day
- DOWNSTREAM
In a measure of Time — Specified using the TARGER_LAG = {<num> seconds | … | days}. Specifying TARGET_LAG in this way defines the maximum amount of time that the dynamic table’s content should lag behind updates to the base tables.
DOWNSTREAM — Specifies that the dynamic table should be refreshed on demand when other dynamic tables depending on it need to refresh. Updates are inferred from upstream database objects. Downstream dynamic tables are only updated when required by upstream consumers.
What Makes Snowflake Dynamic Tables a smarter product for Continuous Data Pipelines?
Snowflake Data Plaform makes the dynamic table smart enough to determine the best refresh schedule for data consistency.
When a dynamic table lag is specified as a measure of time, Snowflake’s 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.
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.
Here’s a classic example from snowflake documentation:
Suppose that a dynamic table A has a target lag of 2 minutes and queries a dynamic table B that has a target lag of 1 minute. The process might determine that A should be refreshed every 96 seconds and B every 48 seconds.
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.
About Me:
Hi there! I am Divyansh Saxena
I am an experienced Data Engineer with a proven track record of success in Snowflake Data Cloud technology. Highly skilled in designing, implementing, and maintaining data pipelines, ETL workflows, and data warehousing solutions. Possessing advanced knowledge of Snowflake’s features and functionality, I work at IBM and am a Snowflake Data superhero & Snowflake Snowpro Core SME. With a major career in Snowflake Data Cloud, I have a deep understanding of cloud-native data architecture and can leverage it to deliver high-performing, scalable, and secure data solutions.
Follow me on Medium for regular updates on Snowflake Best Practices and other trending topics:
Also, I am open to connecting all data enthusiasts across the globe on LinkedIn:
