[Feature]: Using the Snowflake Cache for Cloud Services

[nadirdbit]

Use Cases or Problem Statement

We're finding a lot of "show roles like '' " being repeatedly issued on a daily basis.

Even though this is using Snowflake Cloud Services and not a virtual warehouse, it will assist in utilizing an available cache to fulfill these requests.

Thank you.

Category

category:other

Object type(s)

No response

Proposal

No response

How much impact is this issue causing?

Low

Additional Information

No response

Would you like to implement a fix?

• [ ] Yeah, I'll take it 😎

[sfc-gh-asawicki]

Hey @nadirdbit. Thanks for reaching out to us.

Can you point me to the docs describing the cache services you are referring to that you would like to utilize?

[nadirdbit]

Hi Artur,

Result cache - https://community.snowflake.com/s/article/Caching-in-the-Snowflake-Cloud-Data-Platform

Thank you.

[sfc-gh-asawicki]

Hey @nadirdbit. The article mentions https://docs.snowflake.com/en/user-guide/querying-persisted-results, which I checked before answering you initially because, as far as I understand this doc, we cannot leverage caching:

• we are not running subsequent queries that are the same queries (we have no control over the ordering of operations on the resources in Terraform)
• results may change in the meantime
• we are not running select statements but show queries - they are not mentioned in the doc but maybe they work too, however...
• ... it seems caching is enabled by default in Snowflake, so I can't see how we could use it as an improvement

[nadirdbit]

Hi Artur,

The show commands being run aren't presently utilizing the result cache and we're finding that they are running several hundred times a day and sometimes taking a few seconds. On reviewing the output of percentage_scanned_from_cache from snowflake.account_usage.query_history, we're finding that to be 0 for requisite statement query_id's.

Thank you.

[sfc-gh-asawicki]

Hey @nadirdbit, this does not change what I wrote above. If you have any questions about the cache behavior, please reach out to your Snowflake account manager.

[sfc-gh-asawicki]

Closing due to inactivity, the answer was provided above.