Snowflake Query Data: 4 Important Commands

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).

Prerequisites

Listed below are the prerequisites of Snowflake Query Data:

• A Snowflake account
• Basic knowledge of SQL

Snowflake Query Data Commands

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

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;

You can explore more about : Methods to load data into Snowflake.

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.