Course contents

SnowPro Core COF-C03 Study GuidePart 6: Database objects and platformDynamic Tables

Dynamic Tables

Dynamic Tables

Definition

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_wh
  AS
  SELECT order_date, SUM(amount) AS revenue
  FROM orders
  GROUP 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 writeChange-capture logic, MERGE statements, task schedulingOne declarative SELECT
Incremental refreshManual, via stream consumptionAutomatic, Snowflake-managed
Freshness controlTask CRON scheduleTARGET_LAG declaration
Best fitComplex custom transformation logicStandard "keep this aggregate/join fresh" pipelines

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.

Thomas's bronze, silver and gold dynamic tables. Only GOLD has a real target lag, 1 hour. BRONZE and SILVER use TARGET_LAG = DOWNSTREAM: they refresh only when the table that reads them needs fresh input, so GOLD's lag schedules the whole chain. The lag is a target, best effort, not a guarantee. A dynamic table set to DOWNSTREAM that no dynamic table reads, like ORPHAN, never refreshes automatically.A CHAIN: THE LAST TABLE SETS THE PACENO DYNAMIC TABLE READS ITTABLERAW_ORDERSloaded by SnowpipeDYNAMIC TABLEBRONZETARGET_LAG =DOWNSTREAMDYNAMIC TABLESILVERTARGET_LAG =DOWNSTREAMDYNAMIC TABLEGOLDTARGET_LAG = '1 hour'best effortGOLD needs fresh input, so SILVER refreshes first, and SILVER's need refreshes BRONZETABLERAW_LOGSDYNAMIC TABLEORPHANTARGET_LAG =DOWNSTREAMNever refreshes on its ownno consumer ever asks for data;only ALTER … REFRESH updates it✗

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

Figure 6.5 With DOWNSTREAM, a dynamic table refreshes only when a dynamic table that reads it needs fresh data. Top: GOLD's 1-hour target drives refreshes of SILVER and BRONZE (violet arrows). Bottom: nothing reads ORPHAN, so it never refreshes automatically. TARGET_LAG is best effort: a slow or failed refresh can let actual lag exceed it.