How to Use Stream with Task in Snowflake for Automated Data Processing
In Snowflake, you use a
STREAM to track changes in a table and a TASK to automate running SQL statements that process these changes. Create a stream on your source table, then create a task that runs a SQL statement to consume the stream data, enabling automated incremental data processing.Syntax
A stream tracks changes (inserts, updates, deletes) on a table. A task schedules SQL statements to run automatically.
Basic syntax parts:
CREATE STREAM stream_name ON TABLE source_table;- creates a stream on a table.CREATE TASK task_name WAREHOUSE = warehouse_name SCHEDULE = 'USING CRON cron_expression' AS SQL_statement;- creates a task that runs SQL on a schedule.ALTER TASK task_name RESUME;- starts the task.
sql
CREATE STREAM my_stream ON TABLE source_table; CREATE TASK my_task WAREHOUSE = my_warehouse SCHEDULE = 'USING CRON 0 * * * * UTC' AS INSERT INTO target_table SELECT * FROM my_stream;
Example
This example shows how to create a stream on a source table and a task that runs every hour to insert new changes into a target table.
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 0 * * * * UTC' AS INSERT INTO target_table SELECT id, value FROM source_stream; ALTER TASK process_stream_task RESUME;
Output
Task process_stream_task created and resumed; stream source_stream tracks changes on source_table.
Common Pitfalls
Common mistakes when using streams with tasks include:
- Not resuming the task after creation, so it never runs.
- Consuming the stream incorrectly, causing repeated or missed data.
- Using a stream on a table without a unique key, which can cause duplicate rows.
- Not having a warehouse assigned or running out of credits.
sql
/* Wrong: Task created but not resumed, so it won't run */ CREATE TASK my_task WAREHOUSE = my_wh SCHEDULE = 'USING CRON * * * * * UTC' AS INSERT INTO target_table SELECT * FROM my_stream; /* Right: Resume the task to activate it */ ALTER TASK my_task RESUME;
Quick Reference
| Concept | Description | Example |
|---|---|---|
| Stream | Tracks changes on a table | CREATE STREAM my_stream ON TABLE source_table; |
| Task | Schedules SQL to run automatically | CREATE TASK my_task WAREHOUSE=wh SCHEDULE='USING CRON * * * * * UTC' AS SQL; |
| Resume Task | Starts the task execution | ALTER TASK my_task RESUME; |
| Consume Stream | Use stream data in SQL | INSERT INTO target SELECT * FROM my_stream; |
Key Takeaways
Create a stream on the source table to track data changes.
Create a task to run SQL statements that consume the stream data automatically.
Always resume the task after creation to enable execution.
Ensure your stream and task use a warehouse with enough credits.
Use streams with tables that have unique keys to avoid duplicates.
