OMOP-AI — Boyce Data Science

Prompt generator

Compose a copy-ready prompt for an AI assistant at each stage of the ETL. Every generated prompt includes constraints on protected health information (PHI), the OMOP conventions the answer has to respect, and an output contract, so results come back in a consistent and reviewable shape.

ETL task

Target CDM version v5.5 is non-breaking on v5.4; check which OHDSI tool versions support the new fields before relying on them

Source system for example: REDCap registry, national registry extract, patient-directed FHIR or CCDA files

Source tables or forms in scope comma separated

Target CDM tables leave blank to let the model propose

Tech stack

Project context and conventions study name, cohort notes, local conventions; no PHI and no row-level data

Never paste PHI, row-level patient data, or small cell counts into an external AI tool. Use synthetic examples, column names, and value patterns only. Generated prompts include this instruction, and enforcing it is still your responsibility.

generated_prompt.md

Choose a task and generate. The full prompt appears here.

Prompt library

Reusable prompts for OMOP work that comes around again and again. Select one to load it into the console, then copy or edit it. Square brackets show where to put your own values.

library_prompt.md

Select a prompt to load it here.

Source to target mapping record

A structured, exportable record of every field-level mapping decision, which is the artifact reviewers, collaborators, and anyone who inherits the pipeline will ask for. Export as Markdown for the ETL repository, CSV for spreadsheets, or JSON for programmatic use.

Source table or form

Source field

Source value example or pattern synthetic only

Target CDM table

Target field

Standard concept_id confirmed in Athena

Transformation logic

Decision rationale and provenance

Status

#	Source	Example	Target	concept_id	Logic	Rationale	Status
Nothing recorded yet. Fill the fields above and add the first row.

Project scaffold

A repository layout that many OMOP ETL projects converge on, with a place for mapping documentation, staged SQL, tests, data quality output, and vocabulary bookkeeping. Generates a directory tree plus a shell script that creates it.

Project name lowercase with hyphens

Primary language

Options

Data Quality Dashboard harness GitHub Actions for continuous integration MkDocs documentation site

repo_tree.txt

Generate to see the suggested tree.

Tracking the project

This toolkit records the decisions inside an ETL. The plan of record for the project itself, with an owner and a target date on every step and a sign-off at the end of each stage, lives in a separate tool.

The OMOP ETL Worksheet

The Worksheet follows a source database into the CDM across the full lifecycle, from the profiling scan and the mapping workshop through semantic mapping, architecture, development, infrastructure, the OHDSI tool stack, testing, data quality approval, documentation, and project management. Each stage asks for the decision behind the work and states what has to be true before the next stage starts. It saves to a file that reloads, and downloads as a Markdown project plan or a CSV of tasks for your tracker.

Use the two together: track the project in the Worksheet, and use the prompts, the mapping record, and the reference notes here for the work inside each stage. The Worksheet points back to this toolkit at its profiling and semantic mapping stages.

Open The OMOP ETL Worksheet

Source system profile

Record the facts about each source system once, in a structured document. The profile is the raw material for White Rabbit scans, mapping prompts, and the ETL specification, and it saves the team from rediscovering the same details later. The completed document belongs in the profiling stage of The OMOP ETL Worksheet, which is where the project record is kept.

System name

System type

Refresh cadence

Approximate patient count order of magnitude, no small cells

Date range covered

Person identifier strategy

Tables and forms inventory one per line, using: name | purpose | row grain

Known data quality issues

Governance and access notes Institutional Review Board (IRB) protocol, data use agreement (DUA) constraints, PHI handling

source_profile.md

Fill the form and generate to see the document here.

CDM table reference

Working notes on the OMOP CDM tables most projects touch in versions 5.4 and 5.5, with the required fields and the mapping decisions that give teams the most trouble. Open a table to read its notes, and verify every detail against the current OHDSI CDM specification linked in the glossary.

What changed in CDM v5.5

Version 5.5 is an incremental, non-breaking update to v5.4, with nothing removed or renamed, so a v5.4 ETL loads unchanged.

New optional fields on existing tables, including value_as_date and value_as_source_concept_id on observation, value_as_source_concept_id and unit_source_concept_id on measurement, and new visit_occurrence_id and visit_detail_id linkage fields. These give source-coded values and visit context a place to go where previously you had to drop them.
New vocabulary metadata tables for fuller bookkeeping of the vocabulary content shipped with a CDM instance.
Tighter definitions: cdm_source gains cdm_release_identifier, and its convention of one record holding the database metadata is now explicit; vocabulary.vocabulary_id gains a unique constraint.
Adoption guidance: OHDSI tools distinguish legacy support, meaning the v5.4 tables and fields, from feature support for the new v5.5 elements. Before an ETL depends on a v5.5-only field, confirm that your Data Quality Dashboard, HADES, and ATLAS versions support it, and record the decision in conventions.md.

This summary describes the specification as of September 2026. To check it against the current text, read the v5.5 specification and the official changes page, both linked in the glossary.

Vocabulary guide

How standard concepts, source concepts, and local mappings fit together, and which parts of the mapping work an AI assistant can take on.

Rules that govern every mapping

Standard concepts only in *_concept_id. Domain-appropriate standard concepts (standard_concept = 'S') go in the main concept field, the verbatim source code goes in *_source_value, and when the source code exists in the vocabularies, its concept goes in *_source_concept_id.
The domain decides the table. A source diagnosis field that maps to a concept in the Measurement domain belongs in measurement rather than condition_occurrence. Route rows by the standard concept's domain_id.
Use 'Maps to' relationships. Resolve non-standard source codes to standard concepts through the concept_relationship 'Maps to' link, never by string similarity alone.
type_concept_id records provenance. Use the standardized Type Concept vocabulary consistently across tables, for example 32817 for electronic health record data, 32851 for registry data, and 32862 for a patient filled survey.
Local codes get a source_to_concept_map. Registry-specific instruments that have no vocabulary coverage are governed through a versioned local mapping table with review sign-off. A custom concept in the 2 billion range is the last resort, used only when nothing in the vocabularies fits.
Pin the vocabulary release. Record the Athena download version in the repository, and re-run the mapping checks after each refresh, since 'Maps to' targets change between releases.

Common vocabularies by domain

Domain or use	Preferred standard vocabulary	Typical source vocabularies
Conditions	SNOMED	ICD10CM, ICD9CM, self-report instruments
Drugs	RxNorm and RxNorm Extension	NDC, free-text medication names, ATC
Measurements and labs	LOINC, with SNOMED	local lab codes, instrument items
Procedures	SNOMED, CPT4, ICD10PCS	CPT4, HCPCS, ICD10PCS
Units	UCUM	free-text units
Observations and survey answers	SNOMED, LOINC survey panels	instrument item codes
Devices	SNOMED, GUDID-derived concepts	HCPCS, free text
Visit types	Visit domain concepts	encounter type strings

How the work divides between AI and reviewers

Reasonable use of AI	Kept with a person
Drafting candidate concept lists for reviewer triage, with every candidate verified in Athena	Final concept selection and sign-off
Normalizing free-text drug strings into ingredient names a reviewer can search in RxNorm	Clinical equivalence judgments, including dose forms and combination products
Explaining domain routing for ambiguous source items	Local convention decisions and the documentation of record
Generating Usagi input files and reading Usagi output for inconsistencies	Approving any mapping below your match score threshold

Guardrails for AI in the ETL

A short operating policy you can adopt verbatim in an AI-assisted ETL project, or adapt to the rules your institution already has.

No PHI in prompts, at any point. Column names, data dictionaries, value patterns, and synthetic rows only. Small cell counts, usually taken as fewer than 11, are also off limits in external tools.
AI proposes, a named person approves. Every AI-generated mapping, transformation, or SQL statement has a status field and requires reviewer sign-off before it is implemented.
Verify concept_ids in Athena. Language models produce concept_ids that look plausible and do not exist. Treat every generated identifier as unverified until it is confirmed against the pinned vocabulary release.
Test what AI writes. Generated ETL code arrives with generated tests, and both are reviewed. Row count reconciliation, referential integrity, and data quality checks gate every load.
Log provenance. Record which artifacts were AI-assisted, along with the prompt, model, and date, in the repository. That record is what you will need for a methods section and for an audit.
Prefer reproducible pipelines over chat transcripts. Promote anything that worked in a conversation into versioned code, configuration, or documentation before the reasoning behind it is lost.

These are general practices, not a compliance opinion. Check them against your own institutional review board determination, data use agreements, and information security policy before adopting them.

Glossary and sources

Every acronym used in this tool, and the published documentation the reference notes are drawn from.

Acronyms

ATLAS: The OHDSI web application for defining cohorts and running characterization against a CDM instance.
CCDA: Consolidated Clinical Document Architecture, an HL7 document standard used for clinical summaries exchanged between systems.
CDM: Common Data Model. In this tool it always means the OMOP CDM.
CI: Continuous integration, the automated running of tests and checks when code is pushed to a repository.
CPT4, HCPCS, ICD9CM, ICD10CM, ICD10PCS, NDC, ATC: Source code systems commonly found in claims and electronic health record extracts for procedures, diagnoses, and drugs.
DQD: Data Quality Dashboard, the OHDSI R package that runs a standard set of data quality checks against a CDM instance.
DUA: Data use agreement, the contract that governs what a recipient may do with data received from another organization.
EHR: Electronic health record.
ETL: Extract, transform, load. The process of moving data out of a source system, reshaping it, and loading it into a target model.
FHIR: Fast Healthcare Interoperability Resources, the HL7 standard for exchanging health data as structured resources through an API.
GUDID: Global Unique Device Identification Database, the FDA device identifier database that some device concepts are derived from.
HADES: Health Analytics Data-to-Evidence Suite, the OHDSI collection of R packages for analysis against the CDM.
IRB: Institutional Review Board. The committee that reviews and approves research involving human participants and sets the conditions under which their data may be used.
LOINC: Logical Observation Identifiers Names and Codes, the standard for laboratory tests, measurements, and many survey instruments.
NLP: Natural language processing, the extraction of structured information from free text.
OHDSI: Observational Health Data Sciences and Informatics, the open science community that maintains the OMOP CDM, the standardized vocabularies, and the analytic tools named here.
OMOP: Observational Medical Outcomes Partnership, the program the common data model originated in and is still named for.
PHI: Protected health information, as defined under the HIPAA Privacy Rule.
PRO: Patient reported outcome, a measure completed by the patient rather than a clinician.
REDCap: Research Electronic Data Capture, a widely used platform for study and registry data collection.
RxNorm: The normalized naming system for clinical drugs maintained by the National Library of Medicine.
SNOMED: SNOMED CT, the clinical terminology used as the standard vocabulary for conditions and many procedures.
UCUM: Unified Code for Units of Measure, the standard vocabulary for units.
Usagi: The OHDSI tool that proposes candidate concept mappings for source codes and records a reviewer's decision on each one.
White Rabbit and Rabbit-in-a-Hat: The OHDSI tools that scan a source database and support drafting the source to target mapping document.

Documentation these notes are drawn from

OMOP CDM specification, including the v5.5 tables and the changes page: ohdsi.github.io/CommonDataModel
Athena, the vocabulary browser and download service: athena.ohdsi.org
The Book of OHDSI, the community reference for the model, vocabularies, and study methods: ohdsi.github.io/TheBookOfOhdsi
Data Quality Dashboard documentation: ohdsi.github.io/DataQualityDashboard
White Rabbit and Rabbit-in-a-Hat: ohdsi.github.io/WhiteRabbit
Usagi: github.com/OHDSI/Usagi
HADES, the analytic package suite: ohdsi.github.io/Hades
The OHDSI forums, where convention questions are debated and answered: forums.ohdsi.org

Links checked September 2026. The vocabulary and specification pages change between releases, so treat anything quoted in this tool as a starting point and confirm it against the current page.

About this tool

Built by Danielle Boyce at Boyce Data Science and offered free alongside the other research tools on the site. Nothing you type is transmitted anywhere: the whole tool runs in your browser, and the session file is written to your own computer when you export it.