METADATA$ACTION is a Snowflake stream metadata column that labels each captured change as INSERT or DELETE. A stream starts from a snapshot of its source object and records differences caused by later INSERT, UPDATE, and DELETE statements. Query the stream with SELECT to inspect pending changes; after a transaction writes stream data to target tables and commits, the stream is consumed and begins collecting new changes. section 12.3
An UPDATE is represented by two change rows: DELETE for the prior version and INSERT for the new version. METADATA$ISUPDATE is TRUE for both rows, distinguishing this update pair from an independent insert, whose METADATA$ACTION is INSERT and whose METADATA$ISUPDATE is FALSE. Streams retain the net difference between versions rather than every intermediate change to a row. section 12.3 section 12.3.2
Append-only product history
After English muffin changes from Bread to Pastry, its stream rows contain DELETE for the old category and INSERT for the new category. A separate Sourdough Bread addition is also INSERT. To preserve history in PRODUCT_TBL, insert only rows where METADATA$ACTION = 'INSERT'; this retains both English muffin versions instead of deleting the prior one. section 12.3.2
Table 12.8 Contents of the PRODUCT_STREAM stream after updating and inserting data in the underlying PRODUCT table
|
Product ID
|
Product name
|
Category
|
Minimum quantity
|
Price
|
Valid from
|
METADATA$
ACTION
|
METADATA$
ISUPDATE
|
|---|---|---|---|---|---|---|---|
|
3
|
English muffin
|
Bread
|
6
|
1.2
|
2023-06-01
|
DELETE
|
TRUE
|
|
3
|
English muffin
|
Pastry
|
6
|
1.2
|
2023-06-01
|
INSERT
|
TRUE
|
|
13
|
Sourdough bread
|
Bread
|
1
|
3.6
|
2023-06-01
|
INSERT
|
FALSE
|
Inspect the stream rows to compare an update’s DELETE/INSERT pair with a standalone INSERT and their METADATA$ISUPDATE values. section 12.3.2
