Definition

The Observation data model represents entries in the source system used to store care record observations, and is the default record type for all content which is not mapped to a more specific resource (ie allergy). An Observation can carry a range of coded and uncoded content and not just restricted to ‘observables’.

Observations can be linked to/have relationships with other resources within the record, which can provide further information and context. For example:

Problems - an observation may be the recording of the onset of an observation or a review of an ongoing condition.

Consultations - consultations will contain observations

Clinical Documents - a clinical document will have a coded observation that it is linked to.

Information

The Observation model provides patient care record observations that surfaces all care record entries from the source that are not classified as allergies, immunisations, or referrals, covering the full breadth of clinical encounters — diagnoses, test results, procedures, family history, template entries, and both coded and uncoded observations.

The model retains deleted observations and observations that have since transitioned into a different category (e.g. a previous observation reclassified as an allergy) so that downstream consumers can detect lifecycle changes. For uncoded observations, fields such as original_term and associated_text are not available due to information governance restrictions, as these free-text fields may contain sensitive data.

This model contains the following key information for observations:

• Record and patient identifiers

• Linking fields to consultation, problem and user

• Clinical values with normal range details where applicable

• Clinical coding and terms

• Observation and system dates

• Confidentiality and sensitivity information

Grain and Scope

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

• 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

• Coded and uncoded observations: The model includes both coded and uncoded observations. If only coded observations are required, filter the uncoded ones having NULLcode_id column.

Overview

flowchart TB
  subgraph container["Data Collection"]
    n10["Clinical Code"]
    n11["Effective Date"]
    n12["Numeric Value & Range"]
    n13["Qualifiers & Episodicity"]
  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 --> n13b["Gather Observation Data"]
  n13b --> |"Unique Observation IDs"|n14["ETL"]
  n14 --> n15["Observation Data Model"]

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

Examples

Get all observations for a patient

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

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

Find sensitive or confidential observations

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

Get observations with SNOMED mapping for interoperability

SELECT
  o.emis_observation_id,
  o.emis_code_id,
  o.emis_original_term,
  m.snomed_concept_id,
  m.snomed_description_id
FROM
  hive.explorer_ipcv_vanilla.observation_v2 o
  LEFT JOIN hive.explorer_ipcv_vanilla.codeable_concept_v2 m ON o.emis_code_id = m.emis_code_id
  AND o.organisation = m.organisation
WHERE
  m.snomed_concept_id IS NOT NULL
LIMIT
  100;