Skip to main content

Streams – Types (Standard, Append-Only, Insert-Only)

A Stream in Snowflake is a Change Data Capture (CDC) object. It acts as a continuous bookmark, tracking the DML changes (inserts, updates, deletes) made to a source table between two points in time.

To optimize performance and storage overhead, Snowflake offers three distinct types of streams.

Topics Covered​

  • Standard Streams
  • Append-Only Streams
  • Insert-Only Streams
  • Advanced Stream Targets (Views, External Tables, Shares)

The Three Stream Types​

1. Standard Streams​

A Standard Stream tracks all DML changes: INSERT, UPDATE, and DELETE.

  • Use Case: Master data tables, Slowly Changing Dimensions (SCDs), or any table where records are actively modified.
  • Overhead: Moderate. Snowflake must track the "before" and "after" state of updated rows.

2. Append-Only Streams​

An Append-Only stream tracks only INSERT operations. If an update or delete occurs on the source table, the stream completely ignores it.

  • Use Case: Time-series data, log files, or raw staging tables (like APP_LOGS) where data is only ever inserted.
  • Overhead: Very Low. Snowflake does not need to compute delta logic for updates/deletes.

3. Insert-Only Streams​

An Insert-Only stream is specifically designed for External Tables. It only tracks when new files are added to the external stage that backs the external table.

  • Use Case: Triggering an ETL pipeline when a new Parquet file lands in AWS S3 and is registered by the External Table.

Hands-On Demonstration: Choosing the Right Stream​

Let's simulate a logistics company tracking truck deliveries across India. We will create both a Standard Stream (for the driver master table) and an Append-Only Stream (for the GPS log table).

1. Database Setup​

Copy and paste the following code into your Snowflake worksheet.

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_LOGISTICS_DB;
USE SCHEMA BEEMS_LOGISTICS_DB.PUBLIC;

-- Master table (Can be updated)
CREATE OR REPLACE TABLE DRIVER_MASTER (DRIVER_ID INT, NAME STRING, STATUS STRING);

-- Log table (Should only ever be inserted into)
CREATE OR REPLACE TABLE GPS_LOGS (TIMESTAMP TIMESTAMP, DRIVER_ID INT, LAT NUMBER, LON NUMBER);

2. Creating the Streams​

-- 1. Standard Stream (Default)
CREATE OR REPLACE STREAM DRIVER_CHANGE_STREAM ON TABLE DRIVER_MASTER;

-- 2. Append-Only Stream
CREATE OR REPLACE STREAM GPS_APPEND_STREAM ON TABLE GPS_LOGS APPEND_ONLY = TRUE;

(To verify the streams were created and check who owns them, you can run SELECT CURRENT_USER(); and SHOW STREAMS;)

3. Simulating DML and Querying the Streams​

Let's insert data and perform an update on the master table.

-- Insert a driver
INSERT INTO DRIVER_MASTER VALUES (1, 'Arjun Nair', 'ACTIVE');
-- Update the driver
UPDATE DRIVER_MASTER SET STATUS = 'ON_LEAVE' WHERE DRIVER_ID = 1;

-- The Standard Stream captures the final state (an INSERT with the ON_LEAVE status)
SELECT * FROM DRIVER_CHANGE_STREAM;

Now let's insert a log, and accidentally delete it.

-- Insert a GPS log
INSERT INTO GPS_LOGS VALUES (CURRENT_TIMESTAMP(), 1, 19.0760, 72.8777); -- Mumbai coordinates

-- Someone accidentally deletes it
DELETE FROM GPS_LOGS WHERE DRIVER_ID = 1;

-- The Append-Only Stream captures the INSERT, but completely ignores the DELETE!
SELECT * FROM GPS_APPEND_STREAM;

If a downstream task consumes GPS_APPEND_STREAM, it will successfully process the GPS coordinate, even though the row was deleted from the raw table. This guarantees no dropped telemetry data.

Advanced Stream Targets​

While streams are typically placed on standard tables, they can also track changes on:

  1. Views: You can place a stream on a standard view. The stream tracks changes to the underlying tables and applies the view's logic to the stream output. (Requires that the underlying tables have change tracking enabled).
  2. Directory Tables: You can place a stream on a Stage's Directory Table to track exactly when new files are uploaded to an S3/Azure/GCS bucket.
  3. Shared Tables: If another Snowflake account shares a table with you (e.g., via Snowflake Data Marketplace), you can place a stream on that shared table to process changes as the provider updates the data.

Summary​

  • Standard Streams track INSERT, UPDATE, and DELETE.
  • Append-Only Streams (APPEND_ONLY = TRUE) track only INSERTs, reducing overhead for log-style tables.
  • Insert-Only Streams (INSERT_ONLY = TRUE) are exclusively used to track new files arriving in External Tables.
  • Streams can be placed on Tables, Views, Directory Tables, and Data Shares.