Access History

Using Access History in Snowflake.Account_Usage views

Find queries that used a specific column or table.

Ever wondered if anyone is using a specific table or column in a table. Use this code to look through the ACCESS_HISTORY view in Snowflake to find out.

--This CTE is where you would specify what table and columns you want to search for in order to grab the IDs and look them up in Access History
with object_ids as ( 
select
    column_id, table_id
from
    snowflake.account_usage.columns
where
    table_catalog = '<enter DB name>'   --Specify your DB name here
    and TABLE_SCHEMA = '<enter Schema name>'  --Specify the schema where your table is
    and table_name = '<enter table name>'    --Specify the Table name you are searching for
    and column_name = '<enter column name>'  --Specify the column you are searching for
    and deleted is NULL --only look for the column ID that is currently active.
), 
--This CTE is flattening out the Access history table down to the column level, so you get a row for each column accessed in base_objects_accessed
ACCESS_HISTORY as ( 
select
    query_id,
    query_start_time,
    base_objects.value:objectDomain::string as object_type, --when you flatten a variant you get a bunch of columns, one of which is "value" so this is looking at the "value" column and returning objectDomain cast as string.
    base_objects.value:objectName::string as table_name, -- Note with semi-structured queries case sensitivity applies ie(objectName != ObjectNAME)
    base_objects.value:objectId::int as table_id,
    columns.value:columnId::int as column_id,
    columns.value:columnName::string as column_name
from
    SNOWFLAKE.ACCOUNT_USAGE.access_history,
    lateral flatten(base_objects_accessed) base_objects, --first flatten strips out the base array from the base_objects_accessed variant column
    lateral flatten(base_objects.value:columns) columns  --second flatten pulls out each column that was accessed by the query into it's own row.
where
    query_start_time >= current_date()  --This table will be pretty large.. so limmit the date range you are looking for
    and object_type = 'Table'            --Filter to only looking for queries accessing Tables
) 
select --This query will give you a list of query_ids that are accessing the column/s specified in your object_ID CTE
    query_id, query_start_time, object_type, ah.table_id, table_name, ah.column_id, column_name
from ACCESS_HISTORY ah
    join OBJECT_IDS oi on ah.table_id = oi.table_id and ah.column_id = oi.column_id; --join your flattened access history table to your filtered table/column list