Reference notes on OMOP’s standard-concept mapping, one level below the usual explanation. For why OMOP forces everything through standard concepts in the first place, see Michael Wornow’s STARR-OMOP post, which walks the same ICD-9 → SNOMED example (427.31 → 313217) via concept_relationship.

The question these notes answer: given a non-standard OMOP concept, which standard concept does it actually resolve to, does that standard concept have any patients in this data, and how do you find out, concretely, at the level of the exact tables, one real row, and the actual WebAPI SQL/JSON?

Three tables

  • concept: every code, every vocabulary, standard or not. One row each. standard_concept: 'S' standard, NULL non-standard, 'C' classification.
  • concept_relationship: edges between concepts. The one that matters here: relationship_id = 'Maps to', concept_id_1 (source) → concept_id_2 (standard target).
  • concept_ancestor: precomputed transitive closure over hierarchy (standard concepts only). Unrelated to cross-vocabulary mapping. Don’t conflate “maps to its standard equivalent” (one hop, concept_relationship) with “rolls up to a broader standard ancestor” (many hops, concept_ancestor).
erDiagram CONCEPT ||--o{ CONCEPT_RELATIONSHIP : "concept_id_1 (source)" CONCEPT ||--o{ CONCEPT_RELATIONSHIP : "concept_id_2 (target)" CONCEPT ||--o{ CONCEPT_ANCESTOR : "ancestor_concept_id" CONCEPT ||--o{ CONCEPT_ANCESTOR : "descendant_concept_id" CONCEPT { int concept_id PK varchar concept_name varchar vocabulary_id varchar standard_concept "S, C, or NULL" } CONCEPT_RELATIONSHIP { int concept_id_1 FK "source concept" int concept_id_2 FK "target concept" varchar relationship_id "e.g. Maps to" } CONCEPT_ANCESTOR { int ancestor_concept_id FK int descendant_concept_id FK int min_levels_of_separation }

Example row

As an example: ICD-10-CM R73.03 (Prediabetes) recorded as a source diagnosis code. The condition_occurrence row:

Column Value
condition_source_value 'R73.03' raw string as sent
condition_source_concept_id 37201113 ICD10CM concept, non-standard, traceability only
condition_concept_id 37018196 SNOMED concept, standard, what every query runs against

37201113 → 37018196 is a 'Maps to' row already in concept_relationship on any stock OHDSI vocabulary load, not something a site builds. The OHDSI Vocabulary Team ships this mapping for every named vocabulary (ICD10CM, ICD9CM, CPT4, RxNorm, LOINC, …) via Athena. A site only maps its own genuinely local/unpublished codes, by hand, with Usagi.

Consequence: Achilles record counts only exist for concept IDs that appear in a clinical table’s *_concept_id column. ETL only ever writes the standard one there. 37201113 structurally cannot have a count; 37018196 is where usage actually lives.

Resolving a search result: 3 WebAPI calls

The point of this chain isn’t the count as a number, it’s finding out whether any patient in the data actually has this condition/drug/procedure recorded, under whichever standard concept it’s really stored as. A candidate code with no reachable count means no data to build on, worth knowing before it goes into a concept set or cohort definition, not after.

All three calls take a list of concept IDs; none loop per concept. Fixed 3 round trips regardless of result count.

1. Search: POST /WebAPI/vocabulary/{sourceKey}/search

// req
{ "QUERY": "prediabetes", "IS_LEXICAL": false }
// res (trimmed)
[
  { "CONCEPT_ID": 37201113, "STANDARD_CONCEPT": "N", "VOCABULARY_ID": "ICD10CM", "CONCEPT_CODE": "R73.03" },
  { "CONCEPT_ID": 37018196, "STANDARD_CONCEPT": "S", "VOCABULARY_ID": "SNOMED", "CONCEPT_CODE": "714628002" }
]

STANDARD_CONCEPT is already on every row: no extra lookup needed to split standard/non-standard. Backing query:

select CONCEPT_ID, CONCEPT_NAME, ISNULL(STANDARD_CONCEPT,'N') STANDARD_CONCEPT, ...
from @CDM_schema.concept
where 1=1 @filters
order by CONCEPT_NAME ASC

2. Resolve: POST /WebAPI/vocabulary/{sourceKey}/related-standard, sending only the non-standard IDs from step 1.

// req: non-standard IDs from step 1
[37201113]
// res
[ { "CONCEPT_ID": 37018196, "STANDARD_CONCEPT": "S", "VOCABULARY_ID": "SNOMED",
    "RELATIONSHIP_CAPTION": "Maps to", "mapped_from": [37201113] } ]

mapped_from is a reverse index, since multiple non-standard codes can map to one target. Backing query (VocabularyService.java):

SELECT c.CONCEPT_ID, c.CONCEPT_NAME, COALESCE(c.STANDARD_CONCEPT, 'N') as STANDARD_CONCEPT, ...
FROM   @CDM_schema.concept_relationship cr
JOIN   @CDM_schema.concept c ON cr.CONCEPT_ID_2 = c.CONCEPT_ID
JOIN   @CDM_schema.relationship r ON cr.RELATIONSHIP_ID = r.RELATIONSHIP_ID
WHERE  cr.CONCEPT_ID_1 IN (@conceptIdList)
  AND  COALESCE(c.STANDARD_CONCEPT, 'N') IN ('S', 'C')
  AND  cr.INVALID_REASON IS NULL
  AND  cr.relationship_id = 'Maps to'

3. Count: POST /WebAPI/cdmresults/{sourceKey}/conceptRecordCount, sending the union of the standard IDs already in step 1 (37018196) and the IDs step 2 resolved to (also 37018196 here, they collapse in this example, but in general they don’t overlap).

// req
[37018196]
// res: one entry per ID Achilles actually has
[ { "37018196": [425, 425, 425, 425] } ]

37201113 never appears in this request at all, it was never standard, so it was never eligible to be counted. Backing query:

select concept_id, record_count, descendant_record_count, person_count, descendant_person_count
from @resultTableQualifier.achilles_result_concept_count
where concept_id IN (@conceptIdentifiers)

Array positions (DescendantRecordAndPersonCount.getValues()):

# Field Meaning
0 record_count rows on exactly this concept
1 descendant_record_count rows on this concept + all descendants (concept_ancestor)
2 person_count distinct patients, this concept only
3 descendant_person_count distinct patients, this concept + descendants

All four being 425 here is coincidental to this example, not a rule.

When one code maps to more than one standard concept

The prediabetes example resolves cleanly because 37201113 maps to exactly one standard target. That’s not guaranteed. A real, reported case: ICD10CM I61.1 (concept_id 35207809, “Nontraumatic intracerebral hemorrhage in hemisphere, cortical”) maps to two different standard SNOMED concepts via 'Maps to', not one:

// res from step 2, input [35207809]
[
  { "CONCEPT_ID": 4176892, "CONCEPT_NAME": "Cortical hemorrhage",
    "STANDARD_CONCEPT": "S", "mapped_from": [35207809] },
  { "CONCEPT_ID": 43530727, "CONCEPT_NAME": "Spontaneous cerebral hemorrhage",
    "STANDARD_CONCEPT": "S", "mapped_from": [35207809] }
]

Step 3 then has to count both 4176892 and 43530727, not just one. Whichever comes back with real usage in this OMOP instance is the concept a concept set should actually reference, not whichever happened to be first in the response. And if both come back with real, non-trivial counts, that’s not a tiebreaker the counts can resolve: it means the two SNOMED concepts capture genuinely different clinical detail, and picking between them (or including both) is a clinical judgment call, not something the record count settles for you.

Why it matters

Cohort definitions match *_concept_id columns. A concept set holding a non-standard concept with no mapping compiles and generates fine; it just silently returns zero patients. No error, just a well-formed query against a column that concept could never populate.

tl;dr

concept = dictionary. concept_relationship ('Maps to') = translator, pre-built for every named vocabulary. concept_ancestor = family tree, unrelated to the first two. A non-standard concept’s own usage count is always empty; resolve it to its standard target(s) first, then count those, since that’s where any patients actually are.