Schema

Raw

Column Name	Data Type	Description	Example
_ingest_time	varchar	Timestamp the row was ingested into the Explorer Platform	20240917084800
user_in_role_id	bigint	Integer identifier for identifying user roles	12345
organisation	varchar	Owning organisation of EMIS Web instance	CDB-50002
extract_datetime	timestamp(6) with time zone	The datetime that the record was last extracted from the source database with a relevant change	2024-10-11T16:39:24+00:00
load_datetime	timestamp(6) with time zone	The datetime that the record was last upserted in to the EXA data lake with a relevant change	2024-10-11T16:39:24+00:00
is_deleted	boolean	If this record should be considered soft deleted	FALSE
transform_datetime	timestamp(6) with time zone	The datetime that the data was made available in the model with a relevant change	2024-12-17T13:44:00+00:00
user_in_role_guid	varchar	GUID for user-in-role record	B2709D32-A3DE-4D0F-A362-7309FA4A66E7
organisation_id	bigint	The emis web organisation identifier	12345
organisation_guid	varchar	Organisation GUID	B2709D32-A3DE-4D0F-A362-7309FA4A66E7
organisation_specialities	array	An array of speciality codes for the organisation that the user belongs to	[100, 200]
user_id	bigint	Integer identifier for identifying users	6789
title	varchar	User’s title	Mr
given_name	varchar	User’s firstname	John
surname	varchar	User’s surname	Doe
healthcare_practitioner_type	varchar	GPES healthcare professional (HCP) type	A
job_category_code	varchar	Job category code	R5007
job_category_name	varchar	Name description of job category	System Administrator
contract_start_date	date	Date the contact started	2020-01-01
contract_end_date	date	Date the contact ended	2023-12-01
sds_user_id	bigint	The user’s Spine Directory Service (SDS) user ID	555250704103
sds_role_profile_ids	array	The SDS role profile ids for the user’s role	[555250714105, 555345652454]
general_medical_practitioner_number	varchar	The NHS prescription services identifier for a GMP	G3371701
general_medical_council_number	varchar	Identifier for the user’s general medical council (GMC) membership if applicable	7219735
general_dental_council_number	varchar	Identifier for the user’s general dental council (GDC) membership if applicable	5689263
nursing_and_midwifery_council_number	varchar	Identifier for the user’s nursing and midwifery council (NMC) membership if applicable	6828373
identifier_issuing_body	varchar	The professional body (GMC (03), GDC (02), or NMC (09)) issuing the ID for the user	03
_execution_date	varchar	Transform datetime in the format yyyyMMddhhmmss	202401010000

Vanilla

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Types	Data Data Exists in v1	Data Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_user_id	bigint	✓	✓
emis_userinrole_guid	varchar	✓	✓
organisation_id	bigint	✗	✓
emis_employer_organisation_guid	varchar	✓	✓
sds_job_role_code	varchar	✓	✓
emis_job_category_name	varchar	✓	✓
sds_user_id	varchar	✗	✓
contract_start_date	date	✓	✓
contract_end_date	date	✓	✓
given_name	varchar	✓	✓
surname	varchar	✓	✓
title	varchar	✓	✓
organisation_specialities	array(bigint)	✗	✓
healthcare_practitioner_type	varchar	✗	✓
general_medical_practitioner_number	varchar	✗	✓
general_medical_council_number	varchar	✗	✓
nursing_and_midwifery_council_number	varchar	✗	✓
general_dental_council_number	varchar	✗	✓
identifier_issuing_body	varchar	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Apollo

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Types	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_userinrole_id	bigint	✓	✓
emis_userinrole_guid	varchar	✓	✓
organisation_id	bigint	✗	✓
emis_employer_organisation_guid	varchar	✓	✓
emis_user_id	bigint	✗	✓
sds_user_id	varchar	✗	✓
contract_end_date	date	✓	✓
contract_start_date	date	✓	✓
title	varchar	✓	✓
given_name	varchar	✓	✓
surname	varchar	✓	✓
hcp_type	varchar	✗	✓
gmp_number	varchar	✗	✓
identifier_issuing_body	varchar(2)	✗	✓
sds_job_role_code	varchar	✓	✓
emis_job_category_name	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar(14)	✓	✓

Artemis

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Types	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_userinrole_id	bigint	✗	✓
emis_userinrole_guid	varchar	✓	✓
organisation_id	bigint	✗	✓
emis_employer_organisation_guid	varchar	✓	✓
emis_user_id	bigint	✓	✓
sds_user_id	varchar	✗	✓
contract_end_date	date	✓	✓
contract_start_date	date	✓	✓
title	varchar	✓	✓
given_name	varchar	✓	✓
surname	varchar	✓	✓
hcp_type	varchar	✗	✓
gmp_number	varchar	✗	✓
identifier_issuing_body	varchar(2)	✗	✓
sds_job_role_code	varchar	✓	✓
emis_job_category_name	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Hestia

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Types	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_userinrole_id	bigint	✗	✓
emis_userinrole_guid	varchar	✓	✓
organisation_id	bigint	✗	✓
emis_employer_organisation_guid	varchar	✓	✓
emis_user_id	bigint	✓	✓
sds_user_id	varchar	✗	✓
contract_end_date	date	✓	✓
contract_start_date	date	✓	✓
title	varchar	✓	✓
given_name	varchar	✓	✓
surname	varchar	✓	✓
hcp_type	varchar	✗	✓
gmp_number	varchar	✗	✓
identifier_issuing_body	varchar(2)	✗	✓
sds_job_role_code	varchar	✓	✓
emis_job_category_name	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Olympus

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Types	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
user_in_role_id	bigint	✗	✓
emis_userinrole_guid	varchar	✓	✓
organisation_id	bigint	✗	✓
emis_employer_organisation_guid	varchar	✓	✓
emis_job_category_code	varchar	✓	✓
emis_job_category_name	varchar	✓	✓
processing_id	integer	✓	✗	Removed as it is deprecated in iPCV v1
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Prometheus

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Types	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
user_in_role_id	bigint	✗	✓
emis_userinrole_guid	varchar	✓	✓
organisation_id	bigint	✗	✓
emis_employer_organisation_guid	varchar	✓	✓
emis_job_category_code	varchar	✓	✓
emis_job_category_name	varchar	✓	✓
processing_id	integer	✓	✗	Removed as it is deprecated in iPCV v1
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	execution_date	✓	Exists in v1 with a different name

Pseudo_Anon

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Types	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
user_in_role_id	bigint	✗	✓
user_in_role_guid	varchar	✓	✓
organisation_id	bigint	✗	✓
organisation_guid	varchar	✓	✓
contract_start_date	date	✓	✓
contract_end_date	date	✓	✓
job_category_code	varchar	✓	✓
job_category_name	varchar	✓	✓
processing_id	integer	✓	✗	Removed as it is deprecated in iPCV v1
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	execution_date	✓	Exists in v1 with a different name

Themis

Refer to the definition / universal-improvements page for detailed differences between v1 and v2 of this model.

Column Name	Data Types	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_user_id	bigint	✓	✓
emis_userinrole_guid	varchar	✓	✓
organisation_id	bigint	✗	✓
emis_employer_organisation_guid	varchar	✓	✓
sds_job_role_code	varchar	✓	✓
emis_job_category_name	varchar	✓	✓
sds_user_id	varchar	✗	✓
contract_start_date	date	✓	✓
contract_end_date	date	✓	✓
given_name	varchar	✓	✓
surname	varchar	✓	✓
title	varchar	✓	✓
organisation_specialities	array(bigint)	✗	✓
healthcare_practitioner_type	varchar	✗	✓
general_medical_practitioner_number	varchar	✗	✓
general_medical_council_number	varchar	✗	✓
nursing_and_midwifery_council_number	varchar	✗	✓
general_dental_council_number	varchar	✗	✓
identifier_issuing_body	varchar	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Zeus

Not used in this schema