Présentation
Le pipeline de transformation nocturne d'un groupe d'assurance a commencé à déborder de sa fenêtre et à empiéter sur la journée de travail. Le réflexe était de passer à un warehouse plus grand, ce qui avait déjà été tenté une fois, sans grand effet. AceMQ a été sollicité pour identifier ce qui consommait réellement le temps.
Défi
Deux problèmes distincts produisaient le même symptôme. Un petit nombre de requêtes de transformation débordaient vers le stockage distant, des ordres de grandeur plus lent que le stockage local, et ces requêtes longues monopolisaient le warehouse pendant que tout le reste s'accumulait en file d'attente derrière elles. Augmenter la taille du warehouse avait légèrement aidé les requêtes concernées par le spilling, mais aggravé la mise en file d'attente au regard des crédits consommés.
Environnement
Snowflake sur Azure exécutant des transformations dbt orchestrées par Airflow sur les données de contrats et de sinistres.
Approche
AceMQ s'est appuyé sur l'historique des requêtes pour distinguer, requête par requête, le temps d'exécution, le temps d'attente et le volume de spilling, afin de traiter les deux problèmes séparément. Les corrections au niveau des requêtes sont passées en premier, car une requête qui déborde continuera de déborder sur toute taille de warehouse incapable de contenir son ensemble de données de travail.
Solution
- Distinction, requête par requête, du temps d'exécution, du temps d'attente et du volume de spilling distant à partir de l'historique des requêtes
- Réécriture des jointures et des fonctions de fenêtrage des requêtes les plus touchées par le spilling, afin de réduire la taille des résultats intermédiaires
- Répartition du pipeline sur des warehouses distincts pour que les longues transformations ne bloquent plus les traitements courts
- Configuration de la mise à l'échelle multicluster sur le warehouse qui sert les requêtes courtes concurrentes
- Correction du clustering et du filtrage sur les plus grandes tables sources pour réduire le nombre de micro-partitions analysées
- Ajout du volume de spilling et de la profondeur des files d'attente à la supervision du pipeline, comme indicateurs avancés d'un dépassement de fenêtre
Résultat obtenu
Le pipeline nocturne est revenu dans sa fenêtre et n'empiète plus sur les heures de bureau. Le spilling distant a été éliminé sur les pires requêtes, et la consommation de crédits a baissé par rapport à l'approche du warehouse plus grand testée auparavant.