Universal improvements

Enhancements

Optimised data ingestion timing - faster data delivery

We are shifting the start time of our data ingestion process from midnight to 11 PM every day. This adjustment allows us to deliver EMIS Web data updates earlier, giving access to fresh data faster.

Benefit

This change provides the following benefits:

• Faster data availability: Begin working with updated data sooner, providing more time for analysis

• Clear insights on new updates: A new consolidated table shows what changed and when, helping track with ease

Customer Action

To adopt this change, customers should:

1. Review Data Access Schedules: Adjust any internal schedules or processes that rely on the availability of updated data to align with the new ETL times

2. Monitor Data Availability: Ensure that systems and applications are configured to handle the earlier data refresh and validate the timely receipt of data

3. Communicate with Teams: Inform relevant team members about the change to ensure smooth adaptation and integration into existing workflows

Standardised column names and new datetime fields

To improve consistency and provide more granular time-based filtering, we have introduced new columns and standardised column names in iPCV v2.

• A new transform_datetime column has been added to all views. This column shows the exact datetime the data was made available in the model.

• In all bulk views (except MKB-related models) that has _ingest_time will have additional formatted datetime column named load_datetime for faster query response

• In all the iPCV v2 flavours, the information on ingest time, deleted and execution date will be available under _ingest_time, is_deleted and _execution_date column respectively for better clarity and consistency.

Benefit

This change provides the following benefits:

• Efficient data filtering: The new datetime columns allow for more precise and faster data filtering.

• Consistent naming: Standardised column names across all data models reduce confusion and improve usability.

Customer Action

To adopt this change, customers should:

• Utilize the new datetime columns in the filters for improved performance.

• Update any queries or applications that refer to the old column names to use the new standardised names.

Simplified & faster delta views

In iPCV v2, following changes have been made:

• Delta tables now only include one entry per record, containing the latest update within last 28 days

• All changes whether additions or updates are presented as updates records, removing any distinction between them

• The event_type column will therefore only contain values of ‘update’ or ‘deletion’

• The _ingest_time and is_deleted columns are removed, as delta tables now only present the single latest change for each record.

• Delta data is now being generated at the point of data insertion, eliminating additional post processing steps

These changes have been made due the the following:

• To simplify ingestion logic for customers by ensuring only latest changes are presented

• To improve performance and consistency - customers now receive delta data faster each day

• To enable flexible data recovery - missing data days no longer require sequential processing

• To reduce complexity in how deltas are interpreted and handled

Benefit

This change provides the following benefits:

• Faster availability of daily delta data

• Simpler ingestion: 1 row per changed record, with no need to filter multiple versions

• Reduced processing overhead: no need for CTEs or complex logic to de-duplicate changes

• More resilient ingestion - if a customer misses a day, they can ingest the next available delta without sequential catch up

• Correct handling of deletions: deletions are now explicitly and reliably flagged, improving data integrity

• Cleaner schema: event_type simplifies deletion handling & unifies record change representation

Customer Action

Customers should:

• Perform deletes and treat all ‘update’ records as upserts, even if the record is new or missing in the system referring to the ‘event_type’ column

• If previously ingesting deltas sequentially by execution_date, this can continue, but is no longer required.

• If a CTE was used to de-duplicate delta records, this can be removed as v2 deltas already include only the latest change per record

• Be aware that delta tables do no contain full change history. This aligns with how iPCV v1 operated (latest processed state only), but is now more explicit

• For customers requiring access to older changes (>28 days), the main incremental tables can now be used directly, offering more flexibility beyond the delta window.

Removal of FHIR-Derived Fields from relevant data models

We have now removed fields in the data models that had fields which originated from FHIR specifications (e.g., fhir_patient_active, fhir_registration_type, and similar attributes).

This change has been made due to the following:

• The FHIR version implemented (3.0) in iPCVs is outdated compared to current standards (5.0), creating compliance gaps

• These fields often misrepresent EMIS Web data (e.g., acute medication incorrectly marked as completed), leading to inaccurate clinical and analytical outputs

• Maintaining FHIR-derived fields adds unnecessary complexity and governance overhead without clear benefit, as EMIS-native fields already provide the required functionality

• Removing these fields improves data integrity, simplifies the model, and ensures consistency with EMIS-native logic

Benefit

This change provides the following benefits:

• Improved Data Accuracy: Removing outdated FHIR-derived fields ensures that patient information reflects EMIS-native logic, reducing the risk of misinterpretation in clinical workflows

• Simplified Integrations: Downstream systems and analytics will rely on consistent, validated EMIS fields, minimizing complexity and errors in data exchange

• Enhanced Reliability: Eliminating redundant attributes improves overall data integrity, leading to more trustworthy insights for care planning and reporting.

• Future-Proofing: Aligning with EMIS standards rather than legacy FHIR mappings positions the solution for easier maintenance and compatibility with future updates

Customer Action

To adopt this change, customers should:

1. Update ETL pipelines, queries, and transformations to exclude deprecated FHIR fields

2. Ensure joins and lookups use EMIS-native identifiers. Below are the alternatives to use:

Data Model	FHIR fields (column names as they appear in your flavors)	Alternate EMIS Field
Patient	fhir_patient_active	Customers should use the EMIS is_registered and is_active flags, as these provide the relevant registration and active status information. For more details on the changes to patient registration status and active status logic, please refer to the documentation section on this page covering patient model changes
Patient	fhir_registration_type	Customers should use the EMIS patient_type field, as this contains the same information and was the source for the FHIR registration type.
Allergy	fhir_status	No alternate field is provided, as this was a hard-coded field and did not add value. Customers do not need to replace it
Allergy	fhir_category	Use category field instead as it serves the same purpose as the one being deprecated
Allergy	fhir_episodicity	There is no direct alternate for fhir_episodicity field. Customers should use the existing emis_episodicity field, as fhir_episodicity was derived from it
Medication	intent / fhir_medication_intent	TBD
Medication	agency / nhs_prescribing_agency	No alternate EMIS field is available for these, as they were hard coded and not adding value. Customers do not need to use a replacement field
Medication	type / nhs_prescription_type	Customers should use the emis_prescription_type field, as the FHIR prescription type was derived from this field
Medication	status / fhir_medication_status	Customers should use the emis_drug_status field, as FHIR medication status was derived from this. If issue record status is needed, it can be derived from the cancellation flag in EMIS records
Observation	interpretation_code / fhir_interpretation_code	Customers should use the EMIS abnormal_flag field, as the FHIR interpretation code was derived from this field and there is no direct replacement
Problem	fhir_problem_significance_description / fhir_significance	Customers should use the EMIS significance column, as this provides the same information and was the source for the FHIR significance fields
Problem	relation / fhir_relation	Customers should use the EMIS relation field, as it provides the same information and was the source for the FHIR relation fields
Problem	status / fhir_status	Customers should use the EMIS problem_status field, as it provides the same information and was the source for the FHIR relation field
Referral	nhs_priority_description	Customers should use the EMIS urgency field, which provides the same information and was the source for the NHS priority description
Referral	priority / nhs_priority	Customers should use the EMIS urgency field, as both priority and NHS priority were derived from urgency and it provides the same information
Referral	source / nhs_source	Customers should use the EMIS direction field, as source was derived from direction and provides the same information

mkb_version column removed

We have removed the ‘mkb_version’ column in iPCV v2:

• Only the latest version of drug codes is now displayed

• The ‘_record_version‘, ‘_mkb_version‘ and ‘_ingest_time‘ columns are now removed

• The version information is managed internally

• All queries automatically use the current authoritative version

iPCV v1 exposed multiple versions of MKB codes through the ‘mkb_version’ column, which created potential confusion when analysing data. This approach required users to understand which version to use and increased the risk of inconsistent analysis when comparing results across organizations.

Benefit

This change provides the following benefits:

• Simplified data model with fewer columns to understand and manage

• Elimination of version-related confusion when analysing data

• Consistent results across all analyses without needing to specify versions

Customer Action

Customers should review any existing queries or applications that explicitly reference to the ‘mkb_version’ related columns that are removed |

Streamlined ODS Code Management Across Data Models

To simplify data models and improve consistency, we have streamlined how ODS codes are managed in iPCV v2.

1. The ODS Code field has been removed from all data models except organisation.

2. If customers need the ODS Code, they can now join their model (e.g., patient, observation, consultation and so on) to organisation using the organisation key

These changes are made for the following reason:

• Previously, ODS Code existed in multiple large data models

• When NHS documentation updates the ODS Code, this required re-modeling millions of rows across all models, forcing customers to re-run ETL processes and consume significant compute and Explorer minutes

• By centralizing ODS Code in organisation, only one record needs updating, and customers only need to ETL one record, reducing:

  • Operational overhead

  • Compute costs

  • Impact on Explorer minutes

Benefit

• Reduced Impact of ODS Code Changes: Customers no longer need to reprocess millions of rows when ODS Code updates occur

• Lower Compute and Cost: ETL operations are minimized to a single record in organisation, saving compute resources and reducing Explorer minute consumption

• Simplified Data Maintenance: Centralizing ODS Code in one dimension makes updates easier and ensures consistency across all models

• Improved Performance: Less data movement means faster processing and more efficient queries

Customer Action

1. Update Queries: If ODS Code was previously referenced directly in other data models, update queries to join the relevant model to organisation using the organisation key.

   Example:

   SELECT p.patient_id, p.registration_organisation_id, o.ods_code
   FROM hive.<flavour_schema>.patient_v2 p
   JOIN hive.<flavour_schema>.organisation_v2 o
     ON p.registration_organisation_id = o.organisation_id
     and p.organisation = o.organisation;

2. Review ETL Processes: Ensure any ETL workflows that relied on ODS Code in other models are adjusted to pull it from organisation.

3. Validate Reports/Dashboards: Check any reports or dashboards using ODS Code to confirm they now reference organisation.

Consolidation of Patient Filter and Opt-Out Columns

To reduce data duplication and establish a single source of truth, we have consolidated all patient-level filter and opt-out flags into the Patient model only. These columns have been removed from clinical event models in iPCV v2.

Affected Models:

• Observation
• Allergy
• Immunisation
• Consultation (Encounter)
• Medication
• Recall (Diary)

Note: The Observation Opt-Out view, which was only available in some flavours in iPCV v1, is no longer available in any flavour in iPCV v2. All patient filter and opt-out data points are now exclusively available in the Patient model.

Removed Columns (from clinical models):

• confidential_patient_flag
• dummy_patient_flag
• opt_out_93c1_flag
• opt_out_9nd19nu09nu4_flag
• opt_out_9nd19nu0_flag
• opt_out_9nu0_flag
• non_regular_and_current_active_flag
• regular_and_current_active_flag
• regular_current_active_and_inactive_flag
• regular_patient_flag
• sensitive_patient_flag

Important Note: Record-level confidentiality and sensitivity flags (confidential_flag, sensitive_flag) are retained in clinical models as these indicate record-specific attributes, not patient-level attributes.

Why this change was made

• Eliminate duplication: Previously, patient opt-out and filter information was duplicated across multiple clinical models and via a separate Observation Opt-Out model, increasing processing costs and model complexity

• Single source of truth: The Patient model now contains all patient-level filter and opt-out flags with current state columns (e.g., is_regular rather than was_regular)

• Reduce customer confusion: Having the same patient-level information in multiple places created ambiguity about which source was authoritative

• Improve data consistency: Eliminates risk of conflicting filter information when patient opt-out status changes

• Simplified maintenance: Changes to patient opt-out status only need to be maintained in one location

Benefit

This change provides the following benefits:

• Cleaner schemas: Clinical event models no longer carry duplicate patient-level columns, making them easier to understand and query

• Consistent approach: Uniform pattern for accessing patient filtering across all clinical models

• Reduced data inconsistencies: Patient opt-out status changes are reflected in one place, ensuring consistency across all related clinical records

• More efficient queries: Join to Patient model once rather than carrying duplicate columns in every clinical event model

• Clear separation of concerns: Patient-level attributes (in Patient model) vs. record-level attributes (in clinical models)

• Deprecation of redundant models: The Observation Opt-Out model is no longer needed; equivalent behavior can be achieved via documented joins to the Patient model

Customer Action

To access patient filter and opt-out information in v2, customers must now join clinical models to the patient_v2 table:

Example - Filtering recalls by patient opt-out status:

-- V2: Join to patient_v2 for patient filter information
SELECT
    r.diary_id,
    r.emis_diary_guid,
    r.emis_patient_id,
    r.recorded_date,
    p.is_regular,
    p.is_opt_out_9nu0,
    p.is_dummy
FROM hive.explorer_ipcv_vanilla.recall_v2 AS r
LEFT JOIN hive.explorer_ipcv_vanilla.patient_v2 AS p
    ON r.emis_patient_id = p.emis_patient_id
    AND r.organisation = p.organisation
WHERE r.is_deleted = FALSE
    AND p.is_regular = TRUE
    AND COALESCE(p.is_opt_out_9nu0, FALSE) = FALSE
    AND COALESCE(p.is_dummy, FALSE) = FALSE;

Customer actions:

1. Update queries: Modify any queries that previously used patient filter columns directly from clinical models to join to patient_v2 instead

2. Review ETL pipelines: Ensure any ETL logic that relied on patient filter columns in clinical models is updated to join to Patient model

3. Deprecate Observation Opt-Out usage: The Observation Opt-Out view, which was only available in some flavours in v1, is no longer available in any flavour in iPCV v2. If you were using the Observation Opt-Out model, migrate to using Patient model joins as shown in the example above. All patient opt-out and filter data is now available exclusively in the Patient model

4. Understand the distinction: Recognize that confidential_flag and sensitive_flag remain in clinical models as they indicate record-specific sensitivity, not patient-level status

5. Refer to Patient model documentation: For detailed information on patient opt-out flags and their meanings, see the Patient model documentation

Note: The Patient model in v2 includes patients that are not in scope (with sensitive data nulled) to enable full understanding of patient status. This ensures you can still identify why a patient might be excluded from your dataset.