Snowflake: How to flatten the ACCESS_HISTORY View

Photo by Saad Salim

Snowflake now provides an object-level usage tracking for Snowflake Enterprise Edition and up. This will enable us to identify how often our objects are being used and for what purposes.

This information can be obtained through the ACCESS_HISTORY View under the ACCOUNT_USAGE Schema, however this view stores the object usage information in JSON array format which will need to be post-processed in order to be more readable and usable.

Below is an example:

SELECT *
FROM snowflake.account_usage.access_history
WHERE query_id = '012s1bc4-1234a-0000-sample5678a';

Output:

DIRECT_OBJECTS_ACCESSED and BASE_OBJECTS_ACCESSED contains the objects that were involved in the query with a subtle difference.

Although it’s great to have these information, the format however prohibits us from joining this data with another object data to gain more insight about how the objects was used by the query (e.g. which warehouse this query was executed? How long did it take to execute? Was there an error encountered? Who executed it? etc.)

Flattening the…