Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Create Stream in Snowflake: Syntax and Example

To create a stream in Snowflake, use the CREATE STREAM statement followed by the stream name and the source table. This stream tracks changes (inserts, updates, deletes) on the source table for use in data pipelines or change data capture.
📐

Syntax

The CREATE STREAM statement defines a stream object that tracks changes on a specified table. You specify the stream name and the source table it monitors.

  • CREATE STREAM <stream_name>: Names the stream.
  • ON TABLE <table_name>: Specifies the source table to track.
  • APPEND_ONLY = TRUE|FALSE (optional): Indicates if the stream tracks only inserts (true) or all changes (false).
sql
CREATE STREAM my_stream ON TABLE my_table APPEND_ONLY = FALSE;
💻

Example

This example creates a stream named orders_stream on the orders table to track all changes including inserts, updates, and deletes.

sql
CREATE OR REPLACE TABLE orders (
  order_id INT,
  product VARCHAR,
  quantity INT
);

CREATE STREAM orders_stream ON TABLE orders APPEND_ONLY = FALSE;
Output
Statement executed successfully.
⚠️

Common Pitfalls

Common mistakes when creating streams include:

  • Not specifying the correct source table name.
  • Using APPEND_ONLY = TRUE when updates or deletes need to be tracked.
  • Trying to create a stream on a non-existent or transient table.

Always verify the source table exists and choose APPEND_ONLY based on your change tracking needs.

sql
/* Wrong: Stream on non-existent table */
CREATE STREAM wrong_stream ON TABLE missing_table;

/* Right: Stream on existing table */
CREATE STREAM correct_stream ON TABLE existing_table APPEND_ONLY = FALSE;
📊

Quick Reference

ClauseDescription
CREATE STREAM Defines the stream name.
ON TABLE Specifies the source table to monitor.
APPEND_ONLY = TRUE|FALSETracks only inserts if TRUE; all changes if FALSE (default).
✅

Key Takeaways

Use CREATE STREAM with ON TABLE to track changes on a source table.
Set APPEND_ONLY = FALSE to capture inserts, updates, and deletes.
Ensure the source table exists before creating a stream.
Streams help implement change data capture in Snowflake.
Verify stream creation with successful execution messages.