Data Engineering · Topic 15 of 16
Streams and Tasks - Change Capture and Scheduling
Build change data pipelines with streams and tasks: stream types, offsets and staleness, schedules, task graphs, triggered and serverless tasks.
- 5 min
- Standard, append-only and insert-only streams; updates appear as DELETE plus INSERT with
METADATA$ISUPDATE. - Offset advances only through a committed DML statement; stale streams must be recreated; extension default is 14 days.
SYSTEM$STREAM_HAS_DATAinWHENskips empty runs and saves warehouse credits.
What it is
A stream records the changes made to a table since a point in time, so a pipeline can process only new and changed rows. A task runs a SQL statement or stored procedure on a schedule, on a condition or after another task. Together they form the classic incremental pipeline.
A stream stores no data. It is an offset plus the table's own change tracking, and it exposes the rows that changed after that offset.
How it works
Stream types
| Type | Captures | Use it for |
|---|---|---|
| Standard (default) | Inserts, updates and deletes as a net change | Full change data capture on tables |
| Append-only | Inserts only; updates and deletes are ignored | Event or log tables, cheaper to track |
| Insert-only | Inserts only, on external tables and Iceberg tables you do not manage | Sources that only report new files or rows |
Streams can be created on tables, views, directory tables and dynamic tables. A standard stream shows an update as two rows, a delete and an insert, flagged by metadata columns.
| Metadata column | Meaning |
|---|---|
METADATA$ACTION | INSERT or DELETE |
METADATA$ISUPDATE | TRUE if the row is part of an update |
METADATA$ROW_ID | Unique, unchanging row identifier |
Streams that read a view or other changing source need change tracking enabled on the underlying tables.
Offsets and staleness
The stream offset moves forward only when the stream is used in a DML statement that commits, such as INSERT ... SELECT FROM stream or MERGE. A plain SELECT does not advance it. Several statements in one transaction see the same stream contents, which gives repeatable reads.
A stream goes stale if its offset falls outside the table's data retention window, because the changes can no longer be read. Snowflake can extend the retention period of the source table up to MAX_DATA_EXTENSION_TIME_IN_DAYS (default 14) to prevent that, but you should consume streams regularly. A stale stream must be recreated, and rows that changed in the gap are lost to the stream.
Do not rely on SELECT to "drain" a stream. Consume it with a committed DML statement, and consume it before retention runs out.
SYSTEM$STREAM_HAS_DATA('<stream>') returns TRUE if the stream has changes, and is used in a task WHEN clause.
Tasks
CREATE TASK merge_orders
WAREHOUSE = etl_wh
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
MERGE INTO orders_target t
USING orders_stream s ON t.id = s.id
WHEN MATCHED AND s.METADATA$ACTION = 'INSERT' THEN UPDATE SET t.amount = s.amount
WHEN NOT MATCHED THEN INSERT (id, amount) VALUES (s.id, s.amount);
ALTER TASK merge_orders RESUME;
- Tasks are created suspended, so
ALTER TASK ... RESUMEis required. SCHEDULEtakes an interval such as'5 MINUTE'or aUSING CRONexpression with a time zone.- A
WHENcondition lets Snowflake skip the run, and the warehouse stays suspended, when there is nothing to do. - A task runs with the privileges of its owner role, which needs
EXECUTE TASKon the account and the privileges the SQL needs. - Only one run of a task executes at a time; if a run is still going when the next one is due, that scheduled run is skipped.
Compute for tasks
| Option | How | Best for |
|---|---|---|
| User managed | WAREHOUSE = <name> | Predictable workloads, shared warehouse, existing sizing |
| Serverless | Omit WAREHOUSE; Snowflake sizes compute | Variable or short runs, no warehouse to manage |
Serverless tasks bill for compute actually used, and an initial size can be hinted with USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE.
Task graphs and triggered tasks
A task graph (DAG) has a root task with a schedule and child tasks linked by AFTER. Children run when all their predecessors finish. Resume the children first and the root last, or use SYSTEM$TASK_DEPENDENTS_ENABLE, because resuming the root starts the schedule. A graph can also have a finalizer task that always runs at the end, for cleanup or notifications.
A triggered task has no SCHEDULE. It uses a WHEN SYSTEM$STREAM_HAS_DATA(...) condition and runs shortly after the stream gets data, with a default minimum trigger interval of 30 seconds. It suits event driven pipelines and avoids polling on a clock.
EXECUTE TASK runs a task or graph once on demand. Task run history is in INFORMATION_SCHEMA.TASK_HISTORY and the ACCOUNT_USAGE view. Use ERROR_INTEGRATION on a task to send failure notifications.
Choosing streams and tasks
| Requirement | Choice |
|---|---|
| Control over merge logic, deletes, procedural steps | Streams and tasks |
| Query defines the result and lag is minutes | Dynamic table |
| Latency of seconds to a minute from file arrival | Snowpipe, then a triggered task |
Common mistakes
Querying a stream with SELECT and expecting it to empty. Only a committed DML consumer advances the offset.
Letting a stream go stale. A long outage of the consuming task can exceed retention.
Resuming the root before the children. The graph may run with some tasks still suspended.
Forgetting that tasks start suspended. Nothing runs until RESUME.
What to remember for the exam
- Standard, append-only and insert-only streams; updates appear as DELETE plus INSERT with
METADATA$ISUPDATE. - Offset advances only through a committed DML statement; stale streams must be recreated; extension default is 14 days.
SYSTEM$STREAM_HAS_DATAinWHENskips empty runs and saves warehouse credits.- Tasks are created suspended; run as the owner role; serverless tasks omit the warehouse.
- Task graphs use
AFTER; triggered tasks dropSCHEDULEand react to stream data.
Check yourself
A stream is only read with SELECT every hour. Why does it keep growing?
SELECT does not advance the offset. Only a committed DML statement that consumes the stream does.
How do you stop a task from running a warehouse when nothing changed?
Add
WHEN SYSTEM$STREAM_HAS_DATA('stream')so the run is skipped without using compute.What does a triggered task replace?
The
SCHEDULEpolling interval. It runs when the stream has new data.
Topic 15 of 16 in Data Engineering
Test yourself on Data Engineering
The full Snowflake ARA-C01 question bank has 687 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.