Recientemente estaba discutiendo con un compañero de trabajo sobre cómo realizar pruebas unitarias en SQL y me di cuenta de que, aunque todavía estoy en el comienzo de mi carrera de datos, ya me he encontrado con este problema algunas veces y en cada turno tenía un enfoque diferente para resuélvelo. Pensé que podría ser útil compartir mis trucos para poder consolidar lo que he aprendido, y también para aprender de usted, el lector, lo que ha hecho y cómo le fue. ¿Debemos?

Pero, ¿por qué la unidad prueba SQL?

Es importante aclarar que estoy discutiendo aquí probar la lógica SQL , no probar datos . Probar los datos garantiza que las suposiciones de las propiedades del esquema, como la nulabilidad y la unicidad, se mantengan para los valores disponibles. Un RDBMS tradicional garantizaría eso con las restricciones de la tabla, pero no tenemos esta función en los almacenes de datos, por lo que necesitamos leer y verificar las condiciones previas y posteriores sobre el conjunto de datos.

La prueba de unidad SQL es lo mismo que el código de aplicación de prueba de unidad: asegúrese de que una parte de la lógica produzca la salida esperada para un conjunto dado de entradas. Si bien las mejores prácticas dictan que realizamos pruebas unitarias de casi todo el código de la aplicación, ya sea C++ o Python, rara vez vemos que esto se intente con SQL. ¿Por qué?

La respuesta, para mí, es que escribir SQL no es la parte más difícil . La mayoría de las veces, las transformaciones de datos son triviales y repetitivas, y la dificultad radica más en modelar los datos adecuadamente que en mover bits de A a B. Sin embargo, cuando la lógica comercial es necesaria dentro de las transformaciones, puede terminar con la complejidad necesaria que es difícil. para separarse, como una expresión compleja de CASE-WHHEN para generar una columna, con entradas provenientes de un abrumador JOIN de siete vías y una cláusula WHERE de varias líneas que garantiza una tabla de verdad para comprender lo que está sucediendo...

En mi experiencia, garantizar que la lógica sea sólida se basó solo en pruebas manuales y una aprobación del cliente, que es 1) frágil y 2) casi criminal considerando lo bien que se adapta a las pruebas unitarias de SQL. Si bien las aplicaciones requieren todo tipo de andamios para restringir el estado mutable y garantizar la reproducibilidad en las pruebas, SQL es puro y explícito. Entonces, ¿por qué no usar la misma infraestructura para validar que la lógica funciona como se esperaba?

Primer intento: análisis

Mi primer intento de realizar pruebas unitarias de secuencias de comandos SQL fue cuando trabajaba en Google, usando tecnologías internas como Dremel y Powerdrill (notarás que sus proyectos tenían nombres relacionados con el trabajo con registros ).

Estábamos construyendo una canalización de análisis para una nueva aplicación, procesando registros de usuarios para generar métricas apreciadas por los gerentes de productos, como usuarios activos por país o cohorte. Una consulta representativa sería:

CREATE TABLE _7dau_by_country_platform AS
SELECT
  day,
  country,
  platform,
  COUNT(DISTINCT user_id) AS active_users
FROM UNNEST(dates) day
JOIN app_logs ON DATE(ts) BETWEEN DATE_SUB(day, INTERVAL 7 DAY) AND day
GROUP BY 1, 2, 3

Hackear un marco de prueba

Queríamos hacerlo mejor y asegurarnos de que nuestros scripts fueran confiables, por lo que construimos nuestro propio marco de prueba:

1. Cada tabla de origen tendría un CSV sintético, con muchos usuarios y columnas completadas. Para cada nuevo caso de prueba, agregaríamos filas a las tablas de origen correspondientes.
2. Cuando se ejecutaba una prueba, todo el conjunto de datos se cargaba en tablas temporales en el almacén de datos.
3. Para cada secuencia de comandos, las fuentes se reemplazaban textualmente por la tabla temporal y la secuencia de comandos se ejecutaba.
4. Para cada secuencia de comandos, creamos un CSV de salida esperado y lo comparamos con la salida real, informando cualquier diferencia.

La creación de los CSV requería mucho tiempo porque estábamos interesados ​​en calcular la cantidad de usuarios activos durante ventanas de 1, 7 y 28 días, por lo que necesitaríamos crear muchas filas con eventos de registro solo para probar un script simple que añadió una nueva dimensión. Reutilizamos tanto como fue posible, incluido el uso de la salida esperada escrita para una prueba como entrada para otra, si tenía sentido.

Descomponer

Después de un tiempo, agregar un nuevo caso de prueba se volvió demasiado frágil , ya que podría estar afectando a muchas otras pruebas. Si la prueba A necesita calcular una frecuencia relativa, y para eso contó todas las filas con COUNT (*), en el momento en que agregamos una nueva fila al conjunto de datos "dorado" para la prueba B, el resultado esperado de la prueba A se rompería.

Después de mucho suspirar, mordimos la bala y dividimos el conjunto de datos dorado en un conjunto de datos por secuencia de comandos . Todavía no era perfecto porque agregamos varios casos de prueba por secuencia de comandos, pero se volvió más manejable. Con eso aprendimos:

• Aísle las pruebas tanto como sea posible . Una prueba unitaria tiene valor porque se rompe cuando las suposiciones de un solo componente ya no se cumplen. Hacer que se rompa porque alguna prueba no relacionada cambió el entorno compartido no es útil.
• CSV es un formato de archivo terrible . En serio. Si quisiéramos probar cómo se comporta la lógica con una cadena NULL frente a una cadena vacía, necesitaríamos introducir un valor especial y una lógica de conversión en el paso de carga. No es de extrañar que pandas.read_csv tenga 52 argumentos para manejar todos los casos que CSV no logra estandarizar.
• Aunque las pruebas se benefician de la simplicidad y la claridad, los datos sintéticos son demasiado detallados y dificultan la detección de las diferencias entre una prueba y otra. Confiamos en gran medida en los comentarios para aclarar cuál era la intención de cada fila y caso de prueba (nuevamente, no es algo estandarizado en CSV).

Segundo intento: cierre de sesión del cliente con JSON

En mi segunda experiencia, trabajé como consultor de ingeniería de datos en Zup Innovation, asignado a un equipo en Itaú, el banco más grande de América Latina. Estábamos trabajando con una implementación de Hadoop en las instalaciones.

El tipo de trabajo aquí era muy diferente del análisis, y consistía principalmente en aplanar y deduplicar registros. El principal desafío derivaba de que los scripts fueran incrementales , con la salida de una transformación determinada dependiendo de la salida de la anterior. Por lo general, lo probaríamos en dos pasos: llamamos a T0 una ejecución sin estado anterior y T1 una ejecución que se basa en T0.

Como antes, sufríamos por encontrar errores solo después de la implementación y queríamos asegurarnos de que todos los casos de esquina estuvieran cubiertos durante el desarrollo. El equipo de analistas de negocios preparó una extensa lista de casos de prueba, especificando para cada uno de ellos si una columna tendría un valor bueno o malo, y cuál era el resultado esperado. Tenga en cuenta que estas eran solo descripciones en una tabla, pero todavía bastante cerca de una prueba parametrizada .

¡La principal dificultad fue traducir esta lista de casos de prueba a entradas reales de Hadoop! Al principio teníamos el siguiente proceso:

1. Creamos un archivo de Excel compartido donde cada pestaña era una tabla, ya sea desde la primera (T0) o la segunda ejecución (T1). Cada caso de prueba se convertiría en 0–2 filas en cada tabla.
2. Damos salida a cada tabla T0 como un CSV, realizamos transformaciones cosméticas, las subimos a Hadoop y ejecutamos el script SQL. Se recopila la salida T0.
3. Damos salida a cada tabla T1 como un CSV, realizamos transformaciones cosméticas, las subimos a Hadoop y ejecutamos el script SQL. Se recopila la salida T1.

Hackear un generador de tablas

Frustrado con el ir y venir causado por estos errores, construí una pequeña herramienta de sintetizador, que tomaría como entradas archivos JSON que describen un caso de prueba , y luego generaría todas las tablas que necesitábamos a la vez . Al principio, esperaba que lo usaran los equipos de ingeniería y control de calidad, pero uno de los analistas comerciales tenía conocimientos técnicos y entendía JSON, ¡y ayudó a convertir sus propios casos de prueba a este formato! Un archivo con un caso de prueba se veía así:

{
  "companies_t0": [
    {"company_id": "001-3", "company_name": "ACME Inc."},
    {"company_id": "002-9", "company_name": "Umbrella LLC"}
  ],
  "companies_t1": [
    {"company_id": "001-3", "company_name": "ACME Inc."}
  ],
  "associates_t0": [
    {"company_id": "001", "person_id": "p01"},
    {"company_id": "001", "person_id": "p02"},
    {"company_id": "002", "person_id": "p02"}
  ],
  "associates_t1": [
    {"company_id": "001", "person_id": "p01"},
    {"company_id": "001", "person_id": "p02"},
    {"company_id": "002", "person_id": "p02"} 
  ],
  "persons": [...]
}

La herramienta recopiló todas las filas de cada caso de prueba y creó archivos Companies_t0.csv, Companies_t1.csv, etc., que luego se cargaron en Hadoop. Cuando se descubría un error, podíamos pasar al archivo JSON y comprobar si se trataba de un error en el código del caso de prueba, lo que era mucho más fácil de hacer que buscar la fila específica dentro de muchas pestañas de Excel que podrían haber afectado un caso de prueba determinado. .

Otra característica de esta herramienta fue permitir que cada fila "heredara" columnas de una plantilla determinada. Esto hizo que los casos de prueba fueran agradables de leer, ya que solo incluían las columnas relevantes para ellos.

Nuevas lecciones

No puedo decir que esta herramienta haya sido un éxito. Primero, pocas personas lo adoptaron, y debería haberme comunicado mejor con los ingenieros y el control de calidad sobre cómo podría encajar en el proceso general. En segundo lugar, introdujo nuevas oportunidades de error manual, como crear casos con ID duplicados, dejar las columnas sin cambios al copiar y pegar o editar los archivos CSV directamente en lugar de sus progenitores JSON.

Obtuve las siguientes lecciones de esto:

• Las empresas piensan en casos que abarcan tablas y el código piensa en tablas que contienen casos de prueba . Se debe realizar una transposición entre ellos al crear un arnés de prueba.
• Que el cliente escriba casos de prueba para ti es oro . ¡Nadie sabe mejor cuáles son las entradas relevantes y los resultados esperados que el experto en el dominio! Es posible que no piensen en todos los casos extremos que el código debe manejar, pero pueden proporcionar información con un puñado de casos de prueba sobre transformaciones complicadas. Además, establecer un lenguaje compartido de entradas y salidas con ellos lo ayuda a comunicar esos casos extremos más adelante.
• La ejecución de todos los casos de prueba a la vez dificulta la comprobación y la depuración . Tuvimos que mantener una asignación entre ID de usuario y caso de prueba para asignar los resultados a los casos de prueba y verificarlos. Intentamos agregar el número de caso de prueba a la ID de usuario, etiquetando los datos como "en banda", pero esto trajo más problemas con las ID no válidas. Hubiera sido mejor ejecutar cada caso por separado o tener una forma de rastrear a qué caso corresponde una salida determinada.
• JSON no es un buen formato para humanos. En el ejemplo anterior, no está explícito lo que se está probando y JSON no permite que los comentarios lo expliquen. Sí, podemos usar un "_comment": "blah"campo, pero es un truco que debe deshacerse en la conversión a CSV. Además, hacer un seguimiento de las comas y citar claves de objetos es un poco exasperante .
• Las plantillas de fila mejoran en gran medida la legibilidad. Aunque nos gustaría que las pruebas fueran muy explícitas y con poca lógica oculta, usar una plantilla para completar columnas poco interesantes ayuda mucho a detectar diferencias entre valores que son realmente importantes para el caso de prueba en cuestión.

Esta tercera y última experiencia ocurrió en mi empleador anterior, Cherre, que brinda conexión de datos para empresas de bienes raíces. Principalmente usamos dbt para escribir transformaciones en Google BigQuery, la oferta comercial de Dremel. Un cliente había solicitado que modificáramos mucho la lógica (¡afortunadamente, simplificándola!), y queríamos asegurarnos de que no introduciríamos ningún cambio no deseado.

Reutilización de modelos dbt

Dado que la lógica existente no se probó por unidad, comencé con una prueba de humo , que simplemente crea entradas y se asegura de que nada se rompa con ellas. El gráfico de dependencia del modelo bajo prueba era profundo, terminando en ~20 fuentes, por lo que sería un dolor de cabeza simular fuente por fuente. En su lugar, elegí un corte DAG apropiado y reemplacé solo ~ 7 referencias con datos sintéticos, creados con el mismo SQL :

WITH
-- Synthetic sources
foo AS (
  -- Test case 1, active foo
  SELECT "i001" AS foo_id, 200_000 AS foo_value, TRUE AS is_active,
  UNION ALL
  -- Test case 2, inactive foo without any bar
  SELECT "i002" AS foo_id, 240_000 AS foo_value, FALSE AS is_active,
),
bar AS (
  -- Test case 1, active foo with two bars
  SELECT "b001" AS bar_id, 451.40 AS bar_value, "i001" AS foo_id
  UNION ALL
  SELECT "b002" AS bar_id, 123.98 AS bar_value, "i001" AS foo_id
),
-- From now on, everything should be left the same as the DBT model.
model_x AS (
  SELECT ...
  FROM foo
  JOIN bar USING (foo_id)
)

Para visualizar y compartir mejor las entradas y salidas, he creado una copia del script para cada uno de los CTE (es decir, cada una de las tablas intermedias) y la anexo SELECT * FROM <cte>al final. Luego ejecuté cada copia y obtuve un CSV para cada transformación intermedia: foo.csv, bar.csv, model_x.csv, etc. gente, cuáles serían los resultados de los cambios lógicos, dadas algunas entradas sintéticas, en formato CSV.

ganancias limitadas

Al final, esta prueba tuvo éxito al garantizar que no agregaríamos ningún error con el cambio de lógica, ya que se usaron las mismas entradas para la lógica existente y la nueva. También ayudó a depurar un problema de producción más tarde, al permitirnos reproducirlo con datos sintéticos. Desafortunadamente, dado que se creó con un propósito específico, no intentamos generalizarlo para escribir pruebas en otros modelos.

Las principales lecciones hasta ahora han sido:

• SQL es la opción con menor desajuste de resistencia para declarar datos sintéticos. Al escribir los datos sintéticos en SQL puro, no tenemos que preocuparnos por la conversión, los valores NULL, los comentarios, la legibilidad... ¡ya que la sintaxis del lenguaje ya los proporciona!
• Es importante burlarse de los árbitros. La parte más compleja de la lógica SQL se encuentra al final de un DAG grande, y tener que burlarse de cada una de las fuentes hubiera sido contraproducente. Burlándonos de las fuentes, podríamos probar todos los modelos intermedios... pero no quería probarlos todos, ¡solo los interesantes!
• Agrupar filas por casos de prueba es mejor que por tabla. Ya había aprendido esto en la experiencia anterior, pero me gustaría reiterar que incluso cuando se escriben casos de prueba en SQL con comentarios, aún se pierde algo de contexto al leer casos y retroceder de los resultados a las pruebas.

No sé si podemos llegar a una solución perfecta para la prueba unitaria de SQL, pero aún podemos soñar con eso:

• Los casos de prueba se ejecutarían de forma aislada;
• Cada salida indicaría de qué caso proviene;
• Las columnas poco interesantes se pueden autocompletar y/o ignorar;
• Toda la lógica se reutiliza, no serían necesarias adaptaciones para las pruebas;
• El punto de corte del gráfico de dependencia puede ser arbitrario;
• Los casos de prueba y sus resultados serían comprensibles y escribibles por un experto en el dominio/especialista en negocios;

Epílogo

Eso es todo, ¡gracias por leer! Si llegaste hasta aquí, deja 10 aplausos para ayudar a que este artículo sea visto por otras personas como tú. Lo más importante es que comparta sus experiencias pasadas con las pruebas unitarias en ingeniería de datos. Todavía estoy comenzando y cualquier informe es bienvenido.

Gracias a David Barer, Bruno Santos y Rodrigo Donizetti por su disponibilidad y revisión de borradores de esta publicación.