Using Persisted Query Results

When a query is executed, the result is persisted (i.e. cached) for a period of time (currently 24 hours). At the end of the time period, the result is purged from the system.

Persisted query results can be used for either of the following purposes:

  • To avoid re-generating results when nothing has changed (i.e. “retrieval optimization”).
  • To post-process the results (e.g. layering a new query on top of the results already calculated).

In this Topic:

Reusing Query Results to Optimize Retrieval

If a user repeats a query that has already been run, and the data in the table(s) hasn’t changed since the last time that the query was run, then the result of the query is the same. Instead of running the query again, Snowflake can simply return the same result that it returned previously. This substantially reduces query time because Snowflake bypasses query execution and, instead, retrieves the result directly from the cache.

Typically, query results can be re-used if all of the following conditions are met:

  • The user executing the query has the necessary access privileges for all the tables used in the query.
  • The new query syntactically matches the previously-executed query.
  • The table data contributing to the query result has not changed.
  • The persisted result for the previous query is still available.
  • Any configuration options that affect how the result was produced have not changed.
  • The query does not include functions that must be evaluated at execution (e.g. /sql-reference/function/current_timestamp).
  • The table’s micro-partitions have not changed (e.g. been re-clustered or consolidated) due to changes to other data in the table.

Note

Meeting all these conditions does not guarantee that query results will be re-used.

Result reuse is controlled by the USE_CACHED_RESULT session parameter. By default, the parameter is enabled, but can be overridden at the account, user, and session level.

Note

Each time the persisted result for a query is reused, Snowflake resets the 24-hour retention period for the result, up to a maximum of 31 days from the date and time that the query was first executed. After 31 days, the result is purged and the next time the query is submitted, a new result is generated and persisted.

Post-processing Query Results

In some cases, you might want to perform further processing on the result of a query that you’ve already run. For example:

  • You are developing a complex query step-by-step and you want to add a new layer on top of the previous query and run the new query without recalculating the partial results from scratch.
  • The previous query was a SHOW <objects> or DESCRIBE <object> statement, which returns results in a form that are not easy to reuse.

Post-processing can be performed using the RESULT_SCAN table function. The function returns the results of the previous query as a “table” and a new query can then be run on the tablular data.

Examples

Process the result of a SHOW TABLES command and extract the following columns and rows from the result:

  • schema_name, table_name, and rows columns.
  • Rows for tables that are empty.
SHOW TABLES;

+-----+-------------------------------+-------------+-------+-------+------+
| Row |           created_on          | name        | ...   | ...   | rows |
+=====+===============================+=============+=======+=======+======+
|  1  | 2018-07-02 09:43:49.971 -0700 | employees   | ...   | ...   | 2405 |
+-----+-------------------------------+-------------+-------+-------+------+
|  2  | 2018-07-02 09:43:52.483 -0700 | dependents  | ...   | ...   | 5280 |
+-----+-------------------------------+-------------+-------+-------+------+
|  3  | 2018-07-03 11:43:52.483 -0700 | injuries    | ...   | ...   |    0 |
+-----+-------------------------------+-------------+-------+-------+------+
|  4  | 2018-07-03 11:43:52.483 -0700 | claims      | ...   | ...   |    0 |
+-----+-------------------------------+-------------+-------+-------+------+
| ...                                                                      |
| ...                                                                      |
+-----+-------------------------------+-------------+-------+-------+------+

-- Show the tables that are empty.
SELECT  "schema_name", "name" as "table_name", "rows"
    FROM table(RESULT_SCAN(LAST_QUERY_ID()))
    WHERE "rows" = 0;

+-----+-------------+-------------+------+
| Row | schema_name | name        | rows |
+=====+=============+=============+======+
|  1  |  PUBLIC     | injuries    |    0 |
+-----+-------------+-------------+------+
|  2  |  PUBLIC     | claims      |    0 |
+-----+-------------+-------------+------+
| ...                                    |
| ...                                    |
+-----+-------------+-------------+------+

Additional examples are provided in RESULT_SCAN.