CONCEPT_MAP: Gaps affecting <3% of medication orders, 445K missing unit mappings and 90% of observation displays #33
Description
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
- Priority 1: Map 666 missing clinical codes to SNOMED-CT:
- 291 medication order codes
- 285 medication statement codes
- 90 observation/diagnostic codes
- Priority 2: Map 584 missing unit codes (result_value_unit_concept_id) - important for interpreting lab results
- Priority 3: Add mappings for appointment codes and populate display text for 45K+ SNOMED concepts
- 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 |
Metadata
Metadata
Assignees
Labels
Type
Projects
Status