Today I was presenting Snowflake to some smart people, there I got the question: when you run a query twice, and the second run comes from cache, but after that a record is inserted in one of the tables ….. what happens with the next query: will it use the result cache or not.
My answer: I have to figure this out. I can guess what will happen, let’s run a test to see how Snowflake behaves.
First, I created a table with some records:
Then, I ran a query (select *) against the new table, running it twice to make sure it is cached in the result cache of Snowflake:
First run, 16 minutes:
Run again, results from cache:
Then, insert some records
Run the same query again:
To conclude: if, after the first query, the results cache is available for a query, and in the meantime inserts happen in the table, the result cache will not be used. This makes sense, and now we have really tested it as well. The good thing: we do not need to worry about a cache being outdated since Snowflake understands this and ignores the result cache. On the other hand, this makes the usage of the result cache less likely. Perhaps when you have a nightly batch updating tables it will work but with records streaming in during the day, the cache will most like not be used.
This post was called “Snowflake caching part 1”. In part 2 I want to dive into PowerBI filters with regards to the use of the Snowflake cache. In part 3 I will try to summarize some conclusion on how useful this Snowflake caching really is, as it might not be that useful after all. Stay tuned !!