Skip to main content

πŸ”„ Snowflake Streams

πŸ“˜ What Are Streams in Snowflake?​

A Stream in Snowflake is a change tracking object that records row-level changes β€” inserts, updates, and deletes β€” on a table.

It enables:

  • Efficient incremental data processing
  • Lightweight Change Data Capture (CDC)
  • Seamless replication workflows

πŸ‘‰ Changes are tracked without modifying the source table.


πŸ” Why Use Streams?​

  • βœ… Reduce need to scan entire tables
  • βœ… Enable event-driven ingestion
  • βœ… Automate delta processing with tasks
  • βœ… Efficient for large datasets
  • βœ… Read-only and safe for parallel usage

🧠 Key Concepts​

ConceptDescription
StreamTracks changes (INSERT, UPDATE, DELETE) in a source table
OffsetInternal pointer to track what has already been read
ConsumedOnce read, changes are no longer visible to the stream
Append-OnlyTracks only INSERT operations (faster, no metadata columns)

πŸ“₯ Stream Types in Snowflake​

Stream TypeINSERTUPDATEDELETENotes
Standardβœ…βœ…βœ…Full change tracking with metadata
Append-Onlyβœ…βŒβŒLighter, best for log/event ingestion

πŸ“‹ Stream Metadata Columns​

When querying a standard stream:

ColumnDescription
METADATA$ACTIONType of change (INSERT, UPDATE, DELETE)
METADATA$ISUPDATETRUE if the row is part of an UPDATE
METADATA$ROW_IDUnique identifier for the change event

πŸ” Stream Lifecycle​

  • Streams are consumption-based.
  • After querying, changes are marked as consumed.
  • New changes appear in future queries; old changes don’t.

⚠️ Limitations​

  • Ephemeral data: changes expire after 14 days
  • Not compatible with external tables
  • Requires compute when paired with tasks
  • Multiple consumers can cause conflicts

🧠 Key Takeaways​

FeatureDescription
Tracks ChangesInsert, update, delete events
Read-OnlyStreams cannot modify source data
Use with TasksAutomate CDC pipelines
Lightweight & ServerlessIdeal for incremental workflows

🎯 Who Should Use Streams?​

  • Data Engineers building incremental pipelines
  • Developers implementing CDC logic
  • Analysts performing real-time sync
  • ETL designers working with S3 batch loads + deltas

πŸ“š Common Stream SQL Commands​

-- View all streams in the current schema
SHOW STREAMS;

-- Drop an existing stream
DROP STREAM employees_stream;

πŸ”„ Change Data Capture (CDC) Using Streams

πŸ“˜ What is CDC?​

Change Data Capture (CDC) is the process of detecting and delivering changes from a source system to a target, such as a data warehouse or reporting layer.

In Snowflake, CDC is enabled using:

  • βœ… Streams – for change tracking
  • βœ… Tasks – for scheduled automation
  • βœ… MERGE statements – for applying changes to target tables

🧱 Snowflake CDC Architecture​

ComponentPurpose
Source TableTable where changes originate
StreamTracks INSERT / UPDATE / DELETE events
Target TableDestination for applying changes
TaskAutomates CDC using MERGE logic
Monitoring ViewsSHOW STREAMS, DESC STREAM, TASK_HISTORY()

βš™οΈ Stream Options​

OptionPurpose
APPEND_ONLY = TRUETrack only inserts
APPEND_ONLY = FALSEDefault β€” track inserts, updates, deletes
SHOW_INITIAL_ROWS = TRUECapture existing rows at creation time
COMMENTAdd description to the stream

πŸš€ CDC in Action β€” Step-by-Step Demo​

πŸ”Ή Step 1: Create Source Table​

CREATE OR REPLACE TABLE emp_source (
emp_id INT,
emp_name STRING,
dept STRING,
updated_at TIMESTAMP
);

INSERT INTO emp_source VALUES
(1, 'Alice', 'HR', CURRENT_TIMESTAMP()),
(2, 'Bob', 'IT', CURRENT_TIMESTAMP());

πŸ”Ή Step 2: Create a CDC Stream​

CREATE OR REPLACE STREAM emp_cdc_stream 
ON TABLE emp_source
APPEND_ONLY = FALSE;
  • βœ… Tracks INSERT, UPDATE (as DELETE + INSERT), DELETE
  • βœ… Includes METADATA$ACTION, METADATA$ROW_ID, METADATA$ISUPDATE

πŸ”Ή Step 3: Modify Source Table​

UPDATE emp_source SET emp_name = 'Alice Smith' WHERE emp_id = 1;
DELETE FROM emp_source WHERE emp_id = 2;
INSERT INTO emp_source VALUES (3, 'Charlie', 'Finance', CURRENT_TIMESTAMP());

πŸ”Ή Step 4: Query the Stream​

SELECT 
METADATA$ACTION,
METADATA$ISUPDATE,
METADATA$ROW_ID,
emp_id,
emp_name,
dept,
updated_at
FROM emp_cdc_stream;

πŸ”Ή Step 5: Create the Target Table​

CREATE OR REPLACE TABLE emp_target (
emp_id INT,
emp_name STRING,
dept STRING,
updated_at TIMESTAMP
);

πŸ”Ή Step 6: Create a Task to Apply Changes​

CREATE OR REPLACE TASK apply_emp_cdc_task
WAREHOUSE = compute_wh
SCHEDULE = '5 MINUTE'
AS
MERGE INTO emp_target tgt
USING (
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY updated_at DESC) AS rn
FROM emp_cdc_stream
)
WHERE rn = 1
) src
ON tgt.emp_id = src.emp_id
WHEN MATCHED AND METADATA$ACTION = 'DELETE' THEN DELETE
WHEN MATCHED THEN UPDATE SET
emp_name = src.emp_name,
dept = src.dept,
updated_at = src.updated_at
WHEN NOT MATCHED THEN INSERT (
emp_id, emp_name, dept, updated_at
) VALUES (
src.emp_id, src.emp_name, src.dept, src.updated_at
);

πŸ”Ή Step 7: Activate the Task​

ALTER TASK apply_emp_cdc_task RESUME;

⚠️ Stream Behavior: Key Warnings​

IssueExplanation
Data lossQuerying stream before task runs can consume data early
No rollbackOnce consumed, changes cannot be recovered
Single consumerMultiple consumers may conflict β€” use tasks only
Debugging cautionAvoid testing with SELECT * FROM stream_name in dev

βœ… Stream Modes Comparison​

ModeTracksMetadataBest For
APPEND_ONLY = TRUEINSERT only❌ Not availableIoT/log/event ingestion
APPEND_ONLY = FALSEINSERT, UPDATE, DELETEβœ… YesFull CDC pipelines
SHOW_INITIAL_ROWS = TRUEBootstrap dataβœ… If not append-onlyInitial sync or migration

πŸ›‘οΈ Best Practices​

PracticeWhy It Helps
βœ… Use MERGE with metadataSimplifies full CDC application
πŸ”„ Track updated_at in sourceHelps with ordering, conflict resolution
πŸ•΅οΈ Monitor SHOW STREAMS, DESC STREAMPrevent stale data or missed deltas
πŸ’ͺ Use task-specific warehousesEnsures performance isolation
πŸ§ͺ Avoid SELECT in devPrevents accidental consumption of change data

πŸ“ˆ Monitoring​

-- Stream metadata
SHOW STREAMS;
DESC STREAM emp_cdc_stream;

-- Task run history
SELECT *
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(TASK_NAME => 'APPLY_EMP_CDC_TASK'));

πŸ” Secure CDC Pipeline Layers​

LayerSecurity SupportRole
Streamsβœ…Change tracking
Tasksβœ…Scheduled execution
Merge logicβœ…Idempotent updates and deletes
IAM/Network Policyβœ…Governance and compliance

πŸ“¦ Summary​

StepExample SQL
SourceCREATE TABLE emp_source ...
StreamCREATE STREAM emp_cdc_stream ...
TargetCREATE TABLE emp_target ...
TaskCREATE TASK apply_emp_cdc_task ...
MonitorSHOW STREAMS, TASK_HISTORY()

πŸ“ Final Notes​

  • πŸ“Œ Use APPEND_ONLY = TRUE for insert-only logs
  • ❗ Avoid SELECT queries on active streams in pipelines
  • πŸ“† Streams have 14-day retention window
  • ❗ Not real-time: for batch CDC, not millisecond latency
  • 🧼 Dropping the stream resets tracking β€” use with caution

βœ… Snowflake Streams + Tasks = Modern, scalable, serverless CDC pipeline!