Definition

The Immunisation data model represents patient vaccination records captured in source system. It includes immunisation events for patients, with associated clinical coding, dates, consultation context, and relevant audit metadata. It provides a consistent, structured view of immunisation activity across organisations.

Information

The Immunisation data model is a structured representation of immunisation data recorded as observations in the source and filtered specifically to entries categorised as immunisations. It captures coded immunisation entries recorded by clinicians, including the associated clinical information.

This model is essential for understanding a patient’s vaccination history and supporting public health reporting across healthcare services. Each immunisation record is derived from the broader observation data, distinguished using code categories. These code categories match the immunisation filtering available to GPs at source and MKB.

This model contains the following key information for immunisations:

• Record and patient identifiers

• Linking fields to consultation, problem and user

• Clinical coding and terms

• Immunisation and system dates

• Vaccination details like Batch number, manufacturer, expiry date and injection site.

• Confidentiality and sensitivity information

Grain and Scope

Each row is a unique immunisation 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 immunisation record has been deleted at source or transitioned from being an immunisation 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.

Overview

flowchart TB
  subgraph container["Data Collection"]
    n10["Vaccine Code"]
    n11["Administration Dates"]
    n12["Batch & Manufacturer Details"]
  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 --> n13["Gather Immunisation Data"]
  n13 --> n14["ETL"]
  n14 --> n15["Immunisation Data Model"]

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

Examples

Get all immunisations for a patient

SELECT
  *
FROM
  hive.explorer_ipcv_vanilla.immunisation_v2
WHERE
  patient_uuid = 'eb97f15f-268e-545e-ba64-7168dd5b08c0'
LIMIT
  100;

Find sensitive or confidential immunisations

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

Get immunisations with SNOMED mapping for interoperability

SELECT
  i.observation_id,
  i.emis_code_id,
  i.emis_original_term,
  i.batch_number,
  i.manufacturer,
  m.snomed_concept_id,
  m.snomed_description_id
FROM
  hive.explorer_ipcv_vanilla.immunisation_v2 i
  LEFT JOIN hive.explorer_ipcv_vanilla.mkb_mapping_dmdpreparation_v2 m ON i.emis_code_id = m.drug_preparation_code_id
  AND i.organisation = m.organisation
WHERE
  m.snomed_concept_id IS NOT NULL
LIMIT
  100;