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 = TRUEwhen 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
| Clause | Description |
|---|---|
| CREATE STREAM | Defines the stream name. |
| ON TABLE | Specifies the source table to monitor. |
| APPEND_ONLY = TRUE|FALSE | Tracks 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.
