Skip to main content
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
⌘I