Dynamic Tables
Snowflake · Advanced Features
Theory
A dynamic table is a table defined by a query, which Snowflake keeps up to date for you. You write CREATE DYNAMIC TABLE clean_orders TARGET_LAG = '1 hour' WAREHOUSE = analytics_wh AS SELECT ..., and Snowflake schedules refreshes so the data is never more than about the target lag behind its sources. Where the query allows it, refreshes are incremental, processing only changed source rows; otherwise Snowflake does a full refresh.
Dynamic tables can read from other dynamic tables, forming a pipeline. A downstream table can use TARGET_LAG = DOWNSTREAM to refresh only when the tables that depend on it need fresh data. You cannot run INSERT or UPDATE on a dynamic table; its contents always come from its query.
Why we use it
Hand-building streams, tasks and MERGE statements for each transformation is a lot of code to maintain. Dynamic tables let you declare the result you want and how fresh it must be, and Snowflake handles the scheduling and incremental logic.
When to use it
Use dynamic tables for transformation pipelines where freshness of minutes or hours is fine and the logic is a SELECT. Keep streams and tasks for procedural logic, calls to external systems, or precise control over when and how changes are applied.
How Refresh Works
You set a TARGET_LAG (e.g. '5 minutes') and Snowflake decides how and when to refresh the table to stay within that lag, incrementally where possible instead of always recomputing from scratch.
Dynamic Tables vs Streams + Tasks
The same incremental pipeline that used to require a Stream, a Task, and a MERGE statement can often be replaced by a single CREATE DYNAMIC TABLE statement with much less code to maintain.
When to Still Use Streams + Tasks
Dynamic tables cover common transform-and-refresh patterns, but a Stream + Task pipeline still gives more fine-grained control when the logic is genuinely complex or conditional.
How it works
- Define the dynamic table query and TARGET_LAG
- Snowflake tracks changes in the source tables
- Would the data fall behind the target lag? Yes: Schedule a refresh on the warehouse. No: Wait.
- Can this query refresh incrementally? Yes: Process only the changed rows. No: Recompute the full result.
- Downstream dynamic tables refresh in order
Example
CREATE OR REPLACE DYNAMIC TABLE clean_orders
TARGET_LAG = '1 hour' WAREHOUSE = analytics_wh
AS SELECT order_id, UPPER(TRIM(product)) AS product, amount, order_date
FROM orders WHERE amount > 0;
CREATE OR REPLACE DYNAMIC TABLE daily_metrics
TARGET_LAG = '2 hours' WAREHOUSE = analytics_wh
AS SELECT order_date, COUNT(*) AS order_count, SUM(amount) AS revenue
FROM clean_orders GROUP BY order_date;
SELECT * FROM daily_metrics ORDER BY order_date DESC;Key points
- TARGET_LAG sets maximum staleness, not a fixed schedule
- Refreshes incrementally where the query allows, otherwise in full
- TARGET_LAG = DOWNSTREAM refreshes only when dependents need it
- Contents come only from the query; no direct DML
- Simplifies many pipelines that used to need streams, tasks and MERGE