Dynamic Tables

Streams and tasks are imperative: capture changes, write a merge, schedule it, resume it. Dynamic tables are declarative — you write the SELECT that defines the result and how stale it may be, and Snowflake works out the rest.

One statement instead of a pipeline

create or replace dynamic table stg_orders
    target_lag = '5 minutes'
    warehouse = bookshop_wh
as
select
    order_id,
    customer_id,
    ordered_at,
    lower(status) as status,
    amount
from orders_raw
where status != 'pending';

+-----------------------------------------------+
| status                                        |
|-----------------------------------------------|
| Dynamic table STG_ORDERS successfully created.|
+-----------------------------------------------+
1 Row(s) produced. Time Elapsed: 2.104s

That replaces a stream, a merge statement, a task and a RESUME. No offsets to manage and no metadata$action branching to get right.

insert into orders_raw values (1006, 3, '2026-01-14', 'COMPLETED', 31.20, current_timestamp());

Within the lag window:

select order_id, status, amount from stg_orders order by order_id;

+----------+-----------+--------+
| ORDER_ID | STATUS    | AMOUNT |
|----------+-----------+--------|
|     1001 | completed |  25.50 |
|     1002 | completed |  12.00 |
|     1003 | returned  |  40.00 |
|     1006 | completed |  31.20 |
+----------+-----------+--------+
4 Row(s) produced. Time Elapsed: 0.402s

The row arrived with no pipeline code. Note it is a real table — reading it costs a scan, not a recomputation, unlike a view.

Chaining

Dynamic tables can read other dynamic tables, and Snowflake derives the dependency graph:

create or replace dynamic table customer_orders
    target_lag = downstream
    warehouse = bookshop_wh
as
select
    c.customer_id,
    c.full_name,
    c.country_code,
    count(o.order_id)          as order_count,
    coalesce(sum(o.amount), 0) as lifetime_value
from customers c
left join stg_orders o on o.customer_id = c.customer_id
group by 1, 2, 3;

create or replace dynamic table country_revenue
    target_lag = '15 minutes'
    warehouse = bookshop_wh
as
select country_code, sum(lifetime_value) as revenue, count(*) as customers
from customer_orders
group by 1;

+----------------------------------------------------+
| status                                             |
|----------------------------------------------------|
| Dynamic table COUNTRY_REVENUE successfully created.|
+----------------------------------------------------+
1 Row(s) produced. Time Elapsed: 2.882s

target_lag = downstream means “refresh only as often as whatever depends on me needs”. country_revenue wants 15-minute freshness, so customer_orders inherits that, and stg_orders keeps its own 5-minute target. Set the lag at the edges of the graph and let the middle inherit — that is the pattern that avoids refreshing intermediate tables far more often than anyone reads them.

select name, target_lag, scheduling_state:state::string as state, refresh_mode
from table(information_schema.dynamic_tables());

+------------------+-------------+---------+--------------+
| NAME             | TARGET_LAG  | STATE   | REFRESH_MODE |
|------------------+-------------+---------+--------------|
| STG_ORDERS       | 5 minutes   | ACTIVE  | INCREMENTAL  |
| CUSTOMER_ORDERS  | DOWNSTREAM  | ACTIVE  | INCREMENTAL  |
| COUNTRY_REVENUE  | 15 minutes  | ACTIVE  | INCREMENTAL  |
+------------------+-------------+---------+--------------+
3 Row(s) produced. Time Elapsed: 0.402s

Check the refresh mode

REFRESH_MODE is the column that decides whether a dynamic table is cheap or ruinous. Some constructs cannot be tracked incrementally, and Snowflake silently falls back to recomputing everything on every refresh:

create or replace dynamic table orders_ranked
    target_lag = '5 minutes'
    warehouse = bookshop_wh
as
select
    order_id,
    customer_id,
    amount,
    current_timestamp() as refreshed_at,        -- non-deterministic
    row_number() over (partition by customer_id order by ordered_at desc) as rn
from stg_orders;

+------------------+------------+---------+--------------+
| NAME             | TARGET_LAG | STATE   | REFRESH_MODE |
|------------------+------------+---------+--------------|
| ORDERS_RANKED    | 5 minutes  | ACTIVE  | FULL         |
+------------------+------------+---------+--------------+

FULL — every five minutes, forever, over the whole table. On 4 billion rows that is a five-figure monthly bill for a table nobody reads that often.

Common causes of a fallback to FULL:

Removing current_timestamp() alone restores incremental refresh here. Always check REFRESH_MODE after creating a dynamic table — and pass refresh_mode = incremental explicitly if you want creation to fail rather than silently degrade:

create or replace dynamic table orders_ranked
    target_lag = '5 minutes'
    warehouse = bookshop_wh
    refresh_mode = incremental
as select ...;

091003 (22000): Invalid expression in dynamic table definition:
incremental refresh is not supported for the expression CURRENT_TIMESTAMP().

A loud failure at creation beats a quiet bill.

Monitoring refreshes

select
    name,
    state,
    refresh_action,
    data_timestamp,
    refresh_start_time,
    datediff('second', refresh_start_time, refresh_end_time) as seconds
from table(information_schema.dynamic_table_refresh_history())
order by refresh_start_time desc
limit 5;

+------------------+-----------+----------------+-------------------------------+---------+
| NAME             | STATE     | REFRESH_ACTION | REFRESH_START_TIME            | SECONDS |
|------------------+-----------+----------------+-------------------------------+---------|
| COUNTRY_REVENUE  | SUCCEEDED | INCREMENTAL    | 2026-09-09 16:45:00.114 -0700 |       2 |
| CUSTOMER_ORDERS  | SUCCEEDED | INCREMENTAL    | 2026-09-09 16:44:55.882 -0700 |       4 |
| STG_ORDERS       | SUCCEEDED | NO_DATA        | 2026-09-09 16:44:50.204 -0700 |       0 |
| STG_ORDERS       | SUCCEEDED | INCREMENTAL    | 2026-09-09 16:39:50.118 -0700 |       3 |
+------------------+-----------+----------------+-------------------------------+---------+
4 Row(s) produced. Time Elapsed: 0.688s

NO_DATA means nothing changed, so nothing was recomputed — the equivalent of a SKIPPED task, and free. A dynamic table over a quiet source costs almost nothing.

Whether the graph is meeting its promise:

select name, target_lag_sec, mean_lag_sec, maximum_lag_sec
from table(information_schema.dynamic_table_graph_history());

+------------------+----------------+--------------+-----------------+
| NAME             | TARGET_LAG_SEC | MEAN_LAG_SEC | MAXIMUM_LAG_SEC |
|------------------+----------------+--------------+-----------------|
| STG_ORDERS       |            300 |          182 |             294 |
| CUSTOMER_ORDERS  |            900 |          402 |             884 |
| COUNTRY_REVENUE  |            900 |          412 |             891 |
+------------------+----------------+--------------+-----------------+
3 Row(s) produced. Time Elapsed: 0.402s

Maximum lag under target on all three. If maximum consistently exceeds target, the refresh cannot keep up — widen the lag or size up the warehouse.

Operating them

alter dynamic table stg_orders suspend;
alter dynamic table stg_orders resume;
alter dynamic table stg_orders refresh;                   -- force one now
alter dynamic table stg_orders set target_lag = '1 hour';

+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

Suspending a table suspends everything downstream of it — the graph will not serve stale data built on a frozen parent.

Choosing between the three

• Dynamic tables for transformation chains — the default for new pipelines.
• Streams and tasks when you need to do something, not just derive a table: unload a file, call a stored procedure, branch on a condition.
• Materialized views for a single-table aggregate you want the optimiser to substitute automatically into queries that never mention it.

A note on dbt, if you use it: dbt supports materialized='dynamic_table', which lets these sit inside the same project as everything else — ref, tests and docs all keep working, and Snowflake handles the refresh instead of a scheduled dbt run.

Practice

+----------+-----------+--------+
| ORDER_ID | STATUS    | AMOUNT |
|----------+-----------+--------|
|     1006 | completed |  31.20 |
+----------+-----------+--------+

| ORDERS_RANKED | 5 minutes | ACTIVE | FULL |

+------------------+-------------+--------------+
| NAME             | TARGET_LAG  | REFRESH_MODE |
|------------------+-------------+--------------|
| STG_ORDERS       | DOWNSTREAM  | INCREMENTAL  |
| CUSTOMER_ORDERS  | DOWNSTREAM  | INCREMENTAL  |
| COUNTRY_REVENUE  | 15 minutes  | INCREMENTAL  |
+------------------+-------------+--------------+

alter dynamic table stg_orders suspend;
select * from table(information_schema.dynamic_tables());

+------------------+---------+
| NAME             | STATE   |
|------------------+---------|
| STG_ORDERS       | SUSPENDED |
| CUSTOMER_ORDERS  | SUSPENDED |
| COUNTRY_REVENUE  | SUSPENDED |
+------------------+---------+

That closes the Snowflake track. The thread through all ten lessons: storage is immutable and compute is disposable — pruning, cloning, Time Travel, sharing and dynamic tables are all consequences of that one design decision.