2 0
Read Time:1 Minute, 27 Second

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.

Access_History

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.

TABLES

So as per the below query we are just combining ACCESS_HISTORY + INFORMATION_SCHEMA.TABLES .

ACCESS_HIST_TABLES

As we can see the combination of the above views returns the Mostly Queried Table name with the ROW Count.

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

Leave a Reply

Your email address will not be published. Required fields are marked *