Snowflake Time Travel Notes

0


SELECT ... FROM ... { AT( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id -->Query ID> } ) | BEFORE( STATEMENT => <id> ) }  

  • AT keyword specifies that the request is inclusive of any changes made by a statement or transaction with timestamp equal to the specified parameter.

  • BEFORE keyword specifies that the request refers to a point immediately preceding the specified parameter.


Example: Select historical data from a table as of 5 minutes ago:

Create table test_time_travel(data string,inserted_time timestamp)


Select current_timestamp,LOCALTIMESTAMP,SYSDATE(),CURRENT_REGION()
 

insert into test_time_travel values ('First Row', SYSDATE())

insert into test_time_travel values ('Second Row', SYSDATE())

insert into test_time_travel values ('Third Row', SYSDATE())

Select * from test_time_travel at(offset => -60*5)