Snowflake – How to fetch history data from Snowflake without using UI?

The Snowflake UI’s History tab is a great tool for basic monitoring and viewing your query history. However, if you’re looking for more detailed information, you may want to consider querying the Query_History view directly or using Snowflake‘s dedicated Information Schema table functions.

These functions, known as QUERY_HISTORY_BY_*, allow you to retrieve information based on specific dimensions such as time range, session, user, and warehouse. For example, if you want to view the query history for a specific session, you can use the QUERY_HISTORY_BY_SESSION function. If you’re interested in viewing the query history for a specific user, you can use the QUERY_HISTORY_BY_USER function. And if you’re looking for information related to a specific warehouse, you can use the QUERY_HISTORY_BY_WAREHOUSE function.

Overall, Snowflake’s Information Schema table functions provide an additional way for you to access detailed information about your query history. By using these functions, you can easily retrieve the information you need without having to query the Query_History view directly.

e.g.,

  • if we are only interested in getting Query_Text, Error_Code, Error_Description
SELECT QUERY_TEXT, ERROR_MESSAGE,ERROR_CODE
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION(RESULT_LIMIT => 10))
  • if we are only interested in getting Session Details by Session ID
SELECT *
FROM TABLE(
INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION (
        session_id => (SELECT current_session();)
       ,result_limit => 1000
   )
)
ORDER BY start_time DESC;

Likewise, based on your needs, you can create custom queries by joining multiple views or querying other views. Snowflake’s flexible data architecture allows you to easily access the information you need to effectively monitor and manage your data.

 

If you are unsure how to compare your data in Snowflake or would like to speak with an expert to learn more about them, Anyon ConsultingBI group can help! Our database experts and consultants that can answer any questions on customize dashboards, help with your database implementation, optimize your database platform, and much more. Contact us today to learn more about our  Custom Database Development.

Scroll to top