Search code examples
cachingsnowflake-cloud-data-platformcommon-table-expressionwith-clause

Snowflake queries with CTE seems not to cache results

When I execute a query containing a CTE (common table expression defined by WITH clause) in Snowflake, the result is not cached.

The question now is: is this how Snowflake works-as-designed, or do I need to consider something to force a result caching?

Solution

  • Snowflake does use the result set cache for CTEs. You can confirm that by running this simple one twice. It should show in the history table that the second one did not use a warehouse to run. Drilling down into the query profile should show the second one's execution plan is a single node, query result reuse.

    with 
my_cte(L_ORDERKEY) as
(select L_ORDERKEY from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."LINEITEM")
select * from MY_CTE limit 10000;

    There are certain conditions that make Snowflake not use the result set cache. One of the more common ones is use of a function that can produce different results on multiple runs. For example, if a query includes current_timestamp(), that's going to change each time it runs.

    Here is a complete list of the criteria that all must be met in order to use the result set cache. Even then, there's a note that meeting all of those criteria does not guarantee use of the result set cache.

    https://docs.snowflake.com/en/user-guide/querying-persisted-results.html#retrieval-optimization