CODEWITHSUNDEEP
snowflake-cloud-data-platform
Shakeel Ahammed Shaik
Shakeel Ahammed Shaik

Reputation: 11

How do I get the last accessed time stamp for the table in the snowflake?

I want to get the last accessed timestamp for a table in the snowflake

Upvotes: 1

Views: 1478

Answers (3)

Greg Pavlik
Greg Pavlik

Reputation: 11046

This is an older question, but I am answering it since Snowflake has added a new feature to track last access spanning 1 year. Since this information wasn't tracked from the creation of older objects, you will only see access history since the tracking started.

There is now a view in "SNOWFLAKE"."ACCOUNT_USAGE"."ACCESS_HISTORY". You can see how to query it by flattening the base_objects_accessed array:

select * from "SNOWFLAKE"."ACCOUNT_USAGE"."ACCESS_HISTORY",
 LATERAL FLATTEN(base_objects_accessed) limit 100;

Upvotes: 1

H Roy
H Roy

Reputation: 635

Hope this approach will help

Important Note: Not a very suitable approach as a user must have AccountAdmin access, to run a query on snowflake.account_usage schema and query will have a bit of latency as it is part of account_usage schema. It will also incur WH cost if the data size is too big

select * from "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY" 
where 
    query_text like '%STORE_SALES%' and 
    query_type = 'SELECT'
    order by START_TIME DESC
    LIMIT 1

enter image description here

Alternatively, if the requirement is limited to the last 14 days history, use the history tab which costs nothing using simple filter clauses.

enter image description here

Upvotes: 0

David Garrison
David Garrison

Reputation: 2880

Not always ideal, but a quick way to find this for one-off questions is to use QUERY_HISTORY

SELECT START_TIME, * 
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) 
WHERE QUERY_TEXT LIKE '%MYSCHEMA.MYTABLE%';

Update: Query to specifically get just the most recent query time. Have to filter out the QUERY_HISTORY queries themselves. This is not especailly fast, and does require that the role that's running this has access to all the relevant history.

SELECT MAX(START_TIME)
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) 
WHERE QUERY_TEXT ILIKE '%CONFIG.PIPELINE_LOG%'
AND NOT QUERY_TEXT ILIKE '%INFORMATION_SCHEMA.QUERY_HISTORY%';

Upvotes: 2

Related Questions