A Deep Dive into Snowflake Streams & Tasks

What Are Streams?

A Snowflake Stream is a change-tracking object that records DML operations (INSERT, UPDATE, DELETE) made to a table. Think of it as a CDC (Change Data Capture) mechanism built into the platform — no Kafka, no Debezium required.

-- Create a stream on a source table
CREATE OR REPLACE STREAM orders_stream
  ON TABLE raw.orders;

Once created, every INSERT, UPDATE, or DELETE on raw.orders is recorded in orders_stream with metadata columns:

What Are Tasks?

A Task is a scheduled or event-triggered execution unit that runs a single SQL statement or stored procedure call.

CREATE OR REPLACE TASK process_new_orders
  WAREHOUSE = COMPUTE_WH
  SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
AS
  CALL sp_process_orders();

The WHEN clause is the magic — the task only executes (and consumes warehouse credits) when the stream actually has new data. Zero new rows = zero cost.

Building an Event-Driven Pipeline

Here's a complete pattern: raw orders → transformation → curated layer, driven entirely by Streams and Tasks:

-- 1. Stream on raw table
CREATE STREAM raw_orders_stream ON TABLE raw.orders;

-- 2. Task to process new records
CREATE TASK transform_orders
  WAREHOUSE = TRANSFORM_WH
  SCHEDULE = '1 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw_orders_stream')
AS
INSERT INTO curated.orders
SELECT
  order_id,
  customer_id,
  UPPER(status)           AS status,
  amount * 1.1            AS amount_with_tax,
  CURRENT_TIMESTAMP()     AS processed_at
FROM raw_orders_stream
WHERE METADATA$ACTION = 'INSERT'
  AND METADATA$ISUPDATE = FALSE;

-- 3. Resume the task (tasks start suspended)
ALTER TASK transform_orders RESUME;

Stream Staleness

Important: A stream becomes stale if it is not consumed within the data retention period (default 14 days). Stale streams cannot be read and must be dropped and recreated.

Monitor stream age with:

SELECT SYSTEM$STREAM_GET_TABLE_TIMESTAMP('raw_orders_stream');

Conclusion

Streams + Tasks give you a serverless, event-driven pipeline inside Snowflake with:

• Zero polling cost — the WHEN clause skips empty runs
• Exactly-once semantics — streams advance only when the consuming transaction commits
• No external orchestrator — no Airflow, no dbt Cloud scheduler needed for simple pipelines