Streams
Snowflake · Views, Pipelines & Automation
Theory
A stream records changes (inserts, updates and deletes) made to a table since a point called its offset. It does not store a copy of the data; it uses the table's versioning to work out the net change. Querying a stream returns the changed rows plus metadata columns: METADATA$ACTION (INSERT or DELETE), METADATA$ISUPDATE (TRUE when the row is part of an UPDATE) and METADATA$ROW_ID. An UPDATE appears as a DELETE of the old row and an INSERT of the new one, both with ISUPDATE = TRUE.
A plain SELECT does not move the offset. The offset advances only when the stream is used in a DML statement, such as INSERT ... SELECT FROM stream or MERGE, and that transaction commits.
Why we use it
Incremental pipelines need to know exactly what changed since the last run. Streams provide that change set without timestamps or full table comparisons, so only new and changed rows are processed.
When to use it
Use standard streams for tables with inserts, updates and deletes, and APPEND_ONLY streams for insert-only data like logs, which is cheaper to compute. Consume streams regularly: a stream goes stale if unread beyond the table's retention, which Snowflake extends up to MAX_DATA_EXTENSION_TIME_IN_DAYS.
What a Stream Tracks
A stream doesn't store data itself — it tracks exactly which rows in a table have been inserted, updated, or deleted since it was last read.
Reading and Resetting
That change set is exposed as metadata columns you can query directly; once a stream's changes are consumed inside a task or transaction, it resets automatically.
Why This Enables Incremental Processing
Because a stream only ever shows what's new, downstream pipelines can process just the changes instead of rescanning an entire table every run.
How it works
- CREATE STREAM orders_stream ON TABLE orders
- DML changes rows in orders
- SELECT FROM the stream shows net changes since the offset
- Is the stream used in DML that commits? Yes: Offset moves forward; stream is empty again. No: Changes stay in the stream.
- Next run sees only newer changes
Example
CREATE OR REPLACE STREAM orders_stream ON TABLE orders;
INSERT INTO orders (customer_id, product, amount) VALUES (6, 'SSD', 129.99);
UPDATE orders SET amount = 1099.99 WHERE product = 'Laptop';
DELETE FROM orders WHERE product = 'Mouse';
SELECT * FROM orders_stream;
-- METADATA$ACTION = 'INSERT' or 'DELETE'
-- METADATA$ISUPDATE = TRUE if part of an UPDATE
CREATE OR REPLACE STREAM events_append_stream ON TABLE raw_events APPEND_ONLY = TRUE;Key points
- Powers incremental ETL without full table rescans
- METADATA$ACTION and METADATA$ISUPDATE describe each change
- An UPDATE shows as a DELETE plus an INSERT, both ISUPDATE = TRUE
- Only DML that commits advances the offset; SELECT alone does not
- Unread past retention (extended up to MAX_DATA_EXTENSION_TIME_IN_DAYS) it goes stale