Definition

The MKB Mapping Attributes model provides consolidated information about mappings between national codes and SNOMED concepts, specifically for language codes and sexual orientation codes.

Information

The MKB Mapping Attributes model provides mappings between national data dictionary codes and their corresponding SNOMED concepts, specifically for language and sexual orientation attributes. This view consolidates information from national code dimension and code dimension tables, creating a unified reference for these specialized mappings.

Each record in this model represents a unique combination of a code and a national data dictionary code, with additional context about the attribute type (language or sexual orientation). The model maintains the deletion status of each mapping, ensuring that obsolete mappings can be appropriately filtered out in downstream applications.

Examples

Active language mappings

SELECT
  code_id,
  snomed_concept_id,
  data_dictionary_national_code,
  attribute_type,
  organisation,
  transform_datetime
FROM hive.explorer_open_safely.mkb_mapping_attributes
WHERE
  is_deleted = FALSE
  AND attribute_type = 'language'
ORDER BY
  data_dictionary_national_code

Sexual orientation mappings

SELECT
  code_id,
  snomed_concept_id,
  data_dictionary_national_code,
  attribute_type,
  transform_datetime
FROM hive.explorer_open_safely.mkb_mapping_attributes
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_open_safely.mkb_mapping_attributes
GROUP BY
  attribute_type
ORDER BY
  attribute_type