Written and maintained by CASRAI Editorial Board
Published
On this page
Transforming raw electronic health record (EHR), claims, or registry tables into the OMOP Common Data Model requires both structural schema harmonization and semantic mapping of local clinical codes to standardized vocabularies. To standardize this engineering workflow, the Observational Health Data Sciences and Informatics (OHDSI) collaborative maintains an open-source extract-transform-load (ETL) toolchain centered on four tools: WhiteRabbit, Rabbit-in-a-Hat, Usagi, and the Data Quality Dashboard (DQD).
Used sequentially, these tools allow clinical data engineers and biomedical informaticians to profile source databases without exposing protected health information (PHI), generate human-readable ETL specification documents, map unstandardized local hospital codes into OMOP Standard Concepts, and execute over 3,000 automated SQL quality checks against the resulting database.
Ask CASRAI · free to tryQuestion about this guide? Ask CASRAI answers with linked sources. 2 free questions a day, no account.
How Do WhiteRabbit, Rabbit-in-a-Hat, and Usagi Work in an OMOP ETL Pipeline?
An OHDSI ETL project separates structural table mapping from vocabulary mapping across four distinct stages:
| OHDSI Tool | Pipeline Stage | Primary Input | Primary Output |
|---|---|---|---|
| WhiteRabbit | 1. Source Data Profiling | Live JDBC connection or CSV files of source EHR/claims tables | ScanReport.xlsx summarizing table row counts, column types, null fractions, and distinct value frequencies |
| Rabbit-in-a-Hat | 2. Structural ETL Design | ScanReport.xlsx + target OMOP CDM version schema |
Visual table/field mapping workspace exported as Markdown/Word ETL technical specification documents and test frameworks |
| Usagi | 3. Vocabulary Mapping | CSV of unmapped local source codes, descriptions, and frequencies | Reviewer-approved mapping file exported directly to the OMOP source_to_concept_map table |
| Data Quality Dashboard (DQD) | 4. Post-ETL Validation | Populated OMOP CDM relational database | JSON summary and interactive Shiny application scoring Conformance, Completeness, and Plausibility checks |
Stage 1 & 2: Profiling with WhiteRabbit and Designing with Rabbit-in-a-Hat
Before writing SQL or Python transformation scripts, data engineers run WhiteRabbit directly against the source database. WhiteRabbit inspects every table and column, computing summary distributions and distinct value frequency lists while configurable minimum cell-count thresholds suppress rare values (such as patient names, medical record numbers, or free-text notes) so that no PHI leaves the secure enclave in ScanReport.xlsx.
Informaticians and ETL developers then open ScanReport.xlsx inside Rabbit-in-a-Hat, an interactive graphical application displaying source tables on the left and target OMOP CDM tables (such as person, visit_occurrence, condition_occurrence, drug_exposure, and measurement) on the right. By drawing directed arrows between tables and drilling down to link source columns to target OMOP columns—recording transformation rules, lookup filters, and edge-case logic—the team generates a comprehensive ETL specification document before writing production code.
How Does Usagi Map Local EHR Codes to OMOP Standard Concepts?
While standard coding systems like ICD-10-CM, CPT-4, NDC, or LOINC can be mapped automatically during ETL by joining the OMOP concept and concept_relationship tables (following 'Maps to' relationships downloaded from OHDSI Athena), hospitals and registries frequently contain thousands of local charge-master codes, custom laboratory panel codes, or non-standard nursing flowsheet strings. Usagi is OHDSI’s desktop application designed to map these local source codes to OMOP Standard Concepts.
Usagi Indexing, Similarity Scoring, and Curation Workflow
- Building the Vocabulary Index: On first launch, Usagi reads the downloaded Athena vocabulary files (
CONCEPT.csv,CONCEPT_SYNONYM.csv, andCONCEPT_RELATIONSHIP.csv) and builds a local Apache Lucene vector index of all OMOP concept names and synonyms. - Importing Source Codes by Frequency: The user imports a CSV extract containing the local source code, the local human-readable description (and optional English translation if the source EHR is in another language), and the record frequency count from the source database.
- TF-IDF Cosine Similarity Matching: Usagi computes term-frequency / inverse-document-frequency (TF-IDF) cosine similarity scores between each source string and the indexed OMOP Standard Concepts, optionally restricted by target Domain (e.g.,
Measurement,Drug,Condition,Procedure), Concept Class, and Vocabulary (such as SNOMED CT, LOINC, or RxNorm). - Clinical Review and
source_to_concept_mapExport: Reviewers sort the source codes in descending order of frequency—so that mapping the top few hundred codes often covers over 90% of total row volume—inspect the ranked candidate concepts, set the equivalence status toAPPROVED, and export the resulting file directly in the schema of the OMOPsource_to_concept_maptable.
How Does the OHDSI Data Quality Dashboard (DQD) Validate Kahn’s Data Quality Categories?
Once the structural ETL and source_to_concept_map joins populate the target OMOP CDM tables, the R package DataQualityDashboard (DQD) executes more than 3,300 parameterized SQL checks organized according to the harmonized data quality framework established by Kahn et al. (2016):
- Conformance (Value, Relational, Computational): Verifies whether data values adhere to formal structural and syntactic constraints—for example, checking that required columns are non-null, foreign keys (`person_id`, `visit_occurrence_id`) resolve to existing parent records, and `*_concept_id` columns contain valid Standard Concepts belonging to the expected domain.
- Completeness: Measures the frequency of missing or unmapped data—for example, alerting when the percentage of rows mapped to `concept_id = 0` (unmapped concept) in `condition_occurrence`, `drug_exposure`, or `measurement` exceeds configurable tolerance thresholds.
- Plausibility (Uniqueness, Atemporal, Temporal): Evaluates clinical and biological believability—checking that clinical events do not occur before a patient’s birth date or after a recorded death date, that sex-specific diagnoses or procedures match `gender_concept_id`, and that quantitative laboratory measurement distributions fall within physiologically plausible unit ranges.
Each check is further cross-classified as either Verification (evaluated using internal database logic and expectations alone) or Validation (evaluated against external benchmarks or reference standards).
Frequently Asked Questions
Should standard ICD-10-CM or LOINC codes be mapped manually in Usagi?
No. Standard national and international vocabularies already included in OHDSI Athena (such as ICD-9-CM, ICD-10-CM, CPT-4, HCPCS, NDC, LOINC, and SNOMED CT) should be mapped directly inside the SQL ETL pipeline using the `concept` and `concept_relationship` (`relationship_id = ‘Maps to’`) tables. Usagi is specifically intended for local institutional codes, unstandardized text values, or custom survey items not present in Athena.
Where do Usagi mappings live in the OMOP Common Data Model?
Usagi exports mappings either to the standard OMOP `source_to_concept_map` table (assigning custom local concepts or mapping via `source_code` and `source_vocabulary_id` to `target_concept_id`) or to custom `concept` / `concept_relationship` entries with concept IDs above `2,000,000,000` (the reserved range for site-specific OMOP concepts).
How does the Data Quality Dashboard differ from OHDSI Achilles?
Achilles (Automated Characterization of Health Information at Large-scale Longitudinal Evidence Systems) computes descriptive summary statistics, prevalence distributions, and temporal trends across an OMOP database (visualized in Atlas), whereas the Data Quality Dashboard (DQD) evaluates explicit pass/fail data quality checks with customizable failure thresholds based on Kahn’s Conformance, Completeness, and Plausibility framework.







