Visão geral
O pipeline noturno de transformação de um grupo de seguros começou a estourar sua janela e a invadir o horário comercial. A correção instintiva era um warehouse maior, que já tinha sido testado uma vez sem grande efeito. A AceMQ foi contratada para descobrir o que realmente consumia o tempo.
Desafio
Dois problemas distintos produziam o mesmo sintoma. Um pequeno número de consultas de transformação fazia spill para o armazenamento remoto, que é ordens de grandeza mais lento que o local, e essas consultas longas ocupavam o warehouse enquanto todo o resto ficava na fila atrás delas. Aumentar o tamanho do warehouse tinha ajudado um pouco as consultas com spill, mas piorado as filas em relação a cada crédito gasto.
Ambiente
Snowflake no Azure executando transformações dbt orquestradas pelo Airflow sobre dados de apólices e sinistros.
Abordagem
A AceMQ usou o histórico de consultas para separar, por consulta, o tempo de execução, o tempo em fila e o volume de spill, de modo que os dois problemas pudessem ser tratados de forma independente. As correções no nível das consultas vieram primeiro, porque uma consulta que faz spill continuará fazendo spill em qualquer tamanho de warehouse que não comporte seu working set.
Solução
- Separação, por consulta, do tempo de execução, do tempo em fila e do volume de spill remoto a partir do histórico de consultas
- Reescrita dos joins e das funções de janela nas consultas com mais spill para reduzir o tamanho dos resultados intermediários
- Divisão do pipeline em warehouses separados, para que as transformações longas deixassem de bloquear os jobs curtos
- Configuração de escalonamento multi-cluster no warehouse que atende às consultas curtas simultâneas
- Correção do clustering e da filtragem nas maiores tabelas de origem para reduzir as micro-partições varridas
- Inclusão do volume de spill e da profundidade da fila no monitoramento do pipeline como indicadores antecedentes de estouro da janela
Resultado obtido
O pipeline noturno voltou a caber em sua janela e deixou de invadir o horário comercial. O spill remoto foi eliminado nas piores consultas, e o consumo de créditos caiu em relação à abordagem do warehouse maior testada anteriormente.