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 [KEY (column_name [, ...])]
SELECT ... FROM table_name;

This uses common table expression (CTE) syntax within CREATE MATERIALIZED VIEW or CREATE SINK. It exposes changes from the source relation as rows with metadata columns. The optional KEY clause enables keyed changelogs; omit it to retain the existing per-change behavior.

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

Keyed changelogs

Use KEY (column_name [, ...]) to group change events by a business key. Each key column must be a distinct, visible column in the source relation. This key controls how changes are distributed and ordered; it does not declare a primary key or enforce uniqueness. Keyed changelogs buffer changes until each barrier. They compact changes using the upstream stream key together with the declared key, removing intermediate changes that cancel out within that interval. For rows sharing the declared key, removal events are emitted before addition events. This ensures that replacing a row leaves its addition as the latest event, even if the upstream insert arrives before the delete. Multiple upstream rows with the same declared key can still produce separate events. The following example tracks a business key, class_id, while the upstream materialized view groups by class_id, description, and free_usage. Changing a description replaces an upstream group but keeps the same business key.

SET RW_IMPLICIT_FLUSH TO true;

CREATE TABLE source (
    class_id BIGINT,
    description VARCHAR,
    free_usage BOOLEAN
);

CREATE MATERIALIZED VIEW grouped_classes AS
SELECT class_id, description, free_usage, COUNT(*) AS n
FROM source
GROUP BY class_id, description, free_usage;

CREATE MATERIALIZED VIEW mv AS
WITH changes AS CHANGELOG FROM grouped_classes KEY (class_id)
SELECT class_id, description, free_usage,
       changelog_op AS __op, CAST(_changelog_row_id AS BIGINT) AS __row_id
FROM changes;

INSERT INTO source VALUES (42, 'Beginner', false), (43, 'Basic', false);

UPDATE source
SET description = 'Advanced', free_usage = true WHERE class_id = 42;

SELECT class_id, description, free_usage::INT, __op
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY __row_id DESC) AS rn
    FROM mv
)
WHERE rn = 1
ORDER BY class_id;

The latest event for class 42 is the addition of the replacement row:

class_id | description | free_usage | __op
---------+-------------+------------+-----
42       | Advanced    | 1          | 1
43       | Basic       | 0          | 1

RW_IMPLICIT_FLUSH makes each data modification visible before the next statement in this example. After deleting class 42, the same query returns a latest event with __op = 2 for that class.

DELETE FROM source WHERE class_id = 42;

SQL reference

Python SDK

User-defined functions

Syntax

Example

Keyed changelogs

​Syntax

​Example

​Keyed changelogs

Syntax

Example

Keyed changelogs