Definition

The Observation data model provides a comprehensive record of coded patient care observations recorded in EMIS Web, excluding allergies, immunisations, and referrals. It captures clinical entries made by clinicians including numeric measurements, reference ranges, qualifiers, episodicity, and consultation context, forming the foundation of a patient’s longitudinal care record.

Information

The Observation data model is a structured representation of clinical observations recorded against patients in EMIS Web. Each observation record is derived from the broader observation pipeline and filtered to exclude entries classified as allergies, immunisations, or referrals — representing the full breadth of coded clinical encounters including diagnoses, test results, procedure entries, family history, and template-based data.

Each observation record is uniquely identified by a combination of observation_id and organisation, ensuring a distinct entry for every observation within a specific healthcare organisation.

The model includes several key data points to provide a complete clinical picture of patient observations:

This model includes some of the key identifiers:

• observation_id: The unique internal identifier for the observation record.
• observation_guid: GUID for the observation record
• observation_uuid: UUID for the observation record

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

• is_deleted: Indicates whether the observation record has been soft-deleted or transitioned away from being an observation (v2 only).
• 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 in the data warehouse.

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

Changes in iPCV v2

1. Removal of Columns

Several columns previously available directly in the observation (v1) table have been removed from the observation_v2 (v2) table. These include:

Why?

These columns have been removed to streamline the data model and maintain a single source of truth for reference data:

• MKB reference columns: No longer included to reduce complexity and avoid duplication, as this data is now available through dedicated reference tables
• Patient opt flags columns: Removed as these flags are maintained in the patient scope dimension table, providing a single source of truth

Decommissioned Column	Relevant ID / Column	Reason / Extended Data Model to be Referred
other_code	code_id	codeable_concept_v2
other_code_system	code_id	codeable_concept_v2
other_display	code_id	codeable_concept_v2
readv2_code	code_id	codeable_concept_v2
snomed_concept_id	code_id	codeable_concept_v2
snomed_description_id	code_id	codeable_concept_v2
confidential_patient_flag	patient_id	patient_v2
dummy_patient_flag	patient_id	patient_v2
non_regular_and_current_active_flag	patient_id	patient_v2
opt_out_93c1_flag	patient_id	patient_v2
opt_out_9nd19nu09nu4_flag	patient_id	patient_v2
opt_out_9nd19nu0_flag	patient_id	patient_v2
opt_out_9nu0_flag	patient_id	patient_v2
regular_and_current_active_flag	patient_id	patient_v2
regular_current_active_and_inactive_flag	patient_id	patient_v2
regular_patient_flag	patient_id	patient_v2
sensitive_patient_flag	patient_id	patient_v2
data_filter		Removed as it is deprecated in v2
_record_version		No longer available in v2
_update_date		Changes tracked differently in v2
_update_hour		Changes tracked differently in v2

Customer benefit

• Single source of truth for patient flags through the patient scope dimension table
• Reduced risk of data inconsistencies and synchronization issues
• Improved data lineage tracking with centralized reference tables

Customer action

• Review and update any processes or integrations that relied on the removed columns
• Use dedicated MKB reference tables for code mappings instead of inline columns
• Query the patient scope dimension table for patient opt flags and status information
• Ensure that any necessary mappings or transformations are adjusted accordingly

2. NULL value columns

Several columns in the observation_v2 table now consistently return NULL values and have been retained for backward compatibility. These include:

Why?

These columns are retained to maintain backward compatibility while returning NULL values to ensure consistent behavior in downstream processes.

Deprecated Column	Replacement ID / Column	Reason / Extended Data Model to be Referred
emis_enteredby_userinrole_guid	entered_by_user_in_role_id	user_in_role_v2
emis_authorising_userinrole_guid	authorising_user_in_role_id	user_in_role_v2
clinician_user_in_role_guid	authorising_user_in_role_id	user_in_role_v2
registration_ods_code		Always NULL as it is deprecated in v2
document_guid		Always NULL as it is deprecated in v2
processing_id		Always NULL as it is deprecated in v2
comparator		Always NULL as it is deprecated in v2
value_pq_2		Always NULL as it is deprecated in v2
user_selected		Always NULL as it is deprecated in v2
fhir_episodicity		Always NULL as it is deprecated in v2
fhir_interpretation_code		Always NULL as it is deprecated in v2

Customer benefit

• User in role information is now standardized through entered_by_user_in_role_id and authorising_user_in_role_id foreign keys joined with user_in_role_v2
• Enhanced clinical measurement representation through improved numeric operators and qualifier mapping
• Reduced risk of unexpected behavior in downstream processes
• Clear distinction between deprecated columns and new standardized relationships

Customer action

• Identify any queries or reports that use the deprecated columns
• Update processes to use entered_by_user_in_role_id and authorising_user_in_role_id, joining with user_in_role_v2 to retrieve user in role details
• Replace usage of deprecated comparator, value_pq_2, and user_selected with the new numeric_operator and qualifier_mapping columns for enhanced clinical decision support
• Use episodicity instead of fhir_episodicity for standardized episodicity representation

3. Addition of new columns

The v2 observation model introduces additional columns to enhance auditability, relationships, and clinical measurements. These include:

User tracking columns:

• entered_by_user_in_role_id
• authorising_user_in_role_id

Relationship and context columns:

• parent_observation_id
• consultation_id
• problem_observation_id
• problem_observation_uuid
• observation_organisation_id

Numeric measurement columns:

• numeric_operator
• unit_of_measure

Qualifier and range columns:

• qualifier_mapping
• range_minimum_operator
• range_minimum_text
• range_maximum_operator
• range_maximum_text
• range_units
• range_qualifier_description

Why?

These columns have been added to enhance the data model:

• User tracking columns: Enable auditing and lineage tracking by providing standardized foreign keys to the user_in_role_v2 table, replacing the previously nullable GUID columns
• Relationship and context columns: Support hierarchical observation structures, link observations to consultations and problems, and provide clear organisational context
• Numeric measurement columns: Standardize how numeric values are represented with operators (e.g., <, >, =) and units of measure
• Qualifier and range columns: Provide comprehensive support for reference ranges, qualifiers, and contextual information about measurements

Customer benefit

• Improved data consistency through standardized foreign key relationships with user_in_role_v2
• Enhanced ability to track observation hierarchies and relationships (parent observations, linked problems)
• Better support for temporal analysis and consultation-based reporting
• Standardized representation of numeric values with operators and units
• Comprehensive reference range information for clinical decision support
• More flexible filtering and reporting on observation context and relationships
• Ability to identify deleted or modified observations through soft-deletion tracking

Customer action

Leverage the new columns to enhance reporting and analytics:

• Join entered_by_user_in_role_id with user_in_role_v2 to retrieve the relevant emis_enteredby_userinrole_guid for audit trails
• Join authorising_user_in_role_id with user_in_role_v2 to retrieve the relevant emis_authorising_userinrole_guid for authorization tracking
• Use parent_observation_id to build hierarchical observation structures and understand observation relationships
• Use consultation_id to link observations to specific consultations for temporal and encounter-based analysis
• Use problem_observation_id and problem_observation_uuid to identify observations linked to specific problems
• Incorporate numeric_operator and unit_of_measure when analysing numeric observations to ensure correct interpretation
• Utilize range columns (range_minimum_operator, range_maximum_operator, etc.) for clinical decision support and flagging abnormal values
• Use transform_datetime for change-data-capture and incremental refresh logic

Examples

Get all observations for a patient

SELECT *
FROM hive.explorer_ipcv_vanilla.observation_v2
WHERE patient_id = 123456789
LIMIT 100

Find sensitive or confidential observations

SELECT *
FROM hive.explorer_ipcv_vanilla.observation_v2
WHERE is_sensitive = TRUE
  OR is_confidential = TRUE
LIMIT 100

Get observations with SNOMED mapping for interoperability

SELECT
  o.observation_id,
  o.code_id,
  o.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.code_id = m.emis_code_id
  AND o.organisation = m.organisation
WHERE m.snomed_concept_id IS NOT NULL
LIMIT 100