{"id":903,"date":"2023-06-03T18:26:20","date_gmt":"2023-06-03T18:26:20","guid":{"rendered":"https:\/\/bandittracker.com\/?p=903"},"modified":"2023-06-03T18:26:24","modified_gmt":"2023-06-03T18:26:24","slug":"streams-in-snowflake-beginner-guide","status":"publish","type":"post","link":"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/","title":{"rendered":"Streams In Snowflake \u2013 Beginner Guide"},"content":{"rendered":"\n<p>This article walks you through the basics of streams in Snowflake. We start with what they are for and move into more complex concepts such as their architecture and how they work.<\/p>\n\n\n\n<p>We&#8217;ll also dive into what you need to know to get started:<\/p>\n\n\n\n<ul>\n<li>creating streams<\/li>\n\n\n\n<li>querying streams<\/li>\n\n\n\n<li>handling staleness<\/li>\n\n\n\n<li>dealing with performance considerations<\/li>\n<\/ul>\n\n\n\n<p>Let\u2019s get into it.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_64 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-66116939f337a\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-66116939f337a\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#Role_Of_Streams_In_Snowflake\" title=\"Role Of Streams In Snowflake\">Role Of Streams In Snowflake<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#What_Are_Streams\" title=\"What Are Streams?\">What Are Streams?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#How_Streams_Work\" title=\"How Streams Work\">How Streams Work<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#How_Can_Multiple_Use_Cases_Query_The_Same_Changes\" title=\"How Can Multiple Use Cases Query The Same Changes?\">How Can Multiple Use Cases Query The Same Changes?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#How_Streams_Are_Stored_in_Snowflake\" title=\"How Streams Are Stored in Snowflake\">How Streams Are Stored in Snowflake<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#Streams_Dont_Store_Extra_Data_To_Track_Updates_%E2%80%93_But_Snowflake_Does\" title=\"Streams Don\u2019t Store Extra Data To Track Updates \u2013 But Snowflake Does?\">Streams Don\u2019t Store Extra Data To Track Updates \u2013 But Snowflake Does?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#What_Storage_Costs_Are_Associated_With_Streams\" title=\"What Storage Costs Are Associated With Streams?\">What Storage Costs Are Associated With Streams?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#How_To_Create_A_Stream_On_A_Snowflake_Table\" title=\"How To Create A Stream On A Snowflake Table\">How To Create A Stream On A Snowflake Table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#Snowflake_Create_Stream_Example\" title=\"Snowflake Create Stream Example\">Snowflake Create Stream Example<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#Types_Of_Streams\" title=\"Types Of Streams\">Types Of Streams<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#How_To_Query_A_Stream_In_Snowflake\" title=\"How To Query A Stream In Snowflake\">How To Query A Stream In Snowflake<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#What_Is_A_Stream_Offset\" title=\"What Is A Stream Offset?\">What Is A Stream Offset?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#Can_You_Create_Streams_On_Views\" title=\"Can You Create Streams On Views?\">Can You Create Streams On Views?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#What_Happens_When_A_Snowflake_Stream_Goes_Stale\" title=\"What Happens When A Snowflake Stream Goes Stale\">What Happens When A Snowflake Stream Goes Stale<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#How_To_Check_If_A_Stream_Has_Data\" title=\"How To Check If A Stream Has Data\">How To Check If A Stream Has Data<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-16\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/#Performance_Considerations\" title=\"Performance Considerations\">Performance Considerations<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Role_Of_Streams_In_Snowflake\"><\/span>Role Of Streams In Snowflake<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Streams are Snowflake\u2019s implementation of Change Data Capture (CDC) that is found in other database systems like Microsoft SQL Server.<\/p>\n\n\n\n<p>Streams allow you to track and manage data changes in tables. When you create a stream on a table, Snowflake keeps a continuous record of these changes that you can query.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"400\" src=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/snowflake-streams-1.jpg\" alt=\"\" class=\"wp-image-905\" srcset=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/snowflake-streams-1.jpg 600w, https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/snowflake-streams-1-300x200.jpg 300w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_Are_Streams\"><\/span>What Are Streams?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Streams in Snowflake are objects that record Data Manipulation Language (DML) changes (INSERT, UPDATE, and DELETE operations) in a table from a specific point in time.<\/p>\n\n\n\n<p>A stream allows you to view and analyze the data changes in a table since the stream was initiated or since the last time changes were consumed from the stream. The power comes from providing this tracking in real time.<\/p>\n\n\n\n<p>It\u2019s important to understand that a stream is not a physical storage of data. It contains metadata about the changes made to the data in the table on which it&#8217;s created.<\/p>\n\n\n\n<p>Streams maintain their state independently of all other streams and consumers. This allows multiple use cases to consume the change data independently and according to their individual timelines.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_Streams_Work\"><\/span>How Streams Work<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Take an example of a row in a product table that looks like this:<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-stripes\"><table><tbody><tr><td><strong>ID<\/strong><\/td><td><strong>Name<\/strong><\/td><td><strong>Category<\/strong><\/td><\/tr><tr><td>4<\/td><td>Pen<\/td><td>Office Supplies<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>You run an update that changes the category in this row. What happens if a stream had been created on the table before you run the update?<\/p>\n\n\n\n<p>Here&#8217;s a simplified step-by-step of what happens.<\/p>\n\n\n\n<ol type=\"1\">\n<li>When a stream is created on a table, it starts recording changes.<\/li>\n\n\n\n<li>The update is captured by the stream along with information about the transaction that caused it.<\/li>\n\n\n\n<li>Users can query the stream just like any other table.<\/li>\n<\/ol>\n\n\n\n<p>This infographic shows the process. The update statement is on the left, with a representation of how the row changes.<\/p>\n\n\n\n<p>The stream is on the right. It is literally a table with information about the before-and-after state of the row.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"400\" src=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/snowflake-stream-infographic.jpg\" alt=\"\" class=\"wp-image-906\" srcset=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/snowflake-stream-infographic.jpg 600w, https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/snowflake-stream-infographic-300x200.jpg 300w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/figure>\n\n\n\n<p>When a user queries the stream and commits the transaction, the changes consumed by that query are cleared from the stream. This means that each change is available to be consumed once from the stream.<\/p>\n\n\n\n<p>The stream continues to track and present new changes to the underlying table, allowing for continuous data analysis.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_Can_Multiple_Use_Cases_Query_The_Same_Changes\"><\/span>How Can Multiple Use Cases Query The Same Changes?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You may be wondering how several use cases can query the same changes. After all, once changes are consumed from a stream in Snowflake, they are cleared from that stream.<\/p>\n\n\n\n<p>The key is that each use case creates a separate stream on the same table. Each stream tracks and stores the changes to that table independently.<\/p>\n\n\n\n<p>This way, each use case can consume the changes at its own pace without affecting the others.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_Streams_Are_Stored_in_Snowflake\"><\/span>How Streams Are Stored in Snowflake<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>To understand how streams are stored in Snowflake, you first need to learn about stream metadata.<\/p>\n\n\n\n<p>Rather than storing the actual data, a stream records metadata that allows users to query and understand the changes made to the underlying data.<\/p>\n\n\n\n<ul>\n<li>Updated row: a stream stores both the pre-update and post-update versions of the row.<\/li>\n\n\n\n<li>Deleted row: a stream stores the state of the row before deletion.<\/li>\n\n\n\n<li>Inserted row: a stream stores the state of the row after insertion.<\/li>\n<\/ul>\n\n\n\n<p>Storing metadata makes this a very lightweight mechanism. And because the metadata is stored in relational format, it can be queried like any other table in Snowflake.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Streams_Dont_Store_Extra_Data_To_Track_Updates_%E2%80%93_But_Snowflake_Does\"><\/span>Streams Don\u2019t Store Extra Data To Track Updates \u2013 But Snowflake Does?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"400\" height=\"500\" src=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/confused.jpg\" alt=\"\" class=\"wp-image-907\" srcset=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/confused.jpg 400w, https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/confused-240x300.jpg 240w\" sizes=\"(max-width: 400px) 100vw, 400px\" \/><\/figure><\/div>\n\n\n<p>It may seem contradictory to say that streams don\u2019t store data, but they can access the pre and post-versions of an updated row. Isn\u2019t that just pretending that streams don\u2019t include the extra data?<\/p>\n\n\n\n<p>Well, no.<\/p>\n\n\n\n<p>It\u2019s true that when a row in a Snowflake table is updated, the old version of the row and the new version of the row are both stored physically.<\/p>\n\n\n\n<p>But this is part of Snowflake&#8217;s underlying data storage and versioning mechanism, <strong>not the stream itself<\/strong>. It happens whether there is a stream on the table are not.<\/p>\n\n\n\n<p>Snowflake&#8217;s architecture is based on immutable storage. When a row is updated, Snowflake writes a new version of the row while retaining the old version. This enables features like Time Travel and Fail-safe.<\/p>\n\n\n\n<p>Streams are an optional extra that take advantage of this immutable storage. They basically provide pointers (in terms of metadata) to the old and new versions.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_Storage_Costs_Are_Associated_With_Streams\"><\/span>What Storage Costs Are Associated With Streams?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>I mentioned earlier that streams aren\u2019t physical objects, so how can they produce storage costs?<\/p>\n\n\n\n<p>Because a stream references a historical version of the base table&#8217;s data, Snowflake must maintain the data in its historical state until all the changes have been consumed by the stream and the transaction is committed.<\/p>\n\n\n\n<p>In other words, the pre-change row of our sample data is stored in a micro-partition. When the data is updated on a table that has a stream, that row is kept in storage. The updated row is stored separately.<\/p>\n\n\n\n<p>The longer that changes are left unconsumed in a stream, the more historical data Snowflake must maintain in its storage layer. Over a prolonged period, you will see extra costs.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Create_A_Stream_On_A_Snowflake_Table\"><\/span>How To Create A Stream On A Snowflake Table<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You need to have the necessary privileges on:<\/p>\n\n\n\n<ol type=\"1\">\n<li>the schema where you want to create the stream.<\/li>\n\n\n\n<li>The schema with the table.<\/li>\n<\/ol>\n\n\n\n<p>Yes, the stream can be in a separate schema.<\/p>\n\n\n\n<p>The CREATE STREAM statement creates a new stream. This is the basic syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE STREAM &#91;stream name] ON TABLE &#91;table name];<\/code><\/pre>\n\n\n\n<p>There are lots of additional parameters and options but this will get you started.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Snowflake_Create_Stream_Example\"><\/span>Snowflake Create Stream Example<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You can follow along with this worked example.<\/p>\n\n\n\n<p>This code creates a table and inserts three records:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Create Or Replace Table PRODUCT (\nId VARCHAR NOT NULL PRIMARY KEY,\nNAME VARCHAR NOT NULL,\nCATEGORY VARCHAR\n);\n\nINSERT INTO PRODUCT VALUES (1, 'Pen', 'Office Supplies');\nINSERT INTO PRODUCT VALUES (2, 'Pencil', 'School Supplies');\nINSERT INTO PRODUCT VALUES (3, 'Crayon', 'School Supplies');<\/code><\/pre>\n\n\n\n<p>If you want some extra background on these statements, we have an article on <a href=\"https:\/\/bandittracker.com\/snowflake-create-table\/\" data-type=\"post\" data-id=\"870\">how to create tables in Snowflake<\/a>.<\/p>\n\n\n\n<p>Now that we have a table, we can create a stream on it:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE STREAM product_stream ON TABLE product;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Types_Of_Streams\"><\/span>Types Of Streams<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>There are three types of streams in Snowflake:<\/p>\n\n\n\n<ol type=\"1\">\n<li>Standard Streams<\/li>\n\n\n\n<li>Append-Only Streams<\/li>\n\n\n\n<li>Insert-Only Streams<\/li>\n<\/ol>\n\n\n\n<p>The standard stream is created with the syntax in the previous section and tracks inserts, updates, and delete statements.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Append-Only Streams<\/h3>\n\n\n\n<p>An append-only stream only tracks insert operations. This improves performance if it matches your requirements. This is the syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE STREAM &#91;stream name] ON TABLE &#91;table name] <strong>APPEND_ONLY = TRUE<\/strong>;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Insert-Only Streams<\/h3>\n\n\n\n<p>This is the same concept as the append-only tables but it is only for external tables. This is the syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE STREAM &#91;stream name] ON EXTERNAL TABLE &#91;table name] INSERT_ONLY = TRUE;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Query_A_Stream_In_Snowflake\"><\/span>How To Query A Stream In Snowflake<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The beauty of streams is that you query them just like any other table. In other words, you use the SELECT statement.<\/p>\n\n\n\n<p>Let\u2019s run through an example.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1: Create a stream on the product table.<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE STREAM product_stream ON TABLE product;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 2: Run an update on the product table.<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>UPDATE product SET category = \u2018School Supplies\u2019 WHERE ID = 1;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 3: Query the stream.<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM product_stream;<\/code><\/pre>\n\n\n\n<p>You will see two rows for a single update statement.<\/p>\n\n\n\n<p>Each row in a stream has additional metadata columns that provide information about the change. <\/p>\n\n\n\n<p>For example, when the METADATA$ACTION column shows &#8216;INSERT&#8217;, it is the new version of the row after an update. If it shows &#8216;DELETE&#8217;, it is the old version of the row before the update.<\/p>\n\n\n\n<p>Here is the result from our example:<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"300\" src=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/snowflake-query-stream.jpg\" alt=\"\" class=\"wp-image-908\" srcset=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/snowflake-query-stream.jpg 600w, https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/snowflake-query-stream-300x150.jpg 300w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/figure><\/div>\n\n\n<p>You can run this SELECT statement repeatedly. The change is retained until you clear the stream.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 4: Commit the consumed changes in the stream.<\/h3>\n\n\n\n<p>After querying the changes, you typically process them in some way. This could involve transforming the data, loading it into another table, analyzing it, etc. The specifics will depend on your use case.<\/p>\n\n\n\n<p>Once you have queried and processed the changes, you commit the transaction. You do this using the COMMIT command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>COMMIT;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_Is_A_Stream_Offset\"><\/span>What Is A Stream Offset?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>An offset refers to the position of a specific piece of data within a stream. It&#8217;s a way to keep track of where you are in a stream when reading data from it.<\/p>\n\n\n\n<p>When you create a stream, it takes a snapshot of the current data at that point in time. When any data change occurs in the base table, the offset moves forward.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Can_You_Create_Streams_On_Views\"><\/span>Can You Create Streams On Views?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>So far, we\u2019ve looked at creating streams on tables. But what about views?<\/p>\n\n\n\n<p>When Snowflake first launched, you couldn\u2019t create a stream on a view. Snowflake has since introduced the ability as a new feature\u2026with some limitations. Here are the main three:<\/p>\n\n\n\n<ol>\n<li>You can create streams on standard and secure views, but not on materialized views.<\/li>\n\n\n\n<li>The view can only be on native tables. <\/li>\n\n\n\n<li>Some more unusual types of joins aren\u2019t allowed.<\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_Happens_When_A_Snowflake_Stream_Goes_Stale\"><\/span>What Happens When A Snowflake Stream Goes Stale<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The term &#8220;stale&#8221; refers to the state of a stream when it&#8217;s no longer able to capture and reflect changes from its source table.<\/p>\n\n\n\n<p>This obviously happens when the stream&#8217;s source table has been dropped. However, there is a more common reason for the stream to go stale.<\/p>\n\n\n\n<p>Snowflake tables have a data retention period that defines the duration that Snowflake keeps its historical data. The default is 24 hours.<\/p>\n\n\n\n<p>If the stream offset gets older than the data retention period, the stream becomes stale. This applies to a single table, or any table within a view.<\/p>\n\n\n\n<p>When a stream becomes stale, it can&#8217;t track new changes to the table. If you still need to track changes, you would need to create a new stream on that table.<\/p>\n\n\n\n<p>The best solution is to consume the stream within the data retention period. This ensures that the feature functions as it\u2019s supposed to.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_To_Check_If_A_Stream_Has_Data\"><\/span>How To Check If A Stream Has Data<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You can run the SELECT statements that we\u2019ve shown already.<\/p>\n\n\n\n<p>However, there is a built-in function, SYSTEM$STREAM_HAS_DATA, that provides a faster way to check if there are uncommitted transactions in the table.<\/p>\n\n\n\n<p>The function returns TRUE or FALSE depending on whether there is unconsumed data.<\/p>\n\n\n\n<p>Here is an example of using it with our sample table:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT SYSTEM$STREAM_HAS_DATA('product_stream');<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Performance_Considerations\"><\/span>Performance Considerations<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"600\" height=\"400\" src=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/speed.jpg\" alt=\"\" class=\"wp-image-909\" srcset=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/speed.jpg 600w, https:\/\/bandittracker.com\/wp-content\/uploads\/2023\/06\/speed-300x200.jpg 300w\" sizes=\"(max-width: 600px) 100vw, 600px\" \/><\/figure>\n\n\n\n<p>Streams can help improve the performance of ETL operations by identifying and processing only the data that has changed, rather than reprocessing the entire table.<\/p>\n\n\n\n<p>But keep in mind that querying a stream is similar to querying a table. If a stream has a large number of changes that have not been consumed, querying the stream could take longer.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Impact on DML Operations<\/h3>\n\n\n\n<p>DML operations on a table with a stream may experience a slight increase in latency. This is because every DML operation on the table needs to record the changes to the metadata for the stream.<\/p>\n\n\n\n<p>In general, the impact is minimal and likely unnoticeable in most use cases. But for workloads with a high rate of DML operations, the impact could be more significant.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Data Retention and Storage Costs<\/h3>\n\n\n\n<p>Streams rely on Snowflake&#8217;s Time Travel feature, which retains historical data for a specified period.<\/p>\n\n\n\n<p>This means that the data changed in the table is stored twice:<\/p>\n\n\n\n<ul>\n<li>once in the table itself<\/li>\n\n\n\n<li>once in the Time Travel history<\/li>\n<\/ul>\n\n\n\n<p>This could increase storage costs. Consider adjusting the Time Travel data retention period to balance the need for accessing historical data with storage costs.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Stream Staleness<\/h3>\n\n\n\n<p>Be aware of the risk of streams becoming stale. A stale stream cannot capture new changes to the table.<\/p>\n\n\n\n<p>To maintain the performance of your ETL processes, monitor the status of your streams and recreate them if necessary.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This article walks you through the basics of streams in Snowflake. We start with what they are for and move into more complex concepts such as their architecture and how they work. We&#8217;ll also dive into what you need to know to get started: Let\u2019s get into it. Role Of Streams In Snowflake Streams are &#8230; <a title=\"Streams In Snowflake \u2013 Beginner Guide\" class=\"read-more\" href=\"https:\/\/bandittracker.com\/streams-in-snowflake-beginner-guide\/\" aria-label=\"More on Streams In Snowflake \u2013 Beginner Guide\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[25],"tags":[],"_links":{"self":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/903"}],"collection":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/comments?post=903"}],"version-history":[{"count":1,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/903\/revisions"}],"predecessor-version":[{"id":910,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/903\/revisions\/910"}],"wp:attachment":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/media?parent=903"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/categories?post=903"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/tags?post=903"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}