TopolearnDynamic tables replace much hand-written stream-and-task plumbing with one declarative statement.
A dynamic table is defined by a query and a freshness goal. Snowflake keeps its contents up to date automatically:
CREATE DYNAMIC TABLE daily_revenue TARGET_LAG = '15 minutes' WAREHOUSE = etl_wh AS SELECT ...;Key ideas:
- TARGET_LAG is how stale the table may be relative to its sources. Snowflake schedules refreshes to meet it.
TARGET_LAG = DOWNSTREAMmeans "refresh only as often as the dynamic tables that depend on me need". - Refresh mode: with
REFRESH_MODE = AUTO(the default) Snowflake uses incremental refresh, processing only changed source rows, when the query supports it, and a full refresh otherwise. You can setINCREMENTALorFULLexplicitly; check the chosen mode withSHOW DYNAMIC TABLES. - Dynamic tables can read other dynamic tables, forming a pipeline DAG that Snowflake refreshes in dependency order with consistent snapshots.
- You can't
INSERTorUPDATEthem directly; the query is the only source of truth.
Compared with streams and tasks, dynamic tables suit transformations you can express as a single SELECT (joins, aggregations, flattening). Streams and tasks remain the tool for procedural logic, calling procedures, or exact control over when and how changes are applied.
Monitor refreshes with INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY() or Snowsight's graph view, and suspend with ALTER DYNAMIC TABLE ... SUSPEND when you need to pause costs.
Example
CREATE OR REPLACE DYNAMIC TABLE order_lines
TARGET_LAG = DOWNSTREAM
WAREHOUSE = etl_wh
AS
SELECT e.src:id::INT AS order_id, i.value:sku::STRING AS sku, i.value:qty::INT AS qty
FROM raw_events e, LATERAL FLATTEN(input => e.src:items) i;
CREATE OR REPLACE DYNAMIC TABLE sku_daily
TARGET_LAG = '15 minutes'
WAREHOUSE = etl_wh
AS
SELECT sku, SUM(qty) AS units
FROM order_lines
GROUP BY sku;
SHOW DYNAMIC TABLES LIKE 'SKU_DAILY'; -- check refresh_mode, target_lag, scheduling_state
ALTER DYNAMIC TABLE sku_daily REFRESH; -- manual refresh when neededRemember
- Define the result as a query plus a TARGET_LAG; Snowflake keeps it fresh
- AUTO refresh mode picks incremental when the query allows it
- Chain dynamic tables; use DOWNSTREAM lag for intermediate steps
Watch out for
- Setting a very low TARGET_LAG on everything, which keeps warehouses busy and costs credits.