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
In a nutshell
  • 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_DATA in WHEN skips 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.

Source tableinserts, updates, deletestracked byStreamoffset and metadata columnsread byTaskWHEN stream has dataMERGE intoTarget table

How it works

Stream types

TypeCapturesUse it for
Standard (default)Inserts, updates and deletes as a net changeFull change data capture on tables
Append-onlyInserts only; updates and deletes are ignoredEvent or log tables, cheaper to track
Insert-onlyInserts only, on external tables and Iceberg tables you do not manageSources 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 columnMeaning
METADATA$ACTIONINSERT or DELETE
METADATA$ISUPDATETRUE if the row is part of an update
METADATA$ROW_IDUnique, 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.

Watch out

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 ... RESUME is required.
  • SCHEDULE takes an interval such as '5 MINUTE' or a USING CRON expression with a time zone.
  • A WHEN condition 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 TASK on 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

OptionHowBest for
User managedWAREHOUSE = <name>Predictable workloads, shared warehouse, existing sizing
ServerlessOmit WAREHOUSE; Snowflake sizes computeVariable 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.

Root taskscheduleAFTERLoad taskCOPY or MERGEAFTERTransform taskprocedureAFTERPublish taskrefresh summary

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

RequirementChoice
Control over merge logic, deletes, procedural stepsStreams and tasks
Query defines the result and lag is minutesDynamic table
Latency of seconds to a minute from file arrivalSnowpipe, 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_DATA in WHEN skips 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 drop SCHEDULE and 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 SCHEDULE polling 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.