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

  1. CREATE STREAM orders_stream ON TABLE orders
  2. DML changes rows in orders
  3. SELECT FROM the stream shows net changes since the offset
  4. Is the stream used in DML that commits? Yes: Offset moves forward; stream is empty again. No: Changes stay in the stream.
  5. 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

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic