9 millions de lignes, 17 ans, un ETL : comment j'ai nettoyé les données H-1B
17 ans de déclarations H-1B du Département du Travail américain. Environ 9 millions de lignes. 260 colonnes la pire année. 4 ères de fichiers différentes qui ont chacune renommé tout. Des codes SOC transformés en dates par Excel. Des salaires encodés de 3 façons. Des villes tapées comme des adresses. Voici le carnet de bord. Chaque règle que le pipeline connaît aujourd'hui est la cicatrice d'un combat précis.
Comment charger un fichier Excel de 260 colonnes sans planter ?
Première cible : FY2024 Q4. 29 colonnes propres, des noms sains, Excel calme. Le pipeline l'a lu en 2 minutes. Confiance au maximum. Puis FY2019 est arrivé. 260 colonnes. Des noms finissant en _1 à _10, un par lieu de travail. Le même salaire stocké dans WAGE_RATE_OF_PAY_FROM_1 et 9 autres endroits. Le chargeur naïf a tapé 3 Go de RAM et s'est écrasé. Première leçon : ne lire que ce qu'il faut. Les lectures ciblées divisent la mémoire par 8 et le temps par 5.
Comment gérer des noms de colonnes qui changent tous les 4 ans ?
FY2008 à FY2009 appelle la clé primaire CASE_NO. FY2010 à FY2014 la renomme LCA_CASE_NUMBER avec un préfixe LCA_CASE_ sur chaque champ. FY2015 à FY2018 retire le préfixe. FY2019 ajoute le suffixe _1. FY2020 s'installe sur CASE_NUMBER. Chaque champ a cet historique. EMPLOYER_NAME a été NAME, puis LCA_CASE_EMPLOYER_NAME. SOC_TITLE a été OCCUPATIONAL_TITLE, puis LCA_CASE_SOC_NAME, puis SOC_NAME.
La réponse : un dictionnaire COLUMN_ALIASES d'environ 50 entrées. Chaque nom historique est réécrit vers la forme canonique FY2020+ avant toute logique en aval. À partir de là, le pipeline ne parle qu'une seule langue. Ajouter une nouvelle ère revient à ajouter quelques lignes au dictionnaire.
Pourquoi Excel détruit-il les codes d'emploi SOC ?
Les codes SOC comme 15-1132 décrivent le métier (ici développeur logiciel). Excel voit 15-1132 et pense au 15 novembre 1132. Il réécrit la cellule à cette date, stocke un nombre en interne, et le code d'origine est perdu. Parfois Excel va plus loin et convertit 15-1132 en chaîne Jan-15. Parfois Feb-32. Parfois 1132-01-15. Tout ça est irrécupérable.
Le pipeline détecte désormais 4 motifs de corruption Excel. SOC avec nom de mois : NULL. SOC en format ISO date : NULL. Anciens codes DOT d'avant 2000 : NULL (ils ne correspondent pas 1 pour 1 aux SOC modernes). SOC à 6 chiffres sans tiret (151132) : tiret réinséré. Environ 2 % des codes SOC sur 17 ans tombent dans un de ces cas. Chaque règle est un combat qui a livré.
Comment annualiser 5 formats d'unités de salaire différents ?
Les déclarants reportent les salaires en 5 unités : Année, Mois, Bi-Mensuel, Semaine, Heure. Pour comparer, tout doit devenir annuel. Année x1. Mois x12. Bi-Mensuel x26. Semaine x52. Heure x2080 (40 heures x 52 semaines). Pandas vectorisé fait le calcul en millisecondes par fichier.
Les vieux fichiers abrègent différemment. FY2008 utilise yr, hr, mth, wk, bi en minuscules. FY2009 passe en majuscules YR, HR, MTH, WK, BI. Ratez le mapping et 1,2 million de lignes anciennes produisent des salaires NaN car la recherche du multiplicateur échoue en silence. La correction : un dictionnaire WAGE_UNIT_NORMALIZE qui tourne avant tout calcul. FY2015 à FY2018 ajoute un nouveau monstre : salaires stockés comme une chaîne unique du style 66000 - 70000. Le pipeline découpe la chaîne, force les deux moitiés en numérique, et se rabat sur l'unité du salaire prévalent si l'unité du salaire elle-même manque.
Quand nullifier une valeur sale plutôt que supprimer la ligne ?
Un salaire de 0 $ par an n'est pas un salaire légitime. Ça signifie que le déclarant a sauté le champ. NULL. Un salaire horaire prévalent de 1 000 $ n'est pas légitime non plus. Quelqu'un a tapé un annuel dans le champ horaire. NULL. Une ville de travail comme SUITE 100 ou 123 MAIN ST n'est pas une ville. NULL. Un code d'État qui est en fait un pays (GEORGIA est apparu 282 fois dans le champ pays, toujours pour des dossiers Atlanta et Alpharetta). Correction vers l'État GA, avec UNITED STATES OF AMERICA remis dans le champ pays.
La règle la plus longue à apprendre : ne pas supprimer la ligne. Nullifier le champ. Environ 1 ligne sur 300 a au moins une valeur parasite. Supprimer des lignes entières aurait perdu des millions d'enregistrements utiles. Seules les lignes sans la clé primaire (case_number) sont supprimées. Tout le reste survit avec des NULL là où c'est nécessaire.
Comment vérifier la sortie d'un pipeline ETL de façon indépendante ?
L'ETL passe ses propres tests. Ça ne prouve rien. Alors un script QA séparé a été construit, sans partager la moindre logique avec l'ETL. Il ouvre la base SQLite finale en boîte noire et vérifie chaque ligne contre des valeurs de référence : les 50 États américains plus territoires, les statuts valides (Certified, Certified - Withdrawn, Denied, Withdrawn), des plages de salaires plausibles, le format des codes SOC, les bornes de dates. L'ETL et le QA ne peuvent pas se mentir parce qu'ils ne se parlent jamais.
Puis est venu verify_computed.py. Chaque colonne dérivée (salary_min, salary_max, prevailing_wage_annual, employer_name_clean) est recalculée indépendamment à partir des champs bruts et comparée à ce que l'ETL a écrit. Un écart signifie ETL non déterministe. Ensuite compare_dbs.py relance l'ETL complet deux fois à partir de zéro et diffe chaque ligne. Mêmes entrées, mêmes sorties, zéro dérive. Ce trio (QA, verify_computed, compare_dbs) est le contrat qualité.
À quoi ressemble une base SQLite propre de 9 millions de lignes ?
La sortie est un seul fichier SQLite. 9 millions de lignes. 37 colonnes. 7 index couvrant les requêtes courantes (recherche employeur, code SOC, État, année fiscale, plage de salaires, statut, intitulé). Une table virtuelle FTS5 donne une recherche sous la milliseconde sur le nom d'employeur, l'intitulé et la ville. Taille totale environ 4 Go. L'ETL tourne en 30 à 60 minutes sur un laptop pour l'historique complet, ou 2 minutes pour une mise à jour trimestrielle. Le prochain arc commence déjà : FY2025 et FY2026 Q1 arrivent, et l'audit de colonnes repart.
Questions fréquentes
Pourquoi SQLite et pas PostgreSQL ?
Charge lecture lourde, un seul écrivain, pas de contention en écriture. SQLite gère 9 millions de lignes avec les bons index. Pas de processus serveur, pas de pool de connexions, pas de saut réseau. La base entière est un fichier qu'on expédie, versionne ou échange en quelques secondes. Pour cette forme précise de charge, elle bat Postgres en latence et ne coûte rien à héberger.
Combien de temps prend l'ETL complet ?
30 à 60 minutes sur un MacBook Pro 2024 pour 17 ans de données. Les plus gros fichiers (FY2019 à 260 colonnes, FY2024 avec trimestres complets) dominent le temps. Les mises à jour trimestrielles ajoutent environ 300k lignes en moins de 2 minutes car elles ne touchent qu'un fichier. Les vrais leviers : lectures Excel ciblées sur colonnes, opérations pandas vectorisées, insertions SQLite par lots.
Quel est le plus dur dans ce type de pipeline ?
Savoir quand nullifier versus réparer versus supprimer. Supprimer une ligne est toujours le choix le plus sûr pour la correction, mais ça cache l'échelle. Réparer est risqué car chaque correction embarque un jugement. Nullifier préserve la ligne tout en marquant le champ comme inutilisable. Le bon défaut est presque toujours de nullifier, avec les suppressions réservées aux lignes sans clé primaire.
Cette approche est-elle réutilisable sur d'autres données publiques ?
Oui. Données brevets USPTO, dépôts SEC EDGAR, extraits ACS du Census, dossiers d'émissions EPA ont tous la même forme : dérive de schéma multi-époques, renommages partiels, valeurs parasites, dégâts Excel. Les ingrédients sont toujours un dictionnaire d'alias de colonnes, des tables de normalisation d'unités, de la détection de motifs pour les valeurs parasites, et une étape QA séparée qui valide la sortie en boîte noire.
Besoin d'aide pour votre site ?
Je conçois des sites rapides et bien référencés pour les entreprises qui prennent leur présence en ligne au sérieux.