Understanding dynamic table target lag¶

How upstream and downstream relationships affect target lag¶

The following diagram illustrates suspend, resume, and manual refresh operations in the context of upstream and downstream relationships to other dynamic tables.

The diagram depicts a simple declarative data pipeline built with dynamic tables:

• DT2 is described as downstream of DT1 because it depends on that dynamic table, and as upstream of DT3, which depends on it.

• DT3 is downstream of both DT2 and DT1 because it depends on DT2 directly and on DT1 indirectly.

• DT1 is directly or indirectly upstream of the other dynamic tables.

Types of target lag¶

Target lag is specified in one of following ways. Target lag is inversely proportional to the dynamic table’s refresh frequency: frequent refreshes imply a lower lag.

1. Measure of freshness: Defines the maximum amount of time that the dynamic table’s content should lag behind updates to the base tables.

   The following example sets my_dynamic_table to refresh and maintain freshness within every hour:

   ALTER DYNAMIC TABLE my_dynamic_table SET TARGET_LAG = '1 hour';
   

   Copy

2. Downstream: Specifies that the dynamic table should refresh on demand when other dependent dynamic tables refresh. This refresh can be triggered by initialization at creation, manual refresh, or scheduled refresh of a downstream dynamic table.

   In the following example, my_dynamic_table is set to refresh based on the target lag of its downstream dynamic tables. If my_dynamic_table doesn’t have any dynamic tables that depend on it, then it won’t refresh.

   ALTER DYNAMIC TABLE my_dynamic_table SET TARGET_LAG = DOWNSTREAM;
   

   Copy

Determine the optimal target lag for a dynamic table¶

The target lag you set affects the frequency of your dynamic table’s scheduled refreshes.

Snowflake schedules refreshes slightly earlier to allow time for the refresh to complete. For example, if you set the target lag to 5 minutes, it doesn’t mean the table will refresh exactly every 5 minutes. Actual refresh intervals might be shorter than the specified lag. If you want more consistent 5-minute refreshes, consider increasing the target lag slightly.

You can use either the DYNAMIC_TABLE_REFRESH_HISTORY table function in INFORMATION_SCHEMA or Snowsight to determine the optimal target lag time per your requirements. For example, analyzing refresh details, including duration and skipped refreshes, to make an informed decision.

For information about monitoring dynamic table refreshes and troubleshooting, see About monitoring dynamic tables and Troubleshooting dynamic tables.

SELECT
    name
FROM
  TABLE (
    INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY (
      NAME_PREFIX => 'MYDB.MYSCHEMA.', ERROR_ONLY => TRUE
    )
  );

Alter target lag¶

You might want to adjust the target lag for a dynamic table for any of the following reasons:

• You need more up-to-date data. Reducing the target lag ensures that the dynamic table refreshes more frequently, providing fresher data closer to real time.

• You want to optimize resources. Increasing the target lag can reduce the frequency of refreshes, saving compute resources, which is useful for workloads that don’t need constant updates or where data changes infrequently. For example, if a dynamic table refreshes every 20 minutes but only needs to be within one hour of the source tables, setting a one-hour target lag can lower costs.

• You want the target lag to align with the data pipeline. If your dynamic table depends on other tables that have longer refresh intervals, adjusting the target lag ensures that it aligns with dependencies.

To change target lag, use the ALTER DYNAMIC TABLE command. For more information, see Alter the warehouse or target lag for dynamic tables.

Example: target lag for dynamic table chains¶

Consider the following example where a dynamic table (DT2) reads from another dynamic table (DT1) to materialize its contents. In this scenario, a report consumes DT2’s data via a query.

The following results are possible, depending on how each dynamic table specifies its lag: