Dynamic Tables – Target Lag Configuration
Unlike classic Tasks which execute on a strict cron schedule (e.g., "Run exactly at 12:00 PM"), Dynamic Tables use a declarative refresh model based on Target Lag.
Target Lag is the maximum acceptable delay between a change happening in the base source table and that change being reflected in the Dynamic Table.
Topics Covered
- Understanding Target Lag
- The
DOWNSTREAMMode - Cost Implications
- Monitoring Refresh History
Understanding Target Lag
When you create a Dynamic Table, you define the TARGET_LAG. Snowflake's automated background scheduler will analyze the table, look at how long the query typically takes to run, and schedule the refresh so that the data is at most that far behind the source data.
Valid formats include '1 minute', '5 hours', or '1 day'.
CREATE OR REPLACE DYNAMIC TABLE SALES_SUMMARY
TARGET_LAG = '5 minutes'
WAREHOUSE = COMPUTE_WH
AS
SELECT STORE, SUM(AMOUNT) FROM RAW_SALES GROUP BY STORE;
If new sales data arrives in RAW_SALES at 10:00 AM, Snowflake guarantees that SALES_SUMMARY will reflect that data no later than 10:05 AM.
The DOWNSTREAM Mode
In a Medallion Architecture, you often have chains of Dynamic Tables (e.g., Bronze -> Silver -> Gold).
If you specify a specific time (e.g., 5 minutes) for every table in the chain, Snowflake might trigger them out of sync, leading to inconsistent data at the Gold layer.
Instead, you use the DOWNSTREAM mode for the intermediate tables, and only specify a time for the final reporting table.
Hands-On Demonstration: DOWNSTREAM DAG
Let's build a mini-DAG for a hospital network in India.
(Run these in your Snowflake worksheet)
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_HEALTH_DB;
USE SCHEMA BEEMS_HEALTH_DB.PUBLIC;
-- 1. The Source Table
CREATE OR REPLACE TABLE RAW_PATIENT_EVENTS (EVENT_TIME TIMESTAMP, HOSPITAL STRING, EVENT STRING);
-- 2. The Silver Table (Intermediate)
-- It uses DOWNSTREAM lag. It will only refresh when a table downstream of it demands data.
CREATE OR REPLACE DYNAMIC TABLE SILVER_EVENTS
TARGET_LAG = 'DOWNSTREAM'
WAREHOUSE = COMPUTE_WH
AS
SELECT EVENT_TIME, UPPER(HOSPITAL) AS HOSPITAL_NAME, EVENT FROM RAW_PATIENT_EVENTS;
-- 3. The Gold Table (Final Dashboard)
-- It drives the schedule. It demands data every 10 minutes.
CREATE OR REPLACE DYNAMIC TABLE GOLD_HOSPITAL_METRICS
TARGET_LAG = '10 minutes'
WAREHOUSE = COMPUTE_WH
AS
SELECT HOSPITAL_NAME, COUNT(*) AS EVENT_COUNT
FROM SILVER_EVENTS
GROUP BY HOSPITAL_NAME;
How it works: Snowflake sees that GOLD needs data every 10 minutes. It knows GOLD depends on SILVER. Therefore, Snowflake automatically orchestrates the refresh of SILVER, waits for it to finish, and immediately refreshes GOLD.
Cost Implications
The lower your TARGET_LAG, the more frequently the Dynamic Table refreshes.
If you set TARGET_LAG = '1 minute', your Virtual Warehouse will wake up every 60 seconds. Because Snowflake warehouses bill for a minimum of 60 seconds every time they wake up, setting a 1-minute lag means your warehouse will literally never suspend, burning credits 24/7.
Best Practice: Only use 1-minute lag if the business explicitly requires near real-time dashboards. For most daily reporting, a lag of '1 hour' or 'DOWNSTREAM' from a daily Gold table is sufficient and significantly cheaper.
Altering the Target Lag
If your costs are too high, you can easily slow down the refresh cadence without dropping the table.
-- Change the lag to 1 hour
ALTER DYNAMIC TABLE GOLD_HOSPITAL_METRICS SET TARGET_LAG = '1 hour';
(If you get a permission error running ALTER, check if your current user has the OPERATE privilege on the table. You can verify your user identity by running SELECT CURRENT_USER();)
Monitoring Actual vs Target Lag
Sometimes, a Dynamic Table query is too complex to finish within the target lag time. If TARGET_LAG is 1 minute, but the query takes 5 minutes to run, the table will fall behind.
You monitor this using the DYNAMIC_TABLE_REFRESH_HISTORY table function.
-- Check the refresh history for our Gold table
SELECT
NAME,
STATE,
TARGET_LAG,
DATA_TIMESTAMP AS ACTUAL_FRESHNESS,
REFRESH_START_TIME,
REFRESH_END_TIME
FROM table(information_schema.dynamic_table_refresh_history(
NAME_PREFIX => 'GOLD_HOSPITAL_METRICS'
))
ORDER BY REFRESH_START_TIME DESC;
If STATE is consistently showing LAGGING, you need to either increase your warehouse size or increase your target lag setting.
Summary
- Target Lag defines the maximum acceptable data delay.
- Use
DOWNSTREAMfor intermediate tables so Snowflake can automatically orchestrate the DAG. - Aggressive lags (like 1 minute) prevent warehouses from auto-suspending, increasing costs.
- Monitor
DYNAMIC_TABLE_REFRESH_HISTORYto ensure your warehouse is keeping up with the requested lag.