CONCEPT_MAP: Gaps affecting <3% of medication orders, 445K missing unit mappings and 90% of observation displays

[EddieDavison92]

Concept Map Testing

On the whole, most concepts map correctly and we are in a significantly advanced position from where we were and are able to conduct meaningful comparisons with the data we have.

The tests below highlight gaps in the concept map other than those raised in issue #18

Impact Summary

• ~120K medication records cannot map to SNOMED-CT (2-3% failure rate)
• 445K observations are missing unit mappings (affects ability to interpret lab results)
• ~90% of observations have correct codes but no human-readable display

SNOMED-CT Clinical Mappings

Medications

MEDICATION_ORDER.medication_order_core_concept_id

• Total: 3.5M records
• Successfully mapped: 3.4M (96.9%) - 11,040 distinct drug codes
• Failed to map: ~100K (2.8%) - 291 unmapped drug codes
• Display missing: ~10K (0.3%) - 6 concepts
• NULL source values: 1,911 records (not expected - orders can't be raised with no medication specified)
• Impact: Unable to map 2-3% of medication orders to SNOMED-CT

MEDICATION_STATEMENT.medication_statement_core_concept_id

• Total: 1M records
• Successfully mapped: 970K (97.0%) - 11,222 distinct drug codes
• Failed to map: ~21K (2.1%) - 285 unmapped drug codes
• Display missing: ~9K (0.9%) - 7 concepts
• Impact: Unable to map around 2% of medication statements to SNOMED-CT

Observations

OBSERVATION.observation_source_concept_id

• Total: 19.9M records
• Successfully mapped: 2.1M (10.5%) - 3,517 distinct codes with full display
• Failed to map: 901 (0.005%) - 90 unmapped codes
• Display missing: 17.8M (~90%) - 45,509 concepts
• Impact: Excellent mapping (99.995% success), but most concepts lack human-readable displays

DIAGNOSTIC_ORDER.diagnostic_order_source_concept_id

• Total: 19.9M records
• Successfully mapped: 2.1M (10.5%) - 3,517 distinct codes with full display
• Failed to map: 901 (0.005%) - 90 unmapped codes
• Display missing: 17.8M (~90%) - 45,509 concepts
• Impact: Same as observations - diagnostic orders work technically but are unreadable to users

Administrative Terminology

Appointment Management

APPOINTMENT.appointment_status_concept_id

• Successfully mapped: 1.36M (68.1%) - 11 distinct statuses
• Failed: 640K of 2M records (~32%) - 1 unmapped status
• Impact: Cannot track appointment workflow states (booked, arrived, completed, etc.) for nearly a third of appointments

APPOINTMENT.contact_mode_concept_id

• Successfully mapped: 860K (42.9%) - 2 distinct modes
• Failed: 1.1M of 2M records (~57%) - 2 unmapped modes
• Impact: Unable to classify over half of appointments by type (in-person, telephone, video), affecting capacity planning and reporting

Clinical Measurements

OBSERVATION.result_value_unit_concept_id

• Successfully mapped: 4.8M (91.5%) - 137 distinct unit codes
• Failed: 445K records - 584 unmapped unit codes
• NULL source values: 12.6M records (expected - not all observations have units)
• Impact: Cannot properly interpret lab results (e.g., is glucose in mmol/L or mg/dL?)

DIAGNOSTIC_ORDER.date_precision_concept_id

• Successfully mapped: 4.8M (91.5%) - 137 distinct precision codes
• Failed: 445K of 5.2M records - 584 unmapped codes
• NULL source values: 12.6M records (might be expected)
• Impact: Unclear whether order dates are exact or estimated (dates can be entered in EMIS without a specific day or month)

Other Clinical Domains

Encounter Classification

ENCOUNTER.encounter_core_concept_id

• Successfully mapped: 922K (14.6%) - 6 distinct encounter types
• Display missing: 5.4M of 6.3M records (~85%) - 38 concepts
• NULL source values: 55K records
• Impact: Cannot display encounter types in human-readable form

ENCOUNTER.date_precision_concept_id

• Successfully mapped: 5.45M (99.9%) - 3 precision types
• Failed: ~5K records - 1 unmapped code

Procedure Requests

PROCEDURE_REQUEST.status_concept_id

• Successfully mapped: 114K (36.7%) - 265 distinct statuses
• Display missing: 197K of 311K records (~63%) - concept 1305
• Impact: Procedure request statuses not human readable

PROCEDURE_REQUEST.procedure_core_concept_id

• Successfully mapped: 76K - 2 distinct procedure types
• NULL source values: 121K records (61.3% - concerning high rate of missing procedure types)

Referrals

REFERRAL_REQUEST.referral_request_source_concept_id

• Successfully mapped: 37K (23.4%) - 102 distinct sources
• Display missing: 121K of 158K records (~77%) - 781 concepts
• Impact: Cannot see human readable text for most referrals

REFERRAL_REQUEST metadata

• referal_request_type_concept_id: 45K NULL values (37.4% - high rate of missing referral types, might be expected)
• referral_request_priority_concept_id: 45K NULL values (37.4% - high rate of missing referral priorities, might be expected)

Allergies

ALLERGY_INTOLERANCE.allergy_intolerance_source_concept_id

• Successfully mapped: 340 (1.7%) - 37 distinct sources
• Display missing: 19K of 20K records (~98%) - concept 1306
• Failed: 2 records
• Impact: Allergy source systems not identifiable, though actual allergy data remains intact

Edge Cases

Minimal failures across multiple tables or in low-impact areas:

• PATIENT.gender_concept_id: 34,608 successful (99.99%) vs 2 failures
• PATIENT_ADDRESS.address_type_concept_id: All 34,610 records are NULL
• OBSERVATION.date_precision_concept_id: 17.8M successful vs ~13K failures
• episodicity_concept_id: 17.8M successful vs 2 failures each (OBSERVATION, DIAGNOSTIC_ORDER)
• Date precision failures: REFERRAL_REQUEST (3), PROCEDURE_REQUEST (85), ALLERGY_INTOLERANCE (647)

Recommendations

1. Priority 1: Map 666 missing clinical codes to SNOMED-CT:
   • 291 medication order codes
   • 285 medication statement codes
   • 90 observation/diagnostic codes
2. Priority 2: Map 584 missing unit codes (result_value_unit_concept_id) - important for interpreting lab results
3. Priority 3: Add mappings for appointment codes and populate display text for 45K+ SNOMED concepts
4. Data quality review: Investigate concerning NULL patterns:
   • MEDICATION_ORDER: 1,911 orders with no medication specified (should not be possible)
   • PROCEDURE_REQUEST: 121K records (61%) missing procedure types
   • REFERRAL_REQUEST: 45K records (37%) missing type/priority metadata
   • PATIENT_ADDRESS: All 34,610 records missing address type

Detailed Results Table

SOURCE_TABLE	SOURCE_COLUMN	FAILURE_REASON	ROW_COUNT	DISTINCT_CONCEPT_IDS
ALLERGY_INTOLERANCE	allergy_intolerance_source_concept_id	Failed at CONCEPT_MAP join	2	1
ALLERGY_INTOLERANCE	allergy_intolerance_source_concept_id	CONCEPT found but display is NULL	19438	1306
ALLERGY_INTOLERANCE	allergy_intolerance_source_concept_id	Successfully mapped	340	37
ALLERGY_INTOLERANCE	date_precision_concept_id	Failed at CONCEPT_MAP join	647	1
ALLERGY_INTOLERANCE	date_precision_concept_id	Successfully mapped	18793	3
APPOINTMENT	appointment_status_concept_id	Failed at CONCEPT_MAP join	640283	1
APPOINTMENT	appointment_status_concept_id	Successfully mapped	1364378	11
APPOINTMENT	booking_method_concept_id	Source concept ID is NULL	747651	0
APPOINTMENT	booking_method_concept_id	Successfully mapped	1257010	4
APPOINTMENT	contact_mode_concept_id	Failed at CONCEPT_MAP join	1145086	2
APPOINTMENT	contact_mode_concept_id	Successfully mapped	859575	2
DIAGNOSTIC_ORDER	date_precision_concept_id	Source concept ID is NULL	12574470	0
DIAGNOSTIC_ORDER	date_precision_concept_id	Failed at CONCEPT_MAP join	444672	584
DIAGNOSTIC_ORDER	date_precision_concept_id	Successfully mapped	4761442	137
DIAGNOSTIC_ORDER	diagnostic_order_source_concept_id	Failed at CONCEPT_MAP join	901	90
DIAGNOSTIC_ORDER	diagnostic_order_source_concept_id	CONCEPT found but display is NULL	17779683	45509
DIAGNOSTIC_ORDER	diagnostic_order_source_concept_id	Successfully mapped	2085643	3517
DIAGNOSTIC_ORDER	episodicity_concept_id	Failed at CONCEPT_MAP join	2	1
DIAGNOSTIC_ORDER	episodicity_concept_id	Successfully mapped	17780582	7
ENCOUNTER	date_precision_concept_id	Failed at CONCEPT_MAP join	4957	1
ENCOUNTER	date_precision_concept_id	Successfully mapped	5452660	3
ENCOUNTER	encounter_core_concept_id	Source concept ID is NULL	54857	0
ENCOUNTER	encounter_core_concept_id	CONCEPT found but display is NULL	5402760	38
ENCOUNTER	encounter_core_concept_id	Successfully mapped	922029	6
LOCATION_CONTACT	contact_type_concept_id	Successfully mapped	1217	4
MEDICATION_ORDER	date_precision_concept_id	Successfully mapped	3511294	1
MEDICATION_ORDER	medication_order_core_concept_id	Source concept ID is NULL	1911	0
MEDICATION_ORDER	medication_order_core_concept_id	Failed at CONCEPT_MAP join	99455	291
MEDICATION_ORDER	medication_order_core_concept_id	CONCEPT found but display is NULL	9551	6
MEDICATION_ORDER	medication_order_core_concept_id	Successfully mapped	3409874	11040
MEDICATION_STATEMENT	medication_statement_core_concept_id	Failed at CONCEPT_MAP join	20702	285
MEDICATION_STATEMENT	medication_statement_core_concept_id	CONCEPT found but display is NULL	9422	7
MEDICATION_STATEMENT	medication_statement_core_concept_id	Successfully mapped	970104	11222
OBSERVATION	date_precision_concept_id	Failed at CONCEPT_MAP join	12993	1
OBSERVATION	date_precision_concept_id	Successfully mapped	17767591	3
OBSERVATION	episodicity_concept_id	Failed at CONCEPT_MAP join	2	1
OBSERVATION	episodicity_concept_id	Successfully mapped	17780582	7
OBSERVATION	observation_source_concept_id	Failed at CONCEPT_MAP join	901	90
OBSERVATION	observation_source_concept_id	CONCEPT found but display is NULL	17779683	45509
OBSERVATION	observation_source_concept_id	Successfully mapped	2085643	3517
OBSERVATION	result_value_unit_concept_id	Source concept ID is NULL	12574470	0
OBSERVATION	result_value_unit_concept_id	Failed at CONCEPT_MAP join	444672	584
OBSERVATION	result_value_unit_concept_id	Successfully mapped	4761442	137
PATIENT	gender_concept_id	Failed at CONCEPT_MAP join	2	1
PATIENT	gender_concept_id	Successfully mapped	34608	2
PATIENT_ADDRESS	address_type_concept_id	Source concept ID is NULL	34610	0
PROCEDURE_REQUEST	date_precision_concept_id	Failed at CONCEPT_MAP join	85	1
PROCEDURE_REQUEST	date_precision_concept_id	Successfully mapped	197037	3
PROCEDURE_REQUEST	procedure_core_concept_id	Source concept ID is NULL	120929	0
PROCEDURE_REQUEST	procedure_core_concept_id	Successfully mapped	76193	2
PROCEDURE_REQUEST	status_concept_id	CONCEPT found but display is NULL	197122	1305
PROCEDURE_REQUEST	status_concept_id	Successfully mapped	114096	265
REFERRAL_REQUEST	date_precision_concept_id	Failed at CONCEPT_MAP join	3	1
REFERRAL_REQUEST	date_precision_concept_id	Successfully mapped	120795	3
REFERRAL_REQUEST	referal_request_type_concept_id	Source concept ID is NULL	45158	0
REFERRAL_REQUEST	referal_request_type_concept_id	Successfully mapped	75640	10
REFERRAL_REQUEST	referral_request_priority_concept_id	Source concept ID is NULL	45158	0
REFERRAL_REQUEST	referral_request_priority_concept_id	Successfully mapped	75640	4
REFERRAL_REQUEST	referral_request_source_concept_id	CONCEPT found but display is NULL	120798	781
REFERRAL_REQUEST	referral_request_source_concept_id	Successfully mapped	36916	102