Schema

Core

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

Column Name	Data Type	Description	Example
is_deleted	boolean	Indicates if this record should be considered soft deleted.	FALSE
organisation_id	bigint	The unique internal identifier for the organisation record within an organisation.	12345
location_id	bigint	The location identifier	67890
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
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
location_guid	varchar	The GUID for the location record within an organisation.	A3E962E4-CB3B-40B2-8D86-D850CB9BF1A4
location_uuid	varchar	The UUID derived from location_id and organisation, providing a stable unique identifier.	a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d
is_main_location	boolean	Flag to indicate if this is the main location for the organisation	TRUE
address_id	bigint	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
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.	20241217134400

Vanilla

Not used in this schema

Apollo

Not used in this schema

Artemis

Not used in this schema

Hermes

The table below presents the flavour specific schema.

Column Name	Data Type	Core Mapping	Comments
is_deleted	boolean
pseudo_organisation_uuid	varchar	organisation_uuid	Hashed
pseudo_location_uuid	varchar	location_uuid	Hashed
is_main_location	boolean
organisation	varchar
transform_datetime	timestamp(6) with time zone

Hestia

Not used in this schema

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	✓	✓
location_id	bigint	✗	✓
location_guid	varchar	✗	✓
location_uuid	varchar	✗	✓
organisation_id	bigint	✗	✓
emis_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
is_deleted	boolean	✓	✓		Renamed from deleted in V1
load_datetime	timestamp(6) with time zone	✗	✓
is_main_location_flag	boolean	✓	✓	is_main_location
emis_main_location_guid	varchar	✓	✗		Always NULL as it is deprecated in v2. Refer to Changes in iPCV V2 for more information
processing_id	bigint	✓	✓		Always NULL as it is deprecated in v2. Refer to Changes in iPCV V2 for more information
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	✗	✓
organisation_id	bigint	✗	✓
emis_organisation_guid	varchar	✓	✓	organisation_guid
organisation_uuid	varchar	✗	✓
location_id	bigint	✗	✓
location_guid	varchar	✗	✓
location_uuid	varchar	✗	✓
emis_main_location_guid	varchar	✓	✗		Always NULL as it is deprecated in v2. Refer to Changes in iPCV V2 for more information
is_main_location_flag	boolean	✓	✓	is_main_location
processing_id	bigint	✓	✓		Always NULL as it is deprecated in v2. Refer to Changes in iPCV V2 for more information
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	✗	✓
organisation_id	bigint	✗	✓
organisation_guid	varchar	✓	✓
organisation_uuid	varchar	✗	✓
location_id	bigint	✗	✓
location_guid	varchar	✓	✓
location_uuid	varchar	✗	✓
is_main_location	boolean	✓	✓
processing_id	bigint	✓	✓		Always NULL as it is deprecated in v2. Refer to Changes in iPCV V2 for more information
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓		Renamed from execution_date in V1

Themis

Not used in this schema

Zeus

Not used in this schema