I have several summary tables that get populated in Snowflake via Snowflake tasks. Some of these are likely not in use by dashboards any more that are no longer used by the business team.
Is there a way to audit and see which tables are no longer being queried in Snowflake? I'd like to sort these by least queried to begin a cleanup process on which tasks are no longer used.
I tried this query but it returns 0 rows even though I'm running as the AccountAdmin and I'm in the relevant schema I'm curious about.
select obj.value:objectName::string objName
, col.value:columnName::string colName
, count(*) uses
, min(query_start_time) since
, max(query_start_time) until
from snowflake.account_usage.access_history
, table(flatten(direct_objects_accessed)) obj
, table(flatten(obj.value:columns)) col
group by 1, 2
order by uses desc
Even though the last query doesn't filter, I verified that I also get nothing just by selecting all from access_history itself:
select *
from snowflake.account_usage.access_history
This probably deals with me incorrectly implementing access_history but I can't see in the documentation, forums, or videos how I'm using this wrong.
I get nothing via:
select *
from snowflake.account_usage.access_history
As account admin, but I am on a Standard account, the doc's for access-history note:
Enterprise Edition Feature
Access History requires Enterprise Edition (or higher). To inquire about upgrading, please contact Snowflake Support.