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;

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.