Timetravel in Snowflake to retrieve previous code back

Introduction

Mainly, Timetravel is focussed on getting the old data back, but what if you have doubts whether some code worked before and now it isn't anymore. Can you retrieve the previous code back? Yes, that is possible. But you can't time travel on stored procedure objects. How do achieve time travel on code? Well, you can use the time travel functionality on schema and database level and then clone the situation at a particular point in time. Let's try this out.

Time travel

I've included the complete script. I'll  explain it in more detail after this code block.

//============================================================================
// Setup Databases
//============================================================================
CREATE OR REPLACE DATABASE TIMETRAVEL_TIMESTAMP_SP;

CREATE OR REPLACE SCHEMA TESTHENNIE;

USE DATABASE TIMETRAVEL_TIMESTAMP_SP;
USE SCHEMA TESTHENNIE;

//============================================================================
// Setup tables
//============================================================================


CREATE OR REPLACE TABLE TESTHENNIE.CUSTOMER (ID INT, NAME VARCHAR);

INSERT INTO TESTHENNIE.CUSTOMER VALUES (1, 'Hennie'), (2, 'Peter'), (3, 'Karen') ;

SELECT * FROM TESTHENNIE.CUSTOMER ;

CREATE OR REPLACE PROCEDURE TESTHENNIE.HITHERE (HI NUMBER) RETURNS NUMBER LANGUAGE SQL AS BEGIN SELECT 1; END;

--Wait a couple of seconds..

--Record the time and use this Later
SELECT CURRENT_TIMESTAMP();
--2023-12-15 02:55:55.722 -0800 

-- Wait for 5 a 10  seconds
INSERT INTO TESTHENNIE.CUSTOMER VALUES (4, 'Frank');

SELECT * FROM TESTHENNIE.CUSTOMER ;

CREATE OR REPLACE PROCEDURE TESTHENNIE.HITHERE (HI NUMBER) RETURNS NUMBER LANGUAGE SQL AS BEGIN SELECT 222222222222222; END;

//============================================================================
// TIMETRAVEL with AT
//============================================================================

SELECT * FROM TESTHENNIE.CUSTOMER AT (TIMESTAMP => '2023-12-15 02:55:55.722 -0800'::timestamp_LTZ);  

CREATE SCHEMA TESTHENNIE_CLONE CLONE TESTHENNIE AT (TIMESTAMP => '2023-12-15 02:55:55.722 -0800'::timestamp_LTZ);

SELECT GET_DDL('Procedure', 'TESTHENNIE_CLONE.HITHERE(NUMBER)')

SELECT GET_DDL('Procedure', 'TESTHENNIE.HITHERE(NUMBER)')

I've created a number of lines of code and there a couple of statements important. The CREATE SCHEMA with the CLONE is necessary to get a copy of the code and data at a certain moment in time. Let us focus on the last two statements. The first GET_DDL retrieves the old version of the Stored procedure :

SELECT GET_DDL('Procedure', 'TESTHENNIE_CLONE.HITHERE(NUMBER)')

And this results in : 

And offcourse, the next GET_DDL command retrieves the current version of the stored procedure:

SELECT GET_DDL('Procedure', 'TESTHENNIE.HITHERE(NUMBER)')

Results in : 

Final Thoughts

I've not really thought about it, but you can also travel back in time and watch the history of code.  

Hennie