Why is this happening?

You ran the below query. I have a warehouse with auto suspend set at 5 seconds

SELECT * FROM INVENTORY;

The query profile looks like as below. Please see below ‘Percentage scanned from cache’ is 0%

You ran the query again before 5 seconds has elapsed and the query profile looks as below. Look at the ‘Percentage scanned for cache’, it is 75%

You ran the query again after 5 seconds. The query profile looks as below. Look at the ‘Percentage scanned from cache’, it is zero again.

Why is this happening?
A . The second run of the query used data cache to retrieve part of the result since it ran before the
warehouse was suspended
B. The second run of the query used query result cache
C. The third run of the query used query result cache

View Answer

Answer: A

Explanation:

This is a very important concept to understand. There may be many different questions on this concept. Lets, understand what is going on in here

Virtual warehouses are an abstraction on the compute instances of the cloud provider(in case of AWS, it is EC2 instances). Each Virtual warehouse is a cluster of these compute instances(or EC2 in case of AWS). The compute instances has local SSD attached to them. When you ran the query for the first time, the results of the query were retrieved from the remote storage(which is the object store of the cloud provider, S3 in case of AWS), part of the results also got cached in the local SSD storage of the compute instance. So, when we ran the query second time, part of the results got retrieved from the SSD cache also known as Data Cache.

Ok, if that is the case why did not it retrieve from data cache the third time. The third run of the query happened after 5 seconds. The virtual warehouse had a auto suspend setting of 5 seconds. So, since there were no activity for 5 seconds, the warehouse suspended itself. When the warehouse is suspended, it loses the data cache.

Why?