Definition

The MKB Mapping Ethnicity model provides a standardised reference that maps EMIS ethnicity codes to their corresponding SNOMED CT concepts, enabling consistent interpretation and analysis of patient ethnicity data across participating organisations.

Information

This model acts as a reusable ethnicity-code lookup, linking each EMIS-specific ethnicity code to its SNOMED CT concept identifier and associated term. This allows downstream consumers to resolve proprietary codes to nationally recognised SNOMED CT representations, supporting consistent ethnicity reporting, cohort analysis, and interoperability with external systems.

Grain and Scope

Each row is a unique mapping ethnicity record and each record is uniquely identified by code_id column.

This model includes the following key identifiers:

• code_id: The unique EMIS identifier for the ethnicity code.

The following fields are important for tracking data lineage and freshness:

• is_deleted: Indicates whether the mapping ethnicity record has been deleted at source.

• transform_datetime: The timestamp indicating when the record was last processed and updated in the data model. This field is crucial for understanding the current state of the data.

Things to be aware of

• Mapping Ethnicity Updates: Mappings from code ids to SNOMED CT can change over time; EMIS MKB is updated monthly and may include relevant code updates, with more changes expected when releases coincide with NHS SNOMED CT updates.

Examples

Active ethnicity codes

SELECT
  emis_code_id,
  snomed_concept_id,
  snomed_description,
  organisation,
  transform_datetime
FROM
  hive.explorer_ipcv_vanilla.mkb_mapping_ethnicity_v2
WHERE
  is_deleted = FALSE
ORDER BY
  snomed_description;

Find specific ethnicity type

SELECT
  emis_code_id,
  snomed_concept_id,
  snomed_description,
  organisation,
  transform_datetime
FROM
  hive.explorer_ipcv_vanilla.mkb_mapping_ethnicity_v2
WHERE
  is_deleted = FALSE
  AND snomed_description LIKE '%Asian%'
ORDER BY
  snomed_description
LIMIT
  100;

Historical ethnicity codes

SELECT
  emis_code_id,
  snomed_concept_id,
  snomed_description,
  snomed_description_id,
  organisation,
  transform_datetime
FROM
  hive.explorer_ipcv_vanilla.mkb_mapping_ethnicity_v2
WHERE
  is_deleted = TRUE
ORDER BY
  transform_datetime DESC
LIMIT
  100;