Schema

Core

The core schema represents the Location schema derived from the EMIS source system.

Column Name	Data Type	Description	Example
location_id	bigint	The unique internal identifier for the location record within an organisation.	123456
organisation	varchar	An identifier for the source of data (an organisation) relating to a given GP practice	‘CDB-12345’
load_datetime	timestamp(6) with time zone	The datetime that the record was last upserted into the EXA data lake from EMIS source system with a relevant change from source system.	‘2023-09-01 09:00:00.000000 UTC’
_ingest_time	varchar	The load_datetime formatted as yyyyMMddHHmmss. This column is provided only for backward compatibility.	‘20230512143015’
is_deleted	boolean	Indicates if this record should be considered soft deleted.	TRUE
location_guid	varchar	The GUID for the location record within an organisation.	‘12a3b456-7c89-0d1e-234f-5gh6i789jk01’
location_uuid	varchar	The UUID derived from location_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
location_name	varchar	The name of the location.	‘Main Surgery’
parent_location_id	bigint	The unique internal identifier for the parent location.	123455
email_address	varchar	The email address of the location.	‘info@surgeryname.nhs.uk’
phone_number	varchar	The phone number of the location.	‘01234 567890’
fax_number	varchar	The fax number of the location.	‘01234 567891’
main_contact_name	varchar	The name of the main contact associated with the location.	‘Dr. Jane Smith’
open_date	date	The date the location was opened.	‘2010-03-15’
close_date	date	The date the location was closed.	‘2022-12-31’
location_type_id	bigint	The unique internal identifier for the location type.	12
location_type_description	varchar	The EMIS description of the location type.	‘GP Practice’
code_id	bigint	The EMIS code identifier for the location type associated with the location	1572871000006117
address_id	bigint	The unique internal identifier for the address.	12345
address_uuid	varchar	The UUID derived from address_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
house_name_flat_number	varchar	The house name or flat number from the address.	‘House’
number_and_street	varchar	The number and street from the address.	‘45 Example Street’
village	varchar	The village or borough from the address.	‘Exampleford’
town	varchar	The town or city from the address.	‘Exampleford’
county	varchar	The county from the address.	‘West Exampleshire’
postcode	varchar	The postcode for the location.	‘AB11 1CD’
transform_datetime	timestamp(6) with time zone	The timestamp indicating when the record was last processed and updated in the data model.	‘2023-09-01 09:00:00.000000 UTC’
_execution_date	varchar	The transform_datetime formatted as yyyyMMddHHmmss. This column is provided only for backward compatibility.	‘20230512143015’

Vanilla

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓		Renamed from _base_ingest_time in V1
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_location_id	bigint	✓	✓	location_id
emis_location_guid	varchar	✓	✓	location_guid
location_uuid	varchar	✗	✓
parent_location_id	bigint	✗	✓
emis_parent_location_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
location_name	varchar	✓	✓
location_type_id	bigint	✗	✓
location_type_description	varchar	✓	✓
code_id	bigint	✗	✓
open_date	date	✓	✓
close_date	date	✓	✓
main_contact_name	varchar	✓	✓
email_address	varchar	✓	✓
phone_number	varchar	✓	✓
fax_number	varchar	✓	✓
address_id	bigint	✗	✓
address_uuid	varchar	✗	✓
house_name_flat_number	varchar	✓	✓
number_and_street	varchar	✓	✓
village	varchar	✓	✓
town	varchar	✓	✓
county	varchar	✓	✓
postcode	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Apollo

Not used in this schema

Artemis

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓		Renamed from _base_ingest_time in V1
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_location_id	bigint	✓	✓	location_id
emis_location_guid	varchar	✓	✓	location_guid
location_uuid	varchar	✗	✓
location_name	varchar	✓	✓
location_type_id	bigint	✗	✓
location_type_description	varchar	✓	✓
parent_location_id	bigint	✗	✓
emis_parent_location_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
email_address	varchar	✓	✓
phone_number	varchar	✓	✓
fax_number	varchar	✓	✓
main_contact_name	varchar	✓	✓
open_date	date	✓	✓
close_date	date	✓	✓
address_id	bigint	✗	✓
address_uuid	varchar	✗	✓
house_name_flat_number	varchar	✓	✓
number_and_street	varchar	✓	✓
village	varchar	✓	✓
town	varchar	✓	✓
county	varchar	✓	✓
postcode	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Hermes

The table below presents the flavour specific schema.

Column Name	Data Type	Core Mapping	Comments
is_deleted	boolean
pseudo_location_uuid	varchar	location_uuid	Hashed
location_type_id	bigint
location_type_description	varchar
code_id	bigint
open_date	date
close_date	date
postcode	varchar
organisation	varchar
transform_datetime	timestamp(6) with time zone

Hestia

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_location_id	bigint	✓	✓	location_id
emis_location_guid	varchar	✓	✓	location_guid
location_uuid	varchar	✗	✓
location_name	varchar	✓	✓
parent_location_id	bigint	✗	✓
emis_parent_location_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
email_address	varchar	✓	✓
phone_number	varchar	✓	✓
fax_number	varchar	✓	✓
main_contact_name	varchar	✓	✓
open_date	date	✓	✓
close_date	date	✓	✓
location_type_id	bigint	✗	✓
location_type_description	varchar	✓	✓
address_id	bigint	✗	✓
address_uuid	varchar	✗	✓
house_name_flat_number	varchar	✓	✓
number_and_street	varchar	✓	✓
village	varchar	✓	✓
town	varchar	✓	✓
county	varchar	✓	✓
postcode	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Olympus

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✓	✓		Renamed from deleted in V1
load_datetime	timestamp(6) with time zone	✗	✓
emis_location_id	bigint	✓	✓	location_id
emis_location_guid	varchar	✓	✓	location_guid
location_uuid	varchar	✗	✓
location_type_id	bigint	✗	✓
location_type_description	varchar	✓	✓
parent_location_id	bigint	✗	✓
emis_parent_location_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
open_date	date	✓	✓
close_date	date	✓	✓
processing_id	bigint	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Prometheus

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✓	✓		Renamed from deleted in V1
load_datetime	timestamp(6) with time zone	✗	✓
emis_location_id	bigint	✓	✓	location_id
emis_location_guid	varchar	✓	✓	location_guid
location_uuid	varchar	✗	✓
location_type_id	bigint	✗	✓
location_type_description	varchar	✓	✓
parent_location_id	bigint	✗	✓
emis_parent_location_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
open_date	date	✓	✓
close_date	date	✓	✓
processing_id	bigint	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓		Renamed from execution_date in V1

Pseudo_Anon

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✓	✓		Renamed from deleted in V1
load_datetime	timestamp(6) with time zone	✗	✓
location_id	bigint	✓	✓
location_guid	varchar	✓	✓
location_uuid	varchar	✗	✓
parent_location_id	bigint	✗	✓
parent_location_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
open_date	date	✓	✓
close_date	date	✓	✓
location_type_id	bigint	✗	✓
location_type_description	varchar	✓	✓
processing_id	bigint	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓		Renamed from execution_date in V1

Themis

The table below presents the flavour specific schema and maps columns between core, V1, and V2. Refer to Changes in iPCV V2 for more information.

Column Name	Data Type	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✓		Renamed from _base_ingest_time in V1
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_location_id	bigint	✓	✓	location_id
location_uuid	varchar	✗	✓
emis_location_guid	varchar	✓	✓	location_guid
parent_location_id	bigint	✗	✓
emis_parent_location_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
location_type_id	bigint	✗	✓
location_type_description	varchar	✓	✓
code_id	bigint	✗	✓	location_id
location_name	varchar	✓	✓
main_contact_name	varchar	✓	✓
open_date	date	✓	✓
close_date	date	✓	✓
email_address	varchar	✓	✓
phone_number	varchar	✓	✓
fax_number	varchar	✓	✓
address_id	bigint	✗	✓
address_uuid	varchar	✗	✓
house_name_flat_number	varchar	✓	✓
number_and_street	varchar	✓	✓
village	varchar	✓	✓
town	varchar	✓	✓
county	varchar	✓	✓
postcode	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Zeus

Not used in this schema