Snowflake Tutorial

Snowflake is a multi-cloud data warehousing solution that can run on AWS, Microsoft Azure, and Google Cloud. 

Snowflake was founded in 2012 and helps businesses unify their siloed data, share data securely, and manage analytic workloads.

This comprehensive guide covers Snowflake's features, robust architecture, step-by-step procedure for loading data into Snowflake, and much more.

Table of Contents:

• What is Snowflake?
• Key Features
• Snowflake Architecture
• Snowflake SQL Commands
• Connecting to Snowflake
• Loading Data into Snowflake
• Snowflake Query Optimization
• Real-time Use Cases of Snowflake
• Benefits of Snowflake
• Snowflake Certification Exams
• Frequently Asked Questions

What is Snowflake?

Snowflake is a Data Warehouse-as-a-Service (DWaaS). It has an exceptional hybrid architecture and data-sharing capabilities.

Snowflake can automatically scale up and down to achieve optimal performance. So you can store your data and scale your computing seamlessly with Snowflake.

For example, you may create a large Snowflake warehouse to make complicated transitions with heavy data loads. Once the need is over, you can scale down the warehouse, significantly reducing costs.

Key Features of Snowflake

Let’s understand Snowflake's features here.

• Cloud Agnostic : Snowflake runs on multiple cloud providers with the same performance. You can easily integrate Snowflake into your existing cloud infrastructure.
• Scalability: Snowflake comes with auto-scaling and auto-suspend capabilities to minimise human intervention. So you can scale up or down virtual warehouses. Snowflake supports instant data warehouse scaling to handle concurrency bottlenecks during heavy traffic. It scales data warehouses without the need to redistribute data, which increases efficiency.
• Separation of Compute and Storage: Traditional warehouse systems are inefficient at managing concurrency issues. Snowflake overcomes this drawback through its multi-cluster design, simplifying synchronization. Snowflake separates compute resources from storage so users can scale storage independently without worrying about execution.
• Cloning: Cloning or zero-copy cloning is one of Snowflake's core capabilities. It helps create a copy of a table, schema, or database. Clones don't use extra memory until you change the copy.
• AI Power: Snowflake offers two key Artificial Intelligence (AI) tools: Snowflake Cortex and Snowflake ML. Snowflake Cortex uses LLMs to learn unstructured data and provide valuable insights to users. Snowflake ML helps detect the underlying patterns in structured data. Leveraging Snowflake’s AI tools helps level up security, data privacy, and control over your data.
• Key Tools: Snowflake offers many powerful tools, such as Snowsight and SnowSQL. Snowsight can monitor resources and system usage, while SnowSQL is a Python-based command-line client.
• Security: Snowflake helps implement network policies effectively. For example, it restricts the IP addresses that you want to prevent from logging into your account. Snowflake supports various authentication techniques, such as two-factor identification and federated authentication for Single Sign-On (SSO). We hope this section of the Snowflake tutorial has provided you with a comprehensive understanding of Snowflake features.

Snowflake Architecture

Now, we will explore the Snowflake architecture in detail.

• Snowflake architecture is an innovative, hybrid form of shared-disk and shared-nothing database architectures.
• Shared disk architecture is well-suited for simplified data management, whereas shared-nothing architecture is the best fit for performance and scaling.

The Snowflake architecture consists of three key layers as follows:

• Database Storage
• Query Processing
• Cloud Services

Let’s discuss them one by one below.

Storage Layer

You can store any structured or semi-structured data in Snowflake. All the tasks related to data are handled through SQL query operations.

This layer manages all aspects of stored data, such as file size, compression, structure, metadata, and many others.

For example, the data stored in the cloud is transformed into a compressed, optimized columnar format in this layer.

Query Processing

In the Snowflake architecture, there is a clear separation between the query processing and disk storage layers. The query processing layer executes SQL queries on the data stored in the storage layer.

We’ll see how it works in the following.

• Snowflake processes the queries using virtual warehouses. 
• Each virtual warehouse can obtain data from the storage layer and run it separately. 
• It has an MPP compute cluster comprising many compute nodes.
• It doesn't share compute resources with others, so each can work independently. 
• The performance of a virtual warehouse doesn't affect the performance of others.

Cloud Services Layer

The cloud services layer eliminates the need for manual data warehousing and tuning. Services in this layer include:

• Authentication
• Session management
• SQL compilation
• Metadata Management
• Infrastructure Management

Furthermore, all three layers of Snowflake are self-scaling. The key advantage of the Snowflake architecture is that each layer can be scaled independently.

Want to enhance your skills to become a master in Snowflake Certification, Enroll in our Snowflake Certification Course

Snowflake SQL Commands

We will learn some crucial Snowflake SQL commands, such as DML and DDL.

DDL Commands

Data Definition Language(DDL) commands can create, manipulate and modify objects in Snowflake. The objects can be users, virtual warehouses, tables, schemas, functions, etc.         

DML Commands

You can use Data Manipulation Language (DML) commands to insert, delete, update, and merge data in Snowflake tables. You can also use these commands to load bulk data into Snowflake.

Connecting to Snowflake

In this section, you'll discover how Snowflake connects with external services.

You can employ the following tools to connect Snowflake with external services:

• Web-based User Interface 
• Command-line Clients 
• ODBC and JDBC drivers 
• Native Connectors 
• Third-party Connectors 

Data Loading in Snowflake

In this section, we’ll review the quick guide to using SQL commands for bulk loading in Snowflake.

Bulk loading of data using SQL commands can be performed in two phases:

• Staging files
• Loading data

Let’s examine how it works!

Staging files

Snowflake allows you to stage files on internal locations called stages. Internal stages provide secure storage of data files without the need for any external locations.

In staging files, data files are uploaded to a location where Snowflake can access them. And then, you will load your data from stage files into tables.

Loading data 

A virtual warehouse is needed to load data into Snowflake. The warehouse extracts data from each file and inserts it as rows in the table.

We'll see how a SnowSQL client loads CSV files from a local machine into a table called ‘contacts’ in the demo database demo_db.

You will store the files before loading them into a named internal stage. The following step-by-step procedure will help you.

1. Use the demo_db database.
   

   Last login: Sat Sep 19 14:20:05 on ttys011
   Superuser-MacBook-Pro: Documents xyzdata$ snowsql -a bulk_data_load
   User: peter
   Password:
   * SnowSQL * V1.1.65
   Type SQL statements or !help
   * SnowSQL * V1.1.65
   Type SQL statements or !help
   johndoe#(no warehouse)@(no database).(no schema)>USE DATABASE demo_db;
   +----------------------------------------------------+
   | status                                             |
   |----------------------------------------------------|
   | Statement executed successfully.                   |
   +----------------------------------------------------+
   1 Row(s) produced. Time Elapsed: 0.219s

2. Create tables using the following SQL commands.
   

   peter#(no warehouse)@(DEMO_DB.PUBLIC)>CREATE OR REPLACE TABLE        contacts 
   (     
   id NUMBER   (38, 0)  
   first_name STRING,  
   last_name STRING,  
   company STRING,  
   email STRING,  
   workphone STRING,  
   cellphone STRING,  
   streetaddress STRING,  
   city STRING,  
   postalcode NUMBER   (38, 0)
   );
   +----------------------------------------------------+
   | status                                             |
   |----------------------------------------------------|
   | Table CONTACTS successfully created.               |
   +----------------------------------------------------+
   1 Row(s) produced. Time Elapsed: 0.335s

3. Populate the tables with records.
4. Next, create an internal stage.
   

   peter#(no warehouse)@(DEMO_DB.PUBLIC)>CREATE STAGE csvfiles;
           
   +----------------------------------------------------+
   | status                                             |
   |----------------------------------------------------|
   | Stage area CSVFILES successfully created.          |
   +----------------------------------------------------+
   1 Row(s) produced. Time Elapsed: 0.311s

5. Execute the PUT command to stage the records in CSV files. The PUT command compresses data files using GZIP compression.
   

   peter#(no warehouse)@(DEMO_DB.PUBLIC)>PUT file:///tmp/load/contacts0*.csv @csvfiles;
   contacts01.csv_c.gz(0.00MB): [##########] 100.00% Done (0.417s, 0.00MB/s),
   contacts02.csv_c.gz(0.00MB): [##########] 100.00% Done (0.377s, 0.00MB/s),
   contacts03.csv_c.gz(0.00MB): [##########] 100.00% Done (0.391s, 0.00MB/s),
   contacts04.csv_c.gz(0.00MB): [##########] 100.00% Done (0.396s, 0.00MB/s),
   contacts05.csv_c.gz(0.00MB): [##########] 100.00% Done (0.399s, 0.00MB/s),
   
           
   +----------------+-------------------+-------------+------------------------+
   | source | target | source_size | target_size | status |               
   |---------------------------------------------------------------------------|
   | contacts01.csv | contacts01.csv.gz | 554 | 412 | UPLOADED |
   | contacts02.csv | contacts02.csv.gz | 524 | 400 | UPLOADED |
   | contacts03.csv | contacts03.csv.gz | 491 | 399 | UPLOADED |
   | contacts04.csv | contacts04.csv.gz | 481 | 388 | UPLOADED |
   | contacts05.csv | contacts05.csv.gz | 489 | 376 | UPLOADED |
   +------------------+-------------------+-------------+----------------------+
   5 Row(s) produced. Time Elapsed: 2.111s

6. Confirm that the CSV files have been staged. You use the LIST command to see if the files are staged.
   

   peter#(no warehouse)@(DEMO_DB.PUBLIC)>LIST @csvfiles; 

7. Specify a virtual warehouse. Now you can load the files from the staged files into the CONTACTS table.
   

   peter#(no warehouse)@(DEMO_DB.PUBLIC)>USE WAREHOUSE dataload; 
   
   +----------------------------------------------------+
   | status |
   |----------------------------------------------------|
   | Statement executed successfully. |
   +----------------------------------------------------+
   1 Row(s) produced. Time Elapsed: 0.203s

8. Load the staged files into a Snowflake table.
   

   peter#(DATALOAD)@(DEMO_DB.PUBLIC)>COPY INTO contacts;
                       FROM @csvfiles
                       PATTERN = '.*contacts0[1-4].csv.gz'
                       ON_ERROR = 'skip_file';

Finally, if the load was successful, you can query your table using SQL.

peter#(DATALOAD)@(DEMO_DB.PUBLIC)>SELECT * FROM contacts LIMIT 10;

We hope this step-by-step Snowflake tutorial has taught you how to load data into Snowflake.

Related Blog: Top 10 Data Warehousing Tools

Snowflake Query Optimization

Optimizing queries is technically crucial in Snowflake to maximise the performance, run queries faster, and reduce costs.

The following best practices will help you in optimizing query performance.

• Selecting Fewer Columns: Snowflake stores data in micro partitions in a columnar file format. This setup reduces the amount of data read from storage. By reducing the number of columns, you can reduce the data read.
• Using Pre-aggregated Tables: Using the pre-aggregated tables, you can reduce the required storage space, speeding up query processing.
• Leveraging Query Pruning: Query pruning is a technique that reduces the number of micro partitions scanned by a query. You can use a ‘where’ or ‘join’ filter to limit the data required by the query. 
• Reducing the volume of data processed: The less data you use, the higher the data processing speed in Snowflake. Reducing the number of rows and columns processed by each step can improve query performance.
• Using Windows Functions: To optimise query performance, you need to maximise the use of window functions instead of a self-join. That’s because self-joins result in a Join explosion, which slows down the query performance.

Hence, implementing these best practices can significantly improve query optimisation.

Real-world use cases of Snowflake

Let’s witness some crucial real-world use cases of Snowflake here.

Media and Entertainment

Videoamp is an advertising and Media Company that leveraged Snowflake for its data warehouse solutions. Snowflake helped the company reduce complexity, save costs, and enhance customer experience to new levels. 

To be precise, the company reduced costs by up to 90%.

Finance

TSIMAGINE, a finance company, used Snowflake’s AI capabilities to unify its data, teams, and associated technologies. As a result, the company reduced costs by 30% by using Snowflake Cortex AI.

Healthcare 

Honeysuckle, a healthcare company, adopted Snowflake to combine data from multiple sources. Snowflake helped the company gain a holistic view of patients' health, enabling personalized care.  

Supply Chain and Logistics

Penske, a supply chain and logistics company, employed Snowflake to consolidate all its KPIs and improve employee performance. Snowflake helps generate comparison reports for years of data in minutes.

Manufacturing

Siemens Energy adopted Snowflake to help its employees access and secure data solutions. Snowflake's AI-powered chatbots helped the employees gain valuable insights for design optimization and training.

Snowflake helped the company with secure data management and rapid deployment of AI applications.

Benefits of Snowflake

There are many reasons why Snowflake is one of the critical cloud data warehouse solutions. Here they are:

• Ease of Use: Snowflake has an exceptional multi-cluster architecture and intuitive interface. It allows you to load and process data easily.
• High Speed: Snowflake always ensures that queries are processed at an optimal rate.
• Improved Performance: Snowflake supports running high volumes of queries. It offers improved flexibility, elasticity, accessibility, and value.
• External Tools Support: Snowflake allows integration with tools like Tableau, Power BI, etc., to run queries against large datasets.
• Multiple Data Format Support: Snowflake supports various formats such as structured, semi-structured, and unstructured data within a single warehouse.
• Cost-effective: Snowflake avoids idle time and only considers usage time. Computing and storage costs are billed separately. Snowflake's compressing and partitioning features help you save a lot of storage space and costs.

Yes, Snowflake provides incredible benefits to its users with high-level performance and cost-effectiveness.

Snowflake Certification Exams

Snowflake offers many certification exams for beginners and professionals. Cracking these exams helps them demonstrate their Snowflake expertise in and out of their organization.

Let's closely examine the details of some crucial Snowflake certification exams.

1. SnowPro Core - Overview

Clearing the SnowPro Core certification exam proves your proficiency in the following:

• Performing data loading and transformation
• Executing DDL and DML queries

Exam Information

Duration	115 minutes
Exam registration cost	$175
Exam registration cost (India)	$140
Question types	Multiple Select Multiple Choice Interactive Questions
The passing score	750 (scaled scoring from 0-1000)

 

2. SnowPro Advanced Architect - Overview

Acing the Snowpro Advanced Architect certification exam showcases your expertise in the following:

• Designing end-to-end data flow from source to consumption
• Deploying a Snowflake architecture that meets various business and technical requirements.

Exam Information

 

Duration	115 minutes
Exam registration cost	$375
Exam registration cost (India)	$300
Question types	Multiple Select Multiple Choice  Interactive Questions
The passing score	750 (scaled scoring from 0-1000)

Summary:

• Snowflake is a cloud-native data warehouse
• It supports multi-cloud platforms such as GCP, AWS, and Microsoft Azure.
• Its multi-cluster shared data architecture allows users and workloads to run concurrently without compromising performance.
• Its architecture allows Compute and storage devices to scale up and down independently.
• Snowflake can load, store and query structured and semi-structured data without complex transformations.

Frequently Asked Questions

1. Is Snowflake easy to learn?

Ans: Snowflake is easy to learn. Learners familiar with data warehousing and SQL queries can quickly master it.

2. How is Snowflake different from traditional data warehouses?

Ans: 

• Snowflake has an architecture that separates compute and storage resources
• It is a virtual warehouse, so there is no IT infrastructure to manage
• It provides extraordinary scalability and elasticity compared to traditional ones
• It allows concurrency by running workloads in independent compute clusters

3. What is a virtual warehouse in Snowflake?

Ans: A virtual warehouse is a Compute resource cluster that enables data loading, query processing, and other operations. You can resize, pause, and resume the compute resources independently.

4. Does Snowflake integrate with BI tools?

Ans: Yes, Snowflake integrates seamlessly with the following BI tools.

• Tableau
• Looker
• Power BI
• Qlik

Conclusion:

Let’s wrap up! Snowflake reshapes data management in industries with its multi-cloud architecture and high performance. It supports all types of data structures, providing seamless scalability and elasticity.

Do you want to dig deeper into Snowflake? Formal training will help you deepen your understanding. MindMajix offers advanced Snowflake training for beginners and experienced learners. Sign up for the hands-on training and boost your Snowflake expertise to the next level.