Search code examples
snowflake-cloud-data-platformtime-travel

Time travelling a 'drop & recreate' table for any past day within retention period

Is it possible to see how a table look going back 10 days, provided the retention period is 30 days but the table is dropped and recreated on a daily basis?

If the table is truncated, instead of recreate, will going back to the 30th day possible?

Undrop probably restores the latest version of the table before it is dropped. Can it restore any version within the retention period?

Solution

  • This was an interesting question. We can do UNDROP if table was deleted multiple times. A good explanation for this can be found here - https://community.snowflake.com/s/article/Solution-Unable-to-access-Deleted-time-travel-data-even-within-retention-period

    https://docs.snowflake.com/en/user-guide/data-time-travel.html?_ga=2.118857801.110877935.1647736580-83170813.1644772168&_gac=1.251330994.1646009703.EAIaIQobChMIuZ3o2Zeh9gIVjR-tBh3PvQUIEAAYASAAEgKYevD_BwE#example-dropping-and-restoring-a-table-multiple-times

    I tested the scenario too, as shown below -

    Refer below history

    SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select query_id,query_text,start_time from table(information_schema.query_history()) where query_text like '%test_undrop_1%';
+--------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+-------------------------------+
| QUERY_ID                             | QUERY_TEXT                                                                                                                    | START_TIME                    |
|--------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+-------------------------------|
| 01a31a99-0000-81fe-0001-fa120003d75e | select query_id,query_text,start_time from table(information_schema.query_history()) where query_text like '%test_undrop_1%'; | 2022-03-22 14:13:58.953 -0700 |
| 01a31a99-0000-81c6-0001-fa120003f7ee | drop table test_undrop_1;                                                                                                     | 2022-03-22 14:13:55.098 -0700 |
| 01a31a99-0000-81fe-0001-fa120003d73e | create or replace table test_undrop_1(id number, name varchar2(10));                                                          | 2022-03-22 14:13:53.425 -0700 |
| 01a31a99-0000-81fe-0001-fa120003d72a | drop table test_undrop_1;                                                                                                     | 2022-03-22 14:13:46.968 -0700 |
| 01a31a99-0000-81c6-0001-fa120003f79e | create or replace table test_undrop_1(id1 number, name varchar2(10));                                                         | 2022-03-22 14:13:44.002 -0700 |
| 01a31a99-0000-81fe-0001-fa120003d70e | drop table test_undrop_1;                                                                                                     | 2022-03-22 14:13:36.078 -0700 |
| 01a31a99-0000-81c6-0001-fa120003f77e | select query_id,query_text,start_time from table(information_schema.query_history()) where query_text like '%test_undrop_1%'; | 2022-03-22 14:13:14.711 -0700 |
| 01a31a99-0000-81fe-0001-fa120003d70a | select count(*) from test_undrop_1;                                                                                           | 2022-03-22 14:13:04.640 -0700 |
| 01a31a98-0000-81fe-0001-fa120003d706 | select * from test_undrop_1;                                                                                                  | 2022-03-22 14:12:52.230 -0700 |
| 01a31a98-0000-81c6-0001-fa120003f75e | create or replace table test_undrop_1(id1 number, name1 varchar2(10));                                                        | 2022-03-22 14:12:43.734 -0700 |
+--------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+-------------------------------+

SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select * from test_undrop_1;
+----+------+
| ID | NAME |
|----+------|
+----+------+
0 Row(s) produced. Time Elapsed: 0.760s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>alter table TEST_UNDROP_1 rename to test_undrop_1_1;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.142s

    UNDROP-1

    SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>undrop table test_undrop_1;
+--------------------------------------------+
| status                                     |
|--------------------------------------------|
| Table TEST_UNDROP_1 successfully restored. |
+--------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.155s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select * from test_undrop_1;
+----+------+
| ID | NAME |
|----+------|
+----+------+
0 Row(s) produced. Time Elapsed: 0.223s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>alter table TEST_UNDROP_1 rename to test_undrop_1_2;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.191s

    UNDROP-2

    SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>undrop table test_undrop_1;
+--------------------------------------------+
| status                                     |
|--------------------------------------------|
| Table TEST_UNDROP_1 successfully restored. |
+--------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.155s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select * from test_undrop_1;
+-----+------+
| ID1 | NAME |
|-----+------|
+-----+------+
0 Row(s) produced. Time Elapsed: 0.140s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>alter table TEST_UNDROP_1 rename to test_undrop_1_3;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.396s

    UNDROP-3 (Yey! got my table version)

    SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>undrop table test_undrop_1;
+--------------------------------------------+
| status                                     |
|--------------------------------------------|
| Table TEST_UNDROP_1 successfully restored. |
+--------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.149s
SNOWFLAKE1#COMPUTE_WH@TEST_DB.PUBLIC>select * from test_undrop_1;
+-----+-------+
| ID1 | NAME1 |
|-----+-------|
+-----+-------+
0 Row(s) produced. Time Elapsed: 0.178s