This can be used to create materialized views and sinks. See the practice in Sink data with upsert in Snowflake. The AS CHANGELOG clause adds two metadata columns:
  • changelog_op: the change type (1 = Insert, 2 = Delete, 3 = UpdateInsert, 4 = UpdateDelete)
  • _changelog_row_id: a monotonically increasing identifier that captures the order of the change.

​
Syntax

WITH table_name AS CHANGELOG FROM source_table;
This is done using the CTE syntax. It converts the source_table change record to a column of table_name.

​
Example

Create MV
CREATE MATERIALIZED VIEW ss_mv AS
WITH sub AS CHANGELOG FROM user_behaviors
SELECT
    user_id,
    target_id,
    event_timestamp AT TIME ZONE 'America/Indiana/Indianapolis' AS event_timestamp,
    changelog_op AS __op,
    _changelog_row_id::bigint AS __row_id
FROM
    sub;
Changelog operation
INSERT INTO user_behaviors (v1, v2) VALUES (1, 1);
INSERT INTO user_behaviors (v1, v2) VALUES (2, 2);

DELETE FROM user_behaviors WHERE v1 = 2;

UPDATE user_behaviors SET v2 = 100 WHERE v1 = 1;
Query MV
SELECT * FROM ss_mv;

-------RESULT
v1  v2  changelog_op
1   1   1
2   2   1
2   2   2
1   1   4
1   100 3