Snowflake show tables not accessed in last 20 days

Question

There is a situation where I need to clean up my database in snowflake. we have around 40 database and each database has more than 100 table. Some are getting loaded everyday and some are not, but used everyday. However, There has been lots of table added for testing and other purpose (by lots of developer and user).

Now we are working on cleaning up un-used table.

We have query_history table which gives us the information of query run in past, however it has field such as database, warehouse, User etc. but not table.

I was wondering is there is any way we can write a query which give us table name not used (DDL and DML b0th) in last 10 days.

"query_history" has a column "query_text", which contains the executed SQL statement, so you can search that for a reference to your table name? — Nathan Griffiths, Sep 07 '20 at 19:53

score 2 · Answer 1 · edited Sep 23 '21 at 12:38

2

select obj.value:objectName::string objName
      , max(query_start_time) as QUERY_DATE_TIME
    from snowflake.account_usage.access_history 
    , table(flatten(direct_objects_accessed)) obj
    group by 1
    order by QUERY_DATE_TIME desc;

edited Sep 23 '21 at 12:38

Suraj Rao

29,388
11
94
103

answered Sep 23 '21 at 12:37

Prasanth

21
2

Code-only answers are not particularly helpful. Please add some descriptions of how this code solves the problem. – Simas Joneliunas Sep 23 '21 at 15:31

score 1 · Answer 2 · answered Sep 06 '20 at 15:01

1

The information schema has a tables view and in that you have a last altered column, will that work with you? It will not give you the last accessed table but will give the last altered table. Other than this, there are no easy way to get this information from snowflake at this time. I also needed this feature, I think we should request for this feature.

select table_schema,
       table_name,
       last_altered
from information_schema.tables
where table_type = 'BASE TABLE'
      and last_altered < dateadd( 'DAY', -10, current_timestamp() ) 
order by table_schema,
         table_name;

answered Sep 06 '20 at 15:01

Rajib Deb

1,496
11
30

1

INFORMATION_SCHEMA.QUERY_HISTORY has a column called QUERY_TEXT and, in theory, it would be possible to parse this text to extract the names of the tables being queried. However, this would definitely be a non-trivial exercise and unlikely to be 100% successful unless you could reliably identify every SQL statement pattern that could include a reference to a table e.g. different join syntax, sub-queries, etc. etc. – NickW Sep 07 '20 at 10:45
I agree with rajib, but what i dont agree is why some body voted the answer down. although it does not answer my query but at least it was a try and it might help other user in some way.. I will vote the the answer up – danD Sep 14 '20 at 01:45

Snowflake show tables not accessed in last 20 days

2 Answers2