A stream records what changed in a table since you last read it. A task runs SQL on a schedule or after another task. Together they give you a simple incremental pipeline inside Snowflake: the stream says what is new, and the task processes it.
Streams
CREATE STREAM orders_stream ON TABLE raw.orders; SELECT * FROM orders_stream;
A stream does not copy data. It stores an offset into the table's change history, and when you query it, Snowflake returns the rows that changed since that offset, with extra columns: METADATA$ACTION (INSERT or DELETE), METADATA$ISUPDATE and METADATA$ROW_ID. An update shows up as a DELETE and an INSERT pair, both with ISUPDATE = TRUE.
The offset moves forward only when you consume the stream in a DML statement that commits, for example an INSERT ... SELECT or a MERGE that reads from it. A plain SELECT does not advance it. Because of that, if the MERGE fails and rolls back, the stream still holds the same changes, so nothing is lost.
Tasks
CREATE TASK merge_orders
WAREHOUSE = etl_wh
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
MERGE INTO core.orders t
USING orders_stream s ON t.order_id = s.order_id
WHEN MATCHED AND s.METADATA$ACTION = 'INSERT' THEN UPDATE SET ...
WHEN NOT MATCHED AND s.METADATA$ACTION = 'INSERT' THEN INSERT (...) VALUES (...);The WHEN SYSTEM$STREAM_HAS_DATA condition makes the task skip empty runs, so you do not pay to start a warehouse for nothing. Tasks can use your warehouse, or serverless compute that Snowflake manages. Tasks can also depend on each other (AFTER other_task) to form a graph.
Limits to know
A stream has a staleness period tied to the table's retention. If you do not consume it for long enough, it can become stale and you need to recreate it. For simple transformations, dynamic tables (next question) can replace this whole stream, task and MERGE pattern.