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$actioncolumn 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
| Concept | Purpose | Key Notes |
|---|---|---|
| STREAM | Captures table changes (insert/update/delete) | Must be on a table with primary key or unique identifier |
| TASK | Schedules SQL to process stream data | Requires warehouse and must be started with ALTER TASK RESUME |
| metadata$action | Indicates change type in stream | Values: INSERT, UPDATE, DELETE |
| MERGE | Applies changes from stream to target table | Use 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.
