Snowflake QUALIFY para principiantes: Qué es y cómo utilizarlo

Como profesional de los datos que trabaja con Snowflake, puede que necesites filtrar los resultados de las consultas basándote en funciones de ventana. Tradicionalmente, esto requiere subconsultas o una compleja lógica de filtrado. Sin embargo, Snowflake proporciona una potente alternativa: la cláusula QUALIFY. 

En esta guía, te explicaré cómo utilizar QUALIFY para agilizar el filtrado, con ejemplos paso a paso y las mejores prácticas.

¿Qué es la cláusula QUALIFY en Snowflake?

La cláusula QUALIFY de Snowflake se utiliza para filtrar resultados con funciones de ventana. Puedes definir funciones de ventana como parte de la lista SELECT o hacer referencia a ellas directamente dentro de la cláusula QUALIFY.

Normalmente, WHERE filtra las filas sin procesar antes de la agregación, mientras que HAVING filtra después de una agregación GROUP BY.

En cambio, QUALIFY permite filtrar los resultados de sentencias SQL como ROW_NUMBER(), RANK() y DENSE_RANK(). Esto significa que podemos utilizarla en nuestras funciones de ventana para las salidas y filtrarlas inmediatamente.

Puedes pensar que QUALIFY hace con las funciones de ventana lo que HAVING hace con los agregados: filtrar el resultado una vez calculado.

Hay varios casos de uso en los que la cláusula QUALIFY resulta especialmente útil: 

• Necesitas filtrar los resultados de la consulta basándote en las salidas de la función ventana.
• Quieres simplificar tus consultas SQL reduciendo la necesidad de subconsultas.
• Necesitas clasificar, deduplicar o segmentar datos de forma eficiente dentro de las particiones.
• Estás preparando un informe o cuadro de mando que requiere los registros de mayor rendimiento por categoría.

> Si eres nuevo en Snowflake, nuestro curso Introducción a Snowflake te proporciona un punto de partida práctico antes de sumergirte en cláusulas como QUALIFY. También puedes explorar las capacidades más amplias de Snowflake con este tutorial para principiantes.

Sintaxis de QUALIFY en Snowflake

Repasemos la sintaxis básica de QUALIFY para que entiendas sus partes. He aquí cómo sería una consulta sencilla utilizando QUALIFY:

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

Tienes la cláusula habitual SELECT seguida de unas cuantas columnas. A continuación, tienes tu window_function() escrito en el formato habitual para obtener una columna llamada rank.

Está la declaración habitual FROM, que nos indica la tabla, y finalmente nuestro QUALIFY. Este QUALIFY va seguido de la "condición_función_ventana", que suele ser una igualdad y puede tener un aspecto similar a QUALIFY rank = 1

Nota: Puedes hacer referencia al alias de una función ventana o reescribir toda la expresión de la función ventana dentro de la cláusula QUALIFY. Ambos enfoques funcionan.

> Si necesitas un repaso de las funciones de ventana, echa un vistazo a esta hoja de trucos sobre funciones de ventana. 

Ejemplos de uso de QUALIFY en Snowflake

Ahora que ya conoces la estructura general de QUALIFY, te mostraré algunos ejemplos concretos en los que se utiliza esta cláusula.

Ejemplo 1: Filtrar las N primeras filas por grupo

Supongamos que queremos encontrar a los 3 empleados mejor pagados de cada departamento. En primer lugar, veamos un ejemplo que no utiliza QUALIFY, seguido de una versión más concisa que sí lo hace.

/* 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;

Como puedes ver, el ejemplo anterior requiere un CTE, pero con QUALIFY, la consulta es más sencilla:

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

La consulta anterior asigna un rango a cada empleado dentro de su departamento y devuelve los 3 primeros de cada departamento. Utiliza la sentencia QUALIFY para filtrar inmediatamente sin necesidad de una subconsulta o CTE. Esto es especialmente útil en cuadros de mando o API, donde es importante minimizar la longitud de la consulta y el tiempo de respuesta.

Ejemplo 2: Filtrar registros duplicados

Si una tabla tiene registros duplicados y queremos conservar sólo la primera aparición basándonos en una función de clasificación, podemos utilizar QUALIFY con ROW_NUMBER():

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;

La columna row_num se genera particionando en cada cliente y ordenándolos por fecha de pedido. Esto asigna un número secuencial a cada pedido, empezando por el más antiguo. Al filtrar mediante QUALIFY row_num = 1, nos aseguramos de que sólo se conserva el pedido más antiguo de cada cliente.

Esta técnica se utiliza habitualmente en tareas de deduplicación durante la limpieza de datos.

Ejemplo 3: Filtrado basado en la función ventana en agregados

Pongámonos un poco más elegantes. Utilizar QUALIFY con funciones de ventana agregada permite un filtrado más flexible y dinámico.

Por ejemplo, si queremos encontrar a los empleados cuyos salarios están por encima de la media del departamento, podemos utilizar QUALIFY con AVG() sobre una función ventana:

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

Este tipo de lógica es genial para identificar a los atípicos o a los que más rinden dentro de los grupos de compañeros.

> Si quieres reforzar tus conocimientos de SQL para realizar consultas más complejas como éstas, consulta nuestro curso de SQL Intermedio.

Buenas prácticas para utilizar QUALIFY en Snowflake

Aquí tienes algunas buenas prácticas y usos de la cláusula QUALIFY. 

Simplifica las consultas complejas

La potencia de QUALIFY reside en su capacidad para simplificar las consultas eliminando la necesidad de subconsultas y CTE adicionales. Apóyate en esta funcionalidad. Utilízala siempre que escribas una función ventana y necesites filtrar en función de su salida, sin crear una subconsulta.

Menos CTEs significan una depuración más fácil, una lógica más clara y una mejor mantenibilidad.

Combinar QUALIFY con otras funciones de ventana

Puedes combinar varias funciones de ventana para un filtrado más refinado. Por ejemplo, utilizar ROW_NUMBER() junto con RANK() para resolver los empates en la clasificación, limitando al mismo tiempo los resultados:

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;

Esta consulta devuelve los empleados que se encuentran en los 3 primeros puestos salariales de su departamento. Sin embargo, si más de tres empleados empatan dentro de ese top 3, sólo mostrará los tres primeros ordenados por ID de empleado.

Consideraciones sobre el rendimiento

Aunque QUALIFY simplifica las consultas en términos de código escrito, hay que tener en cuenta algunos aspectos de rendimiento.

Las funciones ventana pueden ser caras computacionalmente, y las consultas mal estructuradas pueden consumir muchos recursos. Asegúrate de tener en cuenta estos conceptos:

• Las funciones ventana pueden ser costosas desde el punto de vista computacional en grandes conjuntos de datos, así que ten cuidado en qué fase del proceso las utilizas.
• Garantiza una indexación y partición adecuadas para optimizar el rendimiento.
• Utiliza LIMIT junto con QUALIFY para mejorar aún más la eficacia de las pruebas.
• Utiliza la herramienta Perfil de Consulta de Snowflake para identificar los cuellos de botella de rendimiento en las consultas con ventanas.

Resolución de problemas en las consultas QUALIFY

Puede ser fácil caer en trampas con las declaraciones QUALIFY, dada la complejidad de las funciones de ventana. Aquí tienes algunos consejos que te ayudarán con los problemas más comunes que te puedas encontrar.

1. Utilizar funciones de ventana en la cláusula WHERE

Si intentas utilizar funciones de ventana directamente en la cláusula WHERE, se producirán errores porque WHERE se evalúa antes de procesar las funciones de ventana.

-- 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;

Solución: Utiliza en su lugar la cláusula QUALIFY, que se evalúa después de las funciones de ventana.

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

2. Omitir el predicado en QUALIFY

La cláusula QUALIFY requiere un predicado para filtrar los resultados de las funciones ventana. Omitir el predicado dará lugar a errores.

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

Solución: Asegúrate de que la cláusula QUALIFY incluye un predicado, como = 1 para filtrar por la fila mejor clasificada.

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

3. Alias ambiguos

Utilizar el mismo alias para una columna y para una función de ventana puede dar lugar a confusiones y resultados inesperados.

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

Solución: Utiliza alias distintos para evitar ambigüedades.

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

4. Partición u ordenación incorrecta

Especificar incorrectamente las cláusulas PARTITION BY o ORDER BY en las funciones de ventana puede provocar resultados inesperados.

Solución: Define cuidadosamente la partición y el orden para que coincidan con la lógica deseada. Por ejemplo, para obtener el salario más alto por departamento:

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

Conclusión

La cláusula QUALIFY de Snowflake es una potente herramienta para filtrar resultados basándose en funciones de ventana, reduciendo la necesidad de subconsultas y haciendo las consultas más legibles. Comprendiendo cómo utilizarlo eficazmente, los científicos de datos pueden simplificar sus consultas y mejorar la eficacia cuando trabajan con datos clasificados, agrupados o agregados.

¿Estás preparado para llevar más lejos tus conocimientos de Snowflake y SQL? Empieza con nuestro curso Introducción a Snowflake, perfecciona tus consultas analíticas en el curso Funciones de ventana PostgreSQL o avanza en tu carrera con el programa Ingeniero de Datos Asociado en SQL.