Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 38 ยท Streams, Tasks and Pipelines

Dynamic Tables and Declarative Pipelines

Learn Snowflake dynamic tables: declare a query and a target lag, let Snowflake refresh incrementally, chain tables into pipelines and compare them to streams and tasks.

With streams and tasks you write the pipeline's mechanics yourself: capture changes, schedule the merge, handle failures. A dynamic table lets you describe only the result you want and how fresh it must be. Snowflake works out when and how to refresh it.

Dynamic tables run only in Snowflake, so their SQL is read-only here. The runnable examples model a refresh in SQLite so the semantics are clear.

Declaring a dynamic table

SQL
CREATE DYNAMIC TABLE daily_revenue
  TARGET_LAG = '15 minutes'
  WAREHOUSE  = etl_wh
AS
  SELECT o.order_date, c.region, SUM(o.amount) AS revenue
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  GROUP BY o.order_date, c.region;

That one statement replaces a stream, a merge and a task. You can then query daily_revenue like any table. Parts of the definition:

  • AS <query>: the transformation. It can join several tables, use aggregates and window functions, and read other dynamic tables.
  • TARGET_LAG: the maximum staleness you accept, such as '1 minute', '4 hours' or DOWNSTREAM. Snowflake schedules refreshes to keep the data no older than this.
  • WAREHOUSE: the compute used for refreshes.
  • REFRESH_MODE: AUTO (default), INCREMENTAL or FULL.
  • INITIALIZE: ON_CREATE (default) fills it immediately; ON_SCHEDULE waits for the first refresh.

Incremental or full refresh

In incremental mode Snowflake processes only the changes in the source tables since the last refresh, much like a stream-and-merge, and applies them to the result. In full mode it recomputes the whole query. With AUTO it picks incremental when the query is supported, and otherwise falls back to full. Some constructs (certain functions, non-deterministic expressions) force a full refresh. Check which is used:

SQL
SHOW DYNAMIC TABLES LIKE 'daily_revenue';       -- refresh_mode column
SELECT * FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
         NAME => 'daily_revenue'))
ORDER BY refresh_start_time DESC;

If a table mostly changes in tiny parts, incremental refresh is far cheaper. If every refresh rewrites most of the result, full costs about the same.

A lag calculator

TARGET_LAG is a promise about age. When one dynamic table feeds another, lags add up through the chain; DOWNSTREAM lets upstream tables refresh only when a consumer needs it.

Python Freshness of a chain (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The worst-case age of the last table is the sum of every layer's period and runtime. If it is over the target you choose a shorter lag, a bigger warehouse (shorter duration) or fewer layers.

Modelling a refresh

A dynamic table behaves like a result table that is rebuilt (or patched) from its query. Here is the full refresh version using plain SQL:

SQL A table defined by a query (stand-in) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

An incremental refresh would only recompute the days that received new order lines. The next example does that: it patches only the affected days.

SQL Incremental refresh of changed days (stand-in) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The patch touched a single day instead of the whole table: that is why incremental refresh scales.

Operating dynamic tables

SQL
ALTER DYNAMIC TABLE daily_revenue REFRESH;            -- refresh now
ALTER DYNAMIC TABLE daily_revenue SUSPEND;            -- stop refreshing
ALTER DYNAMIC TABLE daily_revenue RESUME;
ALTER DYNAMIC TABLE daily_revenue SET TARGET_LAG = '1 hour';

You can see the whole graph in Snowsight (Monitoring, Dynamic Tables), including each table's actual lag. Dynamic tables are billed as warehouse time for refreshes plus storage, so lag is your cost dial: a 1-minute lag refreshes far more often than a 1-hour one.

Dynamic tables, streams and tasks, or materialized views?

Need Choose
Declarative transformation, joins, freshness target Dynamic table
Custom logic per change, side effects, calling procedures Stream plus task
Single-table aggregate, always current, optimizer rewrite Materialized view
Fixed batch times (nightly job) Task

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does TARGET_LAG express?

    Snowflake schedules refreshes so the table is no older than the target lag.
  2. 2. Which refresh mode processes only changes in sources?

    Incremental refresh applies just the deltas; FULL recomputes the whole query.
  3. 3. Which feature fits a multi-table join that must stay fresh within 10 minutes?

    Materialized views are single-table; dynamic tables support joins and a lag target.
  4. 4. What does TARGET_LAG = DOWNSTREAM mean?

    A table with downstream lag follows the needs of the dynamic tables that read it.

Summary

  • A dynamic table declares a query and a target lag; Snowflake manages refreshes.
  • Incremental mode processes only changes; AUTO picks the mode.
  • Chains add up their lags; use DOWNSTREAM for intermediate layers.
  • Cost is the lag dial; choose dynamic tables for declarative pipelines, streams and tasks for custom logic.

Next: Designing an ELT pipeline