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