CODEWITHSUNDEEP
snowflake-cloud-data-platform
Ashok
Ashok

Reputation: 59

Snowflake - Views Usage by external access

I have set of views in the two schema under one database , i wanted to know which views are getting most used by external access like (Power BI , BO..) . Is there any way in snowflake to identify that?

Upvotes: 0

Views: 72

Answers (1)

Mansoor Rashed
Mansoor Rashed

Reputation: 41

Everytime an external tool accesses a Snowflake object (Views in your case), this is captured in Query History in Snowflake.

To compare which Views are most used, you can compare the number of times each of those Views is accessed.

Following SQL query accesses the query history, and filters by View name, and gives the number of times a View was accessed, in a given timeframe:

select count(1) as "ACCESS_COUNT" from snowflake.account_usage.query_history where start_time between '<yyyy-mm-dd hh:mm:ss>' and '<yyyy-mm-dd hh:mm:ss>' and database_name = '<db_name>' and query_text ilike '%<view_name>%' ;

Please note that the result will also include the count a View was accessed by any other users as well (and not just by external tools)

To refine your search, you can add more filters. Here's the list of columns in query_history View.

Upvotes: 1

Related Questions