Definition

The Allergy data model represents allergies, intolerances, and adverse reactions recorded against the patients in source systems. Allergy records can also be linked to related problems and consultations, while remaining part of the observation domain.

Information

The Allergy data model provides a structured representation of patient allergy information derived from observation data. It captures coded allergy entries recorded by clinicians, including clinical code, episodicity, recording date, and consultation context. This model supports a reliable view of allergy history for safer clinical decision-making.

The model includes both medication and environmental allergies, distinguished using the code_category field. These code categories match the allergy filtering available to GPs at source and MKB. Inclusion in the allergy model is determined by code category only to match this behaviour.

This model contains the following key information for allergies:

• Record and patient identifiers

• Linking fields to consultation, problem and user

• Clinical coding and terms

• Allergy and system dates

• Confidentiality and sensitivity information

Grain and Scope

Each row is a unique allergy record and each record is uniquely identified by either combining observation_id and organisation or the observation_uuid column.

This model includes the following key identifiers:

• observation_id: The unique internal identifier for the observation record within an organisation.

• observation_guid: The GUID for the observation record within an organisation.

• observation_uuid: The UUID derived from observation_id and organisation, providing a stable unique identifier.

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

• is_deleted: Indicates whether the allergy record has been deleted at source or transitioned from being an allergy code.

• is_sensitive: Indicates whether the record is flagged as sensitive.

• is_confidential: Indicates whether the record is confidential.

• 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

• Observation type not included in categorisation: Allergy is only filtered using code category, therefore, an observation with an observation type of Allergy that doesn’t map to a code under the relevant code categories remains in the Observation view and is not interpreted as an Allergy. Where that occurs it is an indication that the code id attached to that record was classified as an allergy but since has been recategorised.

Overview

flowchart TB
  subgraph container["Data Collection"]
    n10["Allergy Code"]
    n11["Clinical Dates"]
    n12["Episodicity & Context"]
  end

  n17["Organisation 1"] --> n7
  n17["Organisation 1"] --> n5
  n18["Organisation 2"] --> n6
  n18["Organisation 2"] --> n8
  n18["Organisation 2"] --> n4

  n7["Patient 123"] --> container
  n5["Patient 98"] --> container
  n6["Patient 456"] --> container
  n4["Patient 20"] --> container
  n8["Patient 47"] --> container

  container --> n16["Gather allergy data"]
  n16 --> n14["ETL"]
  n14 --> n15["Allergy Data Model"]

  n7["Patient 123"]:::rect
  n5["Patient 98"]:::rect
  n6["Patient 456"]:::rect
  n4["Patient 20"]:::rect

Examples

Get all allergies for a patient

-- Using patient_uuid column
SELECT
  *
FROM
  hive.explorer_ipcv_vanilla.allergy_v2
WHERE
  patient_uuid = 'eb6be680-fc39-5a27-8642-d75d6960758f'
LIMIT
  100;

-- Using patient_id column
SELECT
  *
FROM
  hive.explorer_ipcv_vanilla.allergy_v2
WHERE
  emis_patient_id = 50081
  AND organisation = 'CDB-50002'
LIMIT
  100;

Find sensitive or confidential allergies

SELECT
  *
FROM
  hive.explorer_ipcv_vanilla.allergy_v2
WHERE
  sensitive_flag
  OR confidential_flag
LIMIT
  100;

Get allergies with SNOMED mapping for interoperability

SELECT
  a.observation_id,
  a.emis_code_id,
  m.snomed_concept_id,
  m.snomed_description_id
FROM
  hive.explorer_ipcv_vanilla.allergy_v2 a
  LEFT JOIN hive.explorer_ipcv_vanilla.codeable_concept_v2 m ON a.emis_code_id = m.emis_code_id
  AND a.organisation = m.organisation
WHERE
  m.snomed_concept_id IS NOT NULL
LIMIT
  100;

Get authorised user in role data for an allergy record

SELECT
  a.observation_id,
  a.authorising_user_in_role_uuid,
  u.emis_userinrole_guid,
  u.healthcare_practitioner_type
FROM
  hive.explorer_ipcv_vanilla.allergy_v2 a
  LEFT JOIN hive.explorer_ipcv_vanilla.user_in_role_v2 u ON a.authorising_user_in_role_uuid = u.user_in_role_uuid
LIMIT
  100;