When most people hear the word “snowflake,” they think of cold days and long nights. But some get excited, and anyone in the data business knows exactly why. Snowflake is an analytic Data Cloud that is much faster, easier to use, and more versatile than its peers. It’s a SaaS platform that makes use of a brand-new SQL database engine with a unique cloud-specific architecture.
Snowflake’s architecture combines the benefits of shared-disk and shared-nothing architectures. Its architecture consists of three key layers.
Database Storage
Snowflake reorganizes data when it is loaded into its internal optimized, compressed, columnar structure. Snowflake saves the optimized data on the cloud.
Snowflake manages the overall structure and organization, file size, compression, metadata, statistics, and other aspects of data storage. Customers cannot see or access the data objects saved by Snowflake; they can only access them through SQL query operations performed with Snowflake.
Query Processing
The processing layer is responsible for query execution. Virtual warehouses are used by Snowflake to process queries. Each virtual warehouse is an MPP compute cluster made up of numerous compute nodes provided by a cloud provider and allotted by Snowflake.
Each virtual warehouse has its own compute cluster, with no shared resources. As a result, the performance of each virtual warehouse is unaffected.
Cloud Services
A group of services make up this layer, and they help Snowflake coordinate activities. By processing user requests, the cloud services layer connects all of Snowflake’s components.
Tips to Get The Most Out of Snowflake
1. Discover new external data with Data Marketplace – Explore a variety of open and commercial data sets across 16 categories, including public health, weather, location, demographics, SaaS providers, and more to find the data that drives insight.
With direct, secure, and controlled access from our Snowflake account to ready-to-query data sets, you can virtually remove the expenses and effort associated with typical ETL processes.
2. Search Optimization to a table – Adding search optimization to a huge table, one with terabytes or more of data could result in a rapid increase in credit use.
When you enable search optimization for a table, the maintenance service begins constructing the table’s search access paths in the background. If the table is vast, the maintenance service may massively parallelize the job, resulting in higher expenditures in a short period of time.
Get an estimate of these expenditures before you add search optimization to a huge table so you know what you’re in for.
3. Snowflake Query Optimization – Drop unused tables and select Only Required Columns instead of picking all columns from a table or view with a select * from and maximizing cache usage is a straightforward way to enhance overall query performance.
4. Usage Monitoring – Track history, performance, and costs using Account Usage Views.
5. Avoid Using ORDER BY – Sorting can be quite costly. Use an ORDER BY clause solely at the top level of the query if you want the results of the outer query sorted, and avoid using ORDER BY clauses in subqueries unless absolutely required.
6. Auto-Suspend should be enabled – Ensure that all virtual warehouses are set to suspend automatically. When they’re done processing queries, auto-suspend will turn off your virtual warehouses and stop credit consumption.
7. Auto-Resume should be enabled – Ensure that all virtual warehouses are set to resume automatically. Auto-resume must be enabled if you plan to use auto-suspend and specify proper timeout limits; otherwise, users will be unable to query the system.
8. Shortcut for Code Commenting – If you don’t want to manually comment out many lines of code, select it and hit [CTRL]+ [/] to automatically comment it out. To uncomment the code, use the same combination.
9. Efficient Loading Process – Instead of INSERT, use COPY INTO since it takes use of the more efficient bulk loading operations.
10. Decide when to create a Materialized view – Materialized views are intended to boost query performance for workloads that contain a lot of the same queries. However, there are extra costs associated with materialized views. As a result, before you create any materialized views, think about whether the expenses will be compensated by the savings from reusing the results frequently enough.
Learn how Atrium can help you get the most value out of Snowflake.
