Advanced Snowflake

Chapter 3. Snowflake SQL

In this chapter, I will go through some of the more advanced Snowflake SQL functions. Let’s start by looking at Snowflake’s order of execution, then move to the different window functions and see where you can use them. Following that, you’ll learn about Snowflake’s geospatial functions.

Order of Execution

Snowflake follows a very logical order of execution of SQL clauses. However, with the introduction of a QUALIFY clause, things got a little interesting. A lot of Snowflake users using window functions and QUALIFY were confused about what would be executed when, and how that affects the result of their queries. In essence, the QUALIFY clause is very similar to the WHERE clause because it filters the data. However, when it does so in the order of execution, the QUALIFY clause differs, and thus, confusion arises. Snowflake executes the SQL clauses in the following order:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. WINDOW

6. QUALIFY

7. DISTINCT

8. ORDER BY

9. LIMIT

Let’s walk through this and see what happens when you execute the following query:

SELECT *
FROM legends
WHERE first_name = 'Cristiano'
   AND last_name = 'Ronaldo';

This is a simple query, extracting details with a very simple WHERE clause. In this case, Snowflake will execute the FROM clause first, which tells Snowflake which table to read the data from. Then comes the WHERE clause, which tells Snowflake what data to filter, or in other words, which micro-partitions to read and which ones to skip.

Let’s ...