Schema

Raw

Column Name	Data Type	Description	Example
_ingest_time	varchar	The datetime that the record was ingested, format YYYYMMDDHHMMSS	’20230512143015’
is_deleted	boolean	If this record should be considered soft deleted	TRUE
session_id	bigint	Numeric identifier for the appointment session	123456
session_guid	varchar	Globally unique identifier for the appointment session	’12a3b456-7c89-0d1e-234f-5gh6i789jk01’
session_uuid	varchar	Deterministic UUID5 generated from session_id + organisation	’a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
organisation_id	bigint	Numeric identifier for the healthcare organisation	987654
organisation_guid	varchar	Globally unique identifier for the healthcare organisation	’4a3b156c-7d89-0e1f-234a-5bc6d789ef01’
organisation_uuid	varchar	Deterministic UUID5 generated from organisation_id + organisation	’a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
location_id	bigint	Internal identifier for the location	12345
location_guid	varchar	Globally unique identifier for the location	’b2c3d4e5-f6a7-5b6c-0d1e-2f3a4b5c6d7e’
location_uuid	varchar	Deterministic UUID5 generated from location_id + organisation	’a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
user_in_role_id	bigint	Numeric identifier for the user role association in the session	456789
user_in_role_guid	varchar	Globally unique identifier for the user role association in the session	’9a8b7c6d-5e4f-3g2h-1i0j-kl9m8n7o6p5q’
user_in_role_uuid	varchar	Deterministic UUID5 generated from user_in_role_id + organisation	’a1b2c3d4-e5f6-4a5b-9c8d-0e1f2a3b4c5d’
organisation	varchar	An identifier for the source of data (an organisation) this relates to a given GP practice	’CDB-12345’
load_datetime	timestamp(6) with time zone	Formatted ingest time for filtering	’2023-05-12 14:30:15.000000 UTC’
transform_datetime	timestamp(6) with time zone	The datetime that the data was made available in the model with a relevant change	’2023-05-12 14:30:15.000000 UTC’
extract_datetime	timestamp(6) with time zone	The datetime that the record was last extracted from the source database	’2023-05-10 09:30:45.000000 UTC’
_execution_date	varchar	Formatted transform datetime for versioning purposes	’20230512143015’

Vanilla

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

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	session_deleted_flag	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
emis_session_id	bigint	✓	✓
emis_session_guid	varchar	✓	✓
session_uuid	varchar	✗	✓
session_emis_organisation_id	bigint	✓	✓
session_emis_organisation_guid	varchar	✓	✓
organisation_uuid	varchar	✗	✓
emis_session_userinrole_id	bigint	✓	✓
emis_session_userinrole_guid	varchar	✓	✓
user_in_role_uuid	varchar	✗	✓
location_id	bigint	✗	✓
location_guid	varchar	✗	✓
location_uuid	varchar	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Apollo

Not used in this schema

Artemis

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

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	session_deleted_flag	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
emis_session_id	bigint	✓	✓
emis_session_guid	varchar	✓	✓
session_uuid	varchar	✗	✓
emis_session_userinrole_id	bigint	✓	✓
emis_session_userinrole_guid	varchar	✓	✓
user_in_role_uuid	varchar	✗	✓
session_emis_organisation_id	bigint	✓	✓
session_emis_organisation_guid	varchar	✓	✓
organisation_uuid	varchar	✗	✓
location_id	bigint	✗	✓
location_guid	varchar	✗	✓
location_uuid	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 Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	session_user_deleted_flag	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
emis_session_id	bigint	✓	✓
emis_session_guid	varchar	✓	✓
session_uuid	varchar	✗	✓
emis_session_userinrole_id	bigint	✓	✓
emis_session_userinrole_guid	varchar	✓	✓
user_in_role_uuid	varchar	✗	✓
session_emis_organisation_id	bigint	✓	✓
session_emis_organisation_guid	varchar	✓	✓
organisation_uuid	varchar	✗	✓
location_id	bigint	✗	✓
location_guid	varchar	✗	✓
location_uuid	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 Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	deleted	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
session_id	bigint	✓	✓
emis_session_guid	varchar	✓	✓
session_uuid	varchar	✗	✓
user_in_role_id	bigint	✓	✓
emis_session_userinrole_guid	varchar	✓	✓
user_in_role_uuid	varchar	✗	✓
processing_id	bigint	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
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 Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	deleted	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
session_id	bigint	✓	✓
emis_session_guid	varchar	✓	✓
session_uuid	varchar	✗	✓
user_in_role_id	bigint	✓	✓
emis_session_userinrole_guid	varchar	✓	✓
user_in_role_uuid	varchar	✗	✓
processing_id	bigint	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Pseudo_Anon

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

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	deleted	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
session_id	bigint	✓	✓
session_guid	varchar	✓	✓
session_uuid	varchar	✗	✓
user_in_role_id	bigint	✓	✓
user_in_role_guid	varchar	✓	✓
user_in_role_uuid	varchar	✗	✓
processing_id	bigint	✓	✓	Always NULL as it is deprecated in v2. Refer definitions/universal improvements for more details.
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	execution_date	✓

Themis

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

Column Name	Data Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	session_deleted_flag	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
emis_session_id	bigint	✓	✓
emis_session_guid	varchar	✓	✓
session_uuid	varchar	✗	✓
session_emis_organisation_id	bigint	✓	✓
session_emis_organisation_guid	varchar	✓	✓
organisation_uuid	varchar	✗	✓
emis_session_userinrole_id	bigint	✓	✓
emis_session_userinrole_guid	varchar	✓	✓
user_in_role_uuid	varchar	✗	✓
location_id	bigint	✗	✓
location_guid	varchar	✗	✓
location_uuid	varchar	✗	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Zeus

Not used in this schema