Streams and Tasks

Streams and Tasks From Scratch

Streams track table changes and tasks run scheduled SQL. Together they let you build incremental pipelines inside Snowflake.

The Concepts You Must Own

• ✓Streams capture change metadata for inserts, updates, and deletes on a source table.
• ✓Tasks run SQL on a schedule or after another task completes.
• ✓Task graphs chain multiple dependent steps.
• ✓Streams are consumed when used in DML such as INSERT or MERGE.
• ✓Staleness happens if changes are not consumed within retention.

Step-by-Step Workflow

• ✓Create a source table and a stream on it.
• ✓Write a MERGE that reads changed rows from the stream.
• ✓Create a task to run the MERGE on a schedule.
• ✓Chain downstream tasks for Silver and Gold transformations.
• ✓Monitor task history, failures, lag, and stream staleness.

CREATE STREAM ORDERS_STREAM
  ON TABLE RAW.ORDERS;

CREATE TASK MERGE_ORDERS_TASK
  WAREHOUSE = WH_TRANSFORM_M
  SCHEDULE = '5 MINUTE'
AS
MERGE INTO SILVER.ORDERS tgt
USING ORDERS_STREAM src
  ON tgt.order_id = src.order_id
WHEN MATCHED THEN UPDATE SET status = src.status, updated_at = src.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, status, updated_at)
VALUES (src.order_id, src.status, src.updated_at);

ALTER TASK MERGE_ORDERS_TASK RESUME;

Do not read the example as magic syntax to memorize. Read it as a production habit: name the objects clearly, make assumptions visible, preserve enough metadata to debug later, and keep the business promise attached to the SQL.

Common Mistakes That Break Snowflake Projects

How to debug this topic

Start by asking what promise failed: freshness, correctness, access, speed, or cost. Then inspect the Snowflake evidence: query history, warehouse metering, task history, copy history, grants, row counts, and sample records. Good Snowflake debugging is not guessing. It is reading the platform metadata until the failure has a shape.

Production Notes

• ✓Use task history views for monitoring and alerting.
• ✓Keep tasks small and composable instead of one giant SQL script.
• ✓Document whether each incremental model handles deletes.
• ✓Have a full-refresh fallback for corrupted incremental state.

Explain It Like a Professional

Streams track changes and tasks schedule SQL. A common pattern is stream plus MERGE plus task. Mention that streams are consumed by DML, can become stale, and need monitoring. Tasks can form graphs, but production still needs alerting and recovery procedures.

Mini project

Build RAW to SILVER incremental orders with a stream and task, then a second task that updates GOLD.DAILY_REVENUE after the first task succeeds.

Questions you should answer out loud

• ✓How would you explain Streams and Tasks to a non-technical manager?
• ✓Which Snowflake objects, roles, or SQL statements does this topic use?
• ✓What can fail in production and which metadata view would you inspect first?
• ✓What is the cost or security risk if this is implemented carelessly?
• ✓How would you test that the result is correct and rerunnable?