In Snowflake, there are several commands you can use to retrieve metadata from the Snowflake environment without using a virtual warehouse. The following are all examples that fit this approach:

• SHOW – List all objects of a certain type within the account or within a given database/schema
• DESCRIBE –  Retrieve metadata information about a specific object
• LIST – List files within a Snowflake stage

Since these commands do not fit the general format of SELECT … FROM …  it is harder to store the results somewhere. You cannot simply use INSERT INTO … SELECT … FROM …

Fortunately, there is a way around this. Actually, this blog includes two ways, since the first may not cover your requirements sufficiently. For our simple demonstration, we will leverage the SHOW USERS command.

The Quick Way

The fastest way to store results from one of these commands in a table is to combine the RESULT_SCAN() function with the LAST_QUERY_ID( ) function. This can be achieved using the following steps.

Step 1 – Execute Your SQL Command

Execute your SQL command to retrieve the initial results. You don’t need to worry about actually reading these results, so you can execute this via an integration tool and simply not look at the result set. In other words, don’t worry about being able to visibly see the results of this query:

SHOW USERS;

Step 2 – Create a New Table Containing the Result Set of Your Last Query

As mentioned above, we combine the RESULT_SCAN( ) function with the LAST_QUERY_ID( ) function to retrieve the result set from the last query in a standard SELECT … FROM … format, thus creating a new table that contains our results:

CREATE TABLE MY_TABLE
AS
SELECT *
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
;

Summary

So that’s it. In only two steps, we can insert the results of a SHOW/DESCRIBE/LIST command into a table in Snowflake. Depending on your use case, you may find this is all you need. As long as the second step is executed directly after the first and you are running in the same Snowflake session, this will pick up the results of that initial command and drop them into the desired table.

The Safer and More Methodical Approach

There are several flaws with the quick method I have outlined above. Executing any other query between step 1 and step 2 will inevitably result in an undesired output from the LAST_QUERY_ID( ) function and break the process. If you are running a series of scripts concurrently that each leverage the same session (as is often the case when your queries are compiled and executed by third-party orchestration tools) then you may find that your LAST_QUERY_ID( ) function points at a query from another thread in the session. You may also find the quick method fails if your session is interrupted.

To mitigate these issues, a more methodical approach can be configured that can maintain idempotency and improve reliability.

Step 1 – Execute Your SQL Command

As before, execute your SQL command to retrieve the initial results. You still don’t need to worry about actually reading these results, so you can execute this via an integration tool and not look at the result set. You don’t have to worry about being able to visibly see the results of this query:

SHOW USERS;

Step 2 – Store the Query ID of Your Command in a Variable

Use a SQL query, such as the following, to safely retrieve and store the query ID from the execution of your SQL command:

SET query_id = (
  SELECT QUERY_ID
  FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
  WHERE QUERY_TEXT = 'SHOW USERS;'
  ORDER BY START_TIME DESC
  LIMIT 1
)

Note that this example uses the QUERY_HISTORY( ) view; however, you may prefer to use one of the alternatives for your specific use case.

Step 3 – Create a New Table Containing the Result Set of Your Specific Query

Now that we have the query ID for our query, we can combine it with the RESULT_SCAN( ) function as before to retrieve the result set in a standard SELECT … FROM … format and create a new table that contains our results:

CREATE TABLE MY_TABLE
AS
SELECT *
FROM TABLE(RESULT_SCAN($query_id)
;

Summary

It may have taken an additional step, but our process is now more robust whilst still being succinct. We can now insert the results of a SHOW/DESCRIBE/LIST command into a table in Snowflake, easily ensuring that we retrieve the correct result set by specifying the query text that relates to our query ID.

The main beauty of this methodical approach is that it can be integrated into third-party orchestration tools that run multiple flows concurrently, without risking overlap or collision. If you need to improve the specificity of step 2, you can add additional entries to the WHERE clause, such as the session’s database name, schema name, query type, warehouse, start time or even a specific query tag!