3
0
During this post we will discuss how to UNDROP table which was dropped multiple times. Consider the scenario you have drop or replace the table multiple times. This action has created the history of dropped tables which can verify with SHOW HISTORY command.
show tables history like <Table name>
Tables can be recover within the Data Retention Period. The standard retention period is 1 day (24 hours) and is automatically enabled for all Snowflake accounts.
In order to UNDROP a Table, a Table by the same name should not currently exist. Hence, we need to iteratively rename and recover the TABLE to the point we are looking for. We will continue this process until we have either get the Table we are looking or UNDROP statements no longer valid.
Example:
Lets say below ACCOUNT_TABLE created multiple times. We can see multiple versions of table as below:
In order to revert back the table to original version i.e. ROW 3 (based on original timestamp) we have to follow the below process.
First Iteration: Revert the table to Second version
ALTER TABLE ACCOUNT_TABLE RENAME TO ACCOUNT_TABLE_V1
UNDROP TABLE ACCOUNT_TABLE
Second Iteration: Revert the table to Original version
ALTER TABLE ACCOUNT_TABLE RENAME TO ACCOUNT_TABLE_V2
UNDROP TABLE ACCOUNT_TABLE
Now I want this whole process to be automate via Snowflake Javascript procedure.
I have written the below code which will automatically traverse to the original version. Irrespective the number of time Table gets drop. Below proc will handle all iterations and revert original table version without manual intervention.
Say we have below ASSET table:
Now we want the original state of table to be revert back i.e. Table created at 09:03:28 should be revert back.
