QUALIFY Clause in the Snowflake Database.

In this article, we are going to discover the QUALIFY clause. How does it work, its use cases, and its impact on query performance?

What is the Qualify clause in Snowflake SQL?

A Qualify clause is used to filter the results of the windows functions(Analysis Functions ) without having to nest the windows function calculations in a derived table.

Windows functions must be present either in the Select clause or Qualify clause. Only those rows will be returned in the resultset where the Qualify clause is True.

Only a few databases support this clause e.g Teradata, Snowflake, Exasol, BigQuery, etc.

Order of evaluation of the Qualify Clause: It is always evaluated after the windows function in a DML statement. Below is the order in which a DML/Select statement is evaluated.

FROM
WHERE
GROUP BY
HAVING
WINDOWS FUNCTION
QUALIFY
DISTINCT
ORDER BY
LIMIT

Here are some examples.

1. Create a dummy data set of students and their total marks.

DROP TABLE IF EXISTS  "TEMP"."TEST_DATA";
CREATE TABLE "TEMP"."TEST_DATA"
(NAME VARCHAR,
 MARKS VARCHAR
);
INSERT INTO "TEMP"."TEST_DATA"
VALUES
('A',95),
('B',87),
('C',76),
('D',45),
('E',87),
('F',64);

Dummy Data for the e.g of Qualify clause.

E.g1 Let's find out who has scored the maximum marks.

--Use Windows function in the select clause
SELECT NAME,
       MARKS,
       RANK() OVER (ORDER BY MARKS DESC) AS RNK
FROM "TEMP"."TEST_DATA"
QUALIFY RNK =1;
--Use Windows function in the Qualify clause directly
SELECT NAME,
       MARKS
FROM "TEMP"."TEST_DATA"
QUALIFY RANK() OVER (ORDER BY MARKS DESC) =1;

Here is the nested select query if we don't want to use the Qualify clause.

SELECT NAME,
       MARKS 
       FROM (SELECT NAME,
       MARKS,
       RANK() OVER (ORDER BY MARKS DESC) AS RNK
FROM "TEMP"."TEST_DATA") AS A
WHERE A.RNK = 1;

The query performance is slightly improvised as we have a bit of performance gain by using…