Construire un ETL OMOP

L’ETL OMOP : une démarche progressive

Quand on découvre OMOP, on peut se représenter l’ETL comme un chantier de plusieurs années visant à standardiser l’intégralité du dictionnaire local avant de pouvoir exploiter quoi que ce soit. Cette vision est légitime — elle reflète d’ailleurs ce qu’ont effectivement vécu plusieurs équipes pionnières — mais elle n’est pas la seule.

Une autre approche, plus incrémentale, permet de démarrer un premier ETL OMOP utile en quelques semaines, puis d’enrichir progressivement la couverture au fil des projets. Cette démarche s’intègre dans le flux de travail habituel d’une équipe d’entrepôt de données, sans bouleverser les habitudes. Elle repose sur deux principes :

1. Procéder projet par projet. Pour une étude donnée, vous avez besoin de quelques dizaines de variables. Vous n’alignez que celles-ci ; les autres concepts attendront un autre projet, qui réutilisera ce qui aura déjà été aligné.
2. L’alignement sémantique d’abord, le reste suit mécaniquement. Une fois la table SOURCE_TO_CONCEPT_MAP produite, l’ETL devient surtout du routage : on regarde le domain_id de chaque concept standard, et il indique directement dans quelle table OMOP atterrit la donnée.

Une conséquence directe de cette approche : la base OMOP que vous produisez n’aura pas forcément toutes les tables du modèle. Vous remplirez celles qui correspondent aux concepts effectivement alignés pour le projet — typiquement PERSON, VISIT_OCCURRENCE, plus quelques tables d’événements (CONDITION_OCCURRENCE, MEASUREMENT, DRUG_EXPOSURE…). Les autres (DEVICE_EXPOSURE, SPECIMEN, NOTE…) resteront vides tant qu’un projet ne les requiert pas. Ce périmètre réduit est suffisant pour le projet en cours : les analyses ne s’appuieront que sur les tables effectivement peuplées, et c’est ce qui compte pour aboutir.

Cet article suit cette démarche progressive. On présente d’abord la partie sémantique (la plus exigeante, qui mobilise les experts du domaine), puis le routage par domain_id, et enfin on déroule un exemple immersif sur MIMIC-IV : vous exécuterez vous-même le SQL dans votre navigateur, étape par étape, pour bâtir une mini-base OMOP.

L’alignement sémantique, cœur du travail

L’alignement sémantique consiste à relier chaque code local (un itemid MIMIC, un identifiant de laboratoire interne, un libellé propre à un établissement) à un concept standard OMOP : un identifiant LOINC pour une mesure biologique, un SNOMED CT pour un diagnostic ou une observation, un RxNorm pour un médicament. Le résultat tient dans un fichier CSV : la fameuse SOURCE_TO_CONCEPT_MAP.

Cette étape est la plus chronophage de tout l’ETL. Elle s’appuie sur les experts du domaine : biologistes pour les paramètres de laboratoire, pharmaciens pour les médicaments, cliniciens (réanimateurs, internistes, urgentistes…) pour les diagnostics, observations et procédures. La qualité de l’alignement détermine la qualité de tout ce qui suivra — c’est là qu’il faut prendre le temps nécessaire.

Le module d’alignement de Linkr

Pour accompagner ce travail, Linkr propose un module dédié — l’Alignement de concepts. La création d’un projet d’alignement se fait en quelques clics, en important un fichier source (CSV, Parquet…) listant les concepts à aligner. Lors de cet import, Linkr propose une étape de paramétrage où vous choisissez les colonnes du fichier qui correspondent au code local, au libellé, à la catégorie, à la fréquence et aux métadonnées JSON :

Une fois le projet créé, l’éditeur d’alignements s’ouvre en deux panneaux : à gauche les concepts source, à droite les candidats standards :

L’alignement assisté par IA agentique

L’alignement à la main reste long. Pour gagner du temps sur la première passe, Linkr s’intègre avec Claude Code via un skill dédié concept-mapping. Un agent traite les concepts source par lots, propose pour chacun les meilleurs candidats standards, et produit un score combiné :

Quatre familles de méthodes contribuent à ce score :

• Syntaxique — similarité de chaînes entre le libellé local et les libellés standards (et leurs synonymes).
• Sémantique — distance entre embeddings biomédicaux (BioLORD-2023-M), calculée hors ligne sur l’ensemble du vocabulaire OMOP.
• Statistique (à venir) — comparaison des distributions de valeurs source/cible pour les concepts numériques.
• Agentique — un agent LLM examine les meilleurs candidats, applique du raisonnement clinique (unités cohérentes ? bonne granularité ? bon domaine ?) et propose une équivalence (exactMatch, closeMatch, broadMatch, narrowMatch) avec une justification écrite.

Chaque suggestion reste à valider ou corriger explicitement dans l’éditeur — l’IA prépare le terrain, la décision finale revient toujours à l’évaluateur. La page Suggestions détaille tout ce mécanisme.

Métadonnées et traçabilité

Un alignement de concepts n’est pas qu’une table de correspondance. C’est aussi un dossier de traçabilité : qui a aligné quoi, quand, sur la base de quel raisonnement, avec quelle confiance, et quels commentaires ont été échangés au cours de la revue. Ces métadonnées sont précieuses pour l’équipe qui exploitera ensuite les données. Linkr les conserve avec chaque alignement et peut les exposer côté concept source — distributions, statistiques, propriétés, graphiques temporels :

La structure JSON attendue est documentée sur la page Éditeur d’alignements. Elle accepte statistics (min/médiane/max/moyenne…), distributions (graphiques en barres, secteurs ou courbes), properties (paires clé/valeur libres) et metadata (métadonnées scalaires). Le tout sert à l’évaluateur pour valider un alignement en connaissance de cause.

Côté traçabilité, chaque alignement porte sa fiche de détail : libellés source et cible, statistiques, équivalence SKOS, statut, méthode, dates, et la liste des avis posés par les relecteurs. C’est cette fiche qui constitue le dossier complet d’un alignement, consultable à tout moment depuis la page Évaluation.

Le routage par domain_id

Une fois SOURCE_TO_CONCEPT_MAP produite, le reste de l’ETL devient surtout mécanique. Pour chaque ligne de donnée source, on regarde :

1. Quel est le concept standard OMOP associé (via la jointure SOURCE_TO_CONCEPT_MAP) ?
2. Quel est son domain_id ?

Et le domain_id détermine directement la table OMOP de destination :

Tout ce qui ne tombe dans aucune de ces tables atterrit par convention dans OBSERVATION — la table générique d’accueil prévue par OMOP pour ces cas. La logique d’implémentation devient alors la même pour toutes les tables d’événements : extraire les lignes source, joindre sur SOURCE_TO_CONCEPT_MAP, filtrer par domain_id du target, insérer dans la table cible avec les _concept_id standards, les _source_concept_id custom et les _source_value pour la traçabilité.

Construire un ETL MIMIC-IV → OMOP, pas à pas

Place à la pratique. On va construire ensemble une mini-base OMOP à partir de MIMIC-IV, en exécutant le SQL dans votre navigateur (via DuckDB-Wasm). Les données source sont celles de la démo publique MIMIC-IV — 100 patients, leurs séjours, leurs mesures, leurs prescriptions.

Avant d’attaquer le SQL, voici les cinq étapes de la démarche :

Étape A — Extraire les concepts source

La toute première étape de tout ETL OMOP n’est pas un INSERT : c’est l’inventaire de ce qu’il y a à aligner. Pour notre périmètre (démographie, types de visite, parcours, diagnostics, mesures et médicaments), on extrait depuis les tables MIMIC les concepts source distincts, avec leurs fréquences (nombre de lignes, nombre de patients distincts) et quelques métadonnées (unités, catégories…) qui aideront ensuite l’évaluateur. La logique d’extraction tient en une requête SQL par catégorie :

Voici un extrait représentatif du SQL d’extraction pour les paramètres biologiques de labevents. La requête calcule en une passe les statistiques descriptives, l’histogramme, la distribution par sexe, la fréquence typique des mesures et la distribution temporelle — toutes assemblées dans un objet JSON unique qui suit le format attendu par Linkr (les autres catégories — chartevents, diagnoses_icd, transfers, admissions, prescriptions — suivent un patron très proche, ajusté selon que le concept est numérique ou catégoriel) :

-- Paramètres biologiques (labevents) avec info_json riche
WITH lab_stats AS (
  SELECT
    le.itemid,
    COUNT(*)                                       AS record_count,
    COUNT(DISTINCT le.subject_id)                  AS patient_count,
    MIN(le.valuenum)                               AS v_min,
    MAX(le.valuenum)                               AS v_max,
    AVG(le.valuenum)                               AS v_mean,
    STDDEV(le.valuenum)                            AS v_sd,
    QUANTILE_CONT(le.valuenum, 0.05)               AS p5,
    QUANTILE_CONT(le.valuenum, 0.25)               AS p25,
    QUANTILE_CONT(le.valuenum, 0.50)               AS p50,
    QUANTILE_CONT(le.valuenum, 0.75)               AS p75,
    QUANTILE_CONT(le.valuenum, 0.95)               AS p95
  FROM labevents le
  WHERE le.itemid IN (50971, 50983, 50912, 50882, 51265, 51222, 51301, 50931)
  GROUP BY le.itemid
)
SELECT
  'MIMIC_LABITEMS'                                 AS vocabulary,
  'Biologie'                                       AS category,
  CAST(le.itemid AS VARCHAR)                       AS concept_code,
  ANY_VALUE(dli.label)                             AS concept_name,
  ANY_VALUE(s.record_count)                        AS record_count,
  ANY_VALUE(s.patient_count)                       AS patient_count,
  -- Compose the JSON metadata expected by Linkr's mapping editor
  json_object(
    'full_name',  'Lab test / ' || ANY_VALUE(dli.label),
    'data_source', 'MIMIC-IV',
    'data_types',  'numeric',
    'unit',        ANY_VALUE(le.valueuom),
    'numeric_data', json_object(
      'min',    ANY_VALUE(s.v_min),
      'p5',     ANY_VALUE(s.p5),
      'p25',    ANY_VALUE(s.p25),
      'median', ANY_VALUE(s.p50),
      'mean',   ANY_VALUE(s.v_mean),
      'p75',    ANY_VALUE(s.p75),
      'p95',    ANY_VALUE(s.p95),
      'max',    ANY_VALUE(s.v_max),
      'sd',     ANY_VALUE(s.v_sd)
    )
    -- + histogram, categorical_data (sex), temporal_distribution
    -- (assemblés via des sous-requêtes du même genre)
  )                                                AS info_json
FROM labevents le
JOIN d_labitems dli ON dli.itemid = le.itemid
JOIN lab_stats s    ON s.itemid   = le.itemid
WHERE le.itemid IN (50971, 50983, 50912, 50882, 51265, 51222, 51301, 50931)
GROUP BY le.itemid;

La colonne info_json qui en résulte suit la structure documentée dans la page Éditeur d’alignements, que Linkr lit pour afficher la fiche détaillée du concept source. Pour un signe vital comme la fréquence cardiaque, le résultat ressemble à :

{
  "full_name": "Vital sign / Routine Vital Signs / Heart Rate",
  "data_source": "MIMIC-IV",
  "data_types": "numeric",
  "unit": "bpm",
  "missing_rate": 0.0,
  "numeric_data": {
    "min": 0.0, "p5": 62.0, "p25": 78.0, "median": 90.0, "mean": 91.12,
    "p75": 104.0, "p95": 122.0, "max": 200.0, "sd": 18.69
  },
  "histogram": [
    { "x": 10.0, "count": 3 },    { "x": 30.0, "count": 12 },
    { "x": 50.0, "count": 454 },  { "x": 70.0, "count": 3313 },
    { "x": 90.0, "count": 5555 }, { "x": 110.0, "count": 3685 },
    { "x": 130.0, "count": 764 }, { "x": 150.0, "count": 117 }
  ],
  "categorical_data": [
    { "category": "Female", "count": 6284, "percentage": 45.2 },
    { "category": "Male",   "count": 7629, "percentage": 54.8 }
  ],
  "temporal_distribution": {
    "start_date": "2110-04-11", "end_date": "2201-12-13",
    "by_year": [ { "year": 2110, "percentage": 6.5 }, … ]
  },
  "measurement_frequency": "every 1 hours"
}

Le résultat final est un CSV avec une ligne par concept source — pour notre périmètre, 46 concepts au total (2 sexes + 7 types d’admission + 8 unités + 10 diagnostics + 8 vitaux + 8 paramètres bio + 3 médicaments).

Pour aller plus loin, ouvrez la démo Linkr et :

1. Créez un nouveau projet d’alignement (voir la doc Projets d’alignement).
2. Importez le fichier source-concepts.csv ci-dessus, en mappant les colonnes selon les paramètres d’import présentés plus haut.
3. Procédez à l’alignement concept par concept dans l’éditeur — manuel, ou avec l’assistance IA via la page Suggestions.

Pour les besoins de la suite, on saute cette étape et on récupère directement la table d’alignement déjà produite.

Étape B — Récupérer la table d’alignement

On suppose ici l’alignement bouclé : voici le fichier final, prêt à être chargé dans la base OMOP.

46 lignes, 9 colonnes au total (le détail complet est dans la référence de la table source_to_concept_map). Trois lignes représentatives, dans l’ordre des colonnes du schéma :

Notez les source_concept_id dans la plage custom OMOP (≥ 2 milliards) et les target_concept_id qui pointent vers les concepts standards.

Étape C — Charger les vocabulaires de référence dans la base

Avant d’écrire les INSERT métier, la base OMOP cible doit contenir le dictionnaire des concepts standards — typiquement les tables CONCEPT, CONCEPT_RELATIONSHIP, CONCEPT_SYNONYM, CONCEPT_ANCESTOR, VOCABULARY, DOMAIN. On les récupère depuis ATHENA (compte gratuit nécessaire) en cochant les vocabulaires utiles à notre périmètre (SNOMED CT, LOINC, RxNorm, ICD10CM, UCUM…). Le téléchargement renvoie un ZIP de fichiers CSV à charger via un COPY ou un INSERT INTO ... SELECT FROM read_csv(...).

Une fois ces vocabulaires en place, on charge également notre fichier source_to_concept_map.csv dans la table éponyme, et on ajoute les concepts custom (les source_concept_id ≥ 2 milliards de notre STCM) dans la table CONCEPT — c’est ce qui permettra aux _source_concept_id de nos lignes d’événements de pointer vers des entrées valides de la base.

Étape D — Peupler les tables OMOP

Place à l’ETL proprement dit. Les données MIMIC raw et la table d’alignement sont déjà chargées dans une base DuckDB qui tourne dans votre navigateur. Les tables OMOP cibles (PERSON, VISIT_OCCURRENCE, VISIT_DETAIL, MEASUREMENT, CONDITION_OCCURRENCE, OBSERVATION, DRUG_EXPOSURE) sont créées vides au schéma v5.4.

Trois tables sont peuplées en premier — chacune avec son propre INSERT :

• PERSON depuis patients (gender, year of birth dérivé de anchor_year - anchor_age).
• VISIT_OCCURRENCE depuis admissions (un séjour = un hadm_id).
• VISIT_DETAIL depuis transfers (un passage en unité de soins = un transfer_id).

Sur MIMIC, les vocabulaires sources sont cloisonnés par domaine (MIMIC_CHARTEVENTS → Measurement, MIMIC_DRUG → Drug, MIMIC_ICD10 → Condition…) — on pourrait théoriquement écrire un INSERT par vocabulaire en se reposant sur ce cloisonnement.

Cependant, pour rester généraliste et coller à l’approche décrite plus haut, on prépare une CTE matérialisée mapped_events qui unifie les sources d’événements (diagnostics, signes vitaux, biologies) et résout le domain_id du concept cible via CONCEPT. On filtre ensuite cette CTE par target_domain_id pour dispatcher vers la bonne table OMOP :

• target_domain_id = 'Measurement' → MEASUREMENT
• target_domain_id = 'Condition' → CONDITION_OCCURRENCE
• Tout le reste → OBSERVATION (table de repli par défaut)

PROCEDURE_OCCURRENCE, DEVICE_EXPOSURE, SPECIMEN, NOTE… suivent exactement la même logique — il suffit d’ajouter une branche WHERE target_domain_id = '…' ; on les laisse de côté ici, on n’a pas de concept correspondant dans notre périmètre.

DRUG_EXPOSURE est traitée à part : ses sources et ses spécificités (dose, route, unité) ne se prêtent pas au même routeur générique.

Chaque table OMOP a sa propre section ci-dessous, avec sa requête INSERT INTO prête à l’emploi suivie d’un SELECT qui affiche un aperçu du résultat. Pour exécuter une requête, cliquez sur Exécuter (ou utilisez le raccourci ⌘ / Ctrl + Entrée). Le bouton Réinitialiser la requête au-dessus de chaque éditeur remet la version d’origine en un clic. Lancez les requêtes dans l’ordre : mapped_events doit exister avant les INSERT de routage, et PERSON / VISIT_OCCURRENCE doivent exister avant DRUG_EXPOSURE.

Le pattern est toujours le même : JOIN sur source_to_concept_map, INSERT dans la table cible, COALESCE(..., 0) pour les codes non alignés afin de ne pas perdre les lignes, et les colonnes _source_value et _source_concept_id conservent la traçabilité vers les données d’origine.

Étape E — Valider la qualité de la base produite

Une fois les INSERT bouclés, la base OMOP existe. Mais elle peut contenir des incohérences silencieuses : dates aberrantes, concepts non standards là où on attend un standard, champs obligatoires nuls. Avant toute utilisation pour la recherche, il faut mesurer la qualité.

Deux outils OHDSI sont conçus pour ça :

• ACHILLES — un package R qui produit un rapport synthétique de caractérisation : nombre de patients, distributions d’âge, prévalences des conditions, fréquences des médicaments, distributions des mesures biologiques. Excellent pour prendre la mesure de ce que contient la base — et repérer rapidement les anomalies grossières.
• Data Quality Dashboard (DQD) — environ 4 000 contrôles automatisés répartis en trois axes : conformité du schéma, complétude des champs obligatoires, plausibilité des valeurs. Le rapport HTML interactif permet de prioriser les corrections.

À l’usage, le DQD remonte presque toujours des centaines d’anomalies sur une base fraîchement produite. La plupart sont mineures (concepts non standards, dates manquantes sur des événements optionnels…), quelques-unes pointent de vrais bugs dans l’ETL. C’est un cycle itératif : on lance, on corrige, on relance, jusqu’à un niveau de qualité jugé suffisant pour la diffusion.

Et après ?

Une fois votre première base OMOP en place, le projet suivant réutilisera les alignements existants et n’ajoutera que quelques nouveaux concepts. Au fil des projets, votre couverture du dictionnaire local devient conséquente — sans avoir eu à tout aligner en une seule fois.