Definition

The MKB Mapping Attributes model provides consolidated information about mappings between local and national codes and SNOMED concepts, specifically for language codes and sexual orientation codes. Can be joined to the patient table to identify language and sexual orientation demographics for individual patients.

Constraints and Notes

• Soft deletion : When is_deleted is true, data fields may be nullified in upstream transformations and should be excluded from most analytical queries. Primary keys and foreign keys are preserved so deleted linked records can be identified

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