Extract, Transform, and Load (ETL)

Objectif

L'ETL réunit dans un référentiel unique des données que plusieurs systèmes détiennent séparément, chacun avec son format, sa codification et son rythme de mise à jour.

Un tableau de bord, un calcul de réserves, un rapport réglementaire ne lisent que le référentiel cible. Ce qu'ils affichent vaut ce que vaut le traitement qui l'a rempli: sa fréquence fixe l'actualité des chiffres, ses règles fixent leur sens, ses rejets fixent leur complétude. Un écart de chiffres entre deux rapports se résout presque toujours dans le traitement plutôt que dans l'outil qui les affiche.

Usage

Quand l'utiliser

• Plusieurs systèmes sources à réunir: formats, codifications et dictionnaires divergents à réconcilier.
• Rapport récurrent à alimenter: les règles arrêtées une fois, le même traitement se rejoue à chaque échéance sans nouvelle décision.
• Analytique descriptive ou preuve de concept: le terrain où le guide situe l'ETL classique, avant tout déploiement à grande échelle.
• Traçabilité exigée: la piste d'audit du chargement dit quelle valeur a changé, quand et sous quelle exécution.

Quand ne pas l'utiliser

• Décision algorithmique en quasi temps réel: le traitement par lots ne tient pas la latence, passer à un traitement de flux.
• Analyse ponctuelle sur une source unique: une requête ou un export suffit, l'investissement ne s'amortit pas.

Description

Extraire

L'extraction sort les données utiles de leurs systèmes d'origine, et le guide y distingue trois travaux.

Le premier identifie les sources et les types de données à partir du problème métier: on part des grands ensembles (gestion de la relation client, facturation, canaux de vente) et on descend vers les entités puis les champs à mesure que le besoin se précise.

Le deuxième établit une classification universelle. Chaque source arrive avec ses conventions et son dictionnaire de données; définitions, descriptions et formats se réconcilient pour qu'un schéma unique d'extraction s'applique à toutes.

Le troisième vérifie l'intégrité de ce qui sort. Une part se contrôle automatiquement, par un schéma qui met en correspondance les éléments source et les éléments extraits: conformité des tailles et des formats, redondances, pertes en transmission. L'autre part reste manuelle: valeurs minimales et maximales, identifiants, valeurs admises, échantillonnage. Le DMBOK donne le vocabulaire sous lequel ces contrôles se rangent, complétude, validité, cohérence, actualité et unicité, ce qui évite qu'un contrôle soit décrit par le nom du script qui l'exécute.

Reste la question que l'extraction incrémentale pose seule: qu'est-ce qui a changé depuis la dernière exécution? Kimball et Caserta traitent cette capture des données modifiées (change data capture) comme une technique d'extraction, aux côtés d'une discipline de reprise et de redémarrage, dans leur architecture d'ETL. L'horodatage de dernière modification est le moins cher et le plus fragile: il ne voit pas les suppressions et il ne bouge pas quand une correction est passée par un traitement technique, si bien que les écarts s'accumulent sans signal jusqu'au jour où un rapprochement manuel les découvre. Un chargement complet mensuel ou trimestriel borne cette dérive. La lecture du journal de transactions de la base source voit toutes les écritures, au prix d'un accès que l'exploitant n'accorde pas toujours. La comparaison complète avec l'image précédente voit tout, au prix d'une extraction complète à chaque exécution.

La zone de transit

Le guide définit la zone de transit (staging area) comme un emplacement logique pour les données, qui facilite les transformations. Kimball et Caserta lui donnent une seconde fonction: le point de reprise. Un traitement qui dépose son extrait et le conserve redémarre depuis cet extrait après un échec. Reprendre depuis la zone de transit prend quelques minutes. Réextraire trois systèmes de production en prend plusieurs heures et rend un instantané qui n'est plus celui du lot en cours, puisque les systèmes ont continué de tourner entre-temps.

Les mêmes auteurs posent une condition à la reprise: le traitement doit se rejouer sans effet de bord, c'est-à-dire produire le même état, avec le même nombre de lignes. La propriété s'obtient en supprimant puis en réinsérant le lot identifié ou en écrivant sur une clé naturelle. Sans elle, un traitement relancé après un échec partiel réécrit ce qu'il avait déjà écrit: les doublons passent les contrôles de format et gonflent les agrégats de quelques pour cent, une amplitude trop faible pour alerter et trop forte pour être ignorée. Le rejeu à blanc en environnement de test est ce qui vérifie la propriété.

Transformer

La transformation traduit les données extraites dans un format exploitable et exact, conforme à la logique métier.

Le rôle de l'analyste porte sur le sens. Il vérifie qu'aucune règle métier ne se trouve contredite: qu'un regroupement en tranches ne masque pas le seuil sur lequel une décision se prend, qu'une imputation ne fabrique pas une observation, qu'un arrondi appliqué avant une somme ne déplace pas le total. C'est le dernier moment où la question « cette valeur veut-elle encore dire la même chose? » se pose avant que le chiffre n'entre dans un calcul.

Charger

Le chargement fait entrer les données transformées dans le référentiel cible depuis la zone de transit. Les tâches courantes sont de revoir le format cible et le mode de chargement au regard du besoin métier, d'écrire le lot, de produire la piste d'audit, puis de normaliser l'enchaînement pour qu'il se rejoue à l'identique à chaque échéance.

Le chargement incrémental compare le lot aux données déjà présentes et n'écrit que l'écart, ce qui raccourcit le rafraîchissement des rapports décisionnels. Le chargement complet remplace l'ensemble des données de la cible; le guide le tient pour mieux adapté aux travaux prédictifs et prescriptifs, qui travaillent sur un jeu reconstitué à une date donnée. Kimball et Caserta traitent ce choix comme une décision d'architecture, prise en connaissance de ses deux conséquences: la durée de la fenêtre de chargement et la charge imposée aux systèmes sources.

Écraser ou conserver l'historique

Le chargement porte une décision de gestion: que devient l'ancienne valeur quand un attribut change? Kimball et Ross ont catalogué les réponses sous le nom de dimensions à évolution lente (slowly changing dimensions). Le type 1 écrase, la nouvelle valeur remplace l'ancienne et l'historique disparaît. Le type 2 ajoute une ligne, ferme la précédente par une date de fin et marque la version courante, de sorte que chaque fait reste rattaché à la version en vigueur au moment où il s'est produit. Le type 3 garde une seule valeur antérieure, dans une colonne supplémentaire. Une note de conception du Kimball Group décrit les extensions numérotées 0, 4, 5, 6 et 7.

Le type 1 est le comportement par défaut de la plupart des outils, ce qui en fait le piège le plus discret du chargement: il ne produit aucune erreur, aucun rejet et aucune alerte. Le rapport de l'an dernier, régénéré cette année, ne rend plus les mêmes chiffres, et personne ne dispose de la valeur qui permettrait de reconstituer l'écart. Le choix appartient au métier, attribut par attribut: un calcul, un rapport ou une obligation légale a-t-il besoin de savoir ce que cet attribut valait à une date passée? Le canton de domicile d'un assuré et le taux appliqué à un contrat en ont besoin. Une adresse de correspondance ou un numéro de téléphone rarement.

La piste d'audit et le rapprochement

La piste d'audit enregistre, pour chaque exécution, ce que le chargement a écrit: les lignes ajoutées, celles modifiées ou remplacées, la valeur antérieure et l'identifiant de l'exécution. Elle se produit au chargement parce que le chargement est le moment où la valeur change.

Elle alimente le rapprochement qui suit chaque exécution: nombre de lignes lues, transformées, chargées et rejetées, sommes de contrôle sur les montants, écart expliqué ligne à ligne. Le registre des rejets se relit le lendemain matin, avec un motif par enregistrement et une consigne écrite sur leur sort: correction à la source et reprise au prochain lot, correction manuelle dans la cible ou abandon assumé.

Cette trace se distingue du lignage des données, que le DMBOK traite sous la gestion des métadonnées. Le lignage décrit le chemin qu'un élément parcourt entre les systèmes et les transformations qu'il subit, indépendamment de toute exécution. Le lignage répond à « d'où vient ce champ », la piste d'audit à « qui a changé cette valeur mardi soir ».

La frontière avec le data mapping

Le data mapping déclare, l'ETL exécute. La spécification énonce, pour chaque attribut cible, quelle source l'alimente et sous quelle règle; le traitement applique cette déclaration aux enregistrements réels. Tout ce qui relève du temps appartient au traitement: l'ordonnancement, la fenêtre de chargement, la reprise après échec, les rejets, la trace de ce qui a été écrit. Le rapprochement d'après chargement est aussi de ce côté de la frontière. C'est là qu'apparaît une règle appliquée par le traitement mais absente de la spécification.

ETL ou ELT

Les technologies de grande volumétrie inversent l'ordre des deux dernières étapes: extraire, charger, puis transformer. Les données brutes sont déposées sur un système de fichiers distribué ou dans un entrepôt dans le cloud, et la transformation s'exécute sur la puissance de calcul de la plateforme cible. C'est la réponse usuelle aux données massives, hétérogènes ou peu structurées. Le guide y situe les limites des outils ETL classiques, tout en maintenant que les principes de l'ETL tiennent quels que soient les outils: seuls l'ordre et le lieu de la transformation changent. Pour l'analyste, la vérification des règles métier intervient alors après le chargement, sur des données déjà accessibles aux utilisateurs, ce qui oblige à distinguer dans le référentiel les jeux bruts des jeux validés, faute de quoi un tableau de bord finit par lire une table intermédiaire.

Ce qui fait échouer le traitement

Les rejets silencieux. Un outil qui écarte les enregistrements non conformes sans registre ni compteur rend une cible incomplète d'un volume inconnu. Le contrôle tient en une comparaison: lignes lues à la source contre lignes chargées plus lignes rejetées.

Le schéma source qui bouge. Une colonne ajoutée, renommée ou retypée dans un système source casse l'extraction ou, pire, la laisse tourner sur une valeur devenue fausse. Rattacher les systèmes sources au contrôle des changements et faire échouer le traitement sur un schéma inattendu coûte moins que de découvrir l'écart trois mois plus tard dans un rapport.

Considérations IA

La génération du code de transformation est l'usage le plus immédiat. Une règle écrite dans la spécification de mapping se traduit en expression SQL ou en transformation d'outil sans invention, et un premier jet se relit vite. Les jeux d'essai qui couvrent les cas limites d'une règle, valeur nulle, valeur hors domaine, longueur maximale, s'obtiennent de la même façon et sont la partie du travail que les équipes écrivent le moins.

La surveillance des exécutions se prête à l'apprentissage statistique. Un modèle entraîné sur l'historique des chargements signale un lot dont le volume, la distribution d'un montant ou le taux de valeurs nulles s'écarte de ce que rendent les exécutions comparables. Le contrôle par seuil fixe ne voit pas la baisse de 12 % d'un flux régional un lundi férié; un modèle qui a vu deux ans de lots la voit. Proposer la table de correspondance entre deux codifications régionales est un appariement par similarité, dont le résultat se valide ensuite ligne à ligne.

Le sens d'une règle métier ne se délègue pas. Un modèle produit une transformation syntaxiquement correcte sur un champ dont il ignore ce qu'il signifie dans l'organisation. Le choix de conserver ou d'écraser l'historique dépend d'usages en aval et d'obligations légales que le schéma ne porte pas. La décision sur les rejets, écarter un enregistrement, le corriger ou le charger tel quel, engage la complétude de tout ce qui se calcule ensuite.

Reste la donnée elle-même. Une étape de transformation qui appelle un service externe lui transmet des enregistrements de production à chaque exécution, numéros AVS, montants et dates compris. La communication de données personnelles à un tiers qui traite pour le compte du responsable demande un contrat de sous-traitance au sens de l'art. 9 de la loi fédérale sur la protection des données. Un service hébergé hors de Suisse ajoute l'art. 16, sur la communication à l'étranger. Le masquage se place donc avant l'appel.

Exemples

Une caisse-maladie charge chaque nuit les transactions de sinistres de ses systèmes régionaux dans un entrepôt qui sert au calcul des réserves et à la compensation des risques entre cantons. Un assuré déménage de Vaud à Genève le 1^er juin 2024. L'extraction de la nuit suivante remonte le nouveau canton, et le mode de chargement décide du sort de l'ancien.

Une facture du 12 mars 2024, de CHF 1'240.50, pointe vers la clé technique 4711. En type 2, elle reste rattachée à la ligne vaudoise et le décompte de la compensation des risques pour 2024 continue de la compter sur Vaud. En type 1, la même facture se lit désormais sur Genève: le total par canton d'une année close change le jour d'un déménagement, sans qu'aucune écriture ait touché la table des sinistres. La clé technique distincte de la seconde ligne est ce qui rend l'historisation possible; réutiliser la clé 4711 pour la version genevoise ramènerait au comportement du type 1.

Sur 40'000 assurés vaudois dont 1,5 % change de canton dans l'année, soit 600 personnes à une charge moyenne de CHF 3'800, l'écrasement déplace quelque CHF 2'280'000 de sinistres d'un canton à l'autre dans un décompte déjà remis.

Le même assuré repart pour Fribourg en mars 2025. En type 2, la nuit suivante ferme la ligne genevoise et en ouvre une troisième avec sa propre clé; les factures de 2024 restent sur 4711 et 5290 selon leur date. En type 1, la même nuit remplace GE par FR sur la ligne 4711 et déplace une seconde fois le décompte 2024.

Visualisations

Le traitement se dessine, parce que deux de ses propriétés se voient mieux qu'elles ne se lisent: l'endroit d'où une exécution repart après un échec et le fait que la piste d'audit est un produit de l'étape de chargement. Les deux bifurcations méritent aussi leur trait, incrémental ou complet à l'extraction, écriture de l'écart ou remplacement au chargement, parce qu'elles se décident indépendamment l'une de l'autre.

Deux registres restent en tableau, le catalogue des types de transformation et la comparaison entre écrasement et historisation: la matière y tient en lignes et en colonnes. En faire une image lui ferait perdre la sélection et le tri.

Coût

Outils

Les outils ETL graphiques (Talend, SQL Server Integration Services, Azure Data Factory, Informatica, Pentaho) sont la famille que le guide décrit: connecteurs vers les sources d'entreprise, vue graphique de l'enchaînement, ce qui ouvre une partie du travail à des utilisateurs qui n'écrivent pas de code. Leur limite est la lisibilité pour le métier, dont l'accord conditionne pourtant les règles: une transformation exprimée dans l'outil se valide mal en séance.

Les ordonnanceurs (Apache Airflow, Dagster ou l'ordonnanceur d'entreprise déjà en place) portent ce que l'outil de transformation ne porte pas: les dépendances entre tâches, les fenêtres d'exécution, les tentatives et l'alerte. C'est là que se règle la reprise.

Les outils de transformation dans l'entrepôt (dbt en tête) correspondent au schéma ELT: les transformations s'écrivent en SQL versionné, s'exécutent sur la plateforme cible et se testent par des assertions attachées à chaque table. La règle métier redevient lisible, au prix d'une dépendance à la puissance de calcul facturée de la plateforme.

Les plateformes cibles (Snowflake, BigQuery, Databricks ou un entrepôt PostgreSQL sur l'infrastructure interne) déterminent le mode de chargement disponible et le coût d'un remplacement complet. Le choix engage la fenêtre de chargement nocturne.

Les outils de profilage et de qualité (Great Expectations, les fonctions de profilage des outils ETL, une série de requêtes maison) mesurent les dimensions du DMBOK sur chaque lot et font échouer le traitement quand un seuil est franchi. Un contrôle qui journalise sans arrêter le traitement laisse entrer la donnée qu'il vient de qualifier de fausse.

Sources

• IIBA, Guide to Business Data Analytics, §3.10 Extract, Transform, and Load (ETL): la définition, les trois étapes et leurs travaux, la zone de transit, le catalogue des types de transformation, les modes de chargement incrémental et complet, la production des pistes d'audit, la séquence ELT des technologies de grande volumétrie, ainsi que les forces et limites de la technique.
• Ralph Kimball et Joe Caserta, The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data, Wiley, 2004: la zone de transit comme point de reprise, le rejeu sans effet de bord comme condition de la reprise, la capture des données modifiées comme technique d'extraction, le choix entre chargement incrémental et chargement complet comme décision d'architecture.
• Kimball Group, ETL Architecture's 34 Subsystems: la capture des données modifiées et la reprise après échec comme composants nommés d'une architecture d'ETL.
• Ralph Kimball et Margy Ross, The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3^e édition, Wiley, 2013: les dimensions à évolution lente de type 1, 2 et 3.
• Kimball Group, Design Tip #152: Slowly Changing Dimension Types 0, 4, 5, 6 and 7: les extensions du catalogue des dimensions à évolution lente.
• DAMA International, DAMA-DMBOK: Data Management Body of Knowledge, 2^e édition: chapitre 12 Metadata Management pour le lignage des données, chapitre 13 Data Quality pour les dimensions de qualité citées à l'extraction.
• Loi fédérale sur la protection des données (LPD), RS 235.1, art. 9 et 16: le contrat de sous-traitance exigé lorsqu'un tiers traite des données personnelles pour le compte du responsable; les conditions de la communication à l'étranger lorsque ce tiers est hébergé hors de Suisse.