Snowflake Dynamic Tables: Declarative Pipelines Explained

Dynamic Tables are Snowflake's declarative data pipeline primitive. With the traditional Streams + Tasks approach you have to procedurally define how to transform data and when to run it, but with Dynamic Tables you simply declare the final result set you want in a SELECT statement, and Snowflake handles refresh scheduling, incremental processing, and dependency resolution automatically.

Core Concepts of Declarative Pipelines

Dynamic Tables internally form a DAG (directed acyclic graph). When Dynamic Table B references source table A and Dynamic Table C references B, Snowflake automatically detects the A → B → C dependency chain and refreshes them in the correct order. You no longer need to manage Task execution order or Stream consumption timing yourself.

There are two refresh modes — Incremental and Full — and Snowflake analyzes the query to pick the optimal one automatically. Queries built from simple SELECT / JOIN / WHERE / GROUP BY operations qualify for Incremental refresh and only process the changed data, which is far more cost efficient.

CREATE DYNAMIC TABLE Syntax

-- Create a basic Dynamic Table
CREATE OR REPLACE DYNAMIC TABLE silver_orders
  TARGET_LAG = '5 minutes'
  WAREHOUSE = transform_wh
AS
  SELECT
    o.order_id,
    o.order_date,
    o.customer_id,
    c.customer_name,
    c.segment,
    o.total_amount
  FROM raw.orders o
  JOIN raw.customers c ON o.customer_id = c.customer_id
  WHERE o.order_date >= '2025-01-01';

-- Verify after creation
SHOW DYNAMIC TABLES LIKE 'SILVER_ORDERS';
DESCRIBE DYNAMIC TABLE silver_orders;

TARGET_LAG is the maximum allowed delay between a source-data change and its reflection in the Dynamic Table.WAREHOUSE specifies the warehouse used to execute refreshes.

Design Guidelines for TARGET_LAG

DAG Dependency Resolution with DOWNSTREAM

When you chain multiple Dynamic Tables, as in a medallion architecture, set TARGET_LAG = DOWNSTREAM on the intermediate layers. Set a concrete lag value only on the most downstream Dynamic Table, and Snowflake works backwards to automatically schedule refreshes for each upstream table.

-- Medallion pipeline: Bronze -> Silver -> Gold

-- Silver layer: follows downstream via DOWNSTREAM
CREATE OR REPLACE DYNAMIC TABLE silver_daily_sales
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = transform_wh
AS
  SELECT
    DATE_TRUNC('DAY', order_date) AS sale_date,
    product_category,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
  FROM bronze_orders
  GROUP BY 1, 2;

-- Gold layer: concrete lag value (DAG terminal)
CREATE OR REPLACE DYNAMIC TABLE gold_sales_summary
  TARGET_LAG = '10 minutes'
  WAREHOUSE = transform_wh
AS
  SELECT
    sale_date,
    product_category,
    order_count,
    revenue,
    revenue / NULLIF(order_count, 0) AS avg_order_value
  FROM silver_daily_sales
  WHERE sale_date >= DATEADD(MONTH, -12, CURRENT_DATE());

Refresh Monitoring

You can inspect Dynamic Table refresh history through the INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORYtable function and the ACCOUNT_USAGE.DYNAMIC_TABLE_REFRESH_HISTORY view.

-- Inspect refresh history for the past 24 hours
SELECT
  name,
  refresh_trigger,
  refresh_action,       -- INCREMENTAL or FULL
  state,                -- SUCCEEDED / FAILED / CANCELLED
  state_message,
  data_timestamp,
  refresh_start_time,
  refresh_end_time,
  DATEDIFF('SECOND', refresh_start_time, refresh_end_time) AS duration_sec
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
  NAME_PREFIX => 'SILVER_',
  ERROR_ONLY => FALSE
))
WHERE refresh_start_time >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP())
ORDER BY refresh_start_time DESC;

Operational Commands

-- Change TARGET_LAG
ALTER DYNAMIC TABLE silver_orders SET TARGET_LAG = '15 minutes';

-- Change the warehouse
ALTER DYNAMIC TABLE silver_orders SET WAREHOUSE = new_transform_wh;

-- Suspend and resume refreshes
ALTER DYNAMIC TABLE silver_orders SUSPEND;
ALTER DYNAMIC TABLE silver_orders RESUME;

-- Manual refresh (for debugging or validation)
ALTER DYNAMIC TABLE silver_orders REFRESH;

Dynamic Tables vs Streams + Tasks

Design Best Practices

• Concrete lag at the tail, DOWNSTREAM in the middle: set a time-based TARGET_LAG only on the terminal table of the DAG, and let intermediate tables auto-adjust with DOWNSTREAM.
• Design SELECT statements to favor Incremental refresh: avoid window functions and SELECT DISTINCT; SQL built on JOIN / GROUP BY / WHERE is more likely to qualify for Incremental refresh.
• Allocate a dedicated warehouse: isolate the Dynamic Table warehouse so query workloads and refresh workloads do not contend with each other.
• Continuously monitor with DYNAMIC_TABLE_REFRESH_HISTORY: regularly check whether Full Refresh is firing unintentionally and whether any refreshes have failed.
• Temporarily upsize the warehouse for the initial load: the first Full Refresh has to process the entire dataset, so run it on a larger warehouse and scale back once it completes.

Check Your Understanding

Frequently Asked Questions