Understanding Deltas

One of the key capabilities of iPCV is support for incremental data processing, commonly referred to as deltas.

Rather than re-extracting entire datasets every day, customers can identify and process only the records that have changed since their previous extraction. This reduces processing time, storage requirements and data movement, particularly when working with large healthcare datasets.

Customers can identify and process deltas in two ways:

1. Use the main tables and identify changed rows using the transform_datetime column.
2. Use the separate delta tables, where available.

What is a Delta?

A delta represents the set of records that have changed since a previous processing cycle. This is used by customers who wish to maintain a local copy of their dataset.

In a traditional full-load approach, customers reload an entire dataset whenever data is refreshed. As data volumes grow, this becomes increasingly expensive and complex to maintain. iPCV supports incremental processing by providing delta datasets that allow customers to focus only on records that have changed, rather than repeatedly processing unchanged data.

Two Ways to Identify Changes

Option 1: Use the Main Tables

The recommended approach is to identify deltas directly from the main tables (for example patient, allergy, observation) rather than the _delta suffix tables.

Use the transform_datetime and is_deleted columns to identify rows that are new or have changed since your previous successful load.

Main table processing pattern:

1. Track the latest transform_datetime you successfully processed.
2. On the next run, select rows from the main tables where transform_datetime is later than the last successfully processed value.
3. If is_deleted is true, delete the matching record from your local copy.
4. If is_deleted is false, upsert the record into your local copy. It may be a new record or an updated record.
5. After the load completes successfully, update your stored processing point.

There is no time limit on how far back entries can be updated as deltas using the main tables. You can pick up a change from any point in the past, unlike option 2 which has a 28-day limit.

Multiple values for transform_datetime can be loaded without needing to process them sequentially, as only the single latest record for each entry is present.

Option 2: Use Delta Tables

Some customers have access to separate delta tables. These match the names of the main tables and carry the suffix _delta. They are primarily intended to support customers migrating between iPCV v1 and v2, as they more closely match the delta update process in iPCV v1.

Delta tables contain records that have changed within the most recent 28 days. They are not intended to provide a complete historical audit trail and only present the single latest record for each entry.

Each delta record includes an event_type column that describes how the row should be applied to the target system.

event_type	How to process the record
update	Insert or update the record in the target system.
deletion	Delete the record from the target system.

A record with an event_type of update may represent either a newly created record or an updated existing record. For this reason, process all update records as upserts.

The columns in the delta tables are similar to those in the main tables, with these key differences:

• _ingest_time and is_deleted have been removed from delta tables.
• An event_type column has been added to indicate how the row should be applied (update or deletion).

Because deletion is signalled via event_type rather than the is_deleted column, delta tables can be used to directly upsert into customer environments without needing to fetch data from the main tables. Refer to the cross-domain changes documentation for complete details on column changes between iPCV v1 and v2.

Delta table processing pattern:

1. Track the latest transform_datetime you successfully processed.
2. On the next run, select rows from the delta tables where transform_datetime is later than the last successfully processed value.
3. If event_type = 'deletion', delete the matching record from the local copy.
4. If event_type = 'update', upsert the record into the local copy.
5. After the load completes successfully, update the stored processing point.

Delta tables only contain the latest delta history for the last 28 days. If a process may need to recover changes outside this window, use the main table approach instead.

Scope Changes Outside Standard Deltas

In addition to row-level deltas, some changes affect whether an organisation or patient is included in a customer’s dataset at all, rather than changing an individual row:

• Data Sharing Agreement (DSA): Check for organisations moving into or out of scope under your data sharing agreement. If an organisation newly moves into scope, extract all its data; if it moves out, remove it from your local copy. This information is available in the sharing_agreement model.
• Patient scope: Where patient-level inclusion/exclusion criteria apply, check for patients moving into or out of scope in the same way. This information is available in the patient_scope model.

Check these two before applying row-level deltas, as the deltas cannot tell you if entire organisations or patients have moved into or out of scope.

Recommended Data Loading Approach

The recommended approach is to perform an initial full load and then keep the local copy synchronised using one of the delta processing methods described above.

1. Perform an initial extraction of the required data and store it in your reporting, data warehouse or analytical environment.
2. On each subsequent run:
   1. Check sharing_agreement for DSA changes. Perform any necessary extractions and deletions for all records belonging to affected organisations.
   2. Check patient_scope (where applicable) and perform any necessary extractions and deletions for whole patient records.
   3. Identify record-level changes using one of the delta processing methods described above.
   4. Apply those record-level changes to the local copy by deleting removed records and upserting new or updated records.
   5. Record the successful load time so the next run can start from that point.

Recovering from Missed Processing Days

Customers do not need to perform a full reload if one or more processing cycles are missed and sequential processing of every missed day is not required.

Both the main tables and the delta tables only ever carry the single latest state for a record. This means if a record has had multiple updates since the customer’s last processing cycle, only one record will be present with the transform_datetime reflecting when the latest change occurred.

This enables customers to recover more easily from:

• Failed ingestion jobs
• Scheduled maintenance
• Temporary outages
• Missed extraction windows

A deleted record may appear in a table even if the customer has no corresponding record in their local copy. This can happen if one or more iPCV refresh cycles were missed — a record could be added and then deleted across separate refreshes, meaning only the later deletion is visible.

Best Practices

Process deletions explicitly

Ensure deleted records are removed from downstream systems where appropriate. For delta tables, use event_type = 'deletion'. For main tables, use is_deleted.

Maintain processing records

Track the last successfully processed execution to support reliable ingestion and recovery.

Treat updates as upserts

Do not try to distinguish between newly created records and updated records. Apply all non-deleted changed rows as insert-or-update operations.

Process deltas regularly

Regular processing reduces the amount of data that must be handled during each refresh cycle.

Design for recovery

Ensure ingestion processes can recover from failures. If using separate delta tables, remember that only 28 days of delta history is retained.

Frequently Asked Questions

Should I use full extracts or deltas?

For most ongoing reporting and analytical workloads, perform an initial full extraction then use delta processing thereafter. This is generally the most efficient approach for consuming iPCV data.

Should I use delta tables or main tables?

This is a matter of customer preference, but using the main tables is recommended as there is no retention limit.

Do delta tables contain every historical change to a record?

No. Delta tables contain the latest available change within the delta retention window and are designed for synchronisation rather than maintaining a complete change history. Customers requiring historical changes beyond the 28-day retention window should use the main incremental datasets.

What values can event_type contain?

In the separate delta tables, event_type will only be 'deletion' or 'update'.

Should I use transform_datetime or _execution_date to identify changed records?

These columns contain the same data in different formats: transform_datetime as a timestamp and _execution_date as the equivalent varchar in the format yyyyMMddHHmmss. Timestamps are generally more efficient, so transform_datetime is recommended. _execution_date is included only for customers migrating from iPCV v1. See Dates and Time Fields for further detail.