Schema

Raw

Column Name	Data Type	Description	Example
_ingest_time	varchar	Ingestion timestamp in source extract format	’20250101123015’
is_deleted	boolean	Soft-delete flag	false
diary_id	bigint	Internal diary identifier	345678
organisation	varchar	Source organisation identifier	’A12345’
diary_guid	varchar	Source diary GUID	’4df7…a9’
diary_uuid	varchar	Deterministic UUID for diary	’b651…09’
patient_id	bigint	Internal patient identifier	123456
patient_guid	varchar	Registration/patient GUID	’d2b3…11’
patient_organisation_guid	varchar	Patient registration organisation GUID	’e44c…f2’
organisation_id	bigint	Numeric organisation identifier	98765
diary_organisation_guid	varchar	Diary organisation GUID	’f91a…23’
consultation_id	bigint	Internal consultation identifier	456789
consultation_guid	varchar	Source consultation GUID	’7aa1…09’
consultation_uuid	varchar	Deterministic UUID for consultation	’889c…ad’
authorising_user_in_role_id	bigint	Authorising user role id	111
entered_by_user_in_role_id	bigint	Entering user role id	222
availability_datetime	timestamp	Availability datetime	’2025-01-01 09:00:00+00’
effective_datetime	timestamp	Effective datetime	’2025-01-05 09:00:00+00’
effective_datetime_precision	varchar	Precision marker for effective datetime	’YMDT’
associated_text	varchar	Free/structured associated text	’Recall in 3 months’
duration_term	varchar	Duration term value	’3 months’
original_term	varchar	Original coded rubric/term	’Medication review’
consultation_source_code_id	bigint	Consultation source code id	10
code_id	bigint	Primary clinical code id	999999
location_type_description	varchar	Location type description	’Surgery’
is_active	boolean	Diary is active	true
is_complete	boolean	Diary is complete	false
is_sensitive	boolean	Sensitivity flag	false
was_sensitive	boolean	Historical sensitivity state	false
confidentiality_policy_id	bigint	Confidentiality policy id	4
is_confidential	boolean	Confidentiality flag	false
was_confidential	boolean	Historical confidentiality state	false
load_datetime	timestamp	Warehouse load timestamp	’2025-01-01 12:35:00+00’
transform_datetime	timestamp	Warehouse transform timestamp	’2025-01-01 12:40:00+00’
_execution_date	varchar	Formatted transform version value	’20250101124000’

Vanilla

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
diary_id	bigint	✗	✓
emis_diary_guid	varchar	✓	✓
exa_diary_guid	varchar	✓	✓
emis_patient_id	bigint	✓	✓
registration_guid	varchar	✓	✓
diary_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓
exa_encounter_guid	varchar	✓	✓
entered_by_user_in_role_id	bigint	✗	✓
authorising_user_in_role_id	bigint	✗	✓
emis_enteredby_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
emis_authorising_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
emis_code_id	bigint	✓	✓
consultation_source_emis_code_id	bigint	✓	✓
associated_text	varchar	✓	✓
emis_original_term	varchar	✓	✓
is_active_flag	boolean	✓	✓
is_complete_flag	boolean	✓	✓
recorded_date	timestamp	✓	✓
duration_term	varchar	✓	✓
effective_date	timestamp/date	✓	✓
effective_date_precision	varchar	✓	✓
confidential_flag	boolean	✓	✓
confidential_patient_flag	boolean	✓	✗
dummy_patient_flag	boolean	✓	✗
opt_out_93c1_flag	boolean	✓	✗
opt_out_9nd19nu09nu4_flag	boolean	✓	✗
opt_out_9nd19nu0_flag	boolean	✓	✗
opt_out_9nu0_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
sensitive_flag	boolean	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp	✗	✓
_execution_date	varchar	✓	✓

Artemis

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
diary_id	bigint	✗	✓
emis_diary_guid	varchar	✓	✓
exa_diary_guid	varchar	✓	✓
emis_patient_id	bigint	✓	✓
registration_guid	varchar	✓	✓
diary_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓
exa_encounter_guid	varchar	✓	✓
entered_by_user_in_role_id	bigint	✗	✓
authorising_user_in_role_id	bigint	✗	✓
emis_enteredby_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
emis_authorising_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
emis_code_id	bigint	✓	✓
consultation_source_emis_code_id	bigint	✓	✓
emis_original_term	varchar	✓	✓
is_active_flag	boolean	✓	✓
is_complete_flag	boolean	✓	✓
recorded_date	timestamp	✓	✓
duration_term	varchar	✓	✓
effective_date	timestamp/date	✓	✓
effective_date_precision	varchar	✓	✓
confidential_flag	boolean	✓	✓
confidential_patient_flag	boolean	✓	✗
dummy_patient_flag	boolean	✓	✗
opt_out_9nd19nu09nu4_flag	boolean	✓	✗
opt_out_9nd19nu0_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
sensitive_flag	boolean	✓	✓
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
readv2_code	varchar	✓	✗
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp	✗	✓
_execution_date	varchar	✓	✓

Themis

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
diary_id	bigint	✗	✓
emis_diary_guid	varchar	✓	✓
exa_diary_guid	varchar	✓	✓
emis_patient_id	bigint	✓	✓
registration_guid	varchar	✓	✓
diary_organisation_id	bigint	✗	✓
emis_registration_organisation_guid	varchar	✓	✓
consultation_id	bigint	✗	✓
emis_encounter_guid	varchar	✓	✓
exa_encounter_guid	varchar	✓	✓
entered_by_user_in_role_id	bigint	✗	✓
authorising_user_in_role_id	bigint	✗	✓
emis_enteredby_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
emis_authorising_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
emis_code_id	bigint	✓	✓
consultation_source_emis_code_id	bigint	✓	✓
associated_text	varchar	✓	✓
emis_original_term	varchar	✓	✓
is_active_flag	boolean	✓	✓
is_complete_flag	boolean	✓	✓
recorded_date	timestamp	✓	✓
duration_term	varchar	✓	✓
effective_date	timestamp/date	✓	✓
effective_date_precision	varchar	✓	✓
confidential_flag	boolean	✓	✓
confidential_patient_flag	boolean	✓	✗
dummy_patient_flag	boolean	✓	✗
opt_out_93c1_flag	boolean	✓	✗
opt_out_9nd19nu09nu4_flag	boolean	✓	✗
opt_out_9nd19nu0_flag	boolean	✓	✗
opt_out_9nu0_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
sensitive_flag	boolean	✓	✓
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
readv2_code	varchar	✓	✗
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
organisation	varchar	✓	✓
transform_datetime	timestamp	✗	✓
_execution_date	varchar	✓	✓

CPRD (Pseudo_Anon)

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
id_type5	varchar	✓	✓
diary_guid	varchar	✓	✓
patient_guid	varchar	✓	✓
diary_organisation_id	bigint	✗	✓
organisation_guid	varchar	✓	✓
consultation_id	bigint	✗	✓
consultation_guid	varchar	✓	✓
effective_date	date	✓	✓
effective_date_precision	varchar	✓	✓
entered_date	date	✓	✓
entered_time	varchar	✓	✓
clinician_user_in_role_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
entered_by_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
entered_by_user_in_role_id	bigint	✗	✓
authorising_user_in_role_id	bigint	✗	✓
code_id	bigint	✓	✓
original_term	varchar	✓	✓
location_type_description	varchar	✓	✓
deleted	boolean	✓	✗
is_deleted	boolean	✗	✓
is_confidential	boolean	✓	✓
is_active	boolean	✓	✓
is_complete	boolean	✓	✓
processing_id	bigint	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
_execution_date	varchar	execution_date	✓
transform_datetime	timestamp	✗	✓
organisation	varchar	✓	✓

Prometheus

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
emis_diary_guid	varchar	✓	✓
exa_diary_guid	varchar	✓	✓
pseudo_registration_guid	varchar	✓	✓
emis_registration_organisation_guid	varchar	✓	✓
effective_date	date	✓	✓
effective_date_precision	varchar	✓	✓
entered_date	date	✓	✓
entered_time	varchar	✓	✓
emis_authorising_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
authorising_user_in_role_id	bigint	✗	✓
emis_enteredby_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
entered_by_user_in_role_id	bigint	✗	✓
emis_code_id	bigint	✓	✓
emis_original_term	varchar	✓	✓
location_type_description	varchar	✓	✓
deleted	boolean	✓	✗
is_deleted	boolean	✗	✓
confidential_flag	boolean	✓	✓
is_active_flag	boolean	✓	✓
is_complete_flag	boolean	✓	✓
emis_consultation_guid	varchar	✓	✓
processing_id	bigint	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
_execution_date	varchar	execution_date	✓
transform_datetime	timestamp	✗	✓
organisation	varchar	✓	✓

Olympus

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
emis_diary_guid	varchar	✓	✓
exa_diary_guid	varchar	✓	✓
pseudo_registration_guid	varchar	✓	✓
emis_registration_organisation_guid	varchar	✓	✓
effective_date	date	✓	✓
effective_date_precision	varchar	✓	✓
entered_date	date	✓	✓
entered_time	varchar	✓	✓
emis_authorising_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
authorising_user_in_role_id	bigint	✗	✓
emis_enteredby_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
entered_by_user_in_role_id	bigint	✗	✓
emis_code_id	bigint	✓	✓
emis_original_term	varchar	✓	✓
location_type_description	varchar	✓	✓
deleted	boolean	✓	✗
is_deleted	boolean	✗	✓
confidential_flag	boolean	✓	✓
is_active_flag	boolean	✓	✓
is_complete_flag	boolean	✓	✓
consultation_id	bigint	✗	✓
emis_consultation_guid	varchar	✓	✓
processing_id	bigint	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
_execution_date	varchar	✓	✓
transform_datetime	timestamp	✗	✓
organisation	varchar	✓	✓

Zeus v2

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
diary_id	bigint	✗	✓
emis_diary_guid	varchar	✓	✓
exa_diary_guid	varchar	✓	✓
emis_patient_id	bigint	✓	✓
registration_guid	varchar	✓	✓
emis_code_id	bigint	✓	✓
consultation_source_emis_code_id	bigint	✓	✓
emis_encounter_guid	varchar	✓	✓
exa_encounter_guid	varchar	✓	✓
pseudo_registration_guid_ourea	varchar	✓	✓
emis_registration_organisation_guid	varchar	✓	✓
associated_text	varchar	✓	✓
effective_date	date/timestamp	✓	✓
effective_date_precision	varchar	✓	✓
authorising_user_in_role_id	bigint	✗	✓
entered_by_user_in_role_id	bigint	✗	✓
emis_authorising_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
emis_enteredby_userinrole_guid	varchar	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
duration_term	varchar	✓	✓
is_active_flag	boolean	✓	✓
is_complete_flag	boolean	✓	✓
confidential_flag	boolean	✓	✓
emis_original_term	varchar	✓	✓
sensitive_flag	boolean	✓	✓
location_type_description	varchar	✓	✓
regular_and_current_active_flag	boolean	✓	✗
regular_current_active_and_inactive_flag	boolean	✓	✗
regular_patient_flag	boolean	✓	✗
non_regular_and_current_active_flag	boolean	✓	✗
opt_out_93c1_flag	boolean	✓	✗
opt_out_9nu0_flag	boolean	✓	✗
opt_out_9nd19nu0_flag	boolean	✓	✗
opt_out_9nd19nu09nu4_flag	boolean	✓	✗
dummy_patient_flag	boolean	✓	✗
confidential_patient_flag	boolean	✓	✗
sensitive_patient_flag	boolean	✓	✗
other_code	varchar	✓	✗
other_code_system	varchar	✓	✗
other_display	varchar	✓	✗
readv2_code	varchar	✓	✗
recorded_date	timestamp	✓	✗
snomed_concept_id	bigint	✓	✗
snomed_description_id	bigint	✓	✗
pseudo_registration_guid_athena	varchar	✓	✗
pseudo_registration_guid_prometheus	varchar	✓	✗
pseudo_registration_guid_olympus	varchar	✓	✗
pseudo_emis_diary_guid_athena	varchar	✓	✗
pseudo_exa_diary_guid_athena	varchar	✓	✗
pseudo_emis_registration_organisation_guid_athena	varchar	✓	✗
pseudo_emis_authorising_userinrole_guid_athena	varchar	✓	✗
pseudo_emis_enteredby_userinrole_guid_athena	varchar	✓	✗
pseudo_emis_consultation_guid_athena	varchar	✓	✗
data_filter	integer	✓	✗
organisation	varchar	✓	✓
_execution_date	varchar	✓	✓