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