In below Caching flow, Query Result and Warehouse aka Local Cache are empty:
If we query the COUNT(*) on the table then metadata cache is being used.
If execute SELECT * then query fetch data from the Remote storage layer.
In above picture ,User query the table, which fetches data from Remote layer and cache it at VW layer and results are stored at Query Result Layer.
In below diagram, If we execute query second time then results would be available from Result Cache layer. No Remote Cache or Local cache would come into the relevance.
When the user issues a query to service layer, it goes to the VW which fetches data from the storage layer . After that it automatically caches this data at Virtual warehouse. Later on Warehouse execute the query and returned the result to the Service layer . Remember this result gets stores in the Result Cache.
Users cannot see other’s users result, However Result’s Cache can reuse the result cache of one user to present it to other user.So when user B tries to run the same query so instead of going at Virtual warehouse, output returned from the Service layer cache.This helps the customer to reduce the processing cost.
To understand more on Hot, Cold and Warm Cache, please click here.