Snowflake interview questions and answers - Part III

                       Are you preparing for your snowflake interview? Are you looking for Snowflake interview questions and answers? If so, then you reached the right place. In this article, we will focus on snowflake caching interview questions and answers. You can visit the previous article on Snowflake interview questions - Part II here.

                                                                                                            

Q.1  Explain caching in Snowflake ? or How caching works in Snowflake?

                        Snowflake provides caching at two levels - one at the cloud services layer and the second one at compute level. When we execute SQL query against Snowflake. the result from the cloud services layer will be fetched. If cloud services layer cache is disabled then compute layer level cache will be used.

                        The important thing to note here is that cache scenarios work only when the time of execution falls within AUTO_SUSPEND specified time.

Q.2  How does cache work if the underlying table gets updated?

                         As we know when we submit a SQL query to a virtual warehouse in Snowflake, it gets executed against database storage and results are returned back to the cloud services layer. Apart from it, the data gets cached at compute layer and cloud services layer when we execute the same query again the result will be returned using cache.

                        Now let's assume that we either updated the underlying table by deleting a record or updating a record. After updating the table, if we run the same query again the cache will not be used. Instead, the virtual warehouse will be connected to database storage to fetch the data.

Q.3  Is it a good idea to run a select query after updating the table and after deleting the record separately?

                        When we update the table by deleting or updating a record then such change is made in the storage layer. that means, if we execute a query against Snowflake, it needs to connect the storage layer to fetch the latest data. Whenever we connect database storage through compute layer it incurs expenses. so it makes sense to perform all your DML operations such as delete, update as a single unit of work, and then query the results instead of making separate select query calls. This will be helpful to achieve cost-effectiveness.

Q. 4  Does the user's cache share across multiple users?

                         Assume user 1 executes a SQL query against snowflake using virtual warehouse VM1 and after successful execution, if the same SQL query is executed by user 2 with vm2, in such case will cache be used for user2 query execution? the answer is YES As long users use the same or different virtual warehouse and time of execution are under Auto_Suspend timeframe then cache from either result cache or local disk cache will be used.

Q. 5  How long the query results will be cached? 

                         The query results are retained for 24 hrs from last the time of execution. Result cache layer holds queries for 24 hrs. 

Q.6  How much snowflake charges for storing cache? 

                         Snowflake does not charge for storing cache.

Q. 7  Explain more about Remote disk?

                         The remote disk is nothing but database storage which can be achieved using cloud providers like AWS S3. The remote disk is nothing but the blob storage area

Q. 8  How to disable local disk cache 

                     We cannot disable the local disk cache.

Q. 9  How to disable Result cache 

                      In order to disable Result cache use statement below -

                    ALTER SESSION SET USE_CACHED_RESULT=FALSE ;

Q. 10 Can we use the Result cache even if we suspend the warehouse?

                        The answer is YES. We can use the Result cache when the virtual warehouse is in a suspended or Inactive state.

                     Learn more about snowflakes here...