OMOP CDM Method Patterns for RWE

In plain language

The OMOP Common Data Model (CDM) is a standardized blueprint that reorganizes messy, site-specific patient data — claims, electronic health records, registries — into the same table structure and shared medical vocabulary at every participating site. Because every hospital or insurer stores data differently, researchers could not run the same analysis on ten databases without rewriting the code ten times; the CDM solves that problem by translating all source codes (diagnosis codes, drug codes, lab codes) into one universal language before the analysis begins. Once data live in the CDM, a single set of query code can answer the same question across dozens of databases simultaneously, with only summary results — never individual patient records — leaving each site.

OMOP CDM method patterns

are the conventional ways an RWE study is operationalized once raw claims, EHR, or registry data have been transformed (ETL'd) into the Observational Medical Outcomes Partnership Common Data Model. The CDM is a person-centric relational schema in which every clinical event is mapped to a standard concept in the OHDSI vocabulary (RxNorm for drugs, SNOMED for conditions, LOINC for measurements) and every event lives inside an OBSERVATION_PERIOD that declares when the person was observable. The "patterns" are the reusable templates that turn a protocol — eligibility, exposure, outcome, time-at-risk, covariates — into SQL/analytic code that is portable: a study author writes the cohort logic once, and a remote site executes it against its own CDM instance and returns only aggregate results. This is the engine behind the OHDSI network and behind FDA Sentinel-style distributed analytics. The CDM is a data structure and convention, not an estimator; the validity of any result still rests entirely on the design (active comparator, new-user, time-zero alignment) layered on top of it.

Core conceptual distinction

— three CDM constructs do the heavy lifting and are routinely confused, with material consequences. (1) Raw occurrence vs era: `DRUG_EXPOSURE` and `CONDITION_OCCURRENCE` store one row per dispensing/diagnosis, whereas `DRUG_ERA` and `CONDITION_ERA` collapse those rows into continuous exposed/diseased episodes using a persistence-gap rule (the default drug-era persistence window is 30 days). Eras are convenient for "ever on drug" or chronic-disease phenotypes but bake in a gap assumption you did not choose; for a strict new-user, time-varying analysis you usually rebuild episodes from `DRUG_EXPOSURE` with your own `days_supply` + grace-period logic rather than trusting the default era. (2) Standard vs source concept: every event has both a `_source_concept_id` (the original ICD/NDC code) and a mapped `_concept_id` (the standard SNOMED/RxNorm concept). Cohorts must be defined on standard concepts and expanded through `CONCEPT_ANCESTOR` (e.g., an RxNorm ingredient resolves to all descendant clinical-drug and branded NDCs) so the same concept set behaves identically across sites — querying source codes directly destroys portability. (3) Observability vs data presence: absence of a row is informative only inside an OBSERVATION_PERIOD; outside it, "no record" means unobserved, not "did not happen." Washout, eligibility, and censoring must all be bounded by `observation_period_start_date` / `observation_period_end_date`, or you will mistake enrollment gaps for clinical absence.

Pros, cons, and trade-offs

- vs bespoke per-database analytic code (raw claims/EHR scripts): CDM patterns give you provenance, portability, and a shared vocabulary, so a protocol can be run by 10 data partners with no PHI leaving each site and results pooled by meta-analysis (the OHDSI / FDA Sentinel model). Cost: the ETL is lossy and opinionated — source-data nuance (claim adjustment flags, payer type, lab units edge cases) can be dropped or mis-mapped, and you inherit every ETL decision the site made. Prefer CDM patterns for multi-database, reproducible, or regulatory-grade studies; prefer bespoke code only when a single source has irreplaceable detail the CDM cannot represent and portability is not a goal. - vs i2b2 / PCORnet / Sentinel CDMs: OMOP's distinguishing feature is full vocabulary standardization to standard concepts with `CONCEPT_ANCESTOR` hierarchies, which makes concept sets transportable; PCORnet and Sentinel keep more source-code-native structures that are simpler to ETL but harder to query identically across sites. Prefer OMOP when cross-network harmonization and large-scale analytics (PLP, LEGEND-style) matter. - default DRUG_ERA/CONDITION_ERA vs hand-built episodes: eras are fast and standardized but the fixed persistence gap is rarely the clinically correct one and can fabricate or break exposure continuity. Prefer hand-built episodes for any time-varying or new-user safety analysis; use default eras only for coarse "ever-exposed"/prevalence characterization.

When to use

— multi-database or network RWE where the same protocol must execute at sites that cannot share row-level data; OHDSI tooling pipelines (ATLAS/Capr cohort definitions, CohortMethod, PatientLevelPrediction, empirical calibration with negative controls); regulatory submissions and HTA dossiers that demand transparent, reproducible, code-shareable cohort logic; any study where a standardized vocabulary and `CONCEPT_ANCESTOR` expansion materially improves phenotype portability. The CDM is also the natural substrate for target-trial emulation at scale because eligibility, treatment assignment, and time-zero can all be expressed as portable SQL over the standard tables.

When NOT to use — and when it is actively misleading or dangerous

- You have not validated the local ETL. Treating a CDM instance as ground truth without source-to-standard mapping review and a data-quality pass (e.g., OHDSI Achilles/DataQualityDashboard) is dangerous: a missing or wrong vocabulary map silently empties or inflates a concept set, and the analysis runs cleanly while measuring the wrong thing. - The clinical question depends on detail the ETL drops. Payer/plan type, claim line adjustments, free-text severity, units-of-measure subtleties, or device-specific NDC nuance may not survive the transform; forcing the CDM here yields confident, wrong answers. - You trust default eras for a time-varying estimand. The 30-day drug-era persistence gap is not your protocol's grace period; using it for as-treated safety analysis silently mis-defines exposure and time-at-risk. - You query source concepts to "be safe." Bypassing standard-concept + `CONCEPT_ANCESTOR` logic breaks portability and reintroduces the cross-site heterogeneity the CDM exists to remove. - Out-of-observation time leaks into the analysis. Counting person-time or "absence of outcome" outside OBSERVATION_PERIOD manufactures immortal time and misclassifies unobserved as event-free.

Data-source operational depth

- Claims (FFS vs MA/commercial): ETL maps NDC→RxNorm and ICD→SNOMED; `DRUG_EXPOSURE.days_supply` and `drug_exposure_start_date` drive era and time-at-risk logic. OBSERVATION_PERIOD is derived from enrollment spans, so it is only trustworthy when enrollment is complete: Medicare Advantage person-time lacks fee-for-service claims, so an MA-only OBSERVATION_PERIOD looks observable but is missing drug and procedure events — restrict to enrollees with full Parts A/B/D (or commercial medical+pharmacy) and treat MA-only spans as unobserved. Mail-order 90-day fills and free samples distort `days_supply`; capitated/bundled care drops claims and breaks era continuity. - EHR: Events are visit-driven, so OBSERVATION_PERIOD (often defined from first-to-last visit) overstates true observability — a patient who seeks care elsewhere is differentially "absent." Drugs may be orders/prescriptions, not dispensings, so `DRUG_EXPOSURE` overcounts initiation unless linked to fills; conditions in `CONDITION_OCCURRENCE` mix problem-list, encounter, and resolved diagnoses, inflating `CONDITION_ERA`. Phenotypes should add measurement/lab and visit anchors, not codes alone. - Registry: Strong, often adjudicated outcomes and disease severity (e.g., cancer stage in a tumor registry) but sparse, intermittent OBSERVATION_PERIOD coverage and weak drug capture. Map adjudicated endpoints to standard concepts carefully; link to claims for exposure and to a death index for censoring. - Linked claims–EHR–registry: The ideal CDM substrate (EHR severity + claims completeness + adjudicated/mortality outcomes) but linkage introduces a selected subset and reconciling order vs fill vs service dates across sources must happen before time-zero assignment, or competing OBSERVATION_PERIODs will misalign follow-up. In elderly cohorts, differential competing risks (death) by exposure must be modeled, not censored away.

Worked CDM-native example

Question: incident myopathy among new users of a high-intensity statin vs a moderate-intensity statin in adults, built entirely from OMOP tables. (1) Concept sets: resolve each statin ingredient (RxNorm) to all descendants via `CONCEPT_ANCESTOR` so every clinical-drug/branded NDC maps to the correct arm; resolve the myopathy outcome to the SNOMED standard concept and its descendants. (2) Eligibility & washout: require ≥365 days of continuous OBSERVATION_PERIOD before the index drug-exposure, with no statin `DRUG_EXPOSURE` of either class in that window — this is the new-user/incident restriction expressed as "first qualifying `drug_exposure_start_date` with `observation_period_start_date` ≤ index − 365." (3) Time zero & arm: index = earliest qualifying `drug_exposure_start_date`; arm = the standard ingredient dispensed that day. (4) Exposure episodes: rebuild on-treatment time from `DRUG_EXPOSURE.days_supply` with a pre-specified grace period rather than the default 30-day DRUG_ERA, because the default gap is not the protocol's. (5) Covariates: measured only in [index − 365, index] from `CONDITION_OCCURRENCE`, `DRUG_EXPOSURE`, `MEASUREMENT`, and `VISIT_OCCURRENCE`, feeding a (high-dimensional) propensity score. (6) Time-at-risk & censoring: from index to first outcome `condition_occurrence`, censoring at `observation_period_end_date`, death, end of data, and (as-treated) discontinuation or switch. The identical SQL/cohort definition is then shipped to each network site; only aggregate, calibrated estimates (with negative-control empirical calibration) come back.

Worked example

Scenario

Imagine you want to study three different clinical questions — who received a statin, who was diagnosed with diabetes, and how many outpatient visits a patient had — and you need the analysis to run identically at five hospitals that each store data in a different format. After each hospital has converted its data to the OMOP CDM, every question maps to the same named table and the same standard concept codes, so the query code you write once will return valid results from all five sites.

Dataset

How each analytic question maps to OMOP CDM tables and the standard vocabulary concept that drives the query.

Steps

• Raw hospital or insurer data arrives with site-specific codes — ICD-10 diagnosis codes, NDC drug codes, CPT procedure codes — that differ in format across sites; the ETL (extract-transform-load) process translates all of them into the standard OMOP concepts before the analysis begins.

• Every patient gets a row in the PERSON table and one or more rows in OBSERVATION_PERIOD marking the dates they were enrolled; any clinical event (drug, diagnosis, visit, lab) that falls outside these dates must be treated as unobserved, not as evidence the event never happened.

• To find all patients who took a statin, you look up the RxNorm ingredient concept for atorvastatin in CONCEPT_ANCESTOR, which returns every descendant concept — Lipitor 10 mg tablets, generic atorvastatin 40 mg, mail-order 90-day supplies — so the query captures them all without listing hundreds of individual product codes.

• For each analytic need you choose the right table: DRUG_EXPOSURE or DRUG_ERA for drug use, CONDITION_OCCURRENCE or CONDITION_ERA for diagnoses, VISIT_OCCURRENCE for healthcare encounters, MEASUREMENT for labs — the table names and column names are identical at every site because all sites share the CDM structure.

• Once the query is written and validated at one site, it is sent as code (not patient data) to the other four sites, each of which runs it on its own CDM instance and returns only summary counts or aggregate statistics, keeping all individual patient information local.

Result

The same five-line SQL query asking 'how many new statin users had a prior diabetes diagnosis' executes correctly at all five hospital databases and returns comparable aggregate results — because the CDM guarantees that drug_concept_id, condition_concept_id, and observation_period mean the same thing everywhere. Portability is the deliverable: write the logic once, trust it to run anywhere data have been properly converted to OMOP.

Runnable example

import duckdb

WASHOUT_DAYS = 365             # drug-free + continuously-observed lookback defining "new user"
STUDY_INGREDIENT_ID = 0        # standard RxNorm ingredient concept_id for the study drug
COMPARATOR_INGREDIENT_ID = 0   # standard RxNorm ingredient concept_id for the active comparator

COHORT_SQL = """
WITH arm_drugs AS (  -- expand each ingredient to all descendant standard drug concepts (portable across sites)
    SELECT descendant_concept_id AS drug_concept_id, 'STUDY' AS arm
    FROM concept_ancestor WHERE ancestor_concept_id = $study_ing
    UNION ALL
    SELECT descendant_concept_id, 'COMPARATOR'
    FROM concept_ancestor WHERE ancestor_concept_id = $comp_ing
),
exposures AS (  -- every qualifying fill of either arm, tagged with its arm
    SELECT de.person_id, de.drug_exposure_start_date AS dt, ad.arm
    FROM drug_exposure de JOIN arm_drugs ad USING (drug_concept_id)
),
first_fill AS (  -- candidate time zero = earliest fill of either arm; arm from that day's drug
    SELECT person_id, dt AS index_date, arm
    FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY dt) rn FROM exposures)
    WHERE rn = 1
),
new_users AS (  -- incident restriction: no study/comparator fill in the washout window before index
    SELECT f.* FROM first_fill f
    WHERE NOT EXISTS (
        SELECT 1 FROM exposures e
        WHERE e.person_id = f.person_id
          AND e.dt <  f.index_date
          AND e.dt >= f.index_date - INTERVAL ($washout) DAY)
)
SELECT n.person_id, n.arm, n.index_date,
       n.index_date - INTERVAL ($washout) DAY AS baseline_start
FROM new_users n JOIN observation_period op USING (person_id)  -- observability must span the whole washout
WHERE op.observation_period_start_date <= n.index_date - INTERVAL ($washout) DAY
  AND op.observation_period_end_date   >= n.index_date
"""

def build_omop_new_user_cohort(con: duckdb.DuckDBPyConnection):
    return con.execute(COHORT_SQL, {
        "study_ing": STUDY_INGREDIENT_ID, "comp_ing": COMPARATOR_INGREDIENT_ID,
        "washout": WASHOUT_DAYS,
    }).df()

library(DatabaseConnector)
library(SqlRender)

buildOmopNewUserCohort <- function(connection, cdmDatabaseSchema,
                                   studyIngredient, comparatorIngredient,
                                   washoutDays = 365L, targetDialect = "postgresql") {
  sql <- "
  WITH arm_drugs AS (  -- expand ingredients to all descendant standard drugs (portable concept set)
    SELECT descendant_concept_id AS drug_concept_id, 'STUDY' AS arm
    FROM @cdm.concept_ancestor WHERE ancestor_concept_id = @study_ing
    UNION ALL
    SELECT descendant_concept_id, 'COMPARATOR'
    FROM @cdm.concept_ancestor WHERE ancestor_concept_id = @comp_ing
  ),
  exposures AS (
    SELECT de.person_id, de.drug_exposure_start_date AS dt, ad.arm
    FROM @cdm.drug_exposure de JOIN arm_drugs ad ON de.drug_concept_id = ad.drug_concept_id
  ),
  first_fill AS (
    SELECT person_id, index_date, arm FROM (
      SELECT person_id, dt AS index_date, arm,
             ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY dt) AS rn
      FROM exposures) t WHERE rn = 1
  ),
  new_users AS (  -- new-user restriction within the washout window
    SELECT f.* FROM first_fill f
    WHERE NOT EXISTS (
      SELECT 1 FROM exposures e
      WHERE e.person_id = f.person_id AND e.dt < f.index_date
        AND e.dt >= DATEADD(DAY, -@washout, f.index_date))
  )
  SELECT n.person_id, n.arm, n.index_date,
         DATEADD(DAY, -@washout, n.index_date) AS baseline_start
  FROM new_users n
  JOIN @cdm.observation_period op ON op.person_id = n.person_id
  WHERE op.observation_period_start_date <= DATEADD(DAY, -@washout, n.index_date)
    AND op.observation_period_end_date   >= n.index_date;"

  rendered <- SqlRender::render(sql, cdm = cdmDatabaseSchema,
                               study_ing = studyIngredient, comp_ing = comparatorIngredient,
                               washout = washoutDays)
  rendered <- SqlRender::translate(rendered, targetDialect = targetDialect)
  DatabaseConnector::querySql(connection, rendered)
}