Definition

The MKB Mapping Attributes model provides a standardised reference of mappings between national data dictionary codes and their corresponding SNOMED CT concepts, covering the language and sexual orientation patient attribute types.

Information

The MKB Mapping Attributes model consolidates data from the national code and clinical code dimension tables into a single, unified lookup for specialised attribute mappings. Each record represents a distinct pairing of an EMIS code identifier and a national data dictionary code, enriched with the relevant SNOMED CT concept and an attribute type indicator (language or sexual orientation).

Grain and Scope

Each row is a unique mapping attribute record and each record is uniquely identified by code_id and data_dictionary_national_code columns.

This model includes the following key identifiers:

• code_id: The unique EMIS identifier for the clinical code.
• data_dictionary_national_code: The NHS data dictionary code used to identify the national data element associated with the mapping.

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

• is_deleted: Indicates whether the mapping attribute 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 Attribute Updates: National code mappings can change over time; EMIS MKB is updated monthly and may include relevant updates, with more changes expected when releases coincide with NHS data dictionary updates.

Examples

Active language mappings

SELECT
  emis_code_id,
  snomed_concept_id,
  data_dictionary_national_code,
  attribute_type,
  organisation,
  transform_datetime
FROM
  hive.explorer_ipcv_vanilla.mkb_mapping_attributes_v2
WHERE
  is_deleted = FALSE
  AND attribute_type = 'language'
ORDER BY
  data_dictionary_national_code;

Sexual orientation mappings

SELECT
  emis_code_id,
  snomed_concept_id,
  data_dictionary_national_code,
  attribute_type,
  transform_datetime
FROM
  hive.explorer_ipcv_vanilla.mkb_mapping_attributes_v2
WHERE
  attribute_type = 'sex_orientation'
ORDER BY
  is_deleted, -- Show active mappings first
  data_dictionary_national_code;

Mapping statistics by type

SELECT
  attribute_type,
  COUNT(*) AS total_mappings,
  SUM(
    CASE
      WHEN is_deleted = FALSE THEN 1
      ELSE 0
    END
  ) AS active_mappings,
  SUM(
    CASE
      WHEN snomed_concept_id IS NOT NULL THEN 1
      ELSE 0
    END
  ) AS mappings_with_snomed,
  MIN(transform_datetime) AS earliest_update,
  MAX(transform_datetime) AS latest_update
FROM
  hive.explorer_ipcv_vanilla.mkb_mapping_attributes_v2
GROUP BY
  attribute_type
ORDER BY
  attribute_type;