What are Snowflake Dynamic Tables and how do they differ from regular tables and views?

A Dynamic Table is a Snowflake object that automatically maintains the result of a defining SQL query and refreshes it to stay within a user-specified staleness limit. You declare what you want the table to contain and how fresh it must be — Snowflake handles all the scheduling, incremental or full refreshes, and dependency tracking.

The TARGET_LAG parameter is the core control: it sets the maximum acceptable staleness (e.g., '5 minutes', '1 hour'). Snowflake refreshes the Dynamic Table on a schedule that keeps actual lag within that target. Refreshes run on a user-specified warehouse or on serverless compute.

Dynamic Tables can be chained: if DT_B depends on DT_A, Snowflake refreshes them in the correct order automatically — similar to a DAG without explicit orchestration. This makes them ideal for multi-step ELT pipelines.

CREATE DYNAMIC TABLE customer_summary
  TARGET_LAG = '10 minutes'
  WAREHOUSE = transform_wh
AS
  SELECT
    c.customer_id,
    c.name,
    COUNT(o.order_id)  AS total_orders,
    SUM(o.amount)      AS lifetime_value
  FROM customers c
  LEFT JOIN orders o ON c.customer_id = o.customer_id
  GROUP BY c.customer_id, c.name;