π 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β
| Concept | Description |
|---|
| Stream | Tracks changes (INSERT, UPDATE, DELETE) in a source table |
| Offset | Internal pointer to track what has already been read |
| Consumed | Once read, changes are no longer visible to the stream |
| Append-Only | Tracks only INSERT operations (faster, no metadata columns) |
π₯ Stream Types in Snowflakeβ
| Stream Type | INSERT | UPDATE | DELETE | Notes |
|---|
| Standard | β
| β
| β
| Full change tracking with metadata |
| Append-Only | β
| β | β | Lighter, best for log/event ingestion |
When querying a standard stream:
| Column | Description |
|---|
METADATA$ACTION | Type of change (INSERT, UPDATE, DELETE) |
METADATA$ISUPDATE | TRUE if the row is part of an UPDATE |
METADATA$ROW_ID | Unique 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β
| Feature | Description |
|---|
| Tracks Changes | Insert, update, delete events |
| Read-Only | Streams cannot modify source data |
| Use with Tasks | Automate CDC pipelines |
| Lightweight & Serverless | Ideal 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β
SHOW STREAMS;
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β
| Component | Purpose |
|---|
| Source Table | Table where changes originate |
| Stream | Tracks INSERT / UPDATE / DELETE events |
| Target Table | Destination for applying changes |
| Task | Automates CDC using MERGE logic |
| Monitoring Views | SHOW STREAMS, DESC STREAM, TASK_HISTORY() |
βοΈ Stream Optionsβ
| Option | Purpose |
|---|
APPEND_ONLY = TRUE | Track only inserts |
APPEND_ONLY = FALSE | Default β track inserts, updates, deletes |
SHOW_INITIAL_ROWS = TRUE | Capture existing rows at creation time |
COMMENT | Add 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β
| Issue | Explanation |
|---|
| Data loss | Querying stream before task runs can consume data early |
| No rollback | Once consumed, changes cannot be recovered |
| Single consumer | Multiple consumers may conflict β use tasks only |
| Debugging caution | Avoid testing with SELECT * FROM stream_name in dev |
β
Stream Modes Comparisonβ
| Mode | Tracks | Metadata | Best For |
|---|
APPEND_ONLY = TRUE | INSERT only | β Not available | IoT/log/event ingestion |
APPEND_ONLY = FALSE | INSERT, UPDATE, DELETE | β
Yes | Full CDC pipelines |
SHOW_INITIAL_ROWS = TRUE | Bootstrap data | β
If not append-only | Initial sync or migration |
π‘οΈ Best Practicesβ
| Practice | Why It Helps |
|---|
β
Use MERGE with metadata | Simplifies full CDC application |
π Track updated_at in source | Helps with ordering, conflict resolution |
π΅οΈ Monitor SHOW STREAMS, DESC STREAM | Prevent stale data or missed deltas |
| πͺ Use task-specific warehouses | Ensures performance isolation |
| π§ͺ Avoid SELECT in dev | Prevents accidental consumption of change data |
π Monitoringβ
SHOW STREAMS;
DESC STREAM emp_cdc_stream;
SELECT *
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(TASK_NAME => 'APPLY_EMP_CDC_TASK'));
π Secure CDC Pipeline Layersβ
| Layer | Security Support | Role |
|---|
| Streams | β
| Change tracking |
| Tasks | β
| Scheduled execution |
| Merge logic | β
| Idempotent updates and deletes |
| IAM/Network Policy | β
| Governance and compliance |
π¦ Summaryβ
| Step | Example SQL |
|---|
| Source | CREATE TABLE emp_source ... |
| Stream | CREATE STREAM emp_cdc_stream ... |
| Target | CREATE TABLE emp_target ... |
| Task | CREATE TASK apply_emp_cdc_task ... |
| Monitor | SHOW 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!