Definition

The clinical code data model provides a reference set of EMIS clinical codes that standardises coding for patients’ clinical information, such as observations, diagnoses, and treatments. It includes parent-child hierarchy relationships and mapped code attributes.

Information

The clinical code model contains clinical code identifiers and terms, plus related coding attributes such as Read term/code, SNOMED concept/description IDs, EMIS code category and national classification fields. Medicine codes are excluded.

National code metadata is limited to a specific subset of clinically relevant data element categories (for example ethnicity, preferred language, referral/contact, and consultation context categories)

Grain and Scope

Each row is a unique clinical code 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 clinical code.

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

• is_deleted: Indicates whether the clinical code 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

• Clinical Code Mapping Updates: Mappings from code ids to SNOMED CT and Read codes 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

Find child code

SELECT
  *
FROM
  hive.explorer_ipcv_vanilla.clinical_code_v2
WHERE
  parent_emis_code_id = 5568191000006118;

Distinct EMIS Codes

SELECT DISTINCT
  (emis_codecategory_description)
FROM
  hive.explorer_ipcv_vanilla.clinical_code_v2
ORDER BY
  emis_codecategory_description;

Distinct National Codes for a category

SELECT DISTINCT
  national_code,
  national_description
FROM
  hive.explorer_ipcv_vanilla.clinical_code_v2
WHERE
  national_code_category = 'PREFERRED COMMUNICATION LANGUAGE';