Streams: Change Data Capture on a Table
A Snowflake Stream is an object that records the change data capture (CDC) delta on a source table or view: every insert, update, and delete since the stream was last consumed. Internally a stream doesn't copy data; it tracks an offset against the table's versioned metadata (its Time Travel history) and exposes pseudo-columns like METADATA$ACTION, METADATA$ISUPDATE, and METADATA$ROW_ID that describe what happened to each row. When a stream is queried inside a DML transaction, its offset only advances if that transaction commits, which makes streams safe to consume repeatedly without losing changes if a downstream job fails partway through.
Cricket analogy: Like a scorer's ball-by-ball log that records only what changed each over — a wicket, a boundary, a wide — rather than re-writing the entire scorecard from scratch after every delivery.
-- Create a stream on a source table to capture changes
CREATE OR REPLACE STREAM orders_stream ON TABLE raw_orders;
-- The stream exposes standard columns plus CDC metadata
SELECT order_id, status, amount,
METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID
FROM orders_stream;
-- Consuming the stream inside a DML statement advances its offset
INSERT INTO orders_curated (order_id, status, amount, change_type)
SELECT order_id, status, amount, METADATA$ACTION
FROM orders_stream;
-- Check whether a stream still has unconsumed data
SELECT SYSTEM$STREAM_HAS_DATA('orders_stream');Tasks: Scheduling and Chaining SQL
A Task is Snowflake's native scheduler for running a single SQL statement (often a CALL to a stored procedure) on a cron-like schedule or, more commonly in CDC pipelines, triggered when a stream has new data available via the WHEN SYSTEM$STREAM_HAS_DATA() clause. Tasks can be chained into a DAG using AFTER, where a child task fires once its predecessor completes successfully, enabling multi-step pipelines like raw ingestion, transformation, and aggregation to run in sequence without an external orchestrator. Like Snowpipe, tasks run on either a dedicated virtual warehouse you specify or Snowflake-managed serverless compute, and each root task must be explicitly resumed with ALTER TASK ... RESUME since tasks are created in a suspended state by default.
Cricket analogy: Like a bowling rotation set by the captain before the innings, where the next bowler only comes on once the previous over is complete, mirroring a task DAG's AFTER dependency chain.
The Stream-and-Task CDC Pattern
The most common production pattern pairs a stream and a task: the task is scheduled to run frequently (say every minute) with a WHEN SYSTEM$STREAM_HAS_DATA('orders_stream') condition, so it only actually executes its body — typically a MERGE or INSERT that consumes the stream — when there's genuinely new data, avoiding wasted warehouse spin-ups on empty runs. This gives you an incremental, near-real-time ELT pipeline without external orchestration tools like Airflow for simple cases, though complex multi-system dependencies still often warrant an external orchestrator that calls into Snowflake tasks or procedures.
Cricket analogy: Like a twelfth man who only jogs onto the field when the umpire actually signals an injury substitution, rather than running out onto the pitch every single over regardless of need.
Tasks can use Snowflake-managed serverless compute (SERVERLESS = TRUE or by omitting WAREHOUSE) so you don't need to size a warehouse for lightweight, frequent CDC jobs — Snowflake automatically scales compute to the workload and bills per second.
A stream's offset only advances when it's consumed inside a committed DML transaction. If a downstream MERGE or INSERT statement fails or is rolled back, the stream retains those changes for the next run — but if you query a stream outside of DML (e.g., a plain SELECT) that does not advance the offset, which can lead to double-processing if you're not careful about which statement actually consumes it.
- A Stream captures the CDC delta (inserts, updates, deletes) on a source table since it was last consumed, without copying data.
- Streams expose METADATA$ACTION, METADATA$ISUPDATE, and METADATA$ROW_ID pseudo-columns describing each change.
- A stream's offset only advances when consumed inside a committed DML transaction, making it safe to retry after failures.
- Tasks schedule a single SQL statement (often CALL to a stored procedure) on a cron schedule or an AFTER dependency chain.
- The WHEN SYSTEM$STREAM_HAS_DATA() clause lets a task skip execution entirely when there's no new data, avoiding wasted compute.
- Tasks run on either a customer-specified warehouse or Snowflake-managed serverless compute, billed per second.
- Root tasks are created suspended by default and must be explicitly resumed with ALTER TASK ... RESUME.
Practice what you learned
1. What does a Snowflake Stream actually store?
2. When does a stream's offset advance?
3. What does the WHEN SYSTEM$STREAM_HAS_DATA() clause on a task accomplish?
4. How are child tasks chained into a multi-step pipeline?
5. What is true about a newly created root task?
Was this page helpful?
You May Also Like
Snowpipe and Continuous Loading
Learn how Snowflake's Snowpipe service ingests data continuously and automatically from cloud storage, and how it differs from batch COPY INTO loads.
Snowpark Basics
Introduces Snowpark, Snowflake's developer framework for writing DataFrame-style transformations and stored procedures in Python, Java, or Scala that execute inside Snowflake.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics
ProgrammingPowerShell Study Notes
Programming · 30 topics