How to set the USE_CACHED_RESULT to false in Snowflake for the whole dbt run

Help
,
1

The problem I’m having

I am using Snowflake as a DWH and during a dbt run I want to set the USE_CACHED_RESULT parameter to false for the whole run.

I already put it under the on-run-start

dbt_project.yml

on-run-start:
   - "alter session set use_cached_result = false"

But when I look into the query history of Snowflake, I see different session ids in the whole run so I guess the parameter is not set for the whole dbt run

I can use it in a pre-hook also so that for every model it unset the parameter before running it but it’s a lot of queries.
Is there a way to set it only once ?

I am wondering how dbt uses the session in Snowflake, is it

  • one session per model
  • one session per statement (but I see several statements with the same session id)

Edit
I tried using the ALTER statement in the pre-hook key.

  • the ALTER statement is executed in the same session as the subsequent model
  • but the query still fetches results from cache anyway

Thanks for your help.

2

Hi philippev,

Were you ever able to figure this out?

I’m trying to do the same thing. I’m trying to optimize/run some of my dbt tests and I don’t want Snowflake to use the cached query result.

Thanks

3

Hi @amiri.mccain,

I have no final answer but the parameters you can leverage are:

  • USE_CACHED_RESULTS in the pre-hook at the model level (not in the on-run-startat run level) as dbt creates one snowflake session per model.
  • Resuming a Warehouse or creating a new one for each test. The WH seems to have a clean cache when suspended (see this).

You will inevitably have small amout of cache that comes from local disk cache (on the warehouse) during the run. Even if you cold start a warehouse (meaning the local cache is empty) after launching your dbt run, the downstream models will benefit a few cached data from upstream ones. At least that’s what I experienced and saw in the query_history table, I never get 0% cached for every models.

That’s not really a problem, the most important thing is to start every run without any cache (resume a WH or create a new one) so you start the different runs on the same basis.

To better understand how cache works in SF, have a look at this nice article (if you haven’t already). Getting old but the principles remain.

1 Like