ETL

Ce que c'est

ETL signifie extract, transform, load : extraire, transformer,
charger. Ce travail consiste à copier les données depuis les
systèmes qui les produisent, à leur donner la forme et le sens
attendus par un autre système, puis à les y écrire, le plus souvent
dans un entrepôt de données. Une variante proche, ELT, conserve ces
trois étapes mais charge d'abord les données brutes et les
transforme dans le système cible. Beaucoup d'équipes écrivent
ETL/ELT pour désigner les deux.

Chaque lettre correspond à un travail distinct. L'extraction lit une
source : une table entière, un fichier exporté, une API ou seulement
les lignes modifiées depuis la dernière exécution. Elle les repère
par un horodatage `updated_at` ou par la capture des changements de
données, CDC, qui lit le journal que la base tient de chaque
modification. La transformation nettoie et rapproche les données :
convertir le texte en dates et en nombres, harmoniser unités et
fuseaux horaires, supprimer les doublons, rechercher les codes dans
des tables de référence et remodeler le résultat selon le modèle
cible, par exemple en tables de faits et de dimensions. Le
chargement écrit le résultat, soit par insertion en masse dans une
table vide, soit par fusion mettant à jour les lignes existantes et
ajoutant les nouvelles.

L'ordre des deux dernières étapes détermine où le travail s'exécute.
L'ETL classique transforme sur un serveur distinct avant que quoi
que ce soit n'arrive à destination. Cela avait du sens quand le
stockage et le calcul de l'entrepôt étaient rares et coûteux. L'ELT
dépose les données brutes dans des tables intermédiaires de la
cible, puis les y transforme en SQL. La copie brute reste
disponible : si une règle change, on peut retraiter l'historique
sans l'extraire une seconde fois. En contrepartie, tout ce qu'a
envoyé la source, y compris les colonnes que personne n'a demandées,
réside désormais dans l'entrepôt et doit y être gouverné.

PostgreSQL dispose de ses propres outils pour chaque étape. `COPY`
transfère des lignes entre une table et un fichier texte, CSV ou
binaire ; c'est le moyen de chargement le plus rapide. Lire un
fichier sur le serveur exige les droits de superutilisateur ou le
rôle `pg_read_server_files`, tandis que la commande psql `\copy` lit
le fichier côté client. `INSERT ... ON CONFLICT` et `MERGE`
intègrent les nouvelles lignes aux données existantes. Le décodage
logique transforme le journal d'écriture anticipée en flux de
modifications de lignes pour la CDC. Une procédure PL/pgSQL peut
garder la logique de transformation au plus près des données qu'elle
modifie.

Pourquoi c'est important

La plupart des questions utiles à une entreprise nécessitent des
données de plusieurs systèmes. Les commandes se trouvent dans une
base, les paiements dans l'export d'un fournisseur, les fiches
clients dans un CRM. Tant que rien ne les réunit selon un ensemble
commun de règles, chaque réponse est assemblée à la main, et la
méthode change avec la personne qui s'en charge. L'ETL remplit ce
rôle. Un entrepôt ne peut être plus juste que les chargements qui
l'alimentent.

La transformation est aussi l'étape où l'entreprise consigne le sens
de ses données. Quelles lignes comptent comme vente, à quel jour
rattacher un paiement de minuit, quelle fiche l'emporte lorsque deux
fiches clients correspondent : une décision prise une fois dans le
chargement s'applique à tous les rapports en aval. Si chaque rapport
en décide, les réponses divergent et deux tableaux de bord affichent
deux totaux.

Le mode d'extraction détermine comment le coût augmente. Un
rechargement complet recopie tout à chaque fois. Simple, il se
corrige lui-même, puisque l'erreur d'hier est remplacée cette nuit,
mais son coût croît avec le volume total des données. Un chargement
incrémental ne déplace que les changements ; son coût croît donc
avec le rythme des changements. Il doit mémoriser où s'est arrêtée
l'exécution précédente, détecter les lignes supprimées à la source
et accepter celles qui arrivent tard avec d'anciens horodatages. La
plupart des systèmes commencent par des rechargements complets, puis
basculent table par table quand la nuit ne suffit plus.

Le coût récurrent tient à ce que les chargements sont du code
exécuté sans surveillance, généralement la nuit, à partir de sources
qui changent sans prévenir. Il leur faut des journaux indiquant ce
qui s'est exécuté et combien de lignes ont été déplacées, des
alertes en cas d'arrêt et, surtout, une propriété : répéter une
exécution avec les mêmes données d'entrée devrait laisser la cible
exactement dans l'état qu'aurait produit une seule exécution. Un tel
chargement peut être relancé après tout échec sans vérification
préalable par une personne.

À quoi cela ressemble en pratique

Des pipelines fragmentés retardaient les rapports essentiels à
l'activité dans plusieurs environnements PostgreSQL de développement
et de production. 600 pipelines ETL/ELT ont été écrits en PL/pgSQL
pour les remplacer : de l'ETL à l'échelle de centaines de
chargements courants. Des contraintes de clé primaire et étrangère
protégeaient l'intégrité référentielle pendant les transformations.
Des index uniques et composites soutenaient les jointures, et la
gestion des exceptions avec contrôle transactionnel annulait
l'exécution en cas d'erreur. Les chargements incrémentaux
utilisaient la CDC et des deltas fondés sur les horodatages ; la
journalisation des exécutions et l'audit facilitaient le suivi et le
débogage. Le travail livré comprend la logique de transformation,
les contraintes, le chargement incrémental et la journalisation des
exécutions. Le dossier n'établit ni benchmark comparatif ni calcul
de taux d'erreur.

Une migration est un ETL exécuté une fois. Le passage d'une
application cartographique de Microsoft SQL Server à PostgreSQL avec
PostGIS a fait transiter plus de 50 millions d'enregistrements
spatiaux par des pipelines ETL. Les transformations ont converti
toutes les coordonnées vers le système de référence EPSG:4326. Une
fois les données chargées, avec des index GiST et SP-GiST et une
configuration ajustée côté cible, les requêtes spatiales, auparavant
de 2,5 secondes en moyenne, répondaient en moins de 250
millisecondes, dix fois plus vite. La charge CPU du backend a
diminué de 65 %.

À travers une frontière réseau, six pipelines ETL/ELT illustrent
chaque lettre du nom. L'extraction consistait à télécharger à
intervalles programmés des archives compressées depuis un serveur
accessible seulement par un VPN IPsec. Des scripts Bash et Python
triaient les fichiers Parquet, CSV et .bak qu'elles contenaient
selon leur type et la disposition des colonnes ; les sauvegardes
i.bak étaient restaurées dans une instance locale de MSSQL Server.		engineer.company	70
Les données étaient chargées dans des schémas intermédiaires MSSQL
et PostgreSQL avec bcp, psql ou SSIS selon le format, nettoyées,
normalisées et enrichies par des procédures PL/pgSQL et T-SQL, puis
compressées avec gzip et acheminées par un bucket Google Cloud
Storage vers Google BigQuery. Le travail qui prenait au moins trois
heures à la main s'exécutait en moins de 20 minutes de bout en bout.
La synchronisation est passée d'hebdomadaire à quotidienne.

Où les problèmes surviennent

Une relance charge deux fois les mêmes lignes. Une tâche insère avec
un simple `INSERT`, s'arrête à mi-chemin sur une erreur réseau, puis
l'ordonnanceur la relance depuis le début. Les lignes déjà arrivées
sont alors en double, et tous les totaux fondés sur elles sont trop
élevés. Donnez à la cible une clé identifiant la ligne en termes
métier, puis intégrez le lot avec `INSERT ... ON CONFLICT DO
UPDATE`, que PostgreSQL décrit comme une insertion ou mise à jour
atomique même sous forte concurrence, ou avec `MERGE`. Une limite :
une instruction `ON CONFLICT DO UPDATE` refuse de modifier deux fois
la même ligne et lève une violation de cardinalité. Il faut donc
d'abord réduire les clés répétées du lot à une seule ligne par clé.

```sql
INSERT INTO meter_reading (meter_id, read_at, kwh)
SELECT DISTINCT ON (meter_id, read_at) meter_id, read_at, kwh
FROM staging_meter_reading
ORDER BY meter_id, read_at, received_at DESC
ON CONFLICT (meter_id, read_at)
DO UPDATE SET kwh = EXCLUDED.kwh;
```

Un chargement en masse s'arrête vers la fin et laisse le disque plus
rempli qu'avant. `COPY FROM` écrit les lignes au fur et à mesure.
S'il s'arrête sur une ligne incorrecte, les lignes déjà écrites
restent dans un état supprimé : invisibles aux requêtes, elles
occupent encore le disque jusqu'à ce que `VACUUM` récupère cet
espace. Un échec tardif sur un gros fichier peut ainsi gaspiller
beaucoup d'espace. PostgreSQL 17 a ajouté `ON_ERROR ignore`, qui
saute les lignes texte ou CSV dont les valeurs ne peuvent être
converties vers le type de leur colonne. PostgreSQL 18 a ajouté
`REJECT_LIMIT` pour plafonner le nombre de lignes sautées. Cela ne
couvre que la conversion de type ; une ligne violant une contrainte
arrête toujours la commande. Charger d'abord dans une table
intermédiaire, y vérifier les données, puis transférer les bonnes
lignes dans la vraie table en une transaction tient un mauvais
fichier à l'écart des données lues par les utilisateurs.

La capture des changements remplit le disque. Le décodage logique
lit les changements par un slot de réplication. Celui-ci conserve
chaque portion du journal d'écriture anticipée encore nécessaire à
son consommateur, que ce dernier soit connecté ou non. Si la lecture
s'arrête, le journal s'accumule jusqu'à remplir le disque. Dans les
cas extrêmes, le serveur s'arrête pour empêcher le bouclage des
identifiants de transaction. La documentation PostgreSQL est
explicite : un slot devenu inutile devrait être supprimé. Après un
plantage, il peut aussi réémettre des changements récents ; le
consommateur doit donc tolérer les doublons. C'est encore la même
propriété de réexécution.

Le premier rapport du matin est lent après un gros chargement. Le
planificateur choisit comment exécuter une requête à partir des
statistiques de chaque table, et un chargement massif modifie les
données qu'elles décrivent. Autovacuum peut les actualiser seul,
mais pas toujours avant le premier rapport. Exécuter `ANALYZE` en
dernière étape règle ce problème. Pour les très gros chargements
dans une table existante, le guide PostgreSQL sur le remplissage
d'une base ajoute qu'il peut être rentable de supprimer index et
clés étrangères, puis de les recréer : les construire en masse est
plus efficace que les mettre à jour ligne par ligne. Cela retire les
contrôles d'erreur pendant leur absence ; réfléchissez donc à deux
fois avant de supprimer un index unique.

Pour aller plus loin

  * COPY — la commande de chargement en masse, ses formats et
options, et ce qu'un chargement échoué laisse derrière lui.

  * Remplir une base de données — les conseils PostgreSQL pour les
gros chargements, de la transaction unique et de `COPY` à la
reconstruction des index et à l'exécution finale d'`ANALYZE`.

  * MERGE — insérer, mettre à jour et supprimer en une instruction,
selon la correspondance de chaque ligne source avec la cible.

  * INSERT — la clause `ON CONFLICT`, son upsert atomique et
l'interdiction de toucher deux fois une ligne.

  * Concepts du décodage logique — comment les changements sont lus
dans le journal d'écriture anticipée et pourquoi supprimer un slot
de réplication inutilisé.

hCette page sur le Web	URL:https://engineer.company/fr/glossary/etl/	engineer.company	70
