Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

How to Process Stream Data in Snowflake: Simple Guide

To process stream data in Snowflake, create a STREAM object on a table to capture change data, then use a TASK to query and apply these changes periodically. This lets you handle inserts, updates, and deletes in near real-time within Snowflake.
📐

Syntax

STREAM tracks changes on a table, capturing inserts, updates, and deletes.

TASK schedules SQL statements to process stream data automatically.

Basic syntax includes creating a stream, then creating a task that queries the stream and applies changes.

sql
CREATE OR REPLACE STREAM my_stream ON TABLE my_table;

CREATE OR REPLACE TASK my_task
  WAREHOUSE = my_warehouse
  SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
  MERGE INTO target_table t
  USING my_stream s
  ON t.id = s.id
  WHEN MATCHED AND s.metadata$action = 'DELETE' THEN DELETE
  WHEN MATCHED THEN UPDATE SET t.col = s.col
  WHEN NOT MATCHED THEN INSERT (id, col) VALUES (s.id, s.col);
💻

Example

This example shows how to create a stream on a source table, then a task that merges changes into a target table every minute.

sql
CREATE OR REPLACE TABLE source_table (id INT, value STRING);
CREATE OR REPLACE TABLE target_table (id INT, value STRING);

CREATE OR REPLACE STREAM source_stream ON TABLE source_table;

CREATE OR REPLACE TASK process_stream_task
  WAREHOUSE = compute_wh
  SCHEDULE = 'USING CRON * * * * * UTC'
AS
  MERGE INTO target_table t
  USING source_stream s
  ON t.id = s.id
  WHEN MATCHED AND s.metadata$action = 'DELETE' THEN DELETE
  WHEN MATCHED THEN UPDATE SET t.value = s.value
  WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

-- Start the task
ALTER TASK process_stream_task RESUME;
Output
Task process_stream_task created and started. It will run every minute to apply changes from source_stream to target_table.
⚠️

Common Pitfalls

  • Not starting the task after creation, so stream processing never runs.
  • Using streams on tables without a unique key, causing merge conflicts.
  • Ignoring metadata$action column to handle deletes properly.
  • Not assigning a warehouse to the task, so it cannot execute.
sql
/* Wrong: Task created but not started */
CREATE OR REPLACE TASK my_task
  WAREHOUSE = my_wh
  SCHEDULE = 'USING CRON * * * * * UTC'
AS
  SELECT * FROM my_stream;
-- Forgot: ALTER TASK my_task RESUME;

/* Right: Start the task */
ALTER TASK my_task RESUME;
📊

Quick Reference

ConceptPurposeKey Notes
STREAMCaptures table changes (insert/update/delete)Must be on a table with primary key or unique identifier
TASKSchedules SQL to process stream dataRequires warehouse and must be started with ALTER TASK RESUME
metadata$actionIndicates change type in streamValues: INSERT, UPDATE, DELETE
MERGEApplies changes from stream to target tableUse to handle all change types in one statement
✅

Key Takeaways

Create a STREAM on your source table to capture data changes.
Use a TASK to run SQL that processes stream data regularly.
Always handle the metadata$action column to manage inserts, updates, and deletes.
Assign a warehouse to your task and start it with ALTER TASK RESUME.
Ensure your source table has a unique key for reliable merge operations.