2
0
ACCESS_HISTORY: Consider the scenario where you need to identify the tables which have been queries most in snowflake instance. This can be useful in the situation where you need to determine which tables are mostly or frequently used in the process. It helps to determine whether tables are being used genuinely or developers are querying them just to get few records for their development. Based on the usage type and record count of the table we can advise the developers accordingly.
Snowflake ACCESS_HISTORY View, used to query the access history of Snowflake objects. An ACCOUNTADMIN role should be assigned to access the Snowflake account usage views. Also please note , ACCESS_HISTORY view returns object used within 365 days or 1 year.
Querying on the ACCESS_HISTORY view returns the output in the from of JSON and we need to FLATTEN the output to extract the relevant information.
As we have noticed ,Above view returns the Table name only and in addition to it if we get the record count of the table, this will help to identify frequently used Bulky table.
INFORMATION_SCHEMA.TABLES view is use to get the Row count for the particular table. So to get the complete information like Most Queried Table name + Record count , we need to join both views to get the complete information.
So as per the below query we are just combining ACCESS_HISTORY + INFORMATION_SCHEMA.TABLES .
As we can see the combination of the above views returns the Mostly Queried Table name with the ROW Count.
