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 iceberg.<flavour_schema>.patient_v2 p
   JOIN iceberg.<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.