Transformer (ETL/ELT)

Trois verbes

• Extraire (Extract) : aller chercher les données là où elles sont : le tableur partagé, le formulaire d’inscription en ligne, l’export de la plateforme de paiement des cotisations. Chaque source a son format, son rythme de mise à jour et ses défauts.
• Transformer (Transform) : tout ce qu’on a vu au module 2 (dédoublonner, normaliser, écarter ce qui n’est pas valide) et au-delà : croiser des sources (rapprocher une inscription de son paiement), calculer (l’âge à partir de la date de naissance), agréger (compter par mois, par section).
• Charger (Load) : écrire le résultat là où il sera utilisé : un tableau de bord, un entrepôt de donnéesEntrepôt de données : Une base conçue pour l’analyse, où l’on rassemble des données de plusieurs sources, historisées et harmonisées.Image : Le grand entrepôt d’un supermarché, qui reçoit les livraisons de tous les fournisseurs et les range pour que chaque magasin vienne y puiser. Voir dans le glossaire, un fichier envoyé à la fédération.

Enchaînées et automatisées, ces étapes forment un pipelinePipeline de données : Une suite d’étapes automatisées qui fait passer les données d’une source à une destination.Image : Le tapis roulant d’une cuisine industrielle, qui porte les plats d’un poste à l’autre, dans l’ordre, sans qu’on les repose à la main. Voir dans le glossaire.

L’ordre n’est pas un détail

Dans un pipeline, chaque étape travaille sur le résultat de la précédente. Quelques règles de bon sens :

• on nettoie avant d’agréger : une fois qu’on a compté « 37 inscriptions en septembre », on ne peut plus retirer les doublons, ils sont fondus dans le total ;
• on normalise avant de filtrer ou de regrouper par date : un filtre « inscrits en septembre » ne reconnaîtra pas un 09/14/2024 ;
• on charge en dernier : ce qui est transformé après le chargement n’arrive jamais à destination.

ETL ou ELT ?

Dans l’approche ETLETL : Extract, Transform, Load : on extrait les données des sources, on les transforme (nettoyage, calculs), puis on les charge dans la destination.Image : La cuisine industrielle : réception des produits, préparation, dressage des assiettes. Voir dans le glossaire, on prépare tout avant de ranger. Dans l’approche ELTELT : Extract, Load, Transform : on charge d’abord les données brutes dans l’entrepôt, puis on les transforme sur place, à la demande.Image : On range toutes les courses au frigo, et chacun cuisine ensuite ce dont il a besoin. Voir dans le glossaire, on range d’abord les données brutes dans l’entrepôt, puis on les transforme sur place, selon les besoins. L’ELT garde une trace intacte des données d’origine (on peut refaire un calcul autrement plus tard) ; l’ETL évite de stocker des données brutes dont on n’a pas besoin, ce qui va dans le sens de la minimisation.

Scripts ou outils visuels ?

Pour une association comme Cap Sportif, un simple script lancé chaque mois, ou un outil visuel auto-hébergé, suffit largement. L’essentiel n’est pas l’outil, c’est que la recette soit écrite plutôt que dans la tête d’une seule bénévole.

Rejouable et traçable

Deux qualités distinguent un pipeline fiable d’un bricolage :

• l’idempotenceIdempotence : Un traitement est idempotent si le relancer plusieurs fois donne le même résultat qu’une seule fois (pas de doublons en cas de relance).Image : Appuyer deux fois sur le bouton de l’ascenseur ne le fait pas venir deux fois. Voir dans le glossaire : relancer le pipeline (après une panne, par erreur, deux fois dans le mois) donne le même résultat qu’une seule exécution, sans doublons ni totaux gonflés ;
• la traçabilitéTraçabilité (lignage) : Pouvoir retrouver d’où vient une donnée et quelles transformations elle a subies.Image : L’étiquette « origine » sur un produit alimentaire : savoir d’où il vient et par quelles étapes il est passé avant d’arriver dans l’assiette. Voir dans le glossaire : on sait d’où vient chaque chiffre du tableau de bord, quelles transformations il a subies, et quand le pipeline a tourné pour la dernière fois.

L’orchestration

Un pipeline réel tourne tout seul : chaque nuit, chaque lundi, à chaque nouvel export. L’orchestrationOrchestration : Le pilotage des pipelines : dans quel ordre, à quel moment, que faire en cas d’échec.Image : Le chef d’orchestre qui donne le départ à chaque pupitre. Voir dans le glossaire décide quand il se lance, dans quel ordre s’enchaînent plusieurs pipelines qui dépendent les uns des autres, et que faire en cas d’échec : réessayer, prévenir quelqu’un, s’arrêter proprement. C’est précisément la relance automatique après échec qui rend l’idempotence indispensable.

Rendre un chargement idempotent

Plusieurs techniques existent :

• remplacer plutôt qu’ajouter : on recalcule tout et on écrase le résultat précédent (simple, parfait pour de petits volumes comme ceux d’une association) ;
• fusionner sur une clé (on parle d’upsert, ou de MERGE en SQL) : chaque adhérent a un identifiant ; si la ligne existe déjà, on la met à jour, sinon on l’ajoute ;
• charger par partition : on remplace uniquement les données du mois traité, sans toucher aux autres.

Le compteur par mois du widget s’écrirait ainsi en SQL :

SELECT strftime('%m', date_inscription) AS mois, COUNT(*) AS adherents
FROM adherents_nettoyes
GROUP BY mois;

Remarque : cette requête suppose que date_inscription est une vraie date normalisée. Sur la colonne brute du vieux tableur, elle renverrait des résultats faux sans le moindre message d’erreur.

Les chargements incrémentaux

Avec de gros volumes, on ne retraite pas tout à chaque fois : on ne traite que ce qui a changé depuis la dernière exécution (les nouvelles inscriptions de la semaine). Plus rapide, mais plus délicat : il faut mémoriser où l’on s’est arrêté, et gérer les modifications de lignes anciennes.

Tester un pipeline

Un pipeline peut « réussir » techniquement tout en produisant des chiffres faux. D’où l’intérêt de tests de données automatiques après chaque exécution : « aucune date illisible », « aucun e-mail en double », « le total ne varie pas de plus de 20 % d’un mois sur l’autre ». Des outils comme dbt permettent d’écrire ces règles à côté des transformations. Pour Cap Sportif, une simple vérification « le total est-il plausible ? » aurait évité l’incident de l’étude de cas ci-dessous.

Le lignage des données

Dans les grandes organisations, on dessine le lignage (data lineage) : la carte qui relie chaque indicateur à ses sources et à ses transformations. Quand un chiffre surprend, on remonte le fil. C’est aussi une exigence croissante de la réglementation : savoir d’où viennent les données qui alimentent une décision, ou un système d’IA (module 9).