Streams & Tasks
A stream tracks row-level changes (inserts, updates, deletes) on a table since the last time it was consumed, using change data capture built on the same Time Travel metadata. A standard stream tracks inserts, updates and deletes. An append-only stream tracks only inserts, on tables, dynamic tables and views. An insert-only stream does the same for external tables and externally managed Iceberg tables. A task runs SQL on a schedule, after another task completes, or, as a triggered task with no schedule, whenever a stream has new data, wiring streams into automated pipelines.
| TIME | STATEMENT | RESULT | OFFSET |
|---|---|---|---|
| T0 | CREATE STREAM orders_s ON TABLE orders | stream created | T0 |
| T1 | INSERT … (3 rows) INTO orders | 3 changes pending | T0 |
| T2 | SELECT * FROM orders_s | returns 3 rows | T0 |
| T3 | BEGIN; INSERT … SELECT FROM orders_s; ROLLBACK | nothing committed | T0 |
| T4 | MERGE INTO dim … USING orders_s …; (commits) | changes consumed | T4 |
| T5 | SELECT * FROM orders_s | returns 0 rows | T4 |
Only a committed DML statement that reads the stream moves the offset.
CREATE STREAM orders_stream ON TABLE orders; CREATE TASK process_orders WAREHOUSE = etl_whSCHEDULE = '5 MINUTE'WHEN SYSTEM$STREAM_HAS_ ('orders_DATA stream' )ASMERGE INTO orders_summary tgt USING orders_stream src ON tgt.order_id = src.order_id WHEN MATCHED THEN UPDATE SET tgt.amount = src.amountWHEN NOT MATCHED THEN INSERT (order_id, amount) VALUES (src. order_ id, src.amount); ALTER TASK process_orders RESUME; -- tasks are created suspended by default
A stream's offset advances only when it's read inside a DML statement that consumes it (typically the INSERT/MERGE/UPDATE reading from it), not on a plain SELECT, so repeated exploratory SELECTs against a stream don't accidentally lose the changes it's tracking. SYSTEM$STREAM_HAS_DATA lets a task skip a run entirely if there's nothing new, avoiding wasted warehouse time.
Tasks chain via AFTER: a child task fires once its predecessor completes successfully, building a DAG without any external orchestrator. A single root task in a DAG can have a CRON-style schedule; downstream tasks fire off completion, not a clock. Leave out WAREHOUSE and a task runs on serverless compute that Snowflake sizes for you.
SCHEDULEtakes an interval ('5 MINUTE') or a CRON expression ('USING CRON 0 2 * * * UTC').- A task body can be one SQL statement or a
CALLto a stored procedure. - Resume child tasks before the root, or run
SYSTEM$TASK_to resume them all.DEPENDENTS_ ENABLE('<root>') - A finalizer task is created with
FINALIZE = <root task>.
ROOT TASKPROCESS_ORDERSSCHEDULE = '5 MINUTE'WHEN the stream has data
CHILD TASKBUILD_SALESAFTER process_orders
CHILD TASKBUILD_STOCKAFTER process_orders
CHILD TASKPUBLISHAFTER build_sales, build_stock
then, success or failure
FINALIZER TASKNOTIFYFINALIZE = process_orders
Every task is created suspendedresume the children first, then the root
- Tasks are created suspended. Forgetting
ALTER TASK ... RESUMEis the most common "why isn't my pipeline running" mistake in real projects. - A stream doesn't store the changed data itself, only metadata pointing at the relevant Time Travel versions, so it's cheap to create. Snowflake keeps extra history for an unconsumed stream, up to 14 days by default. After that the stream is stale and you have to recreate it. Recreating the source table makes it stale too.
- Materialized views (Part 2) auto-refresh on a Snowflake-managed schedule and can't join tables. Dynamic tables can join and aggregate across multiple sources and let you tune the freshness target directly.
- Account → database → schema → object; every database gets PUBLIC and INFORMATION_SCHEMA automatically.
- Table type is fixed at creation: permanent (full protection), transient (no Fail-safe), temporary (session-scoped).
- Secure views hide logic and protect filtered data; materialized views store single-table results; dynamic tables declare a target freshness across joins.
- Streams track changes via Time Travel metadata from the moment they're created; tasks schedule or chain SQL and start suspended until
ALTER TASK ... RESUME.

