What is Stream in Snowflake: Definition and Usage
stream in Snowflake is a database object that tracks changes (inserts, updates, deletes) made to a table. It allows you to capture and query these changes incrementally without scanning the entire table.How It Works
Think of a stream in Snowflake like a bookmark in a book. When you read a chapter, you place a bookmark to remember where you left off. Similarly, a stream remembers the point in time when you last checked a table for changes.
When data in the table changes, the stream records these changes as new events. Instead of re-reading the whole table, you can ask the stream for only the new or modified rows since your last check. This makes processing data changes faster and more efficient.
Streams work by storing metadata about the table's change history. When you query a stream, it returns the changed rows with information about what kind of change happened (insert, update, or delete).
Example
This example shows how to create a stream on a table and query it to see changes.
CREATE OR REPLACE TABLE sales ( id INT, amount FLOAT ); INSERT INTO sales VALUES (1, 100), (2, 200); CREATE OR REPLACE STREAM sales_stream ON TABLE sales; -- Insert new data INSERT INTO sales VALUES (3, 300); -- Query the stream to see changes SELECT * FROM sales_stream;
When to Use
Use streams when you want to track changes in your data without scanning entire tables repeatedly. This is helpful for:
- Incremental data loading into data warehouses or lakes.
- Building change data capture (CDC) pipelines.
- Synchronizing data between systems efficiently.
- Auditing or monitoring data changes over time.
For example, if you have a sales table that updates frequently, a stream lets you process only new or changed sales records for reporting or analytics.
Key Points
- A stream tracks inserts, updates, and deletes on a table.
- It provides a snapshot of changes since the last time it was read.
- Streams enable efficient incremental data processing.
- They do not store data themselves but metadata about changes.
- Querying a stream marks the changes as consumed until new changes occur.
