We are using Snowflake and trying to evaluate some queries. What I have done is that I take random samples (163) and ran Select queries on them.
select * from where sessionkey=<> and sessionstarttime=<>
Where session key and sessonstarttime are numerical values.
So, When I used new warehouse (or suspended) one, My assumption was cache should not be used but I see more than 95% cache has been used. All samples are distinct and not the same. I am unable to understand this behavior. I see cache utilization from 0 to 95%.
One thought is like initial queries don't use cache and then once queries start running, they start loading partition in the cache and somehow partition for all these queries is the same. I am not sure, can someone suggest cache behavior?
Also, is there a way we can check the partition used by the query?
Tushar Goel