Understanding dynamic table refresh¶

Dynamic table refresh modes¶

Dynamic tables support two refresh modes: incremental and full.

• Incremental refresh: This mode analyzes the dynamic table’s query and calculates changes since the last refresh. It then merges these changes into the table. For information about supported queries, see Supported queries in incremental refresh.

• Full refresh: This mode involves executing the query for the dynamic table and completely replacing the previous materialized results.

You can either set the refresh mode explicitly or set it to AUTO. With AUTO, Snowflake intelligently chooses the most cost- and time-effective refresh mode based on factors like query complexity, supported constructs, operators, functions, and expected performance. If incremental refresh isn’t supported or is likely to perform poorly, Snowflake automatically selects full refresh instead. For more information, see Limitations on incremental refresh.

We strongly recommend using AUTO for most use cases, because it allows Snowflake to optimize refresh behavior without manual tuning.

After you create a dynamic table, you can monitor the table to determine whether incremental or full refreshes are used to update that table.

SHOW DYNAMIC TABLES;

Supported queries in incremental refresh¶

The following table describes the expressions, keywords, and clauses that currently support incremental refresh. For a list of queries that do not support incremental refresh, see Limitations on support for incremental refresh.

Currently, lateral joins are not supported with incremental refresh. However, you can use LATERAL with FLATTEN() by setting the refresh mode to INCREMENTAL.

For example:

CREATE TABLE persons
  AS
    SELECT column1 AS id, parse_json(column2) AS entity
    FROM values
      (12712555,
      '{ name:  { first: "John", last: "Smith"},
        contact: [
        { business:[
          { type: "phone", content:"555-1234" },
          { type: "email", content:"j.smith@example.com" } ] } ] }'),
      (98127771,
      '{ name:  { first: "Jane", last: "Doe"},
        contact: [
        { business:[
          { type: "phone", content:"555-1236" },
          { type: "email", content:"j.doe@example.com" } ] } ] }') v;

CREATE DYNAMIC TABLE example
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = mywh
  REFRESH_MODE = INCREMENTAL
  AS
    SELECT p.id, f.value, f.path
    FROM persons p,
    LATERAL FLATTEN(input => p.entity) f;

How operators incrementally refresh¶

The following table outlines how each operator is incrementalized (that is, how it’s transformed into a new query fragment that generates changes instead of full results) and its performance and other important factors to consider.

CREATE DYNAMIC TABLE sums
  AS
    SELECT date_trunc(minute, ts), sum(c1) FROM table
    GROUP BY 1;

CREATE DYNAMIC TABLE intermediate
  AS
    SELECT date_trunc(minute, ts) ts_min, c1 FROM table;

CREATE DYNAMIC TABLE sums
  AS
    SELECT ts_min, sum(c1) FROM intermediate
    GROUP BY 1;

Supported non-deterministic functions in full refresh¶

The following non-deterministic functions are supported in dynamic tables. Note that these functions are only supported for full refreshes. For a list of what is not supported for incremental refresh, see Limitations on support for incremental refresh.

• VOLATILE user-defined functions

• Sequence functions (e.g., SEQ1, SEQ2)

• The following context functions:

  • CURRENT_ROLE

  • CURRENT_WAREHOUSE

  • CURRENT_ACCOUNT

  • CURRENT_REGION

• The following date and time functions (with their respective aliases):

  • CURRENT_DATE

  • CURRENT_TIME

  • CURRENT_TIMESTAMP

• The following Cortex functions:

  • CLASSIFY_TEXT (SNOWFLAKE.CORTEX)

  • COMPLETE (SNOWFLAKE.CORTEX)

  • EMBED_TEXT_768 (SNOWFLAKE.CORTEX)

  • EMBED_TEXT_1024 (SNOWFLAKE.CORTEX)

  • EXTRACT_ANSWER (SNOWFLAKE.CORTEX)

  • FINETUNE (SNOWFLAKE.CORTEX)

  • SENTIMENT (SNOWFLAKE.CORTEX)

  • TRANSLATE (SNOWFLAKE.CORTEX)

How data is refreshed when dynamic tables depend on other dynamic tables¶

When a dynamic table’s lag is set as a time measure, the automated refresh process schedules refreshes to best meet the target lag times.

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. If refreshes take too long, the scheduler may skip refreshes to try to stay up to date. However, snapshot isolation is preserved.

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:

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.