In Snowflake is there a way to find out when a table was truncated?

Question

I have searched through the documentation and taken a look at the information schema views, however, I am unsure of how to determine when a table was truncated, any ideas?

Answer 1

Giving a few examples of using QUERY_HISTORY. Information schema query_history is a table function.

Searching history within last 1 hour to check a table that was truncated -

select query_id,query_text,start_time,end_time from table(information_schema.query_history(dateadd('hours'
,-1,current_timestamp()),
current_timestamp())) where query_text like 'TRUNCATE%test%';

+--------------------------------------+---------------------------+-------------------------------+-------------------------------+
| QUERY_ID                             | QUERY_TEXT                | START_TIME                    | END_TIME                      |
|--------------------------------------+---------------------------+-------------------------------+-------------------------------|
| 01a397da-3200-5a32-0000-00006ca565d1 | TRUNCATE table test_hash; | 2022-04-13 20:38:48.536 -0700 | 2022-04-13 20:38:48.951 -0700 |
+--------------------------------------+---------------------------+-------------------------------+-------------------------------+

Below is a sample table that will be truncated -

select count(*) from player;
+----------+
| COUNT(*) |
|----------|
|        4 |
+----------+

Gave the truncate command -

truncate table player;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

Will search in QUERY_HISTORY for table truncation. Query is case sensitive, so need to take care when using in predicate to search. Below will not give any result as we used query in lower case (truncate)

select query_id,query_text,start_time,end_time from table(information_schema.query_history(dateadd('hours'
,-1,current_timestamp()),current_timestamp())) where query_text like 'TRUNCATE%player%';
+----------+------------+------------+----------+
| QUERY_ID | QUERY_TEXT | START_TIME | END_TIME |
|----------+------------+------------+----------|
+----------+------------+------------+----------+

Searched again using lower case or you can use function to convert query_text to lower/upper and give same case in predicate. e.g. upper(query_text) like 'TRUNCATE%PLAYER%';

select query_id,query_text,start_time,end_time from table(information_schema.query_history(dateadd('hours'
,-1,current_timestamp()),current_timestamp())) where query_text like 'truncate%player%';
+--------------------------------------+------------------------+-------------------------------+-------------------------------+
| QUERY_ID                             | QUERY_TEXT             | START_TIME                    | END_TIME                      |
|--------------------------------------+------------------------+-------------------------------+-------------------------------|
| 01a397e2-3200-5a1a-0000-00006ca5560d | truncate table player; | 2022-04-13 20:46:22.083 -0700 | 2022-04-13 20:46:22.471 -0700 |
+--------------------------------------+------------------------+-------------------------------+-------------------------------+