Data Loading, Unloading, and Connectivity · Topic 5 of 7
Streams, Tasks and Dynamic Tables
Build continuous pipelines in Snowflake with streams for change data capture, scheduled and triggered tasks, task graphs and declarative dynamic tables.
- 6 min
- Includes a diagram
- Streams: standard (all DML), append-only (inserts), insert-only (external and externally managed Iceberg tables).
- Offsets advance only on committed DML consumption; stale streams must be recreated.
- Tasks: serverless or warehouse, interval or CRON schedule,
WHEN SYSTEM$STREAM_HAS_DATA, created suspended.
What it is
A stream records the changes (inserts, updates, deletes) made to a table since you last consumed it. A task runs SQL or a stored procedure on a schedule or when a stream has data. A dynamic table is a table defined by a query that Snowflake keeps fresh automatically, so you declare the result instead of coding the steps.
How it works
Streams: change data capture
A stream stores an offset, a point in the table's version history. Querying the stream returns the rows changed between that offset and now, plus three metadata columns.
| Column | Meaning |
|---|---|
METADATA$ACTION | INSERT or DELETE |
METADATA$ISUPDATE | TRUE when the row is half of an update, shown as a DELETE and INSERT pair |
METADATA$ROW_ID | Unique, stable ID of the row for tracking changes |
| Stream type | Captures | Created on |
|---|---|---|
| Standard (delta) | Inserts, updates and deletes, netted to the final change | Tables, views, dynamic tables, directory tables, Snowflake managed Iceberg tables |
| Append-only | Inserts only | Standard tables, dynamic tables, Snowflake managed Iceberg tables, views |
| Insert-only | Inserts only | External tables and externally managed Iceberg tables |
The offset advances only when the stream is consumed in a DML statement that commits, such as INSERT INTO target SELECT ... FROM my_stream or a MERGE. A plain SELECT does not move it, so you can inspect a stream safely.
A stream becomes stale if its offset falls outside the source table's data retention period. Snowflake temporarily extends the table's retention for an unconsumed stream, up to MAX_DATA_EXTENSION_TIME_IN_DAYS (default 14 days). SHOW STREAMS and DESCRIBE STREAM show the STALE_AFTER time. A stale stream must be recreated.
Consume streams regularly. A stream left alone past its retention goes stale, and the only fix is to recreate it, which loses the pending changes.
Tasks: scheduling the work

CREATE TASK merge_orders
WAREHOUSE = transform_wh
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
MERGE INTO orders_clean t USING orders_stream s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.amount = s.amount
WHEN NOT MATCHED THEN INSERT (id, amount) VALUES (s.id, s.amount);
| Choice | Options |
|---|---|
| Compute | Serverless (omit WAREHOUSE; Snowflake sizes compute from recent runs) or a user managed warehouse |
| Schedule | An interval such as '5 MINUTE', or 'USING CRON 0 2 * * * UTC' for calendar times |
| Trigger | WHEN SYSTEM$STREAM_HAS_DATA(...) skips the run (and the compute) when there is nothing new. A triggered task with no schedule runs when its stream gets data |
| Body | One SQL statement, a CALL to a stored procedure, or procedural logic in Snowflake Scripting |
Key behaviors:
- New tasks are suspended. Run
ALTER TASK ... RESUMEbefore anything happens.EXECUTE TASKruns one instance on demand for testing. - One run at a time. If a run is still going when the next one is due, that scheduled run is skipped.
- Privileges. The owning role needs the global
EXECUTE TASKprivilege, plusEXECUTE MANAGED TASKfor serverless tasks. - Monitoring. The
TASK_HISTORYtable function shows runs and errors.
Task graphs
Tasks chain into a task graph (a DAG). Only the root task has a schedule or trigger; each child names its parents with AFTER.
- Children of the same parent run in parallel; a task with several parents waits for all of them.
- A graph holds up to 1,000 tasks, and one task can have up to 100 parents and 100 children.
- A finalizer task runs once after the graph finishes or fails, for cleanup or alerts.
- Suspend the root task before changing the graph, then resume it.
Dynamic tables: declare the result
CREATE DYNAMIC TABLE daily_sales
TARGET_LAG = '10 minutes'
WAREHOUSE = transform_wh
AS
SELECT order_date, SUM(amount) AS total
FROM orders GROUP BY order_date;
TARGET_LAGis how stale the result may become, not a fixed interval. The minimum is 60 seconds.TARGET_LAG = DOWNSTREAMrefreshes only when a dynamic table that reads from it needs fresh data.WAREHOUSEis required; refreshes run on it.REFRESH_MODE: the main values areAUTO(default), which lets Snowflake choose,INCREMENTALprocesses only changes,FULLrecomputes everything.INITIALIZE:ON_CREATE(default) fills the table at creation;ON_SCHEDULEwaits for the first scheduled refresh.- You cannot INSERT, UPDATE or DELETE rows in a dynamic table; only its refreshes change data.
| Need | Streams and tasks | Dynamic tables | Materialized views |
|---|---|---|---|
| Style | Imperative: you write MERGE logic and schedules | Declarative: write the SELECT, set the lag | Declarative, always current |
| Joins and complex SQL | Anything SQL or procedures can do | Joins, aggregates, many query types | Single table, limited SQL |
| Control | Exact timing, procedures, external calls | Freshness target only | None needed |
| Best for | Custom CDC, side effects, non SQL steps | Multi step transformation pipelines | Speeding up repeated queries on one table |
"Simplest way to keep a joined, aggregated table within 15 minutes of its sources" is a dynamic table. "Run a stored procedure only when new rows arrive" is a stream plus a task with SYSTEM$STREAM_HAS_DATA.
Common mistakes
Expecting a SELECT to clear a stream. Only a committed DML statement that reads the stream advances its offset.
Forgetting to resume a task. Tasks are created suspended; a new task never runs until ALTER TASK ... RESUME.
Scheduling child tasks. In a task graph only the root has a schedule; children use AFTER.
Treating target lag as a cron schedule. Target lag is a freshness goal; Snowflake decides when to refresh to meet it.
What to remember for the exam
- Streams: standard (all DML), append-only (inserts), insert-only (external and externally managed Iceberg tables).
- Offsets advance only on committed DML consumption; stale streams must be recreated.
- Tasks: serverless or warehouse, interval or CRON schedule,
WHEN SYSTEM$STREAM_HAS_DATA, created suspended. - Task graphs: root schedule,
AFTERfor children, up to 1,000 tasks, one finalizer per root. - Dynamic tables:
TARGET_LAG(minimum 60 seconds orDOWNSTREAM), requiredWAREHOUSE, no manual DML.
Check yourself
Does running SELECT on a stream advance its offset?
No. Only a DML statement that consumes the stream inside a committed transaction advances the offset.
How do you avoid paying for task runs when no new data arrived?
Add WHEN SYSTEM$STREAM_HAS_DATA('stream_name') so the run is skipped when the stream is empty.
What does TARGET_LAG = DOWNSTREAM mean on a dynamic table?
It refreshes only when a downstream dynamic table that depends on it needs fresh data.
Topic 5 of 7 in Data Loading, Unloading, and Connectivity
Test yourself on Data Loading, Unloading, and Connectivity
The full Snowflake SnowPro Core COF-C03 question bank has 845 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.