Dynamic Tables
A dynamic table declares the result you want as a SQL query, and Snowflake automatically manages the pipeline that keeps it fresh, incrementally where possible, up to a target lag you specify. It replaces a common pattern of hand-written streams-plus-tasks pipelines with one declarative object.
CREATE DYNAMIC TABLE daily_revenue TARGET_LAG = '1 hour'WAREHOUSE = transform_whASSELECT order_date, SUM(amount) AS revenueFROM ordersGROUP BY order_date;
Snowflake computes a query graph across chained dynamic tables and figures out how to refresh each one incrementally when its upstream sources change, rather than fully rebuilding from scratch every cycle. TARGET_LAG is a target, not a fixed schedule: "try to keep this no more than 1 hour stale," and Snowflake decides the actual refresh cadence and strategy to hit it as efficiently as possible.
| Streams + Tasks (manual) | Dynamic Tables | |
|---|---|---|
| You write | Change-capture logic, MERGE statements, task scheduling | One declarative SELECT |
| Incremental refresh | Manual, via stream consumption | Automatic, Snowflake-managed |
| Freshness control | Task CRON schedule | TARGET_ declaration |
| Best fit | Complex custom transformation logic | Standard "keep this aggregate/ |
Chaining is where the declarative model pays off. Thomas builds bronze → silver → gold as three dynamic tables, and the upstream two (bronze and silver) declare TARGET_LAG = DOWNSTREAM instead of a duration: they refresh only when a table that reads them needs fresh data, with no clock of their own. Only the final table carries a real lag ('1 hour'), and Snowflake schedules the whole chain backward from that target. Two properties catch people. A dynamic table can't be the target of your own DML; its content is entirely the query's output, so an INSERT into one errors. And after five consecutive scheduled refresh failures it suspends itself (resume it with ALTER DYNAMIC TABLE ... RESUME), which is the first thing to check when one goes quietly stale: SHOW DYNAMIC TABLES exposes the state, and Snowsight's refresh history shows the failing run.
A CHAIN: THE LAST TABLE SETS THE PACE
TABLERAW_ORDERSloaded by Snowpipe
DYNAMIC TABLEBRONZETARGET_LAG = DOWNSTREAM
DYNAMIC TABLESILVERTARGET_LAG = DOWNSTREAM
DYNAMIC TABLEGOLDTARGET_LAG = '1 hour'best effort
GOLD needs fresh input, so SILVER refreshes first, and SILVER's need refreshes BRONZE
NO DYNAMIC TABLE READS IT
TABLERAW_LOGS
DYNAMIC TABLEORPHANTARGET_LAG = DOWNSTREAM
✗Never refreshes on its ownno consumer ever asks for data;only ALTER … REFRESH updates it

