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
organisation_guid	varchar	Globally unique identifier for the healthcare organization	’4a3b156c-7d89-0e1f-234a-5bc6d789ef01’
organisation_id	bigint	Numeric identifier for the healthcare organization	987654
session_guid	varchar	Globally unique identifier for the appointment session	’12a3b456-7c89-0d1e-234f-5gh6i789jk01’
user_in_role_guid	varchar	Globally unique identifier for the user role association in the session	’9a8b7c6d-5e4f-3g2h-1i0j-kl9m8n7o6p5q’
user_in_role_id	bigint	Numeric identifier for the user role association in the session	456789
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_emis_organisation_id	bigint	✓	✓
session_emis_organisation_guid	varchar	✓	✓
emis_session_userinrole_id	bigint	✓	✓
emis_session_userinrole_guid	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	✓	✓
emis_session_userinrole_id	bigint	✓	✓
emis_session_userinrole_guid	varchar	✓	✓
session_emis_organisation_id	bigint	✓	✓
session_emis_organisation_guid	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	✓	✓
emis_session_userinrole_id	bigint	✓	✓
emis_session_userinrole_guid	varchar	✓	✓
session_emis_organisation_id	bigint	✓	✓
session_emis_organisation_guid	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	✓	✓
user_in_role_id	bigint	✓	✓
emis_session_userinrole_guid	varchar	✓	✓
processing_id	bigint	✓	✗	Removed as it is deprecated in iPCV v1
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	execution_date	✓

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	✓	✓
user_in_role_id	bigint	✓	✓
emis_session_userinrole_guid	varchar	✓	✓
processing_id	bigint	✓	✗	Removed as it is deprecated in iPCV v1
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	✓	✓
user_in_role_id	bigint	✓	✓
user_in_role_guid	varchar	✓	✓
processing_id	bigint	✓	✗	Removed as it is deprecated in iPCV v1
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_emis_organisation_id	bigint	✓	✓
session_emis_organisation_guid	varchar	✓	✓
emis_session_userinrole_id	bigint	✓	✓
emis_session_userinrole_guid	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Zeus

Not used in this schema