Schema

Core

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

Column Name	Data Type	Description	Example
organisation_id	bigint	The unique internal identifier for the organisation record within an organisation.	12345
organisation	varchar	An identifier for the source of data (an organisation) relating to a given GP practice	CDB-50002
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.	20240917084800
is_deleted	boolean	Indicates if this record should be considered soft deleted.	FALSE
organisation_guid	varchar	The GUID for the organisation record within an organisation.	‘82E6FF36-87E2-11EF-B3A3-48DF37DF55D0’
organisation_uuid	varchar	The UUID derived from organisation_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
customer_database_id	bigint	EMIS Customer Database ID	50002
name	varchar	The name of the organisation	The Dermatologist Practice
ods_code	varchar	The organisational identifier that the NHS assigns an organisation	V81999
open_datetime	timestamp(6) with time zone	Timestamp of when the organisation opened	‘2023-09-01 09:00:00.000000 UTC’
close_datetime	timestamp(6) with time zone	Timestamp of when the organisation closed	‘2023-09-01 09:00:00.000000 UTC’
is_open	boolean	Flag to indicate if the organisation is classed as open	TRUE
organisation_type_id	bigint	The unique internal identifier for the organisation type	2
organisation_type_description	varchar	The description of organisation type as defined on the source system	General Practice
speciality_codes	varchar	A comma separated speciality codes associated with the organisation	100,101
commissioner	varchar	The commissioning organisation associated with the ODS code	NHS England
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	Number or name from the address	2
number_and_street	varchar	Address number or street name	Hill House
village	varchar	Village from the organisations address	Rawdon
town	varchar	Town from organisations address	Leeds
county	varchar	County from address	West Yorkshire
postcode	varchar	Organisation’s postcode	LS997BZ
region_id	bigint	The unique internal identifier for the region	0
main_location_id	bigint	The unique internal identifier for the main location, can be used to join to location model	12345
main_location_guid	varchar	The GUID for the main location	‘A3E962E4-CB3B-40B2-8D86-D850CB9BF1A4’
main_location_uuid	varchar	The UUID derived from main_location_id and organisation, providing a stable unique identifier.	‘a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
is_this_organisation	boolean	The flag to indicate if the current row’s data relates to the organisation in the organisation column and not for an organisation under it	TRUE
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.	20240917084800

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
_record_version	varchar	✓	✗
_ingest_time	varchar	✓	✓
is_deleted	boolean	✓	✓		Renamed from deleted in V1
load_datetime	timestamp(6) with time zone	✗	✓
emis_organisation_id	bigint	✓	✓	organisation_id
organisation_guid	varchar	✓	✓
organisation_uuid	varchar	✗	✓
organisation_type_id	bigint	✗	✓
organisation_type_description	varchar	✓	✓
main_location_id	bigint	✗	✓
emis_main_location_guid	varchar	✓	✓	main_location_guid
main_location_uuid	varchar	✗	✓
cdb	bigint	✓	✓	customer_database_id
name	varchar	✓	✓
is_this_organisation	boolean	✗	✓
open_date	date	✓	✓	open_datetime
close_date	date	✓	✓	close_datetime
is_open	boolean	✓	✓
speciality_codes	varchar	✗	✓
commissioner	varchar	✗	✓
ods_code	varchar	✓	✓
address_id	bigint	✗	✓
address_uuid	varchar	✗	✓
region_id	bigint	✗	✓
address_line_1	varchar	✓	✓	house_name_flat_number
address_line_2	varchar	✓	✓	number_and_street
address_line_3	varchar	✓	✓	village
address_line_4	varchar	✓	✓	town
address_line_5	varchar	✓	✓	county
postcode	varchar	✓	✓
parent_emis_organisation_id	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
ccg_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
ccg_emis_organisation_id	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
full_address	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
parent_organisation_type_description	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
releaseversion	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
nhs_role	integer	✓	✓		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	✓	✓

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
_record_version	varchar	✓	✗
_ingest_time	varchar	✓	✓
is_deleted	boolean	✓	✓		Renamed from deleted in V1
load_datetime	timestamp(6) with time zone	✗	✓
emis_organisation_id	bigint	✓	✓	organisation_id
organisation_guid	varchar	✓	✓
organisation_uuid	varchar	✗	✓
organisation_type_id	bigint	✗	✓
organisation_type_description	varchar	✓	✓
main_location_id	bigint	✗	✓
emis_main_location_guid	varchar	✓	✓	main_location_guid
main_location_uuid	varchar	✗	✓
speciality_codes	varchar	✗	✓
commissioner	varchar	✗	✓
is_this_organisation	boolean	✗	✓
cdb	bigint	✓	✓	customer_database_id
name	varchar	✓	✓
ods_code	varchar	✓	✓
ccg_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
open_date	date	✓	✓	open_datetime
close_date	date	✓	✓	close_datetime
is_open	boolean	✓	✓
address_id	bigint	✗	✓
address_uuid	varchar	✗	✓
address_line_1	varchar	✓	✓	house_name_flat_number
address_line_2	varchar	✓	✓	number_and_street
address_line_3	varchar	✓	✓	village
address_line_4	varchar	✓	✓	town
address_line_5	varchar	✓	✓	county
postcode	varchar	✓	✓
region_id	bigint	✗	✓
ccg_emis_organisation_id	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
full_address	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
parent_emis_organisation_id	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
parent_organisation_type_description	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
releaseversion	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
nhs_role	integer	✓	✓		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	✓	✓

Hermes

The table below presents the flavour specific schema.

Column Name	Data Type	Core Mapping
is_deleted	boolean
pseudo_organisation_uuid	varchar	organisation_uuid
pseudo_organisation_guid	varchar	organisation_guid
ods_code	varchar
open_datetime	timestamp(6) with time zone
close_datetime	timestamp(6) with time zone
is_open	boolean
organisation_type_id	bigint
organisation_type_description	varchar
speciality_codes	varchar
commissioner	varchar
postcode	varchar
region_id	bigint
pseudo_main_location_uuid	varchar	main_location_uuid
is_this_organisation	boolean
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_organisation_id	bigint	✗	✓	organisation_id
emis_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
main_location_id	bigint	✗	✓
emis_main_location_guid	varchar	✓	✓	main_location_guid
main_location_uuid	varchar	✗	✓
organisation_type_id	bigint	✗	✓
organisation_type_description	varchar	✓	✓
speciality_codes	varchar	✗	✓
commissioner	varchar	✗	✓
is_this_organisation	boolean	✗	✓
cdb	bigint	✓	✓	customer_database_id
name	varchar	✓	✓
ods_code	varchar	✓	✓
address_id	bigint	✗	✓
address_uuid	varchar	✗	✓
region_id	bigint	✗	✓
ccg_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
open_date	date	✓	✓	open_datetime
close_date	date	✓	✓	close_datetime
is_open	boolean	✓	✓
nhs_role	integer	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
address_line_1	varchar	✓	✓	house_name_flat_number
address_line_2	varchar	✓	✓	number_and_street
address_line_3	varchar	✓	✓	village
address_line_4	varchar	✓	✓	town
address_line_5	varchar	✓	✓	county
postcode	varchar	✓	✓
releaseversion	varchar	✓	✓		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	✓	✓

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	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
organisation_id	bigint	✗	✓
emis_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
main_location_id	bigint	✗	✓
emis_main_location_guid	varchar	✓	✓	main_location_guid
main_location_uuid	varchar	✗	✓
organisation_type_id	bigint	✗	✓
organisation_type_description	varchar	✓	✓
speciality_codes	varchar	✗	✓
commissioner	varchar	✗	✓
is_this_organisation	boolean	✗	✓
cdb	bigint	✓	✓	customer_database_id
ods_code	varchar	✓	✓
emis_parent_organisation_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_ccg_organisation_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
ccg_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
open_date	date	✓	✓	open_datetime
close_date	date	✓	✓	close_datetime
is_open	boolean	✓	✓
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	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
organisation_id	bigint	✗	✓
emis_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
main_location_id	bigint	✗	✓
emis_main_location_guid	varchar	✓	✓	main_location_guid
main_location_uuid	varchar	✗	✓
organisation_type_id	bigint	✗	✓
organisation_type_description	varchar	✓	✓
speciality_codes	varchar	✗	✓
commissioner	varchar	✗	✓
is_this_organisation	boolean	✗	✓
cdb	bigint	✓	✓	customer_database_id
ods_code	varchar	✓	✓
ccg_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_parent_organisation_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
emis_ccg_organisation_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
open_date	date	✓	✓	open_datetime
close_date	date	✓	✓	close_datetime
is_open	boolean	✓	✓
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	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
organisation_id	bigint	✗	✓
organisation_guid	varchar	✓	✓
organisation_uuid	varchar	✗	✓
organisation_type_id	bigint	✗	✓
organisation_type	varchar	✓	✓	organisation_type_description
main_location_id	bigint	✗	✓
main_location_guid	varchar	✓	✓
main_location_uuid	varchar	✗	✓
cdb	bigint	✓	✓	customer_database_id
speciality_codes	varchar	✗	✓
commissioner	varchar	✗	✓
is_this_organisation	boolean	✗	✓
ods_code	varchar	✓	✓
open_date	date	✓	✓	open_datetime
close_date	date	✓	✓	close_datetime
is_open	boolean	✓	✓
parent_organisation_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
ccg_organisation_guid	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
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
_record_version	varchar	✓	✗
_ingest_time	varchar	✓	✓
is_deleted	boolean	✓	✓		Renamed from deleted in V1
load_datetime	timestamp(6) with time zone	✗	✓
emis_organisation_id	bigint	✓	✓	organisation_id
organisation_guid	varchar	✓	✓
organisation_uuid	varchar	✗	✓
organisation_type_id	bigint	✗	✓
organisation_type_description	varchar	✓	✓
main_location_id	bigint	✗	✓
emis_main_location_guid	varchar	✓	✓	main_location_guid
main_location_uuid	varchar	✗	✓
speciality_codes	varchar	✗	✓
commissioner	varchar	✗	✓
is_this_organisation	boolean	✗	✓
cdb	bigint	✓	✓	customer_database_id
name	varchar	✓	✓
ods_code	varchar	✓	✓
address_id	bigint	✗	✓
address_uuid	varchar	✗	✓
region_id	bigint	✗	✓
ccg_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
open_date	date	✓	✓	open_datetime
close_date	date	✓	✓	close_datetime
is_open	boolean	✓	✓
address_line_1	varchar	✓	✓	house_name_flat_number
address_line_2	varchar	✓	✓	number_and_street
address_line_3	varchar	✓	✓	village
address_line_4	varchar	✓	✓	town
address_line_5	varchar	✓	✓	county
postcode	varchar	✓	✓
parent_emis_organisation_id	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
ccg_emis_organisation_id	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
full_address	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
parent_organisation_type_description	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
releaseversion	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
nhs_role	integer	✓	✓		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	✓	✓

Zeus

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
_record_version	varchar	✓	✗
_ingest_time	varchar	✓	✓
load_datetime	timestamp(6) with time zone	✗	✓
is_deleted	boolean	✓	✓		Renamed from deleted in V1
emis_organisation_id	bigint	✓	✓	organisation_id
emis_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
main_location_id	bigint	✗	✓
emis_main_location_guid	varchar	✓	✓	main_location_guid
main_location_uuid	varchar	✗	✓
cdb	bigint	✓	✓	customer_database_id
address_id	bigint	✗	✓
address_uuid	varchar	✗	✓
ccg_emis_organisation_id	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
ccg_ods_code	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
parent_emis_organisation_id	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
parent_organisation_type_description	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
releaseversion	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
nhs_role	integer	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
name	varchar	✓	✓
ods_code	varchar	✓	✓
open_date	date	✓	✓	open_datetime
close_date	date	✓	✓	close_datetime
is_open	boolean	✓	✓
organisation_type_id	bigint	✗	✓
organisation_type_description	varchar	✓	✓
speciality_codes	varchar	✗	✓
commissioner	varchar	✗	✓
address_line_1	varchar	✓	✓	house_name_flat_number
address_line_2	varchar	✓	✓	number_and_street
address_line_3	varchar	✓	✓	village
address_line_4	varchar	✓	✓	town
address_line_5	varchar	✓	✓	county
full_address	varchar	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
postcode	varchar	✓	✓
is_this_organisation	boolean	✗	✓
data_filter	integer	✓	✗		Removed in v2
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓