AS CHANGELOG

Use the AS CHANGELOG clause to convert a changelog operation in a stream into a column. This can be used to create materialized views and sinks. See the practice in Sink data with upsert in Snowflake.

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