Streams, tasks, and dynamic tables | Snowflake course

Picture it this way

A stream is a bookmark in a book of changes. It does not copy the table. A task is an alarm clock that runs a statement, and it can look at the bookmark so it only rings when new changes exist. A dynamic table is a recipe taped to the fridge: Snowflake recooks the recipe often enough to meet the freshness you asked for.

Streams

A stream on a table tracks inserts, updates, and deletes since the stream was last consumed. Consuming means using the stream in a DML statement that commits. A plain SELECT does not advance the bookmark. METADATA$ACTION and METADATA$ISUPDATE tell you what happened.

Standard streams show the net change. Append-only streams are for insert-only sources. A stream on a dynamic table must be created with ON DYNAMIC TABLE. ON TABLE errors. Those streams are standard streams only. Append-only is not supported there. The dynamic table itself must be refreshing incrementally. A full refresh has no incremental history to track.

CREATE OR REPLACE STREAM orders_stream ON TABLE orders;
SELECT METADATA$ACTION, METADATA$ISUPDATE, order_id
FROM orders_stream;

Tasks, including triggered tasks

A task runs SQL, a procedure call, or a procedural block. Give it a warehouse, or leave the warehouse off and set TARGET_COMPLETION_INTERVAL for a serverless task. Do not set both. A new task is suspended until you ALTER TASK ... RESUME. Resuming usually requires the task owner and the privilege to run the statement.

A task with WHEN SYSTEM$STREAM_HAS_DATA('orders_stream') skips its statement when the bookmark is empty. Triggered tasks can fire when a stream on a table, view, directory table, or supported Iceberg table changes, so you do not poll. Snowflake's migration guide says a stream on a dynamic table should use a scheduled task plus that WHEN clause, not a triggered task. The triggered-task page lists dynamic tables as a supported source for the table itself. Match the pattern to the object you created, and read the page for that object before you productionize it.

Dynamic tables

A dynamic table materializes a SELECT and refreshes to a target lag. The documented minimum target lag is one minute. Intermediate tables can use TARGET_LAG = DOWNSTREAM so they refresh only when a later table needs them. Joins and aggregations belong here. Stored procedures, MERGE, and loops do not. Use streams and tasks for those.

SCHEDULER = DISABLE has been generally available since March 2026. You cannot combine it with TARGET_LAG. Snowflake will not schedule the refresh. An orchestrator such as Airflow or dbt calls the refresh, and refreshes do not cascade upstream or downstream. That is the right switch when a DAG already owns the order.

Changing the SELECT reinitializes the dynamic table. Non-deterministic functions such as RANDOM() force a full refresh when the mode is AUTO, and they fail the create when you demand incremental. Dynamic tables store the current result, not a history of changes. Slowly changing dimension type 2 still wants a stream and a task.

CREATE OR REPLACE DYNAMIC TABLE orders_daily
  TARGET_LAG = '1 hour'
  WAREHOUSE = LEARN_WH
AS
SELECT region, SUM(amount) AS amount_sum
FROM orders
GROUP BY region;

Process only what changed

A stream remembers where you stopped, a task acts on it, and a dynamic table can replace both.

ordersSource table
orders_streamBookmark of new changes
merge_orders taskSQL on a schedule
Reporting tableAlways up to date

Step 1 of 3A stream keeps an offset on a table and shows the rows inserted, updated or deleted since it was last read. It does not copy the table.

CREATE STREAM orders_stream ON TABLE orders;

ordersSource table
orders_streamBookmark of new changes
merge_orders taskSQL on a schedule
Reporting tableAlways up to date

Step 2 of 3A task runs SQL on a schedule. With SYSTEM$STREAM_HAS_DATA it skips runs when nothing changed. Using the stream in a MERGE moves the bookmark forward.

CREATE TASK merge_orders WAREHOUSE = wh SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
  AS MERGE INTO daily_sales ...;

ordersSource table
orders_streamBookmark of new changes
merge_orders taskSQL on a schedule
Reporting tableAlways up to date

Step 3 of 3A dynamic table is just a query plus a target lag. Snowflake keeps it refreshed, incrementally when it can, without you writing the stream and task.

CREATE DYNAMIC TABLE daily_sales TARGET_LAG = '15 minutes' WAREHOUSE = wh
  AS SELECT order_date, SUM(amount) FROM orders GROUP BY 1;

Lab: a stream and a dynamic table

Insert a row and read the stream. Then create a dynamic table with a one-hour lag so the trial warehouse is not refreshing every minute. Suspend the task if you resume one. Drop the dynamic table at the end of the day if you want to stop its refresh warehouse from waking up.

USE WAREHOUSE LEARN_WH;
USE DATABASE LEARN_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE STREAM orders_stream ON TABLE orders;
INSERT INTO orders VALUES (20, 'south', 12);
SELECT METADATA$ACTION, order_id, region, amount
FROM orders_stream;

CREATE OR REPLACE DYNAMIC TABLE orders_by_region
  TARGET_LAG = '1 hour'
  WAREHOUSE = LEARN_WH
AS
SELECT region, SUM(amount) AS amount_sum
FROM orders
GROUP BY region;

SELECT * FROM orders_by_region;

Concept summary

A stream is a change bookmark. DML consumes it. SELECT does not.
Tasks are suspended when created. Serverless tasks use a target completion interval instead of a warehouse.
Dynamic tables want a lag of at least one minute, or DOWNSTREAM, unless the scheduler is disabled.
Use tasks for MERGE and procedures. Use dynamic tables for declarative SQL.

Knowledge check

What advances a stream's bookmark?

What is the shortest target lag for a dynamic table?

What does SCHEDULER = DISABLE change?

Official sources

Checked against Snowflake documentation in October 2026. Product behavior still depends on your edition, cloud, and region.