How Streams work in Snowflake

Eighteenth Chapter of the Snowflake SnowPro Core Course: Streams

Gonzalo Fernandez Plaza

--

Streams for the Snowflake SnowPro Core Certification.
Streams for the Snowflake SnowPro Core Certification.

Streams in Snowflake are perhaps one of the most complex topics of the Snowflake SnowPro Core certification, although once we understand them, all the questions will seem very simple. Let's go for it!

  1. Introduction to Streams
  2. Stream Examples
  3. Typical Exam Questions about Streams

Remember that all the chapters from the Snowflake course can be found in the following link:

INTRODUCTION TO STREAMS

Streams are Snowflake objects that record data manipulation language (DML) changes made to tables*, including INSERTS, UPDATES, and DELETES, as well as metadata about each change. Thanks to this, actions can be taken using the changed data.
*Streams didn’t work with views; however, as a new feature of March 2022, Streams now support views and secure views!

Streams don't contain table data; they only store offsets. When created, a table stream logically takes an initial snapshot of every row in the source table by initializing a point in time (called an offset) as the current transactional version of the table. The stream then records the DML changes after this snapshot was taken.

Streams in a Snowflake table
Streams in a Snowflake table

We have three different types of Streams:

  • Standard → Tracks all DML changes to the source table, including inserts, updates, and deletes. Supported on tables, directory tables, and views.
  • Append Only → Tracks row inserts only. Supported on tables, directory tables, and views.
  • Insert Only → Tracks row inserts only. The difference with the previous one is that this one is only supported on EXTERNAL TABLES.

--

--

Gonzalo Fernandez Plaza

Computer Science Engineer & Tech Lead 🖥️. Publishing AWS & Snowflake ❄️ courses & exams. https://www.fullcertified.com