Why would you want a stream that ignores updates and deletes? It sounds like missing half the story of your data changes. Yet in Snowflake, the choice between append-only and standard streams is a subtle but critical one, shaping not just what you capture but how you build incremental data pipelines. And then there’s the curious case of the “insert-only” stream—which Snowflake doesn’t really have.
I remember early on, working with a team building a sales analytics platform. We kept trying to use a standard stream to track everything—insert, update, delete—but the pipeline was getting bogged down by complexity we didn’t need. It was like trying to track every move in a chess game when you only wanted to know when a piece was added. What we really needed was to capture new sales as they came in, untouched by subsequent corrections or cancellations. That’s when the difference between stream types started to matter.
If you’ve used Snowflake Streams but never fully questioned what stream type to use, you’re not alone. The decision shapes your data freshness, your pipeline complexity, and sometimes your sanity. Let’s unpack the difference between append-only and standard streams in Snowflake, clarify why “insert-only” is a bit of a misnomer, and talk about how you can choose the right approach for your use case.
How do append-only and standard streams differ in what they track?
Snowflake Streams track data changes on a table to enable incremental processing—without scanning the entire table every time. The default stream type is standard, but you can explicitly create an append-only stream. Here is the key difference:
- A standard stream captures all table changes: inserts, updates, and deletes. It provides a full change data capture (CDC) log, exposing what rows were newly inserted, which ones were updated, and which were removed.
- An append-only stream only tracks new rows that were inserted. Updates and deletes are ignored entirely.
The implication is huge: append-only streams tell you only what was newly added to the table, like a feed of fresh data. Standard streams give you the full set of changes so you can replay or apply those to downstream systems in their entirety.
An example to create an append-only stream to track just inserts looks like this:
-- Create an append-only stream to track only new inserts on the source table
CREATE OR REPLACE TABLE sales (
id INT,
amount NUMBER(10,2),
sale_date DATE
);
CREATE OR REPLACE STREAM sales_append_only_stream ON TABLE sales
APPEND_ONLY = TRUE;
-- Query the stream to see inserted rows (no UPDATE or DELETE changes tracked)
SELECT * FROM sales_append_only_stream;If you query this stream, you get the new rows added since the last time you read the stream—no noise from updates or deletes. The stream won’t have a METADATA$ACTION column because every row is implicitly an insert.
In contrast, creating a standard stream looks like this:
-- Create a standard stream to capture INSERT, UPDATE, and DELETE changes on the source table
CREATE OR REPLACE TABLE customers (
customer_id INT,
name STRING,
status STRING
);
CREATE OR REPLACE STREAM customers_standard_stream ON TABLE customers;
-- Query the stream to see all changes with METADATA$ACTION indicating the DML type
SELECT *, METADATA$ACTION FROM customers_standard_stream;Here, when you query the stream, you get every inserted, updated, or deleted row, with the METADATA$ACTION column indicating which operation took place. This makes standard streams a true CDC source.
So where does “insert-only” fit in?
If you’ve heard the term “insert-only” stream thrown around in Snowflake discussions, it’s usually a shorthand for append-only streams. Snowflake does not natively support a separate “insert-only” stream type. The append-only stream is the closest match: it tracks only inserts and ignores updates or deletes.
This is a crucial point. If your use case demands simply capturing rows added to a table (like transactional sales logs or event tables with append semantics), an append-only stream fits perfectly.
For anything that needs to track row corrections or deletions—like customer status updates or soft deletes—a standard stream is necessary.
How to choose between append-only and standard streams for your data pipeline?
The decision boils down to what changes your downstream processes care about.
- Use an append-only stream when your source data is truly append-only, or when you only want to capture new inserts. Examples include sensor data, sales transactions, log events, or snapshots where updates are rare or irrelevant.
- Choose a standard stream when you need full CDC, including updates and deletes. This is common in customer master data, inventory systems, or any scenario where rows are frequently updated or removed and you want downstream tables to reflect those changes.
- Consider the complexity of downstream logic. Append-only streams simplify pipeline logic because you only apply new rows, no reconciliation needed. Standard streams require logic to handle deletes and updates, possibly upserts or merges.
- Remember that querying a standard stream includes the METADATA$ACTION column, so your code can conditionally handle each DML type. Append-only streams do not have this column, reflecting their simpler semantics.
Here’s a quick refresher on how to reset a stream after processing changes, so you avoid reprocessing the same data repeatedly:
-- Refresh a stream to reset its state and clear tracked changes
ALTER STREAM customers_standard_stream REFRESH;
-- After refresh, the stream will only track new changes made after this commandStreams have a default retention window of 24 hours. If you don’t consume changes within that window, the stream becomes invalid. This means your incremental pipeline must process changes regularly and refresh or consume the stream to maintain data freshness.
What to watch out for when using Snowflake streams for incremental pipelines
- Assuming append-only streams track updates or deletes. They don’t. It’s easy to overlook that append-only streams only track inserts, so data corrections won’t flow downstream if you rely on them.
- Forgetting the 24-hour retention window. Streams cannot track changes indefinitely. Missing the window means data loss in your incremental logic.
- Not refreshing streams after processing. If you don’t reset or consume the stream, you will reprocess the same changes repeatedly, causing duplicates or data inconsistencies.
- Confusing “insert-only” with being a separate Snowflake stream type. Only append-only streams provide insert-only semantics, but under that name.
I find the best metaphor here is a mail sorter. An append-only stream is like a box where you drop every new letter you receive, without worrying about any updates or withdrawals later. A standard stream is like a mailbox that tracks every letter arriving, every returned letter, and even every letter you open and modify the content of.
That difference determines how you organize your mailroom downstream.
*”We are what we repeatedly do. Excellence, then, is not an act, but a habit.”* — Will Durant, summarizing Aristotle
Choosing the right stream type is one such habit. It influences how your team designs pipelines, thinks about data quality, and takes control over data freshness. I’ve seen teams stumble by assuming more than the stream offered, and others thrive by matching the stream type to their real-world data behavior.
The next time you build a Snowflake stream, take a moment to ask yourself: am I tracking every change, or just new additions? That question alone can save days of debugging and refactoring.
And remember, while technology gives us the tools, it’s the choices we make every day that shape the success of our systems and teams. 🌱💡🛠
Leave a Reply