OMOP Standardized Vocabularies (OHDSI/Athena)

In plain language

The OMOP Standardized Vocabularies are a shared translation dictionary that converts every type of medical code — diagnosis codes, drug codes, lab codes, procedure codes — into one universal numbering system called concept_ids, so that the same analysis script can run identically at hospitals and insurers across many countries. Think of it as the Rosetta Stone for healthcare data — a US insurer uses ICD-10-CM codes for diagnoses, a German sickness fund uses a different coding system, and a UK GP uses Read codes, but after the translation every institution's data use the same SNOMED concept_id for type 2 diabetes. One honest caveat: the translation is imperfect — some source codes have no mapping and become invisible to the standard analysis, and the same expression can resolve to slightly different concepts when the vocabulary is updated.

OMOP Standardized Vocabularies

are the semantic foundation of the OHDSI ecosystem and the layer that makes distributed, multi-database real-world evidence possible. Every clinical code that will ever appear in an OMOP CDM database — ICD-10-CM diagnoses, NDC drug codes, CPT procedure codes, LOINC lab codes, SNOMED concepts, RxNorm ingredients, custom EHR codes — first passes through the vocabulary layer, which assigns it a stable numeric `concept_id`, a `standard_concept` flag, a `domain_id`, and a set of `vocabulary_id`-tagged relationships. This translation happens once (in the ETL), is saved alongside the source code, and is what allows a single SQL query to run identically at a hospital in the United States, a claims database in Germany, and a registry in Japan. The vocabulary layer is distinct from every analytic CDM table it feeds — DRUG_EXPOSURE, CONDITION_OCCURRENCE, MEASUREMENT — and must be understood on its own terms before any of those tables can be queried correctly.

The five vocabulary tables and what they carry

- `CONCEPT`: the master catalog. One row per concept, with columns `concept_id` (the stable integer), `concept_name`, `domain_id` (Condition, Drug, Measurement, Procedure, Observation, Device, Spec, Type Concept, Metadata, etc.), `vocabulary_id` (SNOMED, RxNorm, LOINC, ICD10CM, NDC, CPT4, HCPCS, RxNorm Extension, ATC, and ~100 others), `concept_class_id` (Clinical Finding, Ingredient, Lab Test, etc.), `standard_concept` (NULL = source concept, 'S' = standard, 'C' = classification), `concept_code` (the original code string in the source vocabulary, e.g. "I10"), `valid_start_date`/`valid_end_date`/`invalid_reason` (lifecycle tracking), and `concept_synonym` via the companion CONCEPT_SYNONYM table. - `CONCEPT_RELATIONSHIP`: the edge table. Each row is one directed relationship between two concepts with a `relationship_id`. The two most important relationship types for ETL are "Maps to" (source code → standard concept, used in every ETL to convert ICD/NDC/CPT rows) and "Maps to value" (for value-coded answers, e.g. a LOINC observation linked to a SNOMED answer concept). Other relationships encode hierarchy ("Is a", "Subsumes"), ATC classification links, and ingredient ↔ clinical drug links. - `CONCEPT_ANCESTOR`: the precomputed transitive-closure table. For any two concepts where one is a hierarchical ancestor of the other (via "Is a" / "Subsumes" chains in CONCEPT_RELATIONSHIP), this table has a row with `ancestor_concept_id`, `descendant_concept_id`, `min_levels_of_separation`, and `max_levels_of_separation`. This is what powers "include descendants" in concept sets — rather than computing the full hierarchy on the fly, every descendant relationship is precomputed. Without CONCEPT_ANCESTOR, defining "any statin" or "any type 2 diabetes complication" would require multi-hop recursive joins at query time. - `VOCABULARY`: the registry of all supported vocabularies with their `vocabulary_id`, `vocabulary_name`, `vocabulary_reference`, `vocabulary_version`, and `vocabulary_concept_id`. The `vocabulary_version` column is the anchor for version pinning — reproducibility requires recording the vocabulary version at the time of analysis. - `CONCEPT_SYNONYM`: alternate names and synonyms for concepts, useful for full-text search and for mapping legacy code strings to concept_ids.

The standard_concept flag: the most important column in OMOP

Every concept has exactly one `standard_concept` value: - `'S'` (Standard): The intended target for analysis. When an analyst queries `drug_concept_id IN (resolved_set)`, every concept in that set should have `standard_concept = 'S'`. Standard concepts are the ones OMOP guarantees to be stable, non-redundant, and hierarchy-linked. Standard by domain: SNOMED CT for conditions; RxNorm (and RxNorm Extension for non-US drugs) for drugs; LOINC for measurements; a mix of CPT4, HCPCS, SNOMED, and ICD10PCS for procedures (the most heterogeneous domain); SNOMED for observation domain concepts. - `NULL` (Source / Non-Standard): The raw vocabulary codes the data arrive in — ICD-10-CM, ICD-9-CM, NDC, local lab codes. These are preserved in `_source_concept_id` and `_source_value` columns in CDM tables for audit purposes but should not be used as the primary analysis key. A concept_id of 0 means the ETL found no mapping at all for a source code. - `'C'` (Classification): Concepts that are useful for hierarchical queries (e.g., ATC drug classes, high-level SNOMED hierarchy nodes) but that are not appropriate direct targets for individual patient-level data. You can use a Classification concept as an ancestor in CONCEPT_ANCESTOR queries to pull all standard descendants; you should not expect raw CDM rows to carry `'C'` concept_ids in their `*_concept_id` columns.

Domain routing: the unexpected assignment

Every concept belongs to exactly one `domain_id`, and this assignment determines which CDM table the ETL will place a record into. Domain routing follows the standard concept's domain, not the source vocabulary's intuition — this is a persistent source of confusion. An ICD-10-CM code that a US coder treats as a diagnosis can map to a SNOMED concept in the Observation domain rather than the Condition domain, because SNOMED classifies many signs, symptoms, family history findings, and administrative statuses as observations rather than conditions. Similarly, some HCPCS J-codes for drug administration map into the Procedure domain rather than the Drug domain. An analyst who queries only CONDITION_OCCURRENCE for a code whose standard concept is in the Observation domain will find zero rows and incorrectly conclude the condition was never recorded. The safe practice is to check `domain_id` for each anchor concept in the CONCEPT table before writing the query, and to query the corresponding CDM table (OBSERVATION for domain Observation, MEASUREMENT for domain Measurement, etc.).

Athena: the distribution service

The OHDSI Standardized Vocabularies are not stored locally in each CDM instance — they are downloaded from Athena (https://athena.ohdsi.org), the OHDSI vocabulary download portal. Athena provides the current vocabulary release as a set of CSV files (CONCEPT.csv, CONCEPT_RELATIONSHIP.csv, CONCEPT_ANCESTOR.csv, etc.) that a site loads into its CDM schema. Key Athena operational facts: (1) CPT4 requires a separate UMLS-license reconstitution step — because of AMA licensing, CPT4 concept codes are downloaded as placeholder text and must be replaced with actual code strings by running Athena's `cpt4.jar` reconstitution tool against a UMLS license; sites that skip this step have CPT4 concepts with invalid `concept_code` values. (2) Vocabulary releases are versioned with a date string (e.g., "v5.0 2024-02-23"); the version appears in the VOCABULARY table's `vocabulary_version` column. (3) A given vocabulary release bundles specific content versions of each source vocabulary (SNOMED 2024-03-01, RxNorm 2024-04-01, etc.), so two sites on different Athena releases are literally running different mapping tables and may resolve the same concept-set expression to different sets of concept_ids.

source_concept_id preservation: audit and US-code work

Every CDM clinical event table stores both the standard concept (`drug_concept_id`, `condition_concept_id`, etc.) and the source concept (`drug_source_concept_id`, `condition_source_concept_id`). The source concept_id is the concept in the CONCEPT table that represents the original source code (if the source code exists in the vocabulary), and `*_source_value` is the raw string. This dual storage is essential for: (1) auditing ETL quality — confirming that a source ICD-10 code actually mapped to the expected SNOMED concept; (2) recovering person-time from source codes that mapped to `concept_id = 0`; (3) US-specific code-list work where a regulator asks for the exact ICD-10-CM or NDC codes used; (4) phenotype debugging when a standard-concept set undercounts and the analyst needs to trace which source codes are present vs mapped.

Vocabulary versioning as a reproducibility requirement

Because the CONCEPT_RELATIONSHIP "Maps to" table is updated every release, the same source code can map to a different standard concept_id between vocabulary versions — a code may be remapped, deprecated, split, or merged. A study re-run six months later on a newer vocabulary release will produce a different cohort even with identical concept-set expressions. The reproducible artifact is therefore not the concept-set expression alone but the version-frozen resolved set: the complete list of `concept_id`s that the expression resolves to at a fixed vocabulary version, saved as a versioned file in the study repository. The vocabulary version string from the VOCABULARY table should be reported in every study publication.

Why this layer exists: the multi-database international problem

Before OHDSI, a research network wanting to run one pharmacoepidemiology question across, say, a US commercial claims database, a German statutory health insurance database, and a UK GP database would need three separate analysis scripts, three sets of code lists (ICD-10-CM vs ICD-10-SGB vs Read codes), and three teams who understood each local coding system. The OMOP vocabulary layer solves this by centralizing the translation: each site's ETL maps its local codes to standard concepts, and the network study author writes one query against standard concept_ids that is then valid everywhere. The tradeoff is that the translation is imperfect — the mapping loss, domain routing surprises, and vocabulary-version drift documented in the trade-offs section below are the price of portability.

Pros, cons, and trade-offs

- vs querying source codes directly (ICD-10-CM strings, NDC codes in a raw claims script): Querying source codes is simpler at one site and gives access to fine-grained US billing detail (revenue codes, NDC package-level detail). Cost: every new data partner requires a new code list, mapping ICD-9 vs ICD-10 differences must be handled manually per site, and any hierarchy query (all statins, all heart failure subtypes) requires hand-maintaining a complete flat list. Prefer standard-concept queries for any multi-database or network study; prefer direct source-code queries only when a single site is the sole data source and US billing nuance (claim position, revenue code, specific NDC) is itself the scientific question. - vs the PCORnet CDM or FDA Sentinel Common Data Model: Both PCORnet and Sentinel retain more source-code- native structure and are simpler to ETL at each site. OMOP's distinguishing advantage is the full vocabulary standardization with CONCEPT_ANCESTOR precomputation, enabling concept-set expressions that travel unchanged across sites. Cost: the OMOP ETL is more complex, and vocabulary-mapping errors silently distort cohorts in ways a PCORnet or Sentinel query against its native source codes would not. Prefer OMOP when cross-network concept portability and OHDSI tooling (ATLAS, CohortMethod, PLP) matter; prefer PCORnet/Sentinel when ETL simplicity dominates. - Vocabulary drift between releases: Two sites on different vocabulary versions may resolve the same concept-set expression differently. A code remapped between releases changes cohort membership silently. Mitigation: version-pin the resolved set and report the vocabulary version. No equivalent mitigation exists in source-code querying because ICD-10-CM code meanings are stable, but this advantage disappears the moment a network study spans the ICD-9/ICD-10 transition (Oct 2015), where source-code queries also break. - Domain routing loss: A source code mapped to the Observation domain is not visible in CONDITION_OCCURRENCE. A drug administration code mapped to the Procedure domain is not in DRUG_EXPOSURE. Querying only the "expected" CDM table for a concept can undercount events by 5–30% depending on the vocabulary. Always check domain before writing queries. - US billing nuance flattened by SNOMED mapping: SNOMED's clinical ontology does not distinguish ICD-10-CM principal vs secondary diagnosis position, claim type (inpatient vs outpatient), or revenue code. These distinctions, essential for validated phenotypes in US claims, must be recovered from `condition_type_concept_id` in CONDITION_OCCURRENCE (which preserves them) — not from the standard SNOMED concept itself.

When to use

Any time you are building or validating a cohort, exposure, or outcome definition against an OMOP CDM database — whether at a single site or across an OHDSI network — you are using the vocabulary layer, even if you are not explicitly querying the CONCEPT table. Understanding the vocabulary layer is a prerequisite for interpreting any result from DRUG_EXPOSURE, CONDITION_OCCURRENCE, MEASUREMENT, or any other CDM clinical table, because the concept_ids those tables contain only have meaning through their CONCEPT table metadata. Explicitly query the vocabulary layer when: (a) building a concept-set expression and selecting anchor concept_ids; (b) checking domain routing before deciding which CDM table to query; (c) resolving and version-freezing a concept-set expression for a reproducible study; (d) auditing ETL quality by tracing source_concept_ids through Maps to relationships; (e) identifying unmapped source codes (concept_id = 0) that will be invisible to standard-concept queries.

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

- Do not use the vocabulary layer as a substitute for clinical review. The "Maps to" relationship is the OHDSI community's best guess at the semantically correct mapping. A source ICD-10-CM code may map to a SNOMED concept at a higher level of generality than intended, or to the wrong clinical laterality, or (rarely) to an incorrect concept entirely. No vocabulary mapping replaces inspecting the actual resolved concept_ids and having a clinical expert confirm they represent the intended clinical entity. - Do not assume that "standard_concept = 'S'" means the concept is clinically correct for your study. Standard status means "this is the preferred target for this domain in this vocabulary." It does not mean "this is the phenotype you want." A standard SNOMED concept for "type 2 diabetes mellitus" includes many descendants — including neonatal and secondary forms — that a specific study may need to exclude. Standard is a vocabulary property, not a phenotype property. - Do not re-run an analysis on a newer vocabulary release and report the same results. Vocabulary updates can change cohort membership. A study claiming to reproduce a prior result must use the same vocabulary version. If an updated vocabulary is used for a replication, the version difference must be documented and the impact on the cohort assessed. - Do not treat concept_id = 0 as "the patient did not have this exposure/condition." It means the ETL found no mapping for the source code. Counts of persons with concept_id = 0 as their primary code are a data-quality signal, not a clinical finding. Quantify and report the unmapped fraction before drawing conclusions from cohort size. - Do not skip the CPT4 reconstitution step and then run analyses involving procedures. Sites with unreconstituted CPT4 have concepts with placeholder concept_codes; any procedure-based phenotype or exclusion criterion involving CPT4 will silently fail to match.

Data-source operational depth

- Claims (US commercial, Medicare FFS): Source vocabularies are ICD-10-CM (conditions), NDC (pharmacy drugs), CPT4 and HCPCS (procedures and J-code drugs), ICD-10-PCS (inpatient procedures). Mapping quality is generally high for ICD-10-CM→SNOMED and CPT4→SNOMED/procedures. NDC→RxNorm mapping requires the `drug_strength` extension tables and is complicated by repackaged NDCs (the same drug repackaged by a distributor receives a new NDC that may lag vocabulary updates). Always anchor drug sets at the RxNorm ingredient level and expand via CONCEPT_ANCESTOR to capture all clinical drug and branded descendants. The ICD-9→ICD-10 coding transition (October 2015) is a CONCEPT_RELATIONSHIP "Maps to" challenge: OMOP maintains maps in both directions, but the descendant tree under ICD-9-CM SNOMED mappings differs from the tree under ICD-10-CM mappings, producing spurious apparent incidence changes at the transition for any condition concept set spanning 2015. - EHR: Source vocabularies are more heterogeneous — SNOMED is often used directly for problem lists in US EHRs (via SNOMED CT US edition), while local codes, CPT for visit charges, and LOINC for lab panels are also common. LOINC→LOINC mappings (standard LOINC panel members) and local lab code→LOINC mappings depend on the ETL's laboratory harmonization quality; units must be reconciled separately (the MEASUREMENT table carries `value_as_number`, `unit_concept_id`). EHR data frequently surface concepts in the Observation domain for findings that an epidemiologist would call a condition, reinforcing the need to check domain routing. - Registry: Disease classification in a registry often uses a registry-specific coding scheme (e.g., tumor registry ICD-O-3 histology codes, NYHA heart failure class). These may require a custom vocabulary or a manual concept-to-SNOMED mapping before they appear in the CONCEPT table at all. Adjudicated outcomes from a registry that are re-coded into OMOP standard concepts should be documented with the mapping crosswalk, not assumed correct. - Linked claims–EHR–registry: Each source has its own ETL and its own mapping completeness. The same clinical event can receive a different standard concept_id from different source vocabularies (the ICD-10-CM code for a condition and the SNOMED code from the EHR problem list may map to different SNOMED concept_ids at different specificity levels). Reconcile at the standard-concept level and document which source contributed each concept_id assignment.

Worked example

Scenario

A pharmacoepidemiology team is building a claims-based study in an OMOP database and needs to (1) trace a specific ICD-10-CM diagnosis code through the vocabulary to find its standard SNOMED concept_id, (2) trace an NDC drug code to its RxNorm standard concept, and (3) verify that filtering on only the standard concept changes the patient count compared to also including source concepts. The table below shows the key rows from the CONCEPT and CONCEPT_RELATIONSHIP tables for two example codes.

Dataset

Rows from CONCEPT and CONCEPT_RELATIONSHIP illustrating source-to-standard mapping for one ICD-10-CM diagnosis code and one NDC drug code.

Steps

• Step 1 — trace ICD-10-CM to SNOMED: The source code "I10" (Essential hypertension in ICD-10-CM) has concept_id 35207167 in the CONCEPT table with standard_concept = null, meaning it is a source-only concept used for audit but not for querying patient data. In CONCEPT_RELATIONSHIP, the row with concept_id_1 = 35207167 and relationship_id = "Maps to" points to concept_id_2 = 320128 (the SNOMED concept for Essential hypertension, standard_concept = S). An analyst querying CONDITION_OCCURRENCE for hypertension must use 320128, not 35207167.

• Step 2 — trace NDC to RxNorm: The NDC code "0069-0158-03" (a specific Lipitor package) has OMOP concept_id 40213271 with standard_concept = null. Its "Maps to" relationship in CONCEPT_RELATIONSHIP points to concept_id 1545996 (RxNorm "atorvastatin 20 MG Oral Tablet [Lipitor]", standard_concept = S). To capture all atorvastatin products (all doses, all brands, generic), a drug concept set should be anchored at the RxNorm ingredient concept_id for atorvastatin and expanded via CONCEPT_ANCESTOR, which will include concept_id 1545996 as a descendant.

• Step 3 — count difference: Suppose the CONDITION_OCCURRENCE table for this CDM has 9800 rows where condition_concept_id = 320128 (standard SNOMED) and 200 rows where condition_concept_id = 0 (unmapped, source code had no mapping). Filtering on standard concept_id = 320128 alone finds 9800 rows. Including source_concept_id = 35207167 as a fallback adds 0 additional rows here because the ETL already populated condition_concept_id correctly. But if 200 rows have condition_concept_id = 0, those persons are invisible to the standard-concept query; the unmapped fraction is 200 / (9800 + 200) = 200 / 10000 = 0.02, meaning 2% of source rows are invisible to any standard concept query.

• Step 4 — check domain: Before writing the CONDITION_OCCURRENCE query, the analyst checks domain_id for concept_id 320128 in CONCEPT: domain_id = "Condition". This confirms the record will be in CONDITION_OCCURRENCE. If a symptom code had mapped to a SNOMED concept with domain_id = "Observation", the analyst would instead query OBSERVATION — querying only CONDITION_OCCURRENCE would miss it entirely.

Result

For ICD-10-CM "I10": source concept_id = 35207167 (standard_concept null) Maps to standard concept_id = 320128 (SNOMED, domain = Condition). For NDC "0069-0158-03": source concept_id = 40213271 Maps to RxNorm standard concept_id = 1545996 (domain = Drug). Unmapped fraction = 200 / 10000 = 0.02, so 2% of hypertension rows have concept_id = 0 and are invisible to a pure standard-concept query. Domain check confirms correct CDM table. Arithmetic: 200 / 10000 = 0.02

Runnable example

import duckdb
import pandas as pd

# ---------- Pattern 1: Trace a source ICD-10-CM code to its standard SNOMED concept ----------
# Replace the concept table and concept_relationship table references with your CDM schema.

TRACE_SQL = """
SELECT
    src.concept_id         AS source_concept_id,
    src.concept_code       AS source_code,
    src.vocabulary_id      AS source_vocab,
    src.standard_concept   AS source_standard,
    std.concept_id         AS standard_concept_id,
    std.concept_name       AS standard_concept_name,
    std.vocabulary_id      AS standard_vocab,
    std.domain_id          AS domain_id
FROM concept src
-- "Maps to" relationship points from source concept to standard concept
JOIN concept_relationship cr
    ON cr.concept_id_1    = src.concept_id
   AND cr.relationship_id = 'Maps to'
   AND cr.invalid_reason IS NULL
JOIN concept std
    ON std.concept_id    = cr.concept_id_2
   AND std.standard_concept = 'S'
WHERE src.concept_code   = $source_code
  AND src.vocabulary_id  = $vocab_id
"""

def resolve_to_standard(con: duckdb.DuckDBPyConnection,
                        source_code: str, vocab_id: str = "ICD10CM") -> pd.DataFrame:
    """Resolve a source code to its standard concept(s) via Maps to.

    Args:
        source_code: The raw source code string, e.g. 'I10'.
        vocab_id:    The vocabulary identifier, e.g. 'ICD10CM', 'NDC', 'CPT4'.
    Returns:
        DataFrame with one row per standard concept mapped from the source code.
        If empty, the source code has no standard mapping (concept_id = 0 scenario).
    """
    return con.execute(TRACE_SQL, {"source_code": source_code, "vocab_id": vocab_id}).df()

# ---------- Pattern 2: Domain-routing audit (detect Observation-domain surprises) ----------
DOMAIN_AUDIT_SQL = """
-- Find condition-like source codes whose standard concept lands in Observation or Measurement,
-- causing zero rows if the analyst queries only CONDITION_OCCURRENCE.
SELECT
    src.concept_code,
    src.vocabulary_id,
    std.concept_name,
    std.domain_id          AS mapped_domain,
    COUNT(*)               AS source_rows_affected
FROM concept src
JOIN concept_relationship cr
    ON cr.concept_id_1    = src.concept_id
   AND cr.relationship_id = 'Maps to'
   AND cr.invalid_reason IS NULL
JOIN concept std
    ON std.concept_id      = cr.concept_id_2
   AND std.standard_concept = 'S'
WHERE src.vocabulary_id IN ('ICD10CM', 'ICD9CM')
  AND std.domain_id NOT IN ('Condition')  -- these will be INVISIBLE in CONDITION_OCCURRENCE
GROUP BY src.concept_code, src.vocabulary_id, std.concept_name, std.domain_id
ORDER BY source_rows_affected DESC
LIMIT 50
"""

def audit_domain_routing(con: duckdb.DuckDBPyConnection) -> pd.DataFrame:
    """Return the top ICD codes that route to non-Condition domains.

    These codes are invisible to queries that search only CONDITION_OCCURRENCE.
    Typical culprits: signs/symptoms, history findings, screening codes -> Observation.
    """
    return con.execute(DOMAIN_AUDIT_SQL).df()

# Example usage (replace 'con' with your actual DuckDB/SQLite/Postgres connection):
# result = resolve_to_standard(con, "I10", "ICD10CM")
# domain_surprises = audit_domain_routing(con)

library(DatabaseConnector)
library(SqlRender)

# ---------- Pattern 1: Resolve a source code to its standard concept via Maps to ----------
resolveToStandard <- function(connection, cdmSchema,
                             sourceCode, vocabId = "ICD10CM",
                             targetDialect = "postgresql") {
  sql <- "
  SELECT
      src.concept_id         AS source_concept_id,
      src.concept_code       AS source_code,
      src.vocabulary_id      AS source_vocab,
      std.concept_id         AS standard_concept_id,
      std.concept_name       AS standard_concept_name,
      std.vocabulary_id      AS standard_vocab,
      std.domain_id          AS domain_id
  FROM @cdm.concept src
  JOIN @cdm.concept_relationship cr
      ON cr.concept_id_1    = src.concept_id
     AND cr.relationship_id = 'Maps to'
     AND cr.invalid_reason IS NULL
  JOIN @cdm.concept std
      ON std.concept_id     = cr.concept_id_2
     AND std.standard_concept = 'S'
  WHERE src.concept_code  = '@source_code'
    AND src.vocabulary_id = '@vocab_id';"

  rendered <- SqlRender::render(sql,
                               cdm         = cdmSchema,
                               source_code = sourceCode,
                               vocab_id    = vocabId)
  rendered <- SqlRender::translate(rendered, targetDialect = targetDialect)
  DatabaseConnector::querySql(connection, rendered)
}

# ---------- Pattern 2: Unmapped fraction audit (concept_id = 0 in CONDITION_OCCURRENCE) ----------
unmappedFractionAudit <- function(connection, cdmSchema,
                                 targetDialect = "postgresql") {
  # concept_id = 0 means the ETL found no standard mapping for the source code.
  # These rows are INVISIBLE to any standard-concept cohort query.
  sql <- "
  SELECT
      SUM(CASE WHEN condition_concept_id = 0 THEN 1 ELSE 0 END)      AS unmapped_rows,
      COUNT(*)                                                          AS total_rows,
      CAST(SUM(CASE WHEN condition_concept_id = 0 THEN 1 ELSE 0 END) AS FLOAT)
        / CAST(COUNT(*) AS FLOAT)                                       AS pct_unmapped
  FROM @cdm.condition_occurrence;"

  rendered <- SqlRender::render(sql, cdm = cdmSchema)
  rendered <- SqlRender::translate(rendered, targetDialect = targetDialect)
  DatabaseConnector::querySql(connection, rendered)
}

# Example:
# mapping <- resolveToStandard(connection, "cdm", "I10", "ICD10CM")
# unmapped <- unmappedFractionAudit(connection, "cdm")