How can you track the change metadata of a Snowflake table?

The CHANGES clause enables querying the change tracking metadata for a table within a specified interval of time without having to create a table stream with an explicit transactional offset. Multiple queries can retrieve the change tracking metadata between different transactional start and endpoints. Either change tracking must be enabled on the table, or a stream must be created on the table. In the bellow example we are enabled the change tracking.

Syntax

SELECT ...
FROM ...
CHANGES ( INFORMATION => { DEFAULT | APPEND_ONLY } )
AT( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) | BEFORE( STATEMENT => <id> )
[ END( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) ]
[ ... ]

DEFAULT : Returns all DML changes to the source table, including inserts, updates, and deletes
APPEND_ONLY : Returns appended rows only; therefore no join is performed.
TIMESTAMP => timestamp : Specifies an exact date and time to use for Time Travel.
OFFSET => time_difference : Specifies the difference in seconds from the current time to use for Time Travel.
STATEMENT => id Specifies the query ID of a statement to use as the reference point for Time Travel.

–Create sample table

create or replace table test_table (empid number(8) not null, emp_name varchar(255) default null);

— Enable change tracking on table.

alter table test_table set change_tracking = true;

— Initialize a session variable for the current timestamp. This is used when we retrieve the changes

set ts1 = (select current_timestamp());

–Insert into table 

 insert into test_table (empid,emp_name) values (1,'sam'), (2,'peter'), (3,'tom');

— Table operations

delete from test_table where empid = 1;
update test_table set emp_name = 'samual' where empid = 1;

— Query the change tracking metadata

 select * from test_table changes(information => default) at(timestamp => $ts1);

Result

 +-------+--------------+-----------------+-------------------+------------------------------------------+
| EMPID | EMP_NAME | METADATA$ACTION | METADATA$ISUPDATE | METADATA$ROW_ID                               |
|-------+---------------+-----------------+-------------------+------------------------------------------|
| 2     | peter    | INSERT          | False             | 30edlac5714c35cleac4cd093dd4a779a06c2091      |
| 3     | tom      | INSERT          | False             | Cb9ffcb80ff80125163f9f592b8f00ed4cl55ae9      |
+-------+---------------+-----------------+-------------------+------------------------------------------+

Snowflake posts