Lifebit logo
BlogTechnologyHow to Query an OMOP Database: SQL Patterns for Research

How to Query an OMOP Database: SQL Patterns for Research

Detailed close-up of blue soap foam showcasing abstract geometric patterns and texture.
Photo by Antonio Friedemann on Pexels

To query an OMOP database, you work from standard concepts rather than raw source codes: find the concept IDs for your clinical idea in the vocabulary tables, expand them with the concept hierarchy, then join those IDs to the clinical event tables through the person table. Once you follow that pattern, the same SQL runs on any site that uses the OMOP Common Data Model, which is what makes multi-site research practical.

Why querying OMOP matters now

More health systems, biobanks and national programs convert their records to the OMOP Common Data Model (CDM) each year because it lets one analysis run in many places. The model only pays off if researchers can write queries that respect its logic. Teams that treat an OMOP database like any other relational store, filtering on local codes or free text, end up with cohorts that miss patients or double count them.

The second reason is governance. In a Trusted Research Environment, queries often run where the data lives and only approved results come back. That means a query has to be correct the first time it is reviewed, and it has to be portable across sites with different source systems. For a full overview of the model itself, see our OMOP CDM guide. This article focuses on the practical question of how to query it.

The tables you will touch in almost every query

OMOP CDM v5.4 has dozens of tables, but most research questions use a small core. Knowing what each one holds saves hours of exploration.

Person and observation period

The person table holds one row per patient with year of birth, gender, race and ethnicity concepts. The observation_period table records the spans of time during which a person’s data is expected to be captured. Always check observation periods before you claim that a patient had no diagnosis or no prescription: absence of a record outside an observation period means nothing.

Clinical event tables

Conditions sit in condition_occurrence, medications in drug_exposure, procedures in procedure_occurrence, lab results and vital signs in measurement, and everything else that does not fit neatly in observation. Visits live in visit_occurrence, and each event can point to the visit where it was recorded. Deaths have their own table.

Vocabulary tables

The concept table lists every code from every vocabulary with a concept_id, its domain, its vocabulary and whether it is a standard concept. The concept_relationship table maps source codes to standard ones, and concept_ancestor stores the full hierarchy so you can pull every descendant of a concept in one join. These three tables are the key to writing good OMOP queries.

The core query pattern, step by step

Almost every OMOP cohort query follows the same four steps. Write them in this order and your SQL stays readable and reviewable.

Step 1: Find the standard concept

Start in the concept table. Search for your clinical idea by name and filter to standard_concept = 'S' and the right domain, such as Condition or Drug. Conditions usually map to SNOMED CT, drugs to RxNorm and measurements to LOINC. Record the concept_id you choose and the reason, because reviewers will ask.

Step 2: Expand with the hierarchy

A single concept rarely captures a clinical idea. Join concept_ancestor on ancestor_concept_id to collect every descendant, so a query for a broad condition also finds its specific subtypes. For drugs, expanding from an ingredient catches every product and strength that contains it.

Step 3: Join to the event table

Join the expanded concept set to the event table on its standard concept column, for example condition_concept_id or drug_concept_id. Do not filter on condition_source_value: source values differ between sites, and filtering on them breaks portability.

Step 4: Apply time and observation rules

Restrict events to dates inside each person’s observation period, define an index date (often the first qualifying event per person), and apply washout or follow-up windows relative to that date. Then join to the person table for demographics.

Common mistakes and how to avoid them

The same handful of errors appear again and again in OMOP code reviews.

  • Querying source codes instead of standard concepts. It works at one site and fails at the next.
  • Forgetting descendants. Querying one concept without concept_ancestor misses most of the relevant records.
  • Ignoring observation periods. Patients look healthy simply because they were not observed.
  • Counting rows, not people. One person can have many condition records. Count distinct person_id.
  • Mixing domains. Some codes map to a different domain than expected, for example a lab-like concept stored as an observation. Check the domain of each concept you use.
  • Hard-coding concept IDs without documentation. Keep a concept set file with names, IDs and the vocabulary version used.

Ad hoc SQL versus shared tools: a comparison

You can query an OMOP database with hand-written SQL, with open tools from the OHDSI community, or through a governed platform. Each has a place.

DimensionHand-written SQLOHDSI tools (ATLAS, HADES packages)Federated TRE workflow
Best forQuick exploration, data checksStandard cohort and characterization studiesMulti-site studies on sensitive data
PortabilityDepends on author disciplineHigh: definitions are shared as JSON or codeHigh: one query runs at every node
Concept handlingManual joins to vocabulary tablesConcept sets built in a visual editorConcept sets reused across sites
Review and auditManual code reviewVersioned definitionsLogged runs plus output checks before release
Data movementAnalyst needs direct database accessRuns where the database isData never leaves the source; only approved results return
Skills neededStrong SQL and CDM knowledgeCDM knowledge, less SQLCDM knowledge plus the platform’s workflow

Many teams combine all three: SQL to check data quality, OHDSI definitions to make cohorts reusable, and a federated workflow to run them across sites.

Running OMOP queries across many sites

The real promise of OMOP is that one query can answer a question across hospitals, countries or biobanks. In practice, that raises two problems: each site keeps its own database, and many sites cannot let raw records out. A federated approach solves both. The analysis is sent to each site, runs against the local OMOP database, and only aggregate results that pass an output check come back. This is the model behind a Trusted Research Environment built for federation.

For multi-site work, a few habits make queries travel well:

  • Use only standard concepts and the CDM’s own columns.
  • Avoid database-specific functions where a portable form exists, or use a translation layer such as the OHDSI SqlRender package.
  • Agree on a vocabulary version across sites before a study starts.
  • Apply small-cell rules to counts so results can be released safely.

Before queries can travel at all, each site has to finish its own conversion and quality checks. Our OMOP implementation checklist covers that groundwork.

Practical next steps for your team

If your team is starting to query an OMOP database, a short plan keeps the work focused.

  • Profile the database first. Count records per table, check date ranges and look at the share of events mapped to a standard concept (concept_id of zero means unmapped).
  • Build a concept set library. Store each clinical definition with its concept IDs, descendants rule and vocabulary version.
  • Write one reference query per study type. A cohort with an index date, a characterization and an incidence query cover most needs.
  • Review before release. Have a second analyst check concept choices and time windows, and apply output rules to every table.
  • Plan for federation early. Writing portable SQL from day one costs little and saves a rewrite when a second site joins.

Good OMOP queries are less about clever SQL and more about discipline with concepts, time and people. Get those right and the same analysis can run anywhere the model is in place.

Frequently asked questions

What language do you use to query an OMOP database?

SQL. OMOP is a relational model, so any SQL client works. The OHDSI community also provides R packages that generate SQL for different database engines.

Why should I use standard concepts instead of source codes?

Standard concepts are the same at every site that uses OMOP, while source codes vary. Querying standard concepts makes results comparable and lets one query run across many databases.

How do I include all subtypes of a condition?

Join the concept_ancestor table on ancestor_concept_id. It returns every descendant concept, so your query catches specific subtypes as well as the broad condition.

What does a concept_id of zero mean?

It means the source record could not be mapped to a standard concept. A high share of zeros in a table is a sign that the conversion needs more mapping work before analysis.

Can I query OMOP data without moving it?

Yes. In a federated setup the query travels to each site, runs on the local database and only approved aggregate results come back, so the underlying records stay where they are.

Do I need ATLAS to query OMOP?

No. ATLAS is a helpful visual tool for building cohorts, but hand-written SQL and R packages work too. Many teams use ATLAS to design definitions and SQL to check data quality.