Snowflake QUALIFY voor beginners: wat het is en hoe je het gebruikt

Als data­professional die met Snowflake werkt, kan het zijn dat je queryresultaten wilt filteren op basis van vensterfuncties. Traditioneel vraagt dat om subqueries of complexe filterlogica. Snowflake biedt echter een krachtig alternatief: de QUALIFY-clausule. 

In deze gids leg ik stap voor stap uit hoe je QUALIFY gebruikt om filtering te stroomlijnen, met voorbeelden en best practices.

Wat is de QUALIFY-clausule in Snowflake?

De QUALIFY-clausule in Snowflake wordt gebruikt om resultaten met vensterfuncties te filteren. Je kunt vensterfuncties definiëren als onderdeel van de SELECT-lijst of ze rechtstreeks binnen de QUALIFY-clausule aanroepen.

Meestal filtert WHERE ruwe rijen vóór aggregatie, terwijl HAVING filtert ná een GROUP BY-aggregatie.

In plaats daarvan laat QUALIFY je filteren op de resultaten van SQL-instructies zoals ROW_NUMBER(), RANK() en DENSE_RANK(). Dat betekent dat we die in onze vensterfuncties kunnen gebruiken voor output en er meteen op kunnen filteren.

Je kunt QUALIFY zien als voor vensterfuncties wat HAVING is voor aggregaten: filteren op het resultaat nadat het is berekend.

Er zijn verschillende use-cases waarin de QUALIFY-clausule bijzonder handig is: 

• Je moet queryresultaten filteren op basis van de output van vensterfuncties.
• Je wilt je SQL-queries vereenvoudigen door minder subqueries te gebruiken.
• Je moet data efficiënt rangschikken, dedupliceren of segmenteren binnen partities.
• Je bereidt een rapport of dashboard voor dat topresultaten per categorie vereist.

> Als je nieuw bent met Snowflake, biedt onze Introduction to Snowflake-cursus een hands-on startpunt voordat je in clausules zoals QUALIFY duikt. Je kunt ook de bredere mogelijkheden van Snowflake verkennen met deze beginnersvriendelijke tutorial.

Syntax van QUALIFY in Snowflake

Laten we de basis­synax van QUALIFY doornemen zodat je de onderdelen begrijpt. Zo kan een eenvoudige query met QUALIFY eruitzien:

SELECT
 column1,
 column2, 
 window_function() OVER (PARTITION BY column3 ORDER BY column4) AS rank
FROM table_name
QUALIFY window_function_condition;Mogelijk gemaakt door

Je hebt je gebruikelijke SELECT-clausule gevolgd door een paar kolommen. Daarna staat je window_function() in de gebruikelijke notatie, waarmee we een kolom rank krijgen.

Er is de gebruikelijke FROM-instructie die de tabel aangeeft, en tot slot onze QUALIFY. Deze QUALIFY wordt gevolgd door de “window_function_condition”, vaak een gelijkheidsvoorwaarde, bijvoorbeeld QUALIFY rank = 1

Let op: je kunt in de QUALIFY-clausule verwijzen naar de alias van een vensterfunctie of de volledige vensterfunctie opnieuw uitschrijven. Beide werken.

> Heb je een opfrisser nodig over vensterfuncties? Bekijk dan dit cheat sheet voor vensterfuncties. 

Voorbeelden van QUALIFY in Snowflake

Nu je de algemene structuur van QUALIFY kent, laat ik een paar concrete voorbeelden zien met deze clausule.

Voorbeeld 1: Top N rijen per groep filteren

Stel dat we de 3 bestbetaalde medewerkers in elke afdeling willen vinden. Eerst bekijken we een voorbeeld zonder QUALIFY, gevolgd door een beknoptere versie mét.

/* First we have to rank all the employees */
WITH ranked_employees AS (
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees)

/* Then we query to subquery and filter using WHERE */
SELECT *
FROM ranked_employees
WHERE rank <= 3;Mogelijk gemaakt door

Zoals je ziet vereist het bovenstaande voorbeeld een CTE, maar met QUALIFY is de query eenvoudiger:

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank <= 3;Mogelijk gemaakt door

De bovenstaande query kent elke medewerker binnen zijn of haar afdeling een rang toe en geeft de top 3 per afdeling terug. Hij gebruikt de QUALIFY-clausule om direct te filteren, zonder subquery of CTE. Dit is vooral handig in dashboards of API’s waar je de lengte van queries en responstijd wilt minimaliseren.

Voorbeeld 2: Dubbele records filteren

Als een tabel dubbele records heeft en we alleen de eerste voorkomenis willen behouden op basis van een rangschikkingsfunctie, kunnen we QUALIFY met ROW_NUMBER() gebruiken:

SELECT 
order_id, 
customer_id, 
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_num
FROM orders
QUALIFY row_num = 1;Mogelijk gemaakt door

De kolom row_num wordt gegenereerd door per klant te partitioneren en op besteldatum te sorteren. Zo krijgt elke bestelling een volgnummer, beginnend bij de vroegste. Door te filteren met QUALIFY row_num = 1 blijft alleen de vroegste bestelling per klant over.

Deze techniek wordt vaak gebruikt bij deduplicatie tijdens datacleaning.

Voorbeeld 3: Filteren op basis van vensterfunctie in aggregaten

Laten we het iets geavanceerder maken. Met QUALIFY en geaggregeerde vensterfuncties kun je flexibeler en dynamischer filteren.

Als we bijvoorbeeld medewerkers willen vinden met een salaris boven het afdelingsgemiddelde, kunnen we QUALIFY met AVG() over een vensterfunctie gebruiken:

SELECT 
employee_id, 
department, 
salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees
QUALIFY salary > avg_salary;Mogelijk gemaakt door

Dit soort logica is ideaal om uitschieters of toppresteerders binnen peergroepen te identificeren.

> Wil je je SQL-skills aanscherpen voor complexere queries zoals deze? Bekijk dan onze Intermediate SQL-cursus.

Best practices voor het gebruik van QUALIFY in Snowflake

Hier zijn enkele best practices en toepassingen voor een QUALIFY-clausule. 

Vereenvoudig complexe queries

De kracht van QUALIFY zit in het vereenvoudigen van queries door extra subqueries en CTE’s te vermijden. Maak gebruik van deze functionaliteit. Gebruik het wanneer je een vensterfunctie schrijft en op de output wilt filteren zonder een subquery te maken.

Minder CTE’s betekent eenvoudiger debuggen, helderdere logica en betere onderhoudbaarheid.

Combineer QUALIFY met andere vensterfuncties

Je kunt meerdere vensterfuncties combineren voor verfijndere filtering. Bijvoorbeeld ROW_NUMBER() samen met RANK() om gelijke rangen op te lossen terwijl je resultaten beperkt:

SELECT 
employee_id, 
department, 
salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, employee_id) AS row_num
FROM employees
QUALIFY rank <= 3 AND row_num <= 3;Mogelijk gemaakt door

Deze query retourneert medewerkers die in de top 3 salarisklassen van hun afdeling vallen. Als er meer dan drie medewerkers gelijk eindigen binnen die top 3, worden alleen de eerste drie getoond op volgorde van employee ID.

Prestatie-overwegingen

Hoewel QUALIFY queries qua geschreven code vereenvoudigt, zijn er enkele performance-aspecten om rekening mee te houden.

Vensterfuncties kunnen rekenintensief zijn en slecht gestructureerde queries kunnen veel resources vragen. Houd het volgende in gedachten:

• Vensterfuncties kunnen op grote datasets duur zijn; let op in welke fase van je proces je ze inzet.
• Zorg voor passende indexing en partitionering om de performance te optimaliseren.
• Gebruik LIMIT in combinatie met QUALIFY voor nog efficiëntere tests.
• Gebruik de Query Profile-tool van Snowflake om performanceknelpunten in windowed queries op te sporen.

Problemen oplossen met QUALIFY-queries

Door de complexiteit van vensterfuncties kun je met QUALIFY-statements makkelijk in valkuilen lopen. Hier zijn enkele tips voor veelvoorkomende problemen.

1. Vensterfuncties gebruiken in de WHERE-clausule

Pogingen om vensterfuncties direct in de WHERE-clausule te gebruiken leiden tot fouten, omdat WHERE wordt geëvalueerd vóórdat vensterfuncties worden uitgevoerd.

-- This will cause an error
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
WHERE rank = 1;Mogelijk gemaakt door

Oplossing: gebruik de QUALIFY-clausule, die pas ná vensterfuncties wordt geëvalueerd.

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;Mogelijk gemaakt door

2. De predicaatvoorwaarde in QUALIFY weglaten

De QUALIFY-clausule vereist een predicaat om de resultaten van vensterfuncties te filteren. Als je dit weglaat, krijg je fouten.

-- This will cause an error
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY RANK() OVER (PARTITION BY department ORDER BY salary DESC);Mogelijk gemaakt door

Oplossing: zorg dat de QUALIFY -clausule een predicaat bevat, zoals = 1 om op de top­rang te filteren.

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;Mogelijk gemaakt door

3. Dubbelzinnige aliassen

Hetzelfde alias gebruiken voor zowel een kolom als een vensterfunctie kan tot verwarring en onverwachte resultaten leiden.

-- Potentially ambiguous
SELECT salary AS rank,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;Mogelijk gemaakt door

Oplossing: gebruik verschillende aliassen om dubbelzinnigheid te vermijden.

SELECT salary AS salary_amount,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
QUALIFY salary_rank = 1;Mogelijk gemaakt door

4. Onjuiste partitionering of sortering

Onjuiste specificatie van PARTITION BY of ORDER BY in vensterfuncties kan tot onverwachte resultaten leiden.

Oplossing: definieer partitionering en sortering zorgvuldig zodat ze aansluiten op de gewenste logica. Bijvoorbeeld om het hoogste salaris per afdeling te krijgen:

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;Mogelijk gemaakt door

Conclusie

De QUALIFY-clausule in Snowflake is een krachtig hulpmiddel om resultaten op basis van vensterfuncties te filteren, waardoor je minder subqueries nodig hebt en je queries leesbaarder worden. Door te begrijpen hoe je deze effectief inzet, kunnen data scientists hun queries vereenvoudigen en efficiënter werken met gerangschikte, gegroepeerde of geaggregeerde data.

Klaar om je Snowflake- en SQL-skills verder te brengen? Begin met onze Introduction to Snowflake-cursus, scherpt je analytische queries aan in de PostgreSQL-cursus over vensterfuncties, of boost je carrière met de Associate Data Engineer in SQL-track!