0

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?

Sayantan Mandal
  • 53
  • 7

3 Answers3

1

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
Pankaj
  • 2,692
  • 2
  • 6
  • 18
0

Yes, you can query a table at any point in its time travel period.

Undrop will restore the table as it was at the point it was dropped.

To restore a table at a previous point in time you would need to use CREATE TABLE… CLONE…

NickW
  • 8,430
  • 2
  • 6
  • 19
0
  1. 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?

The UNDROP command restores the most recent version of the table only. But @Pankaj's test shows that you can still restore any dropped table that hasn't been purged yet.

To check all version of the table in history (dropped), you can use the following command

SHOW TABLES HISTORY LIKE '<table_name>';

And then do a series of UNDROP and RENAME until you recover the version of the table that you want to restore.

However this is not a good practice. If you recreate the table daily, reduce it's retention period. If you want to recover 1 month of history, do a truncate instead of drop.

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

Yes.

create table tbl_clone clone tbl at (timestamp => dateadd('day',-30,current_timestamp()));
  1. Undrop probably restores the latest version of the table before it is dropped. Can it restore any version within the retention period?

Yes, as already mentioned and pointed out by @Pankaj .

Clark Perucho
  • 486
  • 2
  • 6