FAQ & Troubleshooting

Rebulk

1. What is a Rebulk?

Occasionally, large-scale processing activities may require data to be regenerated or re-ingested. These activities are referred to as rebulks and may temporarily affect data processing schedules or result in larger-than-normal volumes of changes being visible within iPCV datasets.

Billing

1. How do I know how many query seconds our query costs and how much time it took for the data to be transferred?

The cost of a query can only be known for sure after executing it. Query seconds and data transfer time are the same from a billing point of view. To measure the impact of a query in your quota, measure the time it takes for your query to execute end to end.

2. How do I know how much of my quota have I used?

Please contact your account manager.

Columns

1. What exactly is an _execution_date?

_execution_date is a string in the format yyyyMMddHHmmss. It identifies the processing run in which the data was made available.

It is not the timestamp when the underlying clinical or administrative change occurred, nor necessarily the time when the source record was created. Use the load_datetime or _ingest_time when you need to identify when the data was ingested or loaded.

2. What is the difference between _execution_date, transform_datetime, load_datetime and _ingest_time?

_execution_date identifies the processing run in which the data was made available.

transform_datetime is the timestamp for when the data was made available in iPCV. _execution_date represents this time as a string in the format yyyyMMddHHmmss.

load_datetime and _ingest_time both indicate when the data was ingested or loaded. They represent the same point in time in different formats: load_datetime is a timestamp, while _ingest_time is a string.

Use _execution_date to identify a processing run, and use load_datetime or _ingest_time when you need the time at which the data was ingested or loaded.

3. What is the purpose of _ingest_time?

_ingest_time identifies when the data was ingested or loaded. It is stored as a string and is used to find when the data was ingested or loaded.

4. Why are some datetime columns kept as strings instead of timestamps?

Every tech stack has its own notion of datetime type, so it is easier if the datetime is kept as a string and then every user can parse that string to whatever format is needed.

5. Which time zone is used in the date-like columns?

UTC

Data model

1. How to track the same patient across multiple practices?

The closest that EMIS Web has to track a person moving across multiple practices is the Person_GUID on the Patient table. This is an internally generated identifier for an individual, rather than an individual’s registration. This will always be present even if an NHS Number isn’t, or is in the wrong format.

Always remember that EMIS Web suffers from record duplication. There can be patients with multiple registrations at the same practice, with the same Person_GUID but different patient_id and emis_no values. In many of these cases, the NHS Number will also be the same.

Unfortunately, there is also a process where the same patient can have different Person GUIDs, and the chain can be broken if a user performs a merge operation in EMIS Web – this operation discards one of the merged records.

2. What is a patient’s scope?

This is a concept related to the DSA of the user querying the data. A patient scope is whether the patient can be seen by the user of the DSA. We say that a patient “enters” the scope if it wasn’t visible before and it is now. We say that a patient “leaves” or “falls out of” the scope if the patient was visible but it is not anymore.

Take for example a DSA that disallows patients who had opted out via NDOP. Given an NDOP Patient A and a non-NDOP Patient B, the user of this DSA will only be allowed to see patient B. A primary care user (whose DSA allows NDOP) will be able to see both patients A and B.

3. What codes are used when a patient has opted in/out?

This is detailed here.

4. What date is used when determining patient opt out status?

The effective date is used, this is to cope with patient records that are transferred to a different practice as the effective date remains the same but other dates may be re-entered.

5. What happens if a patient has non-corresponding opt out and opt in codes?

Each opt out code has a corresponding opt in code. If several pairs of codes are applied to a flavour the opt out codes are grouped together and the opt in codes are grouped together, the dates of the most recent code for each group are compared to determine if the patient is opted out or opted in. This means that an opt in code does not have to be from the same pair to cancel out a previous opt out code.

6. If a patient has both an opt in and an opt out code for the same effective date which takes precedence?

In this circumstance a patient is considered opted in. A patient is only considered opted out in two circumstances:

They have an opt out code and no recorded opt in codes.

If they have both opt in and opt out codes, the date of the opt out must be exclusively greater than the date of the opt in.

7. How do I know if a patient’s scope has changed?

If a patient opts out of data sharing their data will no longer be visible but this will not trigger a deletion event in the patient delta tables as the record has not been deleted. It will act as a silent deletion. The converse is true if a patient opts in, their data will start to be visible without an addition event.

8. I think the organisation table contains many duplicate rows. Is this expected? Will the live data be the same? How do we identify the primary key / unique row identifier within each iPCV view?

There shouldn’t be duplicates in terms of organisation and organisation_guid. This table is difficult to understand because it has two layers of complexity:

There is an “organisation” column which represents the “owning” organisations

Then there is an “organisation_guid”, which is the guid of every organisation with which the owning organisation has a relationship. Note that one of these is the owning organisation itself.

If you’d like to get the organisation guid of the owning organisation, the below query will do it

SELECT * from explorer_ipcv_vanilla.organisation org where org.organisation = CONCAT('CDB-', CAST(org.cdb AS VARCHAR))

9. Variable string columns generally don’t have a set maximum length. What’s the maximum string length we can expect to encounter?

Our version of the data lake doesn’t require maximum varchar values, which is handy with some columns where text with an unknown length is being added. For example, there are fields that contain full XML documents. For most fields, the length will be quite stable, it is best to decide this empirically by getting the max length of each field, and to add a margin on top. For very long fields you may need to truncate or preprocess the data.

Delta

1. Why is there an update event but none of the rows have changed?

Some flavours of iPCV present restricted views, where not all columns are shown. It is possible that an update has happened in one of the columns that are hidden in the specific flavours, thus there will be a false update event presented in the deltas.

2. Are tables partitioned?

Yes, by organisation.

3. How much history of deltas is kept?

Currently, we allow 28 days worth of deltas to be available. Beware that if a patient falls out of scope, all their deltas will be removed too.

4. Can the delta tables hold more than one update record? If so how are these to be applied to the persisted data (_execution_date)?

Yes. Delta tables are append only. Since they’re updated with every execution, any records changed between the two execution dates will receive an “update” event_type.

Incremental

1. Are tables partitioned?

Yes, by organisation.

2. If a record has a change that only affects one incremental table, is just that table updated?

No, if a change occurs anywhere in a record (e.g. a new observation is added) then the entire record is processed by iPCVs and included in all incremental views with the same execution date.

3. After pulling incrementals, I’ve noticed some execution dates are missing from some tables, am I missing data? For example, the table patient has execution dates A, B and C, but observation only has A and C.

No. Having inconsistencies in _execution_date across tables is not a reason to believe there might be missing data. These inconsistencies are expected in iPCV. There are a few reasons for this:

First, each table is independent, thus some tables may be released before others. So if you pull two tables at the same time, they may have different maximum execution dates.

The execution date will only be added to the delta_runs_index once all tables have been successfully completed. But these become available in the tables themselves as soon as they’re ready.

Similarly, it is possible that some tables have execution dates completely missing from them, either because:

A table failed but the other was successful. In this case the fail table will not have the execution date, but all the data will be included in the next execution date.

In the case of deltas, there could be changes in a subset of tables only.

Then, it also depends on the time you pulled them. As iPCV runs every day, if you pull observation today and patient tomorrow, patient will likely have an extra execution date.

Finally, within a table, the _execution_date of every row in iPCV indicates the execution in which that row was last processed, that means that a single table has multiple execution dates.

Indices

1. What is the difference between each index table?

Each index was created to track tables that are essentially different in iPCV, but these are now considered legacy. The tables runs index and delta runs index are now deprecated in favour of runs status (now called data freshness), which shows a list of update times per table, so if your flavour doesn’t support it, please raise a request for the dev team to include it.

NDOP

1. What happens with NDOP when people deregister from a practice? Does it still get updated? Or will they opt out because of the 12-day rule?

When a patient deregisters, in theory, the patient record should stay as it was at the time of deregistration as they will no longer be classed as a fully registered patient. This would mean that they would automatically opt out after 12 days. However, the record could still be updated if the practice chooses to do so, though it is very unlikely.

2. If a patient moves practices, and opt out in their new practice, does this record also matches to their previous practice?

When moving practices, patients get deregistered from their previous practice. If the practice chooses not to continue updating their record, NDOP status may be different between the two (most likely opted in at the new practice, while opted out at the previous one).

Trino

1. What is trino?

Trino is an open source query engine behind explorer. Learn more

2. If we use a Linked Server with SQL Server, do we still need to write Trino SQL?

Yes, regardless of how you query explorer, queries will always reach our Trino cluster, thus the queries need to be in a format understood by the cluster. This is standard ANSI SQL, so migrating from other SQL flavours should be straightforward. Learn more

User schema

1. What is a user schema?

Users of explorer will notice that they are not allowed to create, update or delete data in the schemas that they have access to. The exception to this rule is a user schema, which is a place for people to bring their own data into the platform.

2. Can I bring my data to EXA and do analytics on the platform?

Absolutely! You can write data to your personal user schema and join it as appropriate to the data that your DSA allows you to see. An administrator will need to give you a user schema so please raise it with your account manager.