By: Koen Verbeeck
Overview
Every time a query is executed, information about its execution is logged into the metadata layer of Snowflake. You can query this data, either through a query – if you have the appropriate permissions – or through the user interface.
History in the User Interface
When you’ve executed at least one query in a worksheet, you can click on Open History on the right-side of the window.
This will open a new pane, where you can view the history of the queries you executed in the worksheet.
You get some summarized information, such as the duration, the number of rows returned, the amount of bytes scanned and if the query succeeded or not. One of the more important columns is the query ID. When you click on this ID, it will take you to a new window with more detailed information of the query. It’s important to notice this also possible for queries that are still executing.
If the query has finished successfully and a result set is returned, you can view it in the results pane. Ideal during debugging and you are wondering what your previous query returned before you made that change. If you click on Profile at the top, you are taken to the query execution plan. Here’s an example of a bit more complicated query:
If you click on one of the nodes, you get a bit more detail.
Unfortunately, the query plan is nowhere as detailed as in SQL Server.
If you’re interested in more than your own queries, you can go to the history pane in the browser.
There you can view all queries executed in the Snowflake account, up to a certain period in time.
There are filters available for searching through the available history.
If you go to the query history detail of a query executed by another user, you cannot see the result set. This would otherwise be a serious security risk. You can however see the actual SQL statement that was executed.
Query Result Cache
An interesting feature of Snowflake is the result set cache. This means as long as an executed query is unchanged and the underlying tables aren’t modified, each subsequent execution of the same query will just pull the result set from cache. This is ideal for speeding up reports that execute the same queries over and over again. How can you check if a query has retrieved the results from cache? If you go to the query history detail, you’ll see 0 as the amount of rows returned, even though an actual result set is returned.
Querying History
If you want more flexibility than the browser, or go further back in time, you can query the metadata database for the query history. If you have the correct permissions of course (by default ACCOUNTADMIN is needed). An example query can be the following:
SELECT * FROM "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY" WHERE 1 = 1 AND query_text ILIKE '%TIME%' AND start_time >= '2020-01-01' AND database_name = 'TEST' AND USER_NAME = 'KOEN' AND EXECUTION_STATUS <> 'SUCCESS' ORDER BY start_time DESC;
Additional Information
- More information about the ACCOUNT_USAGE schema in the Snowflake database. You can query all sorts of information. You can for example create a report on the longest running query and how much compute was used (and thus how much money those queries cost).
