Is there a way to know when a table was last accessed in snowflake

If you mean with a select, I don't think there's a way to do it by table, you'd have to monitor the last executed queries to find that information: https://docs.snowflake.net/manuals/sql-reference/functions/query_history.html

Update-wise: SYSTEM$LAST_CHANGE_COMMIT_TIME should give you the info you need.

Understanding Access History

Access History in Snowflake refers to whether the user query reads column data. Snowflake does not record write operations. The user Access History can be found in the Access_History View (in this topic).

Each row in the ACCESS_HISTORY view contains a single record per query and describes the columns the query accessed directly (i.e. the base table) and indirectly (i.e. derived objects, such as views). These records facilitate regulatory compliance auditing and provide insights on popular and frequently accessed tables and columns since there is a direct link between the user (i.e. query operator), the query, the table or view, the column, and the data.

Additional use cases for the ACCESS_HISTORY view include:

• Discovering unused data to determine whether to archive or delete the data.
• Validating data changes to notify users prior to dropping or altering a given table or view.
• Auditing data access to comply with regulatory requirements and data governance initiatives.

ACCESS_HISTORY View

This Account Usage view can be used to query the access history of Snowflake objects (e.g. table, view, column) within the last 365 days (1 year).

Using the object_id value of 32998411400350, determine when the access occurred in the last 30 days:

SELECT query_id
       , query_start_time
FROM access_history
     , lateral flatten(base_objects_accessed) f1
WHERE f1.value:"objectId"::int=32998411400350
AND f1.value:"objectDomain"::string='Table'
AND query_start_time >= dateadd('day', -30, current_timestamp())
;