I’m doing a little series on some of the nice features/capabilities in Snowflake (the cloud data warehouse). In each part, I’ll highlight something that I think it’s interesting enough to share. It might be some SQL function that I’d really like to be in SQL Server, it might be something else.
This post builds upon part 6 of the series, which dealt with query history. There it is explained how Snowflake caches the query results. You can find a query in the history and take a look at what was returned. Using the RESULT_SCAN table function, you can do this with SQL. Let’s take a look at an example.
We first execute a query, retrieve the query ID from history and use that to get the result set:
If retrieving the query ID is too much work, or you would like a more automated solution, you can use the LAST_QUERY_ID function.
You can specify a positional parameter if you want to retrieve the query ID of a query which is not the last one. In the following example, we retrieve the result of the first query, instead of the last one:
There are many useful scenarios for RESULT_SCAN:
- if you just ran a very long query and you forgot a part of the WHERE clause. Instead of re-running the (adapted) query, you just fetch the result set and filter that one. Much faster and ideal for debugging.
- You can retrieve the output of a stored procedure.
- You can also get the output of the SHOW and DESCRIBE commands.
Keep in mind though that Snowflake only keeps data in the result set cache for 24 hours. Starting from February 2020, it’s also possible you are billed if you query the result set cache too often. More information can be found here.