Skip to main content
Snowflake intermediate Lesson 7 of 10

Streams and Tasks

Capture changes with a stream, consume them exactly once, and schedule the work with tasks — including task DAGs and the suspend that stops everything silently.

Streams and tasks are Snowflake’s built-in pipeline primitives: a stream tells you what changed, a task runs SQL on a schedule, and together they replace a surprising amount of external orchestration.

A stream over a table

create or replace table orders_raw (
    order_id    number,
    customer_id number,
    status      string,
    amount      number(10,2),
    updated_at  timestamp_ntz
);

create or replace stream orders_stream on table orders_raw;
+---------------------------------------------+
| status                                      |
|---------------------------------------------|
| Stream ORDERS_STREAM successfully created.  |
+---------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.288s

Instant, and it stores nothing — a stream is an offset into the table’s change history plus three metadata columns.

insert into orders_raw values
    (1001, 1, 'pending',   25.50, current_timestamp()),
    (1002, 2, 'completed', 12.00, current_timestamp());

select order_id, status, metadata$action, metadata$isupdate, metadata$row_id
from orders_stream;
+----------+-----------+-----------------+-------------------+------------------------------------------+
| ORDER_ID | STATUS    | METADATA$ACTION | METADATA$ISUPDATE | METADATA$ROW_ID                          |
|----------+-----------+-----------------+-------------------+------------------------------------------|
|     1001 | pending   | INSERT          | False             | a1b2c3d4e5f60718293a4b5c6d7e8f9012345678 |
|     1002 | completed | INSERT          | False             | b2c3d4e5f60718293a4b5c6d7e8f901234567890 |
+----------+-----------+-----------------+-------------------+------------------------------------------+
2 Row(s) produced. Time Elapsed: 0.402s

Now update one row:

update orders_raw set status = 'completed', updated_at = current_timestamp()
where order_id = 1001;

select order_id, status, metadata$action, metadata$isupdate from orders_stream;
+----------+-----------+-----------------+-------------------+
| ORDER_ID | STATUS    | METADATA$ACTION | METADATA$ISUPDATE |
|----------+-----------+-----------------+-------------------|
|     1002 | completed | INSERT          | False             |
|     1001 | completed | INSERT          | True              |
+----------+-----------+-----------------+-------------------+
2 Row(s) produced. Time Elapsed: 0.402s

Order 1001 now appears once as an INSERT with ISUPDATE = True. The stream shows the net change since its offset — it was inserted and then updated, so the net effect is one row at its final value. An update to a row that existed before the offset shows as a DELETE and an INSERT pair, both with ISUPDATE = True.

Consuming advances the offset

create or replace table orders_clean like orders_raw;

merge into orders_clean t
using orders_stream s on t.order_id = s.order_id
when matched and s.metadata$action = 'DELETE' and s.metadata$isupdate = 'FALSE'
    then delete
when matched and s.metadata$action = 'INSERT'
    then update set t.status = s.status, t.amount = s.amount, t.updated_at = s.updated_at
when not matched and s.metadata$action = 'INSERT'
    then insert values (s.order_id, s.customer_id, s.status, s.amount, s.updated_at);
+-------------------------+-------------------------+
| number of rows inserted | number of rows updated  |
|-------------------------+-------------------------|
|                       2 |                       0 |
+-------------------------+-------------------------+
1 Row(s) produced. Time Elapsed: 0.911s
select count(*) from orders_stream;
+----------+
| COUNT(*) |
|----------|
|        0 |
+----------+
1 Row(s) produced. Time Elapsed: 0.402s

Empty. The MERGE committed, so the offset advanced past those changes — exactly once, with no bookkeeping table of your own.

The rule that follows: a stream advances only when consumed by a committed DML statement. A SELECT never advances it, which makes inspection safe. And because a failed transaction does not commit, a failed pipeline step leaves the changes waiting for the retry.

Two consumers need two streams. A single stream read by two jobs means whichever runs first consumes the changes and the second sees nothing.

Append-only streams

create or replace stream events_stream on table events_raw append_only = true;

For an event table that is never updated, this is cheaper — it tracks only new rows and does not need the delete-side bookkeeping. There is also insert_only for external tables, and streams work on views and directory tables too.

Staleness

A stream is backed by Time Travel, so it goes stale if left unconsumed longer than the table’s retention:

show streams like 'ORDERS_STREAM';
+---------------+---------------+---------+--------------+-------------------------------+
| name          | table_name    | type    | stale        | stale_after                   |
|---------------+---------------+---------+--------------+-------------------------------|
| ORDERS_STREAM | ORDERS_RAW    | DELTA   | false        | 2026-09-10 15:02:11.000 -0700 |
+---------------+---------------+---------+--------------+-------------------------------+
1 Row(s) produced. Time Elapsed: 0.194s

Past stale_after, the stream cannot be read and must be recreated — losing the changes it had not yet reported. Extending the source table’s data_retention_time_in_days extends the window, and a task that runs more often than the retention avoids it entirely.

Tasks

create or replace task merge_orders
    warehouse = bookshop_wh
    schedule = '5 minute'
    when system$stream_has_data('orders_stream')
as
    merge into orders_clean t
    using orders_stream s on t.order_id = s.order_id
    when matched and s.metadata$action = 'INSERT'
        then update set t.status = s.status, t.amount = s.amount
    when not matched and s.metadata$action = 'INSERT'
        then insert values (s.order_id, s.customer_id, s.status, s.amount, s.updated_at);
+---------------------------------------+
| status                                |
|---------------------------------------|
| Task MERGE_ORDERS successfully created. |
+---------------------------------------+
1 Row(s) produced. Time Elapsed: 0.288s
alter task merge_orders resume;

Tasks are created suspended. Forgetting the RESUME is the most common reason a pipeline “does nothing” with no error anywhere — there is no failure to see, because nothing ran.

The WHEN system$stream_has_data(...) clause is what makes a five-minute schedule cheap: the condition is evaluated in the services layer for free, and the warehouse is only started when there is work. Without it, an idle pipeline resumes a warehouse 288 times a day to do nothing.

Schedules take either an interval or cron:

schedule = '5 minute'
schedule = 'using cron 0 6 * * * Europe/London'      -- 06:00 London, DST-aware

Omitting warehouse makes it serverless — Snowflake sizes the compute itself and bills per second of actual work, which is usually cheaper for short, frequent tasks:

create or replace task merge_orders
    schedule = '5 minute'
    user_task_managed_initial_warehouse_size = 'xsmall'
as ...

Task DAGs

A task with an AFTER clause becomes a child, running only when its predecessors succeed:

create or replace task load_staging
    warehouse = bookshop_wh
    schedule = 'using cron 0 5 * * * UTC'
as
    insert into stg_orders select * from orders_clean where updated_at > dateadd('day', -1, current_timestamp());

create or replace task build_marts
    warehouse = bookshop_wh
    after load_staging
as
    insert overwrite into daily_revenue
    select ordered_at, count(*), sum(amount) from stg_orders group by 1;

create or replace task refresh_exports
    warehouse = bookshop_wh
    after build_marts
as
    copy into @bookshop_stage/export/revenue_ from (select * from daily_revenue) overwrite = true;

-- children first, root last
alter task refresh_exports resume;
alter task build_marts resume;
alter task load_staging resume;
select system$task_dependents_enable('load_staging');
+---------------------------------------------------+
| SYSTEM$TASK_DEPENDENTS_ENABLE('LOAD_STAGING')     |
|---------------------------------------------------|
| Task LOAD_STAGING and its dependents are resumed. |
+---------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.402s

The ordering rule matters: a root task cannot be resumed while it has suspended children in some configurations, and resuming the root first can let it fire before the children are live. system$task_dependents_enable does the whole tree correctly in one call.

Only the root carries a schedule; children inherit their timing from their parents.

Watching them run

select
    name,
    state,
    scheduled_time,
    completed_time,
    error_code,
    error_message
from table(information_schema.task_history(
    scheduled_time_range_start => dateadd('hour', -6, current_timestamp())))
order by scheduled_time desc
limit 5;
+------------------+-----------+-------------------------------+-------------------------------+------------+---------------------------------+
| NAME             | STATE     | SCHEDULED_TIME                | COMPLETED_TIME                | ERROR_CODE | ERROR_MESSAGE                   |
|------------------+-----------+-------------------------------+-------------------------------+------------+---------------------------------|
| REFRESH_EXPORTS  | SUCCEEDED | 2026-09-09 15:12:04.000 -0700 | 2026-09-09 15:12:09.114 -0700 | NULL       | NULL                            |
| BUILD_MARTS      | SUCCEEDED | 2026-09-09 15:11:58.000 -0700 | 2026-09-09 15:12:03.882 -0700 | NULL       | NULL                            |
| LOAD_STAGING     | SUCCEEDED | 2026-09-09 15:11:50.000 -0700 | 2026-09-09 15:11:57.204 -0700 | NULL       | NULL                            |
| MERGE_ORDERS     | SKIPPED   | 2026-09-09 15:10:00.000 -0700 | NULL                          | NULL       | NULL                            |
| MERGE_ORDERS     | SUCCEEDED | 2026-09-09 15:05:00.000 -0700 | 2026-09-09 15:05:03.402 -0700 | NULL       | NULL                            |
+------------------+-----------+-------------------------------+-------------------------------+------------+---------------------------------+
5 Row(s) produced. Time Elapsed: 0.688s

SKIPPED is the WHEN clause working — no data in the stream, no warehouse started, no credits spent.

A failure looks like this:

| BUILD_MARTS | FAILED | 2026-09-09 16:11:58.000 -0700 | ... | 100038 | Numeric value 'n/a' is not recognized |
| REFRESH_EXPORTS | SKIPPED | NULL | NULL | NULL | NULL |

The child was skipped rather than running on incomplete data. Note that nothing emails you — alerting means querying task_history yourself, or attaching a notification integration.

Tasks that overrun their schedule do not overlap: the next run is skipped while one is still going. A task suspended by suspend_task_after_num_failures stops silently after repeated errors, which is another reason to monitor state rather than assume.

Practice

1. Create a stream, insert rows, and select from it twice. 
2 Row(s) produced.
2 Row(s) produced.

Identical both times — SELECT does not advance the offset. Then run a MERGE that consumes it and select again: zero rows. That asymmetry between reads and DML is the whole contract.

2. Update a pre-existing row and inspect the metadata columns. 
+----------+-----------------+-------------------+
| ORDER_ID | METADATA$ACTION | METADATA$ISUPDATE |
|----------+-----------------+-------------------|
|     1001 | DELETE          | True              |
|     1001 | INSERT          | True              |
+----------+-----------------+-------------------+

Two rows for one update. A MERGE that treats every DELETE as a deletion will drop the row — which is why the merge above checks metadata$isupdate = 'FALSE' before deleting.

3. Create a task and forget to resume it. 
show tasks like 'MERGE_ORDERS';
+--------------+-----------+------------+----------------+
| name         | state     | schedule   | warehouse      |
|--------------+-----------+------------+----------------|
| MERGE_ORDERS | suspended | 5 minute   | BOOKSHOP_WH    |
+--------------+-----------+------------+----------------+

state = suspended, and task_history is empty. There is no error to find, which is exactly what makes this failure mode expensive — check state first whenever a task appears not to exist.

4. Build a two-task DAG and confirm the child skips when the parent fails. 
| LOAD_STAGING | FAILED  | ... | 100038 | Numeric value 'n/a' is not recognized |
| BUILD_MARTS  | SKIPPED | NULL | NULL  | NULL                                  |

The mart was left holding yesterday’s correct data rather than being rebuilt from a failed load. That is the same property dbt build gives you with SKIP, achieved here inside the warehouse.

Next: roles, grants and secure sharing — who can see what, and how to share data without copying it.

Frequently Asked Questions

What is a stream in Snowflake?
A change-tracking object that records what has changed in a table since the last time the stream was consumed. It stores no data itself — it is an offset plus metadata columns, so creating one is instant regardless of table size.
When does a Snowflake stream advance?
Only when it is consumed inside a DML statement — an INSERT, MERGE, UPDATE or DELETE that reads from it and commits. A plain SELECT shows the changes without advancing, which is why you can inspect a stream safely but must not build logic on repeated SELECTs.
What is the difference between a standard and an append-only stream?
A standard stream reports inserts, updates and deletes, representing an update as a delete plus an insert. An append-only stream reports only inserts, which is cheaper and is what you want for event tables that are never modified.
Why is my Snowflake task not running?
Most often because it was never resumed — tasks are created suspended and need `ALTER TASK ... RESUME`. In a DAG, resuming the root is not enough: every child must be resumed too, and children must be resumed before the root.