AS CHANGELOG

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

ALTER VIEW BEGIN

⌘I

SQL reference

Python SDK

User-defined functions

Syntax

Example

​Syntax

​Example

Syntax

Example