CACHING in Snowflake

Result Cache

• Stores the results of a query (Cloud Services)
• Same queries can use that cache in the future
• Table data has not changed
• Micro-partitions have not changed
• Query doesn't include UDFs or external functions
• Sufficient privileges & results are still available
• Very fast result (persisted query result)
• Avoids re-execution
• Can be disabled by using
• USE_CACHED_RESULT parameter
• If query is not re-used purged after 24 hours
• If query is re-used can be stored up to 31 days

Tip : Result cache is resides in the CLOUD SERVICES layer

Data Cache

• Local SSD cache
• Cannot be shared with other warehouses
• Improve performance of subsequent queries that use the same data
• Purged if warehouse is suspended or resized
• Queries with similar data ⇒ same warehouse
• Size depends on warehouse size

Tip : Data cache is resides in the QUERY PROCESSING layer

Metadata Cache

• Stores statistics and metadata about objects
• Properties for query optimization and processing
• Range of values in micro-partition
• Count rows, count distinct values, max/min value
• Without using virtual warehouse
• DESCRIBE + system-defined functions
• Called as "Metadata store"
• Virtual Private Edition: Dedicated metadata store

Tip : Result cache is resides in the CLOUD SERVICES layer