Streams and Tasks
Change tracking, scheduled SQL, task graphs, incremental ELT, stream consumption, staleness, serverless tasks, monitoring, and production recovery patterns.
Quick answer
A stream records the inserts, updates and deletes made to a table since it was last consumed; a task runs SQL on a schedule or after another task. Together they build incremental pipelines: a task MERGEs from a stream, and consuming it in DML advances the stream.
- Selecting from a stream does not advance it; using it in DML does.
- A stream not consumed within the retention period goes stale.
- Tasks are created suspended; resume them.
CREATE STREAM raw.orders_changes ON TABLE raw.orders;
CREATE TASK load_silver_orders
WAREHOUSE = etl_wh
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('raw.orders_changes')
AS
MERGE INTO silver.orders t USING raw.orders_changes s ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET status = s.status
WHEN NOT MATCHED THEN INSERT (order_id, status) VALUES (s.order_id, s.status);
ALTER TASK load_silver_orders RESUME;Streams Track Changes; Tasks Run SQL on a Schedule
Streams and tasks are Snowflake's native building blocks for incremental ELT. A stream tracks changes that happen to a table. A task runs SQL automatically on a schedule or after another task completes. Together, they let you build pipelines that process only new or changed data instead of rebuilding every table from scratch.
The most common pattern is: raw table receives new rows, a stream tracks those new rows, a task wakes up, a MERGE consumes the stream, and the cleaned target table updates. This is powerful, but it also introduces operational responsibilities: streams can become stale, tasks can fail, and incremental logic must be idempotent.
Plain-English definition: a stream is a change bookmark; a task is an automated SQL runner. A pipeline uses the bookmark to process changes on a schedule.
RAW.ORDERS receives rows
|
v
RAW_ORDERS_STREAM tracks changes
|
v
MERGE_ORDERS_TASK runs every 5 minutes
|
v
SILVER.ORDERS updates incrementally
|
v
GOLD tasks update business martsIncremental ELT Saves Time and Compute
Full refresh is simple. For small tables, it may be the best choice. But as data grows, rebuilding every row every few minutes becomes slow and expensive. Incremental processing focuses on what changed. Streams and tasks give Snowflake-native support for that pattern.
| Approach | How it works | Best for | Risk |
|---|---|---|---|
| Full refresh | Rebuild whole table each run. | Small tables or simple daily models. | Wastes compute as data grows. |
| Append only | Insert new rows based on watermark. | Immutable event data. | Duplicates if rerun logic is weak. |
| Stream + task + MERGE | Track changes and merge into target. | Mutable incremental tables. | Stream staleness and bad merge logic. |
| Dynamic table | Snowflake manages refresh from SELECT. | Declarative pipelines that fit limitations. | Refresh behavior/cost misunderstood. |
A Stream Records Change Metadata for a Table
A stream does not simply store a second full copy of the source table. It tracks change information so a query can see rows inserted, updated, or deleted since the stream's current offset. When you consume the stream in DML, such as INSERT or MERGE, the stream advances.
CREATE OR REPLACE TABLE RAW.ORDERS (
order_id STRING,
customer_id STRING,
status STRING,
total_usd NUMBER(12,2),
updated_at TIMESTAMP_NTZ
);
CREATE OR REPLACE STREAM RAW_ORDERS_STREAM
ON TABLE RAW.ORDERS;
INSERT INTO RAW.ORDERS VALUES
('O-1', 'C-1', 'completed', 100.00, CURRENT_TIMESTAMP());
SELECT
order_id,
status,
METADATA$ACTION,
METADATA$ISUPDATE,
METADATA$ROW_ID
FROM RAW_ORDERS_STREAM;| Metadata column | Meaning | Why it matters |
|---|---|---|
| METADATA$ACTION | INSERT or DELETE style change action. | Helps handle deletes and updates. |
| METADATA$ISUPDATE | Whether change is part of an update. | Distinguishes updates from simple inserts/deletes. |
| METADATA$ROW_ID | Stable row identifier for change tracking. | Useful for advanced debugging. |
Streams Advance When Consumed by DML
This is one of the most important details. Selecting from a stream lets you inspect changes, but DML statements such as INSERT, MERGE, or CREATE TABLE AS SELECT can consume the stream and advance it. If you accidentally consume a production stream during testing, the real pipeline may miss changes.
CREATE OR REPLACE TABLE OPS.ORDER_CHANGE_AUDIT (
order_id STRING,
action STRING,
is_update BOOLEAN,
consumed_at TIMESTAMP_NTZ
);
INSERT INTO OPS.ORDER_CHANGE_AUDIT
SELECT
order_id,
METADATA$ACTION,
METADATA$ISUPDATE,
CURRENT_TIMESTAMP()
FROM RAW_ORDERS_STREAM;
-- After this DML commit, the stream offset advances.The Most Common Pattern Is Stream Plus MERGE
For mutable entities, a task can merge stream changes into a Silver table. This combines the incremental nature of streams with the upsert safety of MERGE. The merge still needs deduplication, stable keys, update freshness rules, and delete handling.
CREATE OR REPLACE TABLE SILVER.ORDERS (
order_id STRING,
customer_id STRING,
status STRING,
total_usd NUMBER(12,2),
updated_at TIMESTAMP_NTZ,
is_deleted BOOLEAN DEFAULT FALSE
);
MERGE INTO SILVER.ORDERS tgt
USING (
SELECT *
FROM RAW_ORDERS_STREAM
QUALIFY ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY updated_at DESC
) = 1
) src
ON tgt.order_id = src.order_id
WHEN MATCHED AND src.METADATA$ACTION = 'DELETE' THEN UPDATE SET
is_deleted = TRUE,
updated_at = CURRENT_TIMESTAMP()
WHEN MATCHED AND src.updated_at > tgt.updated_at THEN UPDATE SET
customer_id = src.customer_id,
status = src.status,
total_usd = src.total_usd,
updated_at = src.updated_at,
is_deleted = FALSE
WHEN NOT MATCHED AND src.METADATA$ACTION <> 'DELETE' THEN INSERT (
order_id, customer_id, status, total_usd, updated_at, is_deleted
) VALUES (
src.order_id, src.customer_id, src.status, src.total_usd, src.updated_at, FALSE
);| Design choice | Why it matters |
|---|---|
| Deduping stream input. | A target key may appear more than once in the change window. |
| Update condition. | Prevents stale changes from overwriting newer target data. |
| Delete handling. | Keeps target behavior explicit when source deletes rows. |
| Idempotent target logic. | Protects replay and recovery operations. |
Tasks Run SQL Automatically
A task is a Snowflake object that runs SQL on a schedule or as part of a dependency graph. A task can run with a specified warehouse, or in serverless task mode depending on configuration and use case. Tasks are useful for running incremental merges, refreshing marts, updating audit tables, or calling stored procedures.
CREATE OR REPLACE TASK MERGE_ORDERS_TASK
WAREHOUSE = WH_TRANSFORM_M
SCHEDULE = '5 MINUTE'
AS
MERGE INTO SILVER.ORDERS tgt
USING (
SELECT *
FROM RAW_ORDERS_STREAM
QUALIFY ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY updated_at DESC
) = 1
) src
ON tgt.order_id = src.order_id
WHEN MATCHED AND src.updated_at > tgt.updated_at THEN UPDATE SET
status = src.status,
total_usd = src.total_usd,
updated_at = src.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, status, total_usd, updated_at)
VALUES (src.order_id, src.customer_id, src.status, src.total_usd, src.updated_at);
ALTER TASK MERGE_ORDERS_TASK RESUME;Schedule Tasks Based on Freshness Needs
A task schedule should match the business freshness requirement and the cost of running the SQL. Running a heavy merge every minute is wasteful if the dashboard only needs hourly updates. Running a critical support queue once per day is too slow if agents need current data.
| Schedule style | Example | Use when |
|---|---|---|
| Fixed interval | SCHEDULE = '5 MINUTE'. | Simple recurring pipelines. |
| Cron schedule | USING CRON ... | Business-time schedules such as daily at 7 AM. |
| After dependency | AFTER parent_task. | Task graph where downstream waits for upstream. |
| Triggered by stream data check | WHEN SYSTEM$STREAM_HAS_DATA(...). | Avoid running when no stream changes exist. |
CREATE OR REPLACE TASK MERGE_ORDERS_TASK
WAREHOUSE = WH_TRANSFORM_M
SCHEDULE = '5 MINUTE'
WHEN SYSTEM$STREAM_HAS_DATA('RAW_ORDERS_STREAM')
AS
MERGE INTO SILVER.ORDERS tgt
USING RAW_ORDERS_STREAM src
ON tgt.order_id = src.order_id
WHEN MATCHED THEN UPDATE SET status = src.status, updated_at = src.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, status, total_usd, updated_at)
VALUES (src.order_id, src.customer_id, src.status, src.total_usd, src.updated_at);Task Graphs Chain Work in the Correct Order
Many pipelines have dependencies. Load Raw first, then update Silver, then update Gold, then write freshness audit records. Task graphs let child tasks run after parent tasks succeed. This gives Snowflake a native orchestration pattern for SQL workflows.
CREATE OR REPLACE TASK ROOT_PROCESS_RAW
WAREHOUSE = WH_TRANSFORM_M
SCHEDULE = '15 MINUTE'
AS
CALL OPS.PROCESS_RAW_BATCH();
CREATE OR REPLACE TASK MERGE_SILVER_ORDERS
WAREHOUSE = WH_TRANSFORM_M
AFTER ROOT_PROCESS_RAW
AS
CALL OPS.MERGE_SILVER_ORDERS();
CREATE OR REPLACE TASK BUILD_GOLD_REVENUE
WAREHOUSE = WH_TRANSFORM_M
AFTER MERGE_SILVER_ORDERS
AS
CREATE OR REPLACE TABLE GOLD.DAILY_REVENUE AS
SELECT DATE(order_ts) AS order_date, SUM(total_usd) AS revenue_usd
FROM SILVER.ORDERS
WHERE status NOT IN ('cancelled', 'fraud')
GROUP BY 1;
ALTER TASK ROOT_PROCESS_RAW RESUME;
ALTER TASK MERGE_SILVER_ORDERS RESUME;
ALTER TASK BUILD_GOLD_REVENUE RESUME;| Graph shape | Good for | Risk |
|---|---|---|
| Linear chain | Raw -> Silver -> Gold. | Easy to understand but can become slow. |
| Fan-out | One Silver model feeds multiple Gold marts. | One upstream failure blocks many outputs. |
| Fan-in | Gold waits for several upstream models. | Need clear dependency completion semantics. |
| Stored procedure tasks | Multi-step logic in one call. | Logic can become hidden and hard to test. |
Tasks Need Compute: User-Managed or Serverless
A task can use a named warehouse, which gives you explicit control over size, cost, and workload isolation. Snowflake also supports serverless task execution for eligible task patterns, where Snowflake manages compute sizing. The right choice depends on predictability, governance, and cost preferences.
| Compute style | Benefit | Tradeoff |
|---|---|---|
| Named warehouse | Explicit size, ownership, cost attribution, workload isolation. | You manage sizing and auto-suspend behavior. |
| Serverless task | Snowflake manages compute for task runs. | Need to understand billing, limits, and operational visibility. |
| Dedicated transform warehouse | Predictable transformations isolated from BI. | Can be wasteful if schedule is too frequent. |
| Shared warehouse | Simple early setup. | Contention between pipelines and user queries. |
Streams Can Become Stale If Changes Are Not Consumed
Streams depend on change information retained for the source table. If the stream is not consumed for too long, it can become stale. A stale stream cannot provide the needed change history, and the pipeline may require a full refresh or manual repair. This is one of the most important production risks of stream-based designs.
SHOW STREAMS LIKE 'RAW_ORDERS_STREAM';
-- A common operational habit:
-- alert if a stream has not been consumed recently,
-- if task failures keep repeating,
-- or if the source retention window is close to being exceeded.| Staleness cause | Impact | Recovery |
|---|---|---|
| Task disabled too long. | Stream history expires. | Full refresh target or recreate stream after reconciliation. |
| Repeated task failures. | Changes accumulate until retention risk. | Fix task, process backlog, monitor lag. |
| Source table recreated unexpectedly. | Stream may no longer track expected object. | Recreate stream and rebuild downstream target. |
| No monitoring. | Issue discovered after business reports stale data. | Add task and freshness alerts. |
Append-Only Streams Are Useful for Insert-Only Workloads
Some source tables only receive inserts. For insert-only workloads, append-only stream behavior can be simpler and more efficient because you do not need update/delete semantics. This fits event tables, immutable logs, and raw landing tables where changes should not be updated in place.
CREATE OR REPLACE STREAM RAW_EVENTS_APPEND_STREAM
ON TABLE RAW.EVENTS
APPEND_ONLY = TRUE;
INSERT INTO SILVER.EVENTS_DEDUPED
SELECT
event_id,
event_type,
occurred_at,
payload,
loaded_at
FROM RAW_EVENTS_APPEND_STREAM
QUALIFY ROW_NUMBER() OVER (
PARTITION BY event_id
ORDER BY loaded_at DESC
) = 1;| Use append-only when | Do not use it when |
|---|---|
| Raw events are inserted and never updated/deleted. | Source updates or deletes records. |
| You dedupe downstream by event_id. | You need to capture before/after update behavior. |
| The table is an immutable landing log. | The table mirrors a mutable entity state. |
Build Incremental Pipelines One Boundary at a Time
A common mistake is building a huge task that processes every table and every downstream mart. Better production designs keep boundaries clear: one task processes orders, another processes customers, another builds revenue after the required Silver models are complete.
RAW_ORDERS_STREAM -> TASK_MERGE_SILVER_ORDERS
RAW_CUSTOMERS_STREAM -> TASK_MERGE_SILVER_CUSTOMERS
TASK_MERGE_SILVER_ORDERS
TASK_MERGE_SILVER_CUSTOMERS
-> TASK_BUILD_GOLD_CUSTOMER_REVENUE
TASK_BUILD_GOLD_CUSTOMER_REVENUE
-> TASK_WRITE_FRESHNESS_AUDIT- ✓Keep each task small enough to debug.
- ✓Name tasks after the object they update.
- ✓Write audit records for important task outputs.
- ✓Avoid hiding many unrelated transformations in one stored procedure.
- ✓Make dependencies reflect data needs, not just convenience.
Task History Is Your First Debugging Tool
A task pipeline must be monitored. It is not enough that the task exists. You need to know whether it ran, whether it succeeded, how long it took, what error occurred, and whether downstream data is fresh.
SELECT
name,
state,
scheduled_time,
completed_time,
error_code,
error_message
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
SCHEDULED_TIME_RANGE_START => DATEADD(day, -1, CURRENT_TIMESTAMP())
))
ORDER BY scheduled_time DESC;
-- Freshness check after task graph
SELECT
MAX(order_date) AS latest_order_date,
DATEDIFF('hour', MAX(order_date), CURRENT_DATE()) AS hours_behind
FROM GOLD.DAILY_REVENUE;| Signal | Healthy | Unhealthy |
|---|---|---|
| Task state | Resumed when intended. | Suspended accidentally. |
| Task history | Recent successful runs. | Repeated failures or skipped runs. |
| Duration | Stable runtime. | Runtime spike or timeout. |
| Freshness | Gold output inside SLA. | No fresh data despite successful task. |
| Rows changed | Expected range. | Zero rows or huge unexpected changes. |
Alert on Business Impact, Not Just SQL Failure
A task can succeed and still produce bad business results. For example, a Gold revenue task might run successfully but output zero rows because upstream data was missing. Alerting should include task failures, but also freshness, volume, and quality checks.
-- Stale Gold table
SELECT 'GOLD.DAILY_REVENUE is stale' AS alert
WHERE (
SELECT MAX(order_date)
FROM GOLD.DAILY_REVENUE
) < CURRENT_DATE() - 1;
-- Suspicious zero revenue
SELECT 'Revenue is zero for latest day' AS alert
WHERE EXISTS (
SELECT 1
FROM GOLD.DAILY_REVENUE
WHERE order_date = CURRENT_DATE() - 1
AND revenue_usd = 0
);Know How to Recover When Incremental State Breaks
Stream/task pipelines can fail in ways that require more than rerunning the last SQL statement. The stream may become stale. A merge may write incorrect values. A task may be suspended for days. Recovery plans should be written before production incidents.
| Failure | Recovery option | Tradeoff |
|---|---|---|
| Task failed once. | Fix issue and resume/rerun task. | Simple if stream is still valid. |
| Task failed for days. | Process backlog or full refresh target. | Need staleness and retention checks. |
| Bad merge changed target. | Time Travel restore or targeted repair. | Must avoid undoing legitimate later changes. |
| Stream stale. | Recreate stream and rebuild downstream table. | More compute but restores correctness. |
| Wrong dependency graph. | Suspend tasks, fix graph, backfill affected outputs. | Requires careful communication. |
-- Rebuild target from source of truth when stream state is unusable.
CREATE OR REPLACE TABLE SILVER.ORDERS AS
SELECT
order_id,
customer_id,
status,
total_usd,
updated_at
FROM RAW.ORDERS
QUALIFY ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY updated_at DESC
) = 1;
-- Recreate stream after target/source reconciliation if needed.
CREATE OR REPLACE STREAM RAW_ORDERS_STREAM ON TABLE RAW.ORDERS;Small Frequent Tasks Can Waste Compute
Incremental pipelines are not automatically cheap. A task that wakes up every minute, starts a warehouse, finds no data, and shuts down can waste money. A merge that scans too much target data can become expensive. Use stream data checks, appropriate schedules, clustering where justified, and workload-specific warehouses.
- ✓Use WHEN SYSTEM$STREAM_HAS_DATA when a task should run only with changes.
- ✓Choose a schedule based on freshness requirements, not excitement.
- ✓Use Query Profile to inspect heavy MERGE statements.
- ✓Avoid one shared warehouse where BI and tasks fight each other.
- ✓Track task runtimes and warehouse credits over time.
SELECT
warehouse_name,
COUNT(*) AS query_count,
SUM(total_elapsed_time) / 1000 AS total_seconds
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
AND query_text ILIKE '%MERGE INTO SILVER.ORDERS%'
GROUP BY warehouse_name
ORDER BY total_seconds DESC;Streams and Tasks Anti-Patterns
- ✓Creating streams and tasks for tiny tables where full refresh would be clearer.
- ✓Forgetting to resume tasks and wondering why data is stale.
- ✓Leaving failed tasks unnoticed until a business user complains.
- ✓Testing DML against a production stream and accidentally consuming it.
- ✓Ignoring stream staleness risk.
- ✓Running tasks every minute with no data-change check.
- ✓Using one huge task for unrelated transformations.
- ✓Building incremental logic with no full-refresh recovery path.
Hands-On Lab: Stream Raw Orders Into Silver
This lab gives you the core pattern: create a raw table, create a stream, insert changes, create a merge task, run it, and inspect the target.
CREATE OR REPLACE DATABASE STREAM_TASK_LAB;
CREATE OR REPLACE SCHEMA STREAM_TASK_LAB.RAW;
CREATE OR REPLACE SCHEMA STREAM_TASK_LAB.SILVER;
CREATE OR REPLACE TABLE STREAM_TASK_LAB.RAW.ORDERS (
order_id STRING,
customer_id STRING,
status STRING,
total_usd NUMBER(12,2),
updated_at TIMESTAMP_NTZ
);
CREATE OR REPLACE STREAM STREAM_TASK_LAB.RAW.ORDERS_STREAM
ON TABLE STREAM_TASK_LAB.RAW.ORDERS;
CREATE OR REPLACE TABLE STREAM_TASK_LAB.SILVER.ORDERS (
order_id STRING,
customer_id STRING,
status STRING,
total_usd NUMBER(12,2),
updated_at TIMESTAMP_NTZ
);
INSERT INTO STREAM_TASK_LAB.RAW.ORDERS VALUES
('O-1', 'C-1', 'completed', 100.00, CURRENT_TIMESTAMP()),
('O-2', 'C-2', 'pending', 50.00, CURRENT_TIMESTAMP());MERGE INTO STREAM_TASK_LAB.SILVER.ORDERS tgt
USING STREAM_TASK_LAB.RAW.ORDERS_STREAM src
ON tgt.order_id = src.order_id
WHEN MATCHED AND src.updated_at > tgt.updated_at THEN UPDATE SET
customer_id = src.customer_id,
status = src.status,
total_usd = src.total_usd,
updated_at = src.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, status, total_usd, updated_at)
VALUES (src.order_id, src.customer_id, src.status, src.total_usd, src.updated_at);
SELECT * FROM STREAM_TASK_LAB.SILVER.ORDERS ORDER BY order_id;
-- In production, wrap the MERGE in a task and monitor TASK_HISTORY.Lab questions
- ✓What rows appear in the stream before the MERGE?
- ✓What rows appear in the stream after the MERGE consumes it?
- ✓How would you handle an update to O-2?
- ✓How would you detect the task stopped running?
- ✓What full-refresh query would rebuild SILVER.ORDERS if stream state broke?
How to Explain Streams and Tasks in an Interview
A strong answer sounds like this: Snowflake streams track changes on a table, including metadata about inserts, deletes, and updates. Tasks run SQL on a schedule or as part of a dependency graph. Together they are commonly used for incremental ELT, such as merging new raw records into Silver and then building Gold marts. I would design the MERGE to be idempotent, dedupe stream input, handle deletes, monitor task history and table freshness, use stream data checks to avoid unnecessary runs, and keep a full-refresh recovery path in case a stream becomes stale or incremental state breaks.
Questions you should answer out loud
- ✓What does a Snowflake stream track?
- ✓When does a stream advance?
- ✓What is a Snowflake task?
- ✓How do task graphs work?
- ✓What does stream staleness mean?
- ✓How would you monitor a stream/task pipeline?
- ✓How would you recover if stream state became unusable?
🎯 Key Takeaways
- ✓Streams track table changes; tasks run SQL automatically.
- ✓Streams are commonly consumed by MERGE statements for incremental ELT.
- ✓DML consumption advances a stream, so production streams must be handled carefully.
- ✓Task graphs model dependencies between transformation steps.
- ✓Stream staleness and task failures are real production risks.
- ✓Monitoring must include task history, freshness, row counts, and business-quality checks.
- ✓Every incremental stream/task design needs a full-refresh or backfill recovery path.
Quick check
What advances a stream's offset?
HELP US IMPROVE
How was this lesson?
Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.
Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.
Discussion
0Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.
