Snowflake Query Data: 4 Important Commands

Today, Cloud Storage is the best technology for data storage for many companies. Most Cloud platforms are very elastic, allowing their users to scale their storage resources based on requirements.

Data stored in the Cloud is easily accessible as the organization members can access it from any location. The Cloud technology also uses different mechanisms to ensure that data stored there is secure. 

Snowflake is a Data Warehousing Solution created for Cloud Storage. It provides you with a platform where you can store your data. Snowflake Query Data makes it easy for you to use and scale Snowflake well to meet your changing storage needs. With Snowflake Query Data, you can easily add or remove compute and storage resources from Snowflake.

After storing your data in Snowflake, you will want to query the tables and view the data. This means that you should have knowledge about how to query data in Snowflake. You can achieve this by writing Snowflake Query Statements.

This article will introduce you to Snowflake Data Warehouse and provide you a comprehensive guide on Snowflake Query Commands like SELECT Statement, WHERE Clause, GROUP BY Clause, and LIKE Clause (along with their syntaxes and examples).

Table of Contents

• Prerequisites
• Introduction to Snowflake
• Snowflake Query Data Commands
  • Snowflake Query Data Command: SELECT Statement
  • Snowflake Query Data Command: WHERE Clause
  • Snowflake Query Data Command: GROUP BY Clause
  • Snowflake Query Data Command: LIKE Clause
• Querying Data in Snowflake
• Conclusion

Prerequisites

Listed below are the prerequisites of Snowflake Query Data:

• A Snowflake account
• Basic knowledge of SQL

Introduction to Snowflake

Snowflake is a Data Warehouse-as-a-Service platform developed for the Cloud. It has a Data Architecture that uses the scalable, elastic Azure Blobs Storage as the internal storage engine and Azure Data Lake for storage of unstructured, structured, and on-premise data ingested via the Azure Data Factory. 

Snowflake provides security and protection of data using Amazon S3 Policy Controls, SSO, Azure SAS Tokens, and Google Cloud Storage access permissions. You can also scale your storage depending on your storage needs. Thus, when you use a Snowflake Data Warehouse to store your data, while enjoying its features like scalability, security of your data, among many other benefits. 

For more information on Snowflake, click here.

What is Snowflake Query?

Snowflake Query is a SQL support in Snowflake Data Warehouse. Users can use standard SQL commands to manipulate, access and perform other operations on data. It also supports Analytics extension that allow users to use Machine Learning and Analytics using standard SQL language support and run queries.

Snowflake Query Data Commands

Now that you have a basic understanding of Snowflake Data Warehouse, let’s discuss how to write different Snowflake Query Commands to query data from Snowflake. Listed below are some of the most useful Snowflake Query Data Commands that you should always be familiar with:

• SELECT Statement
• WHERE Clause
• GROUP BY Clause
• LIKE Clause

Let’s walk through these Snowflake Query Data Commands in more detail.

1) Snowflake Query Data Command: SELECT Statement

Snowflake allows you to query for data using the standard SELECT statements. The SELECT statement allows you to query data from a table. You can combine it with a number of clauses to help you filter the data to be returned. This means that you can query and get the most detailed data possible when using Snowflake.

Suppose you have a Snowflake table named “Employees”. The table has 4 columns namely name, age, department, and salary as shown below:

To see all the data stored in the table, you can use the SELECT statement as shown below:

SELECT * FROM Employees;

The command should return all the rows contained in the table. 

To select specific columns only, you can use the SELECT statement as follows:

SELECT name, department FROM Employees;

The above command will return only the name and department columns from the Employees table. 

2) Snowflake Query Data Command: WHERE Clause

You can combine the SELECT statement with a WHERE clause to specify a condition that matches a subset of the table rows. This can help you to filter the results of the SELECT statement. 

For example, suppose you want to know the list of employees who work in the Accounting department. You can combine the SELECT statement with a WHERE clause as shown below:

SELECT * FROM Employees
WHERE department = 'Accounting';

The statement should only return the employees who work in the Accounting department.

3) Snowflake Query Data Command: GROUP BY Clause

The GROUP BY clause groups rows with the same group-by-item expressions and calculates the aggregated functions for the result. Give below is the syntax of the GROUP BY Clause:

SELECT ...
FROM ...
[ ... ]
GROUP BY group-item [ , group-item> [ , ... ] ]
[ ... ]
WHERE,
group-item ::= { <column_alias> | <position> | <expr> }

The  above parameters are described below:

• <column_alias>: It denotes the column alias that appears in the SELECT list of the query block. 
• <position>: This denotes the position of the expression in the SELECT list.
• <expr>: It denotes any expression found in the current scope.

For example, suppose you need to know the Amount of Total Salary that goes to all the employees in a particular department. You can sum the salaries of the employees in each department using the GROUP BY Clause as shown below:

SELECT department, SUM (salary) as  total_salary
FROM Employees
GROUP BY department;

The above statement returns the following output:

The result shows that the highest amount of salary goes to the Accounting department. 

4) Snowflake Query Data Command: LIKE Clause

The LIKE command helps you search for a particular pattern in a column. It is used together with the WHERE clause. You can use it with the following 2 wildcard characters:

• “%”: It represents 0, 1, or many characters.
• “_”: It represents only 1 character at a time.

For example, to see the details of all employees whose names start with the letter “N”, you can use the Snowflake Query Statement given below. This command will return the details of the employee named Nicholas because that name in the table starts with the letter “N”.

SELECT * from Employees
WHERE name LIKE 'N%';

Now, you can use the Snowflake Query Statement given below to fetch details of all the employees whose names end with an “h”. This statement will return the details of the employee named “Leah” as that is the only name that ends with an “h”.

SELECT * from Employees
WHERE name LIKE '%h';

You can use the Query Statement given below to return the details of all employees whose names have the letter “e”. This command will return 2 records i.e. “Leah” and “Joel” because only their names contain the letter “e”.

SELECT * from Employees
WHERE name LIKE '%e%';

You can use the Query Statement given below to search for any employee whose name starts with the letter “M” and has 3 other characters. This Query Statement will return the details of the employee named “Mary” because only her name meets the search condition.

SELECT * from Employees
WHERE name LIKE 'M___';

That’s how Snowflake Query Data Commands work.

Querying Data in Snowflake

Step 1: First click on Worksheets.

Step 2: To add a new tabbed view, click on “+,” name the new view as Querying Data.

Step 3: Always make sure that you are using the LAB_WH warehouse and the LAB_DB database.

USE LAB_DB;
USE WAREHOUSE LAB_WH; 

Use the command given below to suspend a warehouse, if you need to:

ALTER WAREHOUSE LAB_WH SUSPEND;

Step 4: Now, run the command given below:

SELECT ORDER_ID, SUM (QUANTITY * UNIT_PRICE) "ORDER TOTAL"
FROM ORDER_DETAILS    
    GROUP BY ORDER_ID;

Step 5: The illustration of the INNER JOIN SQL statement is provided below. Copy and paste in the worksheet tab you were using before.

SELECT O.ORDER_ID, OD.INVENTORY_ID, OD.STATUS_ID, O.EMPLOYEE_ID
    FROM  ORDER_DETAILS OD JOIN  ORDERS O
    ON OD.ORDER_ID = O.ORDER_ID
    ORDER BY O.ORDER_DATE;

Next, we will look at how to use the pagination feature representing OFFSET and FETCH parameters.

Note: LIMIT (the Postgres syntax) and FETCH (the ANSI syntax) are supported in Snowflake to define the maximum range of rows returned by your statement. Moreover, a similar return is produced.

Step 6: Next, run the command given below:

ELECT ORDER_ID, SUM (QUANTITY * UNIT_PRICE) "ORDER TOTAL"
FROM ORDER_DETAILS    
    GROUP BY ORDER_ID
    OFFSET 0 FETCH 5;

Step 7: At last, replace the last line of the code given above with the code provided below and then check how you progress:

 OFFSET 5 FETCH 5;

Conclusion

This article introduced you to Snowflake and Snowflake Query Data Commands. It provided you a comprehensive guide on how to work with some of the most useful Snowflake Query Data Commands such as SELECT Statement, WHERE Clause, LIKE Clause, etc. In case you want to transfer your data from multiple data sources to Snowflake Data Warehouse, then you can explore Hevo Data.

Visit our Website to Explore Hevo

Hevo Data is a No-code Data Pipeline and has awesome 100+ pre-built integrations that you can choose from. Hevo can help you integrate data from numerous free data sources and load it into a destination to analyze real-time data with a BI tool and create your Dashboards. It will make your life easier and make data migration hassle-free. It is user-friendly, reliable, and secure.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of understanding Snowflake Query Data Commands in the comments section below!