TL;DR
Snowflake streams are objects that tell you which rows in a table changed since you last looked. A stream stores no copy of your data, only a bookmark called an offset. When you query it, Snowflake rebuilds the inserts, updates and deletes from the table’s own version history. Reading a stream does not use up the changes, but loading them into another table in a committed transaction does. Standard streams see every change, append-only streams see new rows, and insert-only streams cover external and open-table sources. If nobody consumes a stream before the table’s retention window runs out, it goes stale and the pending changes are gone.
Key Takeaways A Snowflake stream holds an offset, not data, so creating one costs almost nothing. Querying a stream never moves the offset; only a committed DML statement that reads it does. Updates arrive as a DELETE and INSERT pair flagged with METADATA$ISUPDATE = TRUE. Choose standard, append-only or insert-only based on which changes you need and where the data lives. Give every downstream consumer its own stream, because one consumer’s commit empties the delta for everyone. Watch STALE_AFTER, since a stale stream loses its unconsumed changes for good. Watch on YouTube
Snowflake Cortex for Data Quality: What ETL Tools Can’t Do (Demo)
Kanerika demos data quality checks that run inside Snowflake, the kind of validation that keeps incremental loads from stream deltas trustworthy.
The Stream That Holds No Data Create a stream on a table with a billion rows and Snowflake copies exactly zero of them. The Snowflake documentation on streams says it plainly. A stream stores only an offset and builds its change records from the table’s version history when you ask.
So the object that drives change data capture in Snowflake is closer to a bookmark than a table. That one fact explains almost everything about how Snowflake streams behave. It is why you can create dozens of them cheaply, and why reading one leaves it untouched.
It also explains the failure that catches teams off guard. When the history behind the bookmark expires, the changes the stream pointed at disappear with it. No setting brings them back, so the rest of this guide follows the bookmark from creation to the day it expires.
What Are Snowflake Streams? A Snowflake stream is a schema object that records a position in a table’s change history. It returns every row change made after that position. Data teams use streams for incremental processing, so a pipeline touches only the rows that changed instead of rescanning a whole table. That is the core of change data capture (CDC) inside the Snowflake platform .
The practical payoff is speed and cost. A nightly job that reprocesses 200 million rows to catch 40,000 edits wastes most of its warehouse time. With a stream, the same job reads only the 40,000 changed rows and then moves on. Smaller runs also mean you can refresh more often. Reports that once updated every hour can update every few minutes instead, because each run only carries the changes.
What a Stream Stores and What It Returns Snowflake keeps a new table version every time a transaction with DML commits. A stream remembers one point between two of those versions, and that point is the offset. When you query the stream, Snowflake compares the table at the offset with the current table. Then it hands back the difference as rows.
The rows come back in the same shape as the source table, plus three metadata columns that describe each change. Because the stream rebuilds them on demand, the stream itself stays tiny. The heavy lifting happens in table storage, which the Snowflake architecture already versions for features like Time Travel.
Case Study
28% Cost Savings with Snowflake Migration for Analytics
A soft drink manufacturer moved from SSAS to Snowflake and swapped hourly refreshes for table-level refreshes, with 45% faster refresh cycles and 50% fewer outages.
Read the Case Study → Change Tracking: The Hidden Columns Behind Every Stream The first stream you create on a table switches on change tracking. Snowflake then adds a few hidden columns to the source table and starts writing change metadata into them. Those columns use a small amount of storage, and they feed the metadata columns you see in the stream.
You can also enable change tracking yourself with ALTER TABLE … SET CHANGE_TRACKING = TRUE. Only the role that owns the table can do it, so the first stream often needs the owner’s help. Snowflake also locks the table while it switches tracking on, so busy production tables deserve a quiet window for this step.
How Streams Differ From Log-Based CDC Tools Tools that read a database’s transaction log, the classic form of change data capture , pick up changes before data ever reaches Snowflake. Streams work later in the chain. They track changes to tables that already live in Snowflake, such as a raw landing table, a staging table or a cleaned layer.
So the two approaches complement each other rather than compete. An external CDC feed or a loader like Snowflake Snowpipe lands raw changes in a table. A stream on that table then tells every downstream step what is new, and the broader Snowflake data engineering picture shows where each piece sits.
The name causes one more mix-up. A Snowflake stream has nothing to do with the event platforms described in our data streaming glossary entry , which move messages between systems. A stream answers a narrower question, namely what changed in this table since you last processed it.
How Snowflake Streams Work, Step by Step Every stream follows the same four-step cycle, and the offset only moves in the last step. Snowflake’s own streams and tasks quickstart follows the same order. The example below uses an orders table that later sections reuse, so you can follow one set of rows from start to finish.
Step 1: Create the Stream Start with a source table, which can be any standard Snowflake table. Then create the stream on it. The new stream’s offset sits at the current table version, so it starts empty.
CREATE OR REPLACE TABLE orders (
order_id NUMBER,
status VARCHAR,
amount NUMBER(10,2)
);
INSERT INTO orders VALUES
(1, 'placed', 120.00),
(2, 'placed', 75.50),
(3, 'placed', 42.00);
-- The offset starts at the current version, so this stream is empty
CREATE OR REPLACE STREAM orders_stream ON TABLE orders;
-- Optional: return the rows that already exist on first consumption
CREATE OR REPLACE STREAM orders_seed ON TABLE orders
SHOW_INITIAL_ROWS = TRUE;The SHOW_INITIAL_ROWS option helps when a new downstream table needs a full first load. On its first consumption the stream returns the rows that existed at creation, and after that it behaves like any other stream.
Step 2: Commit Changes to the Source Meanwhile, applications and pipelines keep writing to the orders table as usual. Each committed transaction creates a new table version, and change tracking records what moved. The stream sees nothing until a transaction commits, so half-finished work in another session never leaks into it.
Step 3: Read the Delta Without Moving It A plain SELECT on the stream shows every change since the offset. You can run that query ten times and get the same answer ten times, because reading does not consume anything. This surprises engineers who expect queue behaviour, where a message vanishes once you read it.
SELECT order_id, status, amount,
METADATA$ACTION, METADATA$ISUPDATE
FROM orders_stream; -- read-only: the offset stays where it isStep 4: Consume the Delta and Advance the Offset The offset moves only when a DML statement reads the stream and its transaction commits. That covers INSERT, MERGE, UPDATE and DELETE, and it also covers CREATE TABLE AS SELECT and COPY INTO a location. Once the commit lands, the stream points at the new table version and starts collecting the next round of changes.
The Three Types of Snowflake Streams Snowflake has three stream types, and each one records a different slice of change. Picking the right one is mostly a question of which changes you need and where the source data lives.
Standard Streams A standard stream, also called a delta stream, tracks inserts, updates and deletes, including truncates. It joins inserted and deleted rows to work out the net change for each row between two offsets. As a result, a row that someone inserts and then deletes before you consume the stream never shows up at all.
Standard streams work on standard tables, dynamic tables, Snowflake-managed and externally managed V3 Apache Iceberg tables, directory tables and views. They cannot return change data for geospatial columns, so Snowflake suggests append-only streams for tables that hold geospatial data.
Append-Only Streams An append-only stream tracks new rows and ignores updates, deletes and truncates. If ten rows arrive and someone removes five of them before the offset moves, the stream still returns all ten. That sounds like a flaw, but it is exactly what an event or log table needs.
Skipping the join makes append-only streams noticeably faster than standard streams for insert-heavy ELT. You can even truncate the source right after consuming the stream, because deletes add no overhead to the next read. Raw landing tables that receive continuous file loads are a natural fit.
Insert-Only Streams Insert-only streams cover data that Snowflake does not fully manage. That includes external tables , externally managed Iceberg tables built on the Apache Iceberg format and Delta Direct tables without partition columns. They record rows from newly added files, but when a file disappears from cloud storage, the stream records nothing.
Overwrites behave the same way. If a writer replaces a file, the stream treats the new version as a brand-new file and returns all of its rows as inserts. It does not compute a diff between the old file and the new one.
-- Standard (default)
CREATE STREAM orders_stream ON TABLE orders;
-- Append-only: new rows only
CREATE STREAM orders_new ON TABLE orders APPEND_ONLY = TRUE;
-- Insert-only: rows from new files in an external table
CREATE STREAM ext_orders_new ON EXTERNAL TABLE ext_orders INSERT_ONLY = TRUE;Streams on Views, Directory Tables, and Dynamic Tables Streams also work on objects other than plain tables. A stream on a stage tracks the stage’s directory table, which tells a pipeline which files arrived. A stream on a dynamic table lets you react to each refresh, and a stream on a view tracks changes to the tables underneath it.
Views come with rules, though. The view may only use projections, filters, inner or cross joins and UNION ALL, so GROUP BY, DISTINCT, QUALIFY and LIMIT are out. Change tracking must be on for every underlying table, and streams cannot track a Snowflake materialized view at all.
Which Stream Type Should You Choose? Start from the downstream need. If the target must reflect edits and removals, only a standard stream will do. When the source only ever grows, an append-only stream is cheaper. For files in external storage, insert-only is the one option available.
Our advice when unsure is to start with a standard stream. It returns everything, so nothing slips past you. Moving an insert-heavy source to append-only later is still an easy saving. It does mean creating a new stream, because a stream’s type cannot be changed once it exists.
Table 1: Snowflake stream types compared
Factor Standard Append-only Insert-only Inserts Captured Yes, all of them Only from new files Updates DELETE + INSERT pairs Ignored Ignored Deletes and truncates Captured Ignored Ignored (file removals too) Typical sources Standard, dynamic and Iceberg tables, directory tables, views Standard, dynamic and Snowflake-managed Iceberg tables, views External tables, externally managed Iceberg (V2), Delta Direct Change logic Net change per row Every inserted row Rows in newly added files Best fit Keeping a current-state table in sync Logs, events, raw landing tables Data lakes and open table formats Syntax Default APPEND_ONLY = TRUE INSERT_ONLY = TRUE
How the Stream Offset Moves The offset is the only state a stream owns, so most stream bugs come down to misreading when it moves. Four rules cover nearly every case you will meet in production.
When Does the Offset Advance? The offset advances when a DML statement that reads the stream commits. Queries alone never move it, even inside an explicit transaction. With autocommit on, which is the default, each DML statement runs in its own transaction and commits as soon as it finishes.
Inside an explicit transaction, every query sees the stream as it stood when the transaction began. Snowflake calls this repeatable read isolation. If the transaction commits, the offset jumps to that start time, and if it fails, the offset stays exactly where it was.
What Happens When a Transaction Rolls Back? Nothing goes missing. A failed MERGE or a ROLLBACK leaves the offset in place, so the next run sees the same changes plus anything new. That makes stream-based pipelines safe to retry, as long as the target write and the stream read share one transaction.
The flip side matters just as much. If your pipeline writes the target in one transaction and reads the stream in another, a crash between them can double-apply or skip changes. Keep both inside the same commit.
Why Each Consumer Needs Its Own Stream A consumer is any task, script or procedure that reads a stream in DML. When one consumer commits, the offset moves for everyone, and the next consumer finds the delta already gone. Two teams sharing one stream will quietly starve each other.
The fix is cheap because streams store only an offset. Create one stream per target, such as orders_to_finance and orders_to_ops, and each keeps its own position. Scheduling those consumers belongs to Snowflake tasks , which our sibling guide covers in depth.
Kanerika Service
Snowflake Data Engineering Services
Kanerika designs CDC pipelines on Snowflake with one stream per consumer, transaction-safe MERGE logic and staleness alerts built in.
Explore Data Engineering Repositioning an Offset With AT, BEFORE, and STREAM Sometimes you need to replay changes or recreate a stream without losing its place. CREATE STREAM accepts an AT or BEFORE clause that places the offset at a past timestamp, a time offset or a statement ID. The special STREAM parameter copies the position of an existing stream.
-- Recreate a stream but keep its current offset
CREATE OR REPLACE STREAM orders_stream ON TABLE orders
AT (STREAM => 'orders_stream')
COMMENT = 'orders CDC for finance';
-- Start a second consumer at the same position as the first
CREATE STREAM orders_to_ops ON TABLE orders
AT (STREAM => 'orders_stream');
-- Replay the last hour of changes (needs change tracking and Time Travel history)
CREATE STREAM orders_replay ON TABLE orders
AT (OFFSET => -3600);A replay only works while the history still exists. The point you ask for must fall inside the table’s Snowflake Time Travel retention, and change tracking must have been on at that time. Otherwise CREATE STREAM fails instead of giving you a partial answer.
Reading Change Records: The Three Metadata Columns Every row a stream returns carries three extra columns. Together they tell you what happened to the row and how to apply it downstream.
METADATA$ACTION. The recorded operation, either INSERT or DELETE.METADATA$ISUPDATE. TRUE when the row is one half of an UPDATE.METADATA$ROW_ID. A unique, immutable ID that lets you follow one row across changes.The row ID stays stable unless someone switches change tracking off and on again. Streams on the same table share row IDs, and so do streams on a clone for rows that existed when someone made the clone.
How an UPDATE Shows Up in a Stream Streams have no UPDATE action. Instead, an update appears as two rows, a DELETE carrying the old values and an INSERT carrying the new ones. Both rows have METADATA$ISUPDATE set to TRUE, so you can tell them apart from a genuine delete or insert.
Net Changes: What the Stream Leaves Out A standard stream reports the net effect between two offsets, not every intermediate step. Insert a row and then update it before anyone consumes the stream. You get a single INSERT with the final values and METADATA$ISUPDATE set to FALSE. Insert a row and then delete it, and you get nothing.
This design keeps deltas small, but it also means a stream is not an audit log. When you need every intermediate state, write the changes somewhere permanent on each run, or capture them before they reach Snowflake.
A Worked Example: Five Changes, One Delta Go back to the orders table, which held orders 1, 2 and 3 when the stream began. Now run five changes before anything consumes the stream.
INSERT INTO orders VALUES (4, 'placed', 300.00); -- change 1
UPDATE orders SET status = 'shipped' WHERE order_id = 2; -- change 2
DELETE FROM orders WHERE order_id = 3; -- change 3
INSERT INTO orders VALUES (5, 'placed', 18.00); -- change 4
DELETE FROM orders WHERE order_id = 5; -- change 5Querying orders_stream now returns four rows, not five. Order 5 vanished because it came and went inside the same interval.
Table 2: What orders_stream returns after the five changes
order_id status METADATA$ACTION METADATA$ISUPDATE Meaning 4 placed INSERT FALSE New order 2 placed DELETE TRUE Old version of an update 2 shipped INSERT TRUE New version of an update 3 placed DELETE FALSE Deleted order
Consuming a Snowflake Stream With INSERT and MERGE Consuming a stream means reading it inside a DML statement that writes somewhere else. For append-only streams, an INSERT is usually enough. Standard streams usually need MERGE, because the target must absorb inserts, updates and deletes in one pass.
A MERGE That Applies Inserts, Updates, and Deletes The pattern below keeps a current-state copy of the orders table in sync. It first drops the DELETE half of each update pair, so every order_id appears at most once. That matters because MERGE raises an error by default when one target row matches several source rows, as the MERGE reference explains.
MERGE INTO orders_current t
USING (
SELECT *
FROM orders_stream
WHERE NOT (METADATA$ACTION = 'DELETE' AND METADATA$ISUPDATE)
) s
ON t.order_id = s.order_id
WHEN MATCHED AND s.METADATA$ACTION = 'DELETE' THEN
DELETE
WHEN MATCHED AND s.METADATA$ACTION = 'INSERT' THEN
UPDATE SET t.status = s.status, t.amount = s.amount
WHEN NOT MATCHED AND s.METADATA$ACTION = 'INSERT' THEN
INSERT (order_id, status, amount)
VALUES (s.order_id, s.status, s.amount);Run against the worked example, this MERGE inserts order 4, updates order 2 to shipped and deletes order 3. When it commits, orders_stream empties and its offset moves to the current version.
One edge case needs care. Suppose an application deletes a row and inserts a new one with the same business ID in one interval. Both rows then arrive as a plain DELETE and a plain INSERT, so deduplicate on that ID before the MERGE.
Keeping Several Statements on One Delta Some loads need two or more statements, for example an INSERT into a history table and a MERGE into a current table. With autocommit, the first statement would consume the stream and the second would see nothing.
Wrapping both in BEGIN and COMMIT fixes that. Then every statement inside the transaction sees the same delta, and the stream stays locked until the commit.
Checklist
Data Engineering Checklist
A practical checklist for building reliable pipelines, from source mapping and incremental loads to testing, monitoring and recovery.
Get the Checklist → Building SCD Type 2 History From a Stream A slowly changing dimension of Type 2 keeps every version of a record with valid-from and valid-to dates. Streams make this pattern short. The DELETE rows tell you which versions to close, and the INSERT rows give you the new versions.
BEGIN;
-- Close the current version of every changed or deleted customer
UPDATE dim_customer d
SET valid_to = CURRENT_TIMESTAMP(), is_current = FALSE
FROM customer_stream s
WHERE d.customer_id = s.customer_id
AND d.is_current
AND s.METADATA$ACTION = 'DELETE';
-- Open a new version for every inserted or updated customer
INSERT INTO dim_customer
(customer_id, name, segment, valid_from, valid_to, is_current)
SELECT customer_id, name, segment, CURRENT_TIMESTAMP(), NULL, TRUE
FROM customer_stream
WHERE METADATA$ACTION = 'INSERT';
COMMIT; -- the offset advances only hereBoth statements read the same delta because they share one transaction. If either one fails, issue ROLLBACK, not COMMIT. Snowflake undoes only the failed statement and keeps the transaction open, so a later COMMIT would apply the UPDATE and still advance the offset.
Teams often wrap this block in one of their Snowflake stored procedures . There an unhandled error never reaches COMMIT, so the whole block rolls back and the stream keeps its changes for the next attempt.
Clearing a Stream Without Processing It Now and then you need to skip a backlog, for instance after a full reload of the target. Snowflake documents a clean trick for this. Select from the stream into a temporary table with a filter that matches no rows, and the offset still jumps forward.
CREATE TEMPORARY TABLE _drain_orders AS
SELECT * FROM orders_stream WHERE 1 = 0; -- consumes, processes nothingRecreating the stream with CREATE OR REPLACE STREAM also resets the offset to now. Use either option deliberately, because both discard every pending change in that stream.
Snowflake Stream Staleness: Causes, Checks, and Recovery A stream goes stale when its offset falls outside the data retention period of its source table. At that point the table no longer keeps the history the stream needs, so its unconsumed changes are gone. To track changes again, you have to recreate the stream.
How Retention Decides When a Stream Goes Stale Retention comes from DATA_RETENTION_TIME_IN_DAYS, the same setting behind Time Travel. Standard retention is one day, and Enterprise Edition allows up to 90 days on permanent tables, according to the Time Travel documentation . Transient tables top out at one day, which makes them risky sources for slow consumers.
Snowflake adds a safety net for unconsumed streams. When a table keeps less than 14 days of history, Snowflake temporarily stretches retention back to the stream’s offset. By default that extension stops at 14 days on any edition. The MAX_DATA_EXTENSION_TIME_IN_DAYS parameter sets that ceiling, and retention drops back to normal once a consumer reads the stream.
Table 3: How often to consume a stream, from Snowflake’s own examples
DATA_RETENTION_TIME_IN_DAYS MAX_DATA_EXTENSION_TIME_IN_DAYS Consume within 14 0 14 days 1 14 14 days 0 90 90 days
How to Check Whether a Stream Is Stale SHOW STREAMS and DESCRIBE STREAM both return two columns that matter here. STALE says whether Snowflake expects the stream to be stale. STALE_AFTER gives the time when it may go stale, based on the last consumption plus the larger of the two retention settings.
SHOW STREAMS LIKE 'ORDERS_STREAM' IN SCHEMA sales.raw;
DESCRIBE STREAM sales.raw.orders_stream; -- check STALE and STALE_AFTER
SELECT SYSTEM$STREAM_HAS_DATA('sales.raw.orders_stream');Treat STALE_AFTER as a deadline, not a promise. The SHOW STREAMS reference warns that reads may keep working for a while after it passes. Even so, the stream can go stale at any moment once that time is behind you.
Keeping a Quiet Stream Alive The surest way to prevent staleness is to consume the stream regularly, well inside its retention window. Low-traffic tables need one more trick. Calling SYSTEM$STREAM_HAS_DATA on an empty stream keeps it from going stale, but only when the stream really is empty and the function returns FALSE.
The function can return a false positive, though. If rows came and went again, or a view stream’s base tables changed outside the view’s filter, it may report TRUE with nothing to read. The SYSTEM$STREAM_HAS_DATA reference says to consume the stream anyway with the empty-filter trick shown earlier, so the flag resets.
What Breaks When a Stream Goes Stale A stale stream cannot be read, and its pending changes are gone for good. Recreating the stream starts tracking again from the present, but it does not recover the gap. Any target fed by that stream now drifts from its source by an unknown amount.
Watch on YouTube
Snowflake Migration with FLIP: Automated Discovery, Conversion & Validation
See how Kanerika’s FLIP automates discovery, conversion and source-to-target validation for Snowflake migrations, the kind of comparison that also shows how far a target drifted after a stream failure.
Staleness can also arrive without any delay at all. CREATE OR REPLACE TABLE on the source drops its history and makes every stream on it stale right away. Renaming the source table does not break a stream or make it stale, according to the streams introduction . Views, tasks and scripts that hardcode the old name still need updating, as our Snowflake rename table guide explains.
Dropping a table and creating a new one with the same name is different. Old streams stay linked to the dropped object, not the new one. List every dependent stream before a drop, a check our Snowflake DROP TABLE guide walks through.
Recovering Without Losing Business Data Recovery depends on what failed and whether the table still holds the history you need. The table below sorts the common cases by how much work they take and what you can still save.
Table 4: CDC failure and recovery guide for Snowflake streams
Situation What to do Data at risk Consumer transaction failed or rolled back Rerun it; the offset never moved None Consumer committed with wrong logic Restore the target to its state before the bad run (Time Travel or a clone), recreate the stream with AT (TIMESTAMP => …) at that same point, fix the logic and replay None while both points are inside retention STALE_AFTER passed, stream still readable Consume immediately and widen retention All unconsumed changes, at any moment Stream is stale Recreate the stream, then reconcile or rebuild the target from the source All unconsumed changes Source replaced with CREATE OR REPLACE TABLE Recreate the stream on the new table and fully reload the target All prior change history
Reconciliation is the step teams skip, and it is the one that protects the business. Compare row counts and checksums between source and target before you restart incremental loads. Otherwise the missing changes stay hidden until a finance report disagrees with the source system.
Common Snowflake Streams Mistakes Each of these mistakes looks harmless in a test environment and costs real data in production. Four more get a fix in the next section: sharing a stream, splitting the read from the write, ignoring STALE_AFTER and replacing the source table.
Assuming SELECT consumes the stream. Reading changes nothing, so a job that only selects will reprocess the same rows forever.Treating updates as plain inserts. Ignoring METADATA$ISUPDATE creates duplicate rows in the target.Cloning and expecting the backlog. Clone a schema or database and the cloned stream cannot reach its unconsumed records, which surprises teams that use zero-copy cloning for test environments.Snowflake Streams Best Practices for Production CDC Good stream design is mostly about protecting the offset and the history behind it. These five habits keep CDC pipelines honest once finance reports and customer apps depend on them.
One Stream per Consumer Name each stream after the target it feeds, so ownership is obvious in SHOW STREAMS output. Separate offsets let one consumer pause or fail without hurting the others.
Commit the Target and the Offset Together Put the stream read and the target write in the same transaction, whether that is one MERGE or a BEGIN and COMMIT block. Then a retry always sees the same changes, and nothing gets applied twice.
Watch STALE_AFTER Like a Deadline Alert when STALE_AFTER gets within a day or two, not when STALE turns TRUE. A scheduled query against SHOW STREAMS feeds most data observability setups. Checking Snowflake query history also confirms the consumer actually ran.
Protect the Source Table From Replacement Ban CREATE OR REPLACE TABLE on any table that has streams, and use TRUNCATE plus INSERT or ALTER statements instead. Code review and role grants work better here than good intentions.
Test Rollback and Recovery Before Go-Live Run the five-change example from this guide against your own MERGE. Then force a failure mid-transaction and confirm the stream still holds its rows. After that, compare source and target on a sample, the same way a Snowflake data quality check would.
Costs and Limits to Check Before You Scale Streams are cheap to create but not free to run. Most surprises come from compute and retention rather than from the streams themselves.
What Streams Cost The main cost is the warehouse time spent querying the stream, which shows up as ordinary credits. The second cost is storage. When Snowflake extends retention for an unconsumed stream, it keeps more table history, and the monthly storage bill grows with it.
Consuming on a steady rhythm keeps both in check, as our Snowflake cost optimization guide explains. Standard streams also cost more to read than append-only streams, because they join inserted and deleted rows. On insert-only workloads, switching to append-only is one of the easiest savings available.
Where Streams Do Not Work The streams introduction lists several hard limits worth checking during design.
No streams on materialized views, on views with GROUP BY, or on partitioned external tables. Standard streams cannot return geospatial change data. Adding a NOT NULL constraint can make stream queries fail if older rows hold NULLs. Iceberg V2 tables with an external catalog accept only insert-only streams, so plan those sources first. Streams on shared tables do not extend the provider’s retention, which matters for anyone building on Snowflake data sharing . Datasheet
Accelerate Data Modernization with Snowflake
How Kanerika plans, builds and tunes Snowflake data platforms, including pipeline design and cost control.
View the Datasheet → When Should You Use Snowflake Streams? Use a stream when a downstream table must pick up row-level changes and you want to control when they land. That covers most incremental loads, current-state copies and history tables. It also covers hand-offs between layers of a medallion architecture .
Streams are the wrong tool when you need a permanent, ordered record of every event, since they report net change. They also cannot see changes in a source database before those rows reach Snowflake. Two neighbouring features fill narrow gaps, and each appears as a single row in the table below.
Table 5: Matching the need to the right change-capture option
Your need Best fit Why Apply inserts, updates and deletes to a target on your schedule Standard stream Net change per row, transactional offset Process only new rows from logs or events Append-only stream No join, cheaper reads Pick up new files in external or open-table data Insert-only stream Built for files Snowflake does not manage Ask once what changed between two points in time CHANGES clause Read-only query, no offset to manage Declare the result and let Snowflake refresh it Dynamic table Declarative refresh instead of hand-written MERGE
The CHANGES clause reads the same change-tracking metadata without moving any offset, which suits one-off audits. If a declared refresh fits better than custom CDC logic, our guide to Snowflake dynamic tables covers that route. For precise, retry-safe control over applied changes, Snowflake streams remain the answer.
Talk to Kanerika
Review Your Snowflake CDC Design With Our Team
Walk through your streams, retention settings and MERGE logic with Kanerika’s Snowflake engineers and leave with a list of fixes.
Book a Session → How Kanerika Builds CDC Pipelines on Snowflake Kanerika is a Snowflake Select Tier Partner, and stream design is a regular part of the Snowflake pipeline work we deliver. The work follows five stages, each with a concrete output.
Assess. Map every table that feeds a downstream consumer, its retention, its write pattern and its owner.Design. Pick a stream type per source, one stream per consumer, and a retention setting that outlasts the slowest consumer.Build. Write MERGE and SCD logic that commits the target and the offset together, with tests for updates, deletes and rollbacks.Govern. Alert on STALE_AFTER, block CREATE OR REPLACE TABLE on tracked sources, and reconcile source with target on a schedule.Enable. Hand over runbooks that explain what to do for each row of the recovery table above.From Hourly Refreshes to Near Real Time One example comes from a soft drink manufacturer with eight filling plants that moved its reporting from SSAS to Snowflake. Kanerika replaced hourly refreshes with table-level refreshes, so reports such as work order tracking now run close to real time. The client recorded 28% annual cost savings, 45% faster refresh cycles and 50% fewer outages, as the Snowflake migration case study describes.
That project was a reporting migration rather than a streams build. It still shows the payoff this guide is about: fresher data for the people who act on it, without rebuilding everything on every run.
The pitfalls our teams watch for are the quiet ones. A classic is a transient staging table with one-day retention feeding a weekly consumer. Another is a deploy script that recreates a source table with CREATE OR REPLACE.
During migrations such as an Oracle to Snowflake migration , we also check that every legacy CDC job has its own stream before cutover. Our data engineering services and Snowflake consulting teams run these checks as standard practice.
Kanerika Service
Snowflake Consulting and Implementation
As a Snowflake Select Tier Partner, Kanerika builds, migrates and governs Snowflake platforms with CDC pipelines that hold up in production.
Explore Snowflake Services Wrapping Up A Snowflake stream is a bookmark on a table’s history, not a copy of its data. It returns net row changes with three metadata columns, and it moves forward only when a committed DML statement consumes it. Choose the stream type from the changes you need and the place your data lives.
Then give each consumer its own stream, keep reads and writes in one transaction, and treat STALE_AFTER as a hard deadline. Get those habits right, and Snowflake streams give you change data capture that costs little and survives retries.
Frequently Asked Questions
What are Snowflake streams? Snowflake streams are schema objects that track row-level changes to a table, view or other source. A stream stores only an offset, a position in the table’s version history, and returns the inserts, updates and deletes made after it. Data teams use streams for change data capture, so pipelines process only the rows that changed.
Does a Snowflake stream store data? No. A stream holds no copy of the table’s rows. It stores an offset and rebuilds change records from the source table’s version history and hidden change-tracking columns whenever you query it. That is why streams cost almost nothing to create, and why their changes vanish if the table’s history expires before anyone consumes them.
Does selecting from a Snowflake stream consume its data? No. A plain SELECT returns the pending changes but leaves the offset where it is, even inside an explicit transaction. The offset advances only when a DML statement such as INSERT, MERGE or CREATE TABLE AS SELECT reads the stream and its transaction commits. Until then, every query sees the same changes.
What is the difference between standard, append-only and insert-only streams? Standard streams track inserts, updates and deletes and return the net change per row. Append-only streams track only new rows, which makes them faster for logs and event tables. Insert-only streams cover external tables, externally managed Iceberg tables and Delta Direct tables, recording rows from newly added files while ignoring file removals.
How do Snowflake streams show UPDATE operations? A stream has no UPDATE action. Each update appears as two rows, a DELETE carrying the old values and an INSERT carrying the new values, and both have METADATA$ISUPDATE set to TRUE. When you apply changes with MERGE, filter out the DELETE half of each pair so each business key appears only once.
When does a Snowflake stream advance its offset? The offset advances when a DML statement that reads the stream commits successfully. With autocommit, that happens at the end of each statement. Inside BEGIN and COMMIT, every statement sees the same changes, and the offset moves to the transaction start time only when the commit succeeds. A rollback leaves the offset unchanged.
Why does a Snowflake stream become stale? A stream becomes stale when its offset falls outside the source table’s data retention period, so the history it needs is gone. Long gaps between consumption cause it, and so does CREATE OR REPLACE TABLE on the source, which drops the table’s history immediately. A stale stream cannot be read and must be recreated.
How can I check whether a Snowflake stream is stale? Run SHOW STREAMS or DESCRIBE STREAM and read two columns. STALE shows whether Snowflake expects the stream to be stale. STALE_AFTER shows when it may go stale, based on the last consumption plus the larger retention setting. Treat that timestamp as a deadline, because a stream can go stale any time after it.
Can I recover changes from a stale Snowflake stream? No. Once a stream is stale, its unconsumed change records are no longer accessible. Recreating the stream only tracks new changes from that point forward. To repair the target, compare it with the source using row counts and checksums, then rebuild or reconcile the affected tables before you restart incremental processing.
Can multiple consumers read the same Snowflake stream? They can read it, but they should not consume it together. When one consumer commits a DML statement that reads the stream, the offset moves for everyone, so the next consumer finds the changes gone. Snowflake recommends one stream per consumer, which costs little because each stream stores only an offset.
How much do Snowflake streams cost? Creating a stream costs almost nothing because it stores only an offset. The real costs are warehouse credits spent querying the stream and extra storage when Snowflake extends table retention for an unconsumed stream. Consuming streams on a steady schedule and using append-only streams for insert-only tables keeps both costs low.
What is the difference between a Snowflake stream and the CHANGES clause? A stream keeps a persistent offset that advances each time a DML statement consumes it, which suits repeated incremental loads. The CHANGES clause is a read-only query over the same change-tracking metadata between two points in time. It never moves an offset, so it fits one-off audits and investigations rather than pipelines.
