Snowflake – Streams and Tasks

Most warehouses end up with a pipeline: raw data lands, something transforms it, a modelled table appears. The usual answer is to add an orchestrator — Airflow, dbt Cloud, a cron box. Snowflake has the two pieces natively, and for a pipeline that lives entirely inside Snowflake they are enough: streams tell you what changed, tasks run SQL on a schedule.

A stream is an offset, not a copy

This is the sentence that makes everything else make sense. A stream does not store rows. It stores a position in the table's change history, and when you query it, Snowflake computes the difference between that position and the table's current state.

CREATE OR REPLACE STREAM learn_snowflake.staging.orders_stream
  ON TABLE learn_snowflake.staging.raw_orders;

-- Nothing yet: the stream starts at "now".
SELECT COUNT(*) FROM learn_snowflake.staging.orders_stream;

INSERT INTO learn_snowflake.staging.raw_orders (order_id, total)
VALUES ('A-1002', 199.00), ('A-1003', 45.50);

-- Now two rows, plus three metadata columns.
SELECT order_id, total,
       METADATA$ACTION,       -- INSERT or DELETE
       METADATA$ISUPDATE,     -- TRUE if part of an UPDATE
       METADATA$ROW_ID        -- a stable id for the row
FROM   learn_snowflake.staging.orders_stream;

An UPDATE shows as a DELETE and an INSERT pair, both with METADATA$ISUPDATE = TRUE. That is how you tell a genuine delete from the removal half of an update — a distinction that matters as soon as you are maintaining a target table.

The consumption rule

Here is the part that surprises everyone. A stream advances when it is consumed inside a DML statement, and only then. Selecting from it does not move it; a MERGE, INSERT or CREATE TABLE AS SELECT that reads it does — and it advances to the transaction's start point.

Two consequences follow, and both cause real bugs:

• You can look as often as you like. A SELECT against a stream is a free peek, which makes debugging pleasant.
• Reading it twice in one DML statement gives you the same rows twice, and consuming it once discards everything. If a single statement needs the changes for two different targets, either create two streams on the same table, or land the changes in a temporary table first and read that twice.

-- Safe: look without consuming.
SELECT COUNT(*) FROM learn_snowflake.staging.orders_stream;
SELECT SYSTEM$STREAM_HAS_DATA('learn_snowflake.staging.orders_stream');

-- Consumes: after this the stream is empty.
INSERT INTO learn_snowflake.marts.orders_history
SELECT order_id, total, CURRENT_TIMESTAMP()
FROM   learn_snowflake.staging.orders_stream
WHERE  METADATA$ACTION = 'INSERT';

A stream also has a staleness limit tied to the source table's Time Travel retention. If a stream is not consumed within that window it goes stale and cannot be read at all — you recreate it and backfill by other means. A stream on a table with retention set to zero is a stream that breaks the first time a job is paused for a day.

Stream types

CREATE OR REPLACE STREAM events_stream
  ON TABLE learn_snowflake.staging.events
  APPEND_ONLY = TRUE;

Use append-only whenever the source is genuinely append-only. It is cheaper to maintain and the consuming SQL is simpler, since there is no METADATA$ACTION to branch on.

Tasks

A task runs one SQL statement — or a stored procedure, if you need several — on a schedule or after another task.

CREATE OR REPLACE TASK learn_snowflake.staging.load_orders
  WAREHOUSE = loading_wh
  SCHEDULE  = 'USING CRON 0 * * * * UTC'        -- hourly, on the hour
  WHEN SYSTEM$STREAM_HAS_DATA('learn_snowflake.staging.orders_stream')
AS
MERGE INTO learn_snowflake.marts.dim_orders t
USING learn_snowflake.staging.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.total = s.total, t.updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED AND s.METADATA$ACTION = 'INSERT'
     THEN INSERT (order_id, total, updated_at)
          VALUES (s.order_id, s.total, CURRENT_TIMESTAMP());

-- ⚠️ Tasks are created SUSPENDED. This is the step everyone forgets.
ALTER TASK learn_snowflake.staging.load_orders RESUME;

That WHEN clause is the whole pattern. It is evaluated before the warehouse is resumed, so a task that fires every hour against an empty stream costs nothing at all — you get frequent checking without paying for frequent running.

SCHEDULE takes either a cron expression with a time zone, or a simple interval such as '5 MINUTE'. Prefer cron for anything a human reasons about, since an interval drifts relative to the clock.

Serverless tasks

Omit WAREHOUSE and give the task a compute size instead, and Snowflake provides the compute:

CREATE OR REPLACE TASK refresh_summary
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
  SCHEDULE = 'USING CRON 15 2 * * * UTC'
AS
  INSERT INTO marts.daily_summary SELECT * FROM staging.v_daily_summary;

Serverless tasks are billed per second of actual use with no 60-second minimum and no idle time, and Snowflake adjusts the size based on observed runs. For short, frequent tasks that is usually cheaper than a warehouse. For long tasks, or ones that should share a warehouse's warm cache with neighbouring work, a named warehouse still wins.

Task trees

Tasks chain with AFTER, forming a DAG with one root that carries the schedule:

CREATE TASK t_load    WAREHOUSE = loading_wh SCHEDULE = 'USING CRON 0 2 * * * UTC' AS ...;
CREATE TASK t_clean   WAREHOUSE = loading_wh AFTER t_load  AS ...;
CREATE TASK t_model   WAREHOUSE = loading_wh AFTER t_clean AS ...;
CREATE TASK t_verify  WAREHOUSE = loading_wh AFTER t_clean AS ...;   -- runs beside t_model

-- ⚠️ Resume children BEFORE the root, or the root fires with no children attached.
ALTER TASK t_verify RESUME;
ALTER TASK t_model  RESUME;
ALTER TASK t_clean  RESUME;
ALTER TASK t_load   RESUME;

SELECT SYSTEM$TASK_DEPENDENTS_ENABLED('learn_snowflake.staging.t_load');

A child runs only if its parent succeeded. There is no retry and no branching — a task tree is a dependency graph, not a workflow engine. When you need conditional paths, retries with backoff, or steps that reach outside Snowflake, that is the point at which an orchestrator earns its keep.

When to use this instead of an orchestrator

Streams and tasks are not a replacement for Airflow or dbt, and pretending otherwise leads to a pipeline nobody can debug. The honest boundary is about where the work lives.

The strongest argument for the native option is the last row. A three-step nightly pipeline does not justify a scheduler to install, monitor, secure and upgrade, and a task tree has no infrastructure at all. The strongest argument against is observability: an orchestrator gives you a UI, a history and alerting out of the box, whereas here you write the monitoring yourself — which is the next section.

There is also a middle option worth knowing about. A dynamic table declares the query you want kept fresh and a target lag, and Snowflake works out the incremental refresh itself — no stream, no task, no MERGE to get right:

CREATE OR REPLACE DYNAMIC TABLE marts.daily_orders
  TARGET_LAG = '1 hour'
  WAREHOUSE  = loading_wh
AS
SELECT order_date, COUNT(*) AS orders, SUM(total) AS revenue
FROM   staging.raw_orders
GROUP  BY order_date;

When the transformation is expressible as one query, that is less code and less to get wrong. Reach for streams and tasks when it is not — multiple statements, procedural logic, or a MERGE whose behaviour on deletes you need to control precisely.

Monitoring

-- Did it run, and did it work?
SELECT name, scheduled_time, state, error_message, query_id
FROM   TABLE(information_schema.task_history(
         SCHEDULED_TIME_RANGE_START => DATEADD('day', -2, CURRENT_TIMESTAMP())))
WHERE  state != 'SUCCEEDED'
ORDER  BY scheduled_time DESC;

SHOW TASKS IN SCHEMA learn_snowflake.staging;   -- check `state` is `started`

A suspended task is silent, and a failing task is silent too unless something is watching. Both belong in the alerting from lesson 16. And when a task will not fire at all, check SHOW TASKS first: the answer is a suspended task nine times out of ten, usually because somebody replaced it with CREATE OR REPLACE and did not resume it again.

Next: roles and grants.