CODEWITHSUNDEEP
snowflake-cloud-data-platform
Pankaj
Pankaj

Reputation: 2746

Snowflake query result processing

Can someone please help to understand from which layer in snowflake data is being fetched in this below plan? I understand snowflake uses either of 3 (besides results from metadata for queries like select count(*)) - result cache, warehouse cache or disk IO. In the below plan - its not from result cache ( as for the plan would say 'query result reuse'), its not showing any remote disk I/O and also the cache usage is 0%. So its not very clear how data is being processed here. Any thoughts or pointers will be helpful. enter image description here

enter image description here

Upvotes: 1

Views: 1018

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

The data is read from the storage layer. I will assume AWS, thus from the S3 there you table is stored. There are three primary reasons for a remote read:

  • It is the first time this warehouse has used this data. This is the same thing that happens if you stop/start the warehouse.
  • The data has changed (which can be anything from 0% - 100% change of partitions), given in your example there is only one partition, any insertion happening in the back ground will cause 100% cache invalidation.
  • The data was flushed from the local caches by more active data, if you read this table once every 30 minutes, but between then read GB of other tables, like all caches low usage data gets dropped.

The result cache can be used, but it also can be turned off for a session, but then local disk cache still happens. And you WHERE 20 = 20 in theory might cache bust the result cache, but as it's a meaningless statement it might not. But given your results it seems, at this point of time it's enough to trick the result cache. Which implies if you want to not avoid the result cache, stop changing the number, and it you want to avoid, this seems to work.

I see you have highlighted the two spilling options, those are when working state data is too large for memory, and too large for local disk so are sent to remote (s3). The former is a sign your warehouse is undersized, and both are a hint that something in your query is rather bloated. Now maybe that is what you want/needed, but it slows things down very much. Now to know if there is perhaps "another way" if in the profile plan there is some step that goes 100M rows -> 100GB rows -> 42 rows this implies a giant mess was made, and then some filter smashed the heck out of nearly all of it, which implies the work could be done different, to avoid that large explosion/filtering.

Upvotes: 1

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

The picture says that 0.44MB were scanned.

The picture says that 0% of those 0.44MB came from the local cache.

Hence 0.44MB were read from the main storage layer.

Upvotes: 1

Related Questions