Minhas tentativas de teste de unidade SQL

Jan 25 2023

Recentemente eu estava discutindo com um colega de trabalho sobre como realizar testes de unidade em SQL e percebi que, embora ainda esteja no começo da minha carreira de dados, já encontrei esse problema algumas vezes e em cada turno tinha uma abordagem diferente para resolva isso. Achei que seria útil compartilhar meus hacks para que eu possa consolidar o que aprendi e também aprender com você, leitor, o que você fez e como foi.

Mas por que testar o SQL de unidade?

Foto de Lucas Santos no Unsplash

É importante esclarecer que estou discutindo aqui o teste da lógica SQL , não o teste dos dados . Testar dados é garantir que as suposições para propriedades de esquema, como nulidade e exclusividade, realmente sejam válidas para os valores em questão. Um RDBMS tradicional garantiria isso com restrições de tabela, mas não temos esse recurso em data warehouses, portanto, precisamos realmente ler e verificar as pré e pós-condições no conjunto de dados.

O SQL de teste de unidade é o mesmo que o código do aplicativo de teste de unidade: certifique-se de que uma parte da lógica produza a saída esperada para um determinado conjunto de entradas. Embora as melhores práticas determinem que façamos testes de unidade em quase todo o código do aplicativo, seja C++ ou Python, raramente vemos isso sendo tentado com SQL. Por que?

A resposta, para mim, é que escrever SQL não é a parte mais difícil . Na maioria das vezes, as transformações de dados são triviais e repetitivas, e a dificuldade está mais em modelar dados adequadamente do que em mover bits de A para B. No entanto, quando a lógica de negócios é necessária nas transformações, você pode acabar com uma complexidade necessária que é difícil para separar - como uma expressão CASE-WHEN complexa para gerar uma coluna, com entradas provenientes de um assustador JOIN de sete vias e uma cláusula WHERE de várias linhas que garante uma tabela de verdade para entender o que está acontecendo ...

Na minha experiência, garantir que a lógica seja sólida dependia apenas de testes manuais e da aprovação do cliente, o que é 1) frágil e 2) quase criminoso , considerando o quanto o SQL é adequado para testes de unidade! Embora os aplicativos exijam todos os tipos de andaimes para restringir o estado mutável e garantir a reprodutibilidade nos testes, o SQL é puro e explícito. Então, por que não usar a mesma infraestrutura para validar se a lógica funciona conforme o esperado?

Primeira tentativa: análise

Minha primeira tentativa de testar scripts SQL de unidade foi quando eu trabalhava no Google, usando tecnologias internas como Dremel e Powerdrill (você notará que seus projetos tinham nomes relacionados ao trabalho com logs ).

Estávamos construindo um pipeline de análise para um novo aplicativo, processando logs de usuários para gerar métricas caras aos gerentes de produto, como usuários ativos por país ou coorte. Uma consulta representativa seria:

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

Hackeando um framework de teste

Queríamos fazer melhor e garantir que nossos scripts fossem confiáveis, então construímos nossa própria estrutura de teste:

Cada tabela de origem teria um CSV sintético, com muitos usuários e colunas preenchidas. Para cada novo caso de teste, adicionaríamos linhas às tabelas de origem apropriadas.
Quando um teste foi executado, todo o conjunto de dados foi carregado em tabelas temporárias no data warehouse.
Para cada script, as fontes foram textualmente substituídas pela tabela temporária e o script seria executado.
Para cada script, criamos um CSV de saída esperado e o comparamos com a saída real, relatando quaisquer diferenças.

Criar os CSVs era demorado porque estávamos interessados em calcular o número de usuários ativos em janelas de 1 dia, 7 dias e 28 dias, então precisaríamos criar muitas linhas com eventos de log apenas para testar um script simples que acrescentou uma nova dimensão. Reutilizamos o máximo possível, inclusive usando a saída esperada escrita para um teste como entrada para outro, se fizesse sentido.

Discriminação

Depois de um tempo, adicionar um novo caso de teste tornou-se muito frágil , já que você poderia estar afetando qualquer número de outros testes! Se o teste A precisar calcular uma frequência relativa e, para isso, contar todas as linhas com COUNT(*), no momento em que adicionarmos uma nova linha ao conjunto de dados “dourado” para o teste B, a saída esperada do teste A será interrompida.

Depois de muito suspirar, mordemos a bala e dividimos o conjunto de dados dourado em um conjunto de dados por script . Ainda não era perfeito porque adicionamos vários casos de teste por script, mas ficou mais gerenciável. Com isso aprendemos:

Isole os testes tanto quanto possível . Um teste de unidade tem valor porque falha quando as suposições de um único componente não são mais válidas. Quebrá-lo porque algum teste não relacionado alterou o ambiente compartilhado não é útil.
CSV é um formato de arquivo terrível . Sério. Se quiséssemos testar como a lógica se comportou com uma string NULL versus uma string vazia, precisaríamos introduzir um valor especial e uma lógica de conversão na etapa de upload. Não é de admirar que o pandas.read_csv tenha 52 argumentos para lidar com todos os casos que o CSV falha em padronizar.
Embora os testes se beneficiem da simplicidade e clareza, os dados sintéticos são muito detalhados e dificultam a identificação do que é diferente de um teste para o outro. Contamos muito com comentários para deixar claro qual era a intenção de cada linha e caso de teste (novamente, não é algo padronizado em CSV).

Segunda tentativa: finalização do cliente com JSON

Essa imagem não está ligada ao texto, mas achei procurando por “padrão calmo” e gostei. Casa Dolors Calm, de Thomas Ledl, obtido no Wikimedia Commons

Na minha segunda experiência, trabalhei como consultor de engenharia de dados na Zup Innovation, alocado para uma equipe no Itaú, o maior banco da América Latina. Estávamos trabalhando com uma implantação do Hadoop local.

O tipo de trabalho aqui era muito diferente da análise, consistindo principalmente em nivelar e desduplicar registros. O principal desafio derivado de scripts serem incrementais , com a saída de uma determinada transformação dependendo da saída da anterior. Costumamos testá-lo em duas etapas: chamamos de T0 uma execução sem estado anterior e T1 uma execução baseada em T0.

Como antes, estávamos sofrendo para encontrar bugs somente após a implantação e queríamos garantir que todos os casos de canto fossem cobertos durante o desenvolvimento. A equipe de analistas de negócios preparou uma extensa lista de casos de teste, para cada um deles especificando se uma coluna teria um valor bom ou ruim e qual era a saída esperada. Observe que essas eram apenas descrições em uma tabela, mas ainda bem próximas de um teste parametrizado .

A principal dificuldade foi traduzir esta lista de casos de teste para entradas reais do Hadoop! A princípio tivemos o seguinte processo:

Criamos um arquivo Excel compartilhado onde cada aba era uma tabela, seja da primeira (T0) ou da segunda execução (T1). Cada caso de teste se tornaria 0–2 linhas em cada tabela.
Geramos cada tabela T0 como um CSV, realizamos transformações cosméticas, carregamos no Hadoop e executamos o script SQL. A saída T0 é coletada.
Geramos cada tabela T1 como um CSV, realizamos transformações cosméticas, carregamos no Hadoop e executamos o script SQL. A saída T1 é coletada.

Hackeando um gerador de tabelas

Frustrado com o vai-e-vem causado por esses erros, construí uma pequena ferramenta de sintetizador, que receberia como entrada arquivos JSON descrevendo um caso de teste e, em seguida, geraria todas as tabelas necessárias de uma só vez . A princípio, eu esperava que fosse usado pelas equipes de engenharia e controle de qualidade, mas um dos analistas de negócios era tecnicamente experiente e entendia JSON e ajudou a converter seus próprios casos de teste para esse formato! Um arquivo com um caso de teste ficou assim:

{
  "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": [...]
}

A ferramenta coletou todas as linhas de cada caso de teste e criou os arquivos companies_t0.csv, companies_t1.csv, etc., que foram carregados no Hadoop. Quando um erro era descoberto, podíamos acessar o arquivo JSON e verificar se havia um erro no código do caso de teste, o que era muito mais fácil de fazer do que procurar a linha específica em muitas guias do Excel que poderiam ter afetado um determinado caso de teste .

Outro recurso dessa ferramenta era permitir que cada linha “herdasse” colunas de um determinado modelo. Isso tornou os casos de teste agradáveis de ler, pois incluíam apenas as colunas relevantes para eles.

Novas lições

Não posso dizer que esta ferramenta foi um sucesso. Primeiro, poucas pessoas o adotaram e eu deveria ter me comunicado melhor com os engenheiros e o controle de qualidade sobre como ele poderia se encaixar no processo geral. Em segundo lugar, introduziu novas oportunidades de erro manual, como criar casos com IDs duplicados, deixar colunas inalteradas ao copiar e colar ou editar os arquivos CSVs diretamente em vez de seus progenitores JSON.

Tirei as seguintes lições disso:

Os negócios pensam em casos que abrangem tabelas e o código pensa em tabelas que contêm casos de teste . Há uma transposição a ser feita entre eles ao criar um arnês de teste.
Ter o cliente escrevendo casos de teste para você é ouro . Ninguém sabe melhor quais são as entradas relevantes e as saídas esperadas do que o especialista do domínio! Eles podem não pensar em todos os casos extremos que o código precisa manipular, mas podem fornecer informações com alguns casos de teste sobre transformações complicadas. Além disso, estabelecer uma linguagem compartilhada de entradas-saídas com eles ajuda a comunicar esses casos extremos mais tarde!
A execução de todos os casos de teste de uma só vez dificulta a verificação e a depuração . Tínhamos que manter um mapeamento entre o caso de teste de ID do usuário para mapear os resultados de volta aos casos de teste e verificá-los. Tentamos adicionar o número do caso de teste ao ID do usuário, marcando os dados “in-band”, mas isso só trouxe mais problemas com IDs inválidos. Teria sido melhor executar cada caso separadamente ou ter uma maneira de rastrear a qual caso corresponde uma determinada saída.
JSON não é um bom formato para humanos. No exemplo acima, não está explícito o que está sendo testado, e o JSON não permite comentários explicando. Sim, podemos usar um "_comment": "blah"campo, mas é um hack que precisa ser desfeito na conversão para CSV. Além disso, manter o controle de vírgulas e citar chaves de objeto é levemente irritante .
Os modelos de linha melhoram muito a legibilidade. Embora desejemos que os testes sejam muito explícitos e com pouca lógica oculta, usar um modelo para preencher colunas desinteressantes ajuda muito a detectar diferenças entre valores que são realmente importantes para o caso de teste em questão.

Esta terceira e última experiência aconteceu no meu empregador anterior, Cherre, que fornece conexão de dados para empresas imobiliárias. Usamos principalmente dbt para escrever transformações no Google BigQuery, a oferta comercial da Dremel. Um cliente solicitou que modificássemos bastante a lógica (felizmente, tornando-a mais simples!), e queríamos ter certeza de que não introduziríamos nenhuma alteração não intencional.

Reutilizando modelos dbt

Como a lógica existente não foi testada na unidade, comecei com um teste de fumaça , que é simplesmente criar entradas e garantir que nada quebre com elas. O gráfico de dependência do modelo em teste era profundo, terminando em ~ 20 fontes, então seria uma dor de cabeça zombar de fonte por fonte. Em vez disso, escolhi um corte DAG apropriado e substituí apenas ~7 refs por dados sintéticos, criados com o próprio 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 melhor visualizar e compartilhar as entradas e saídas, criei uma cópia do script para cada um dos CTEs (ou seja, cada uma das tabelas intermediárias) e anexo SELECT * FROM <cte>no final. Em seguida, executei cada cópia, obtendo um CSV para cada transformação intermediária — foo.csv, bar.csv, model_x.csv, etc. Isso ajudou a depurar cada etapa do DAG, além de fornecer uma maneira de compartilhar com não técnicos pessoas quais seriam os resultados das mudanças lógicas, dadas algumas entradas sintéticas, no formato CSV.

Ganhos limitados

No final, este teste foi bem-sucedido em garantir que não adicionaríamos nenhum erro com a alteração da lógica, pois as mesmas entradas foram usadas para a lógica existente e para a nova. Também ajudou a depurar um problema de produção posteriormente, permitindo-nos reproduzi-lo com dados sintéticos. Infelizmente, como foi construído para um propósito específico, não tentamos generalizá-lo para escrever testes em outros modelos.

As principais lições até agora foram:

SQL é a opção com menor incompatibilidade de resistência para declarar dados sintéticos. Ao escrever os dados sintéticos em SQL puro não precisamos nos preocupar com casting, NULLs, comentários, legibilidade... pois a própria sintaxe da linguagem já os fornece!
É importante zombar dos refs. A parte mais complexa da lógica SQL fica no final de um grande DAG, e ter que zombar de cada uma das fontes teria sido contraproducente. Fontes zombadoras, poderíamos testar todos os modelos intermediários… mas eu não queria testar todos, apenas os interessantes!
Agrupar linhas por casos de teste é melhor do que por tabela. Eu já havia aprendido isso na experiência anterior, mas gostaria apenas de reiterar que, mesmo quando os casos de teste são escritos em SQL com comentários, algum contexto ainda é perdido ao ler os casos e retroceder das saídas para os testes.

Imagem do autor, licenciada sob CC-BY 4.0

Não sei se podemos chegar a uma solução perfeita para teste de unidade SQL, mas ainda podemos sonhar com isso:

Os casos de teste seriam executados isoladamente;
Cada saída indicaria de qual caso veio;
Colunas desinteressantes podem ser preenchidas automaticamente e/ou ignoradas;
Toda a lógica é reaproveitada, nenhuma adaptação seria necessária para testes;
O ponto de corte do gráfico de dependência pode ser arbitrário;
Os casos de teste e seus resultados seriam compreensíveis — e graváveis — por um especialista de domínio/especialista em negócios;

Epílogo

É isso, obrigado por ler! Se você chegou até aqui, deixe 10 palmas para ajudar este artigo a ser visto por outras pessoas como você. Mais importante ainda, compartilhe suas experiências anteriores com testes de unidade em engenharia de dados. Ainda estou apenas começando e qualquer relatório é bem-vindo!