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
In a nutshell
  • 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.

Raw tableloaded by Snowpipechanges trackedStreamdelta since offsetWHEN has dataTaskMERGE into targetwritesClean tableRaw tablequery definitionDynamic tabletarget lag 5 minutesautomatic refreshAlways fresh result

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.

ColumnMeaning
METADATA$ACTIONINSERT or DELETE
METADATA$ISUPDATETRUE when the row is half of an update, shown as a DELETE and INSERT pair
METADATA$ROW_IDUnique, stable ID of the row for tracking changes
Stream typeCapturesCreated on
Standard (delta)Inserts, updates and deletes, netted to the final changeTables, views, dynamic tables, directory tables, Snowflake managed Iceberg tables
Append-onlyInserts onlyStandard tables, dynamic tables, Snowflake managed Iceberg tables, views
Insert-onlyInserts onlyExternal 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.

Watch out

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

Stream and task pipeline: orders_stream records changes on the orders table; every 5 minutes the merge_orders task checks SYSTEM$STREAM_HAS_DATA, runs a MERGE into orders_clean that consumes the stream and advances its offset, or skips the run with no compute when there is no new data.

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);
ChoiceOptions
ComputeServerless (omit WAREHOUSE; Snowflake sizes compute from recent runs) or a user managed warehouse
ScheduleAn interval such as '5 MINUTE', or 'USING CRON 0 2 * * * UTC' for calendar times
TriggerWHEN 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
BodyOne SQL statement, a CALL to a stored procedure, or procedural logic in Snowflake Scripting

Key behaviors:

  • New tasks are suspended. Run ALTER TASK ... RESUME before anything happens. EXECUTE TASK runs 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 TASK privilege, plus EXECUTE MANAGED TASK for serverless tasks.
  • Monitoring. The TASK_HISTORY table 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.

Root taskschedulethenLoad taskthenTransform task Aruns in parallelLoad taskthenTransform task Bruns in parallelTransform task Aboth doneReport taskwaits for all parents
  • 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_LAG is how stale the result may become, not a fixed interval. The minimum is 60 seconds. TARGET_LAG = DOWNSTREAM refreshes only when a dynamic table that reads from it needs fresh data.
  • WAREHOUSE is required; refreshes run on it.
  • REFRESH_MODE: the main values are AUTO (default), which lets Snowflake choose, INCREMENTAL processes only changes, FULL recomputes everything.
  • INITIALIZE: ON_CREATE (default) fills the table at creation; ON_SCHEDULE waits for the first scheduled refresh.
  • You cannot INSERT, UPDATE or DELETE rows in a dynamic table; only its refreshes change data.
NeedStreams and tasksDynamic tablesMaterialized views
StyleImperative: you write MERGE logic and schedulesDeclarative: write the SELECT, set the lagDeclarative, always current
Joins and complex SQLAnything SQL or procedures can doJoins, aggregates, many query typesSingle table, limited SQL
ControlExact timing, procedures, external callsFreshness target onlyNone needed
Best forCustom CDC, side effects, non SQL stepsMulti step transformation pipelinesSpeeding up repeated queries on one table
Exam tip

"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, AFTER for children, up to 1,000 tasks, one finalizer per root.
  • Dynamic tables: TARGET_LAG (minimum 60 seconds or DOWNSTREAM), required WAREHOUSE, 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.