METADATA$ISUPDATE is a Boolean change-tracking column in a Snowflake stream. TRUE marks a stream record produced by an UPDATE statement; FALSE marks a record not produced by an update. An update appears as two records: a DELETE for the prior row version and an INSERT for the new version. Read this flag with METADATA$ACTION to distinguish update pairs from standalone inserts and deletes. section 12.3
Reading an update and an insert
Updating English Muffin from Bread to Pastry yields DELETE and INSERT stream rows, both with METADATA$ISUPDATE = TRUE. Inserting Sourdough Bread yields one INSERT row with METADATA$ISUPDATE = FALSE. For append-only history, filter on METADATA$ACTION = 'INSERT' so the new update version and new products are retained without deleting prior versions. 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 the DELETE/INSERT update pair with a standalone INSERT and their respective Boolean flags. section 12.3.2
