Snowflake Cache Saves Cash
Johnny Cash on Snowflake Cache saving Cash
I hear the train a coming
It’s coming round the bend
My second Snowflake query
Flies faster than a wren
I’m stuck in Snowflake Heaven
and time travel keeps rolling back
when I hear that whistleblowing
I know my Cache saves Cash
The Snowflake architecture consists of cloud services, virtual warehouses, and data storage. Cloud Services are the brain of a Snowflake system. Cloud Services is a collection of services that manage virtual warehouses, queries, transactions, and metadata for a virtual warehouse(s). When a user runs a query, Cloud Services takes the query, checks the syntax and security, and then comes up with a plan to retrieve the data. The picture below represents the basic architecture of a Snowflake system.
Life is precious, and so is the life of a single Snowflake query. Below is the life of a query in the exact order of steps. The Cloud Services layer is the first to receive the query and the last to gather the answer set and deliver it to the user.
The picture below explains that each query runs on exactly one Virtual Warehouse. Worker nodes are not shared across Virtual Warehouses, resulting in solid performance isolation for queries. When a new query is submitted, each worker node in the respective VW (or a subset of the nodes if the optimizer detects a small query) spawns a new worker process. Each worker’s process lives only for the duration of its query.
Snowflake has automatic caching for queries run more than once in 24 hours. In addition, Snowflake doesn’t re-run a query but delivers the answer set immediately. You could have an original query that takes five minutes to run because it delivers a huge answer set, but the next time it is run, it only takes a second because of the caching.
The cache will spoil if the underlying data has any inserts, updates, or deletes. However, other users in the same warehouse who run the same query will also take advantage of the Snowflake caching feature. Caching is brilliant because employees in a large company often run the same query, and delivery of complex queries in seconds is a huge Snowflake advantage.
Snowflake has two places it keeps answer sets in cache: on disks in the Cloud Services area and on the Virtual Warehouse.
Below is the life of a query in the exact order of steps if caching on the Cloud Services is available. The Cloud Services layer is the first to receive the query. If the Cloud Services layer has the answer set cached on its internal disks, it delivers the answer set without relying on the Virtual Warehouse or Data Storage.
Below is the life of a query in the exact order of steps if caching is available but not on the Cloud Services layer. The Cloud Services layer is the first to receive the query, and if the Cloud Services layer does not have the answer set cached, it builds a plan for the virtual warehouse. However, if the virtual warehouse has the answer set cached on its internal drives, it delivers the answer set to the Cloud Services layer without relying on Data Storage. No matter which cache delivers the answer set, the results come at lightning speed.
The Snowflake query browser is light years behind the 18 years of development of the Nexus Query Chameleon. And the best part is that Nexus can query all systems in your enterprise and has a Super Join Builder that builds the SQL automatically as the user’s point-and-click. And the most amazing part is that Nexus can easily join Snowflake tables with Excel, Access, and tables and views from all other database platforms. Check out just some of the amazing features of Nexus here.
If you like how Tom Coffing presents and teaches, you will be amazed at his Nexus Server software for migrations to Snowflake. Check out the best way to automate the movement from all databases to Snowflake here.
The Nexus built the Information Migration Highway. The picture below shows all of the systems in which Nexus automatically migrates.
Your company can hire Tom Coffing to teach classes on Snowflake. All classes are customized, but Tom is currently doing a 5-day Snowflake boot camp that teaches the Snowflake architecture, tricks and tips, and every SQL command. Check out the agenda here. https://www.nexusdataserver.com/snowflakeclass.
