Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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

ConceptDescriptionExample
StreamTracks changes on a tableCREATE STREAM my_stream ON TABLE source_table;
TaskSchedules SQL to run automaticallyCREATE TASK my_task WAREHOUSE=wh SCHEDULE='USING CRON * * * * * UTC' AS SQL;
Resume TaskStarts the task executionALTER TASK my_task RESUME;
Consume StreamUse stream data in SQLINSERT 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.