Stream Metadata Columns
A Snowflake Stream is a change data capture (CDC) object that records the delta (inserts, updates, deletes) made to a table between two points in time.
When you query a stream, it returns the columns from the underlying source table, but it also appends three critical, hidden metadata columns. These columns tell your downstream tasks exactly what happened to the row so they know how to process it.
Topics Covered
- The Three Metadata Columns
- How Updates are Represented (Delete + Insert Pairs)
- Filtering Streams for Processing
- Using Metadata in
MERGEStatements
The Three Metadata Columns
Every row in a stream output includes these three columns:
METADATA$ACTION: Indicates the DML operation. It only has two possible values:INSERTorDELETE.METADATA$ISUPDATE: A boolean (TRUEorFALSE). If the action was caused by a pureINSERTorDELETEstatement, this isFALSE. If the action was caused by anUPDATEstatement, this isTRUE.METADATA$ROW_ID: A unique, immutable identifier for the row over time.
How Updates are Represented
Snowflake does not have an UPDATE action in a stream. Because of its immutable micro-partition architecture, an UPDATE is actually a delete of the old version and an insert of the new version.
Therefore, a single UPDATE to a source table results in two rows in the stream:
- One row with
METADATA$ACTION = 'DELETE'andMETADATA$ISUPDATE = TRUE(The old version). - One row with
METADATA$ACTION = 'INSERT'andMETADATA$ISUPDATE = TRUE(The new version).
Hands-On Demonstration: Exploring Stream Metadata
Let's build a source table representing a customer loyalty program, create a stream on it, perform various DML operations, and observe the metadata columns.
1. Database & Schema Setup
Copy and paste the following code into your Snowflake worksheet.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_CDC_DB;
USE DATABASE BEEMS_CDC_DB;
CREATE SCHEMA IF NOT EXISTS STREAM_LAB;
USE SCHEMA STREAM_LAB;
2. Creating the Table and Stream
-- Create the source table
CREATE OR REPLACE TABLE STREAM_LAB.LOYALTY_MEMBERS (
MEMBER_ID INT,
NAME STRING,
CITY STRING,
POINTS NUMBER
);
-- Create the stream on the source table
CREATE OR REPLACE STREAM STREAM_LAB.LOYALTY_STREAM ON TABLE STREAM_LAB.LOYALTY_MEMBERS;
Note: A newly created stream is empty because it only captures changes that happen after it was created.
3. Performing DML Operations
Let's simulate a day of activity for customers across India.
-- 1. Pure INSERT
INSERT INTO STREAM_LAB.LOYALTY_MEMBERS VALUES (1, 'Kavya Pillai', 'Kochi', 100);
-- 2. Pure INSERT (We will delete this one next)
INSERT INTO STREAM_LAB.LOYALTY_MEMBERS VALUES (2, 'Rohan Sharma', 'Delhi', 50);
-- 3. Pure DELETE
DELETE FROM STREAM_LAB.LOYALTY_MEMBERS WHERE MEMBER_ID = 2;
-- 4. UPDATE
UPDATE STREAM_LAB.LOYALTY_MEMBERS SET POINTS = 150 WHERE MEMBER_ID = 1;
4. Querying the Stream Metadata
Now, let's query the stream to see how Snowflake captured those changes.
SELECT
MEMBER_ID,
NAME,
POINTS,
METADATA$ACTION,
METADATA$ISUPDATE,
METADATA$ROW_ID
FROM STREAM_LAB.LOYALTY_STREAM
ORDER BY MEMBER_ID;
Understanding the Output:
- Rohan Sharma (ID: 2) was inserted and then deleted before the stream was consumed. Snowflake optimized this out! You will not see Rohan in the stream because the net delta is zero.
- Kavya Pillai (ID: 1) was inserted and then updated. Snowflake collapses this into a single row. Because the row did not exist prior to the stream's current offset, the net delta is a pure
INSERT.
Let's consume the stream (which clears it) and do another update to see the Delete+Insert pair.
-- Consume the stream (e.g., by creating a temp table)
CREATE OR REPLACE TEMP TABLE STREAM_CONSUMER AS SELECT * FROM STREAM_LAB.LOYALTY_STREAM;
-- Now the stream is empty. Let's do a pure update.
UPDATE STREAM_LAB.LOYALTY_MEMBERS SET POINTS = 300 WHERE MEMBER_ID = 1;
-- Query the stream again
SELECT
MEMBER_ID, POINTS, METADATA$ACTION, METADATA$ISUPDATE
FROM STREAM_LAB.LOYALTY_STREAM;
Now you will see two rows for Kavya:
DELETE|TRUE| Points: 150 (The old state)INSERT|TRUE| Points: 300 (The new state)
Using Metadata in MERGE Statements
When you build a downstream task to consume a stream and update a target table (like an SCD Type 2 dimension), you use the metadata columns in a MERGE statement.
(To verify your current user context, run SELECT CURRENT_USER();)
-- Conceptual MERGE statement using stream metadata
-- MERGE INTO TARGET_TABLE t
-- USING STREAM_LAB.LOYALTY_STREAM s
-- ON t.MEMBER_ID = s.MEMBER_ID
-- WHEN MATCHED AND s.METADATA$ACTION = 'DELETE' AND s.METADATA$ISUPDATE = FALSE THEN
-- DELETE -- Handle pure deletes
-- WHEN MATCHED AND s.METADATA$ACTION = 'INSERT' AND s.METADATA$ISUPDATE = TRUE THEN
-- UPDATE SET t.POINTS = s.POINTS -- Handle updates
-- WHEN NOT MATCHED AND s.METADATA$ACTION = 'INSERT' THEN
-- INSERT (MEMBER_ID, NAME, POINTS) VALUES (s.MEMBER_ID, s.NAME, s.POINTS);
Summary
METADATA$ACTIONis alwaysINSERTorDELETE.METADATA$ISUPDATEisTRUEif the change was part of anUPDATEstatement.- An
UPDATEproduces two rows in the stream: a delete of the old, and an insert of the new. - Streams automatically collapse multiple changes into the net delta before you consume them.