Schema

Core

The core schema represents the Clinical Code schema derived from EMIS source systems.

Column Name	Data Type	Description	Example
is_deleted	boolean	Indicates if this record should be considered soft deleted	FALSE
organisation	varchar	Set to ‘EMIS’	‘EMIS’
code_id	bigint	The unique EMIS code identifier for the clinical code	1814041000006118
parent_code_id	bigint	The parent code id, which is populated if EMIS code id has a parent code id.	452768018
term	varchar	The description of the EMIS clinical code	‘Pulmonary valvectomy’
read_term_id	varchar	The unique identifier mapped to the legacy NHS Read Codes v2 system.	9Nu0
readv2_code	varchar	The code that provide a standard vocabulary for clinicians to record patient findings and procedures, in health and social care IT systems across primary and secondary care. These codes have been retired since 2016.	9Nu0.00
snomed_concept_id	bigint	The unique identifier for concepts within the SNOMED CT clinical terminology.	827241000000103
snomed_description_id	bigint	The unique identifier for the preferred term that describes the concept within the SNOMED CT clinical terminology.	2151331000000115
national_code_category_id	bigint	The unique identifier mapping to the national code category	1
national_code_category	varchar	The category of the NHS national codes used for standardised classification of codes across NHS health records.	ETHNIC CATEGORY
national_code	varchar	The NHS national code, defined for specific data items in NHS datasets.These include variables such as admission source, discharge destination, ethnic group, etc. National codes are also used to record the specialised service within which a patient is treated.	A
national_description	varchar	The description of the NHS National code	British
emis_code_category_id	bigint	EMIS internal identifier for code categories	85
emis_code_category_description	varchar	The description of the EMIS code category	Ethnicity
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.	20241015070047

Vanilla

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 Types	Data in V1	Data in V2	Core Mapping	Comments
_record_version	varchar	✓	✗
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
emis_code_id	bigint	✓	✓	code_id
emis_code_category_id	bigint	✗	✓
emis_codecategory_description	varchar	✓	✓	emis_code_category_description
national_code	varchar	✓	✓
national_code_category_id	bigint	✗	✓
national_code_category	varchar	✓	✓
national_description	varchar	✓	✓
parent_emis_code_id	bigint	✓	✓	parent_code_id
emis_old_code	varchar	✓	✓	read_term_id
readv2_code	varchar	✓	✓
snomed_concept_id	bigint	✓	✓
snomed_description_id	bigint	✓	✓
emis_term	varchar	✓	✓	term
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Apollo

Not used in this schema

Artemis

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 Types	Data in V1	Data in V2	Core Mapping	Comments
_record_version	varchar	✓	✗
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
emis_code_id	bigint	✓	✓	code_id
emis_code_category_id	bigint	✗	✓
emis_codecategory_description	varchar	✓	✓	emis_code_category_description
national_code	varchar	✓	✓
national_code_category_id	bigint	✗	✓
national_code_category	varchar	✓	✓
national_description	varchar	✓	✓
parent_emis_code_id	bigint	✓	✓	parent_code_id
emis_old_code	varchar	✓	✓	read_term_id
readv2_code	varchar	✓	✓
snomed_concept_id	bigint	✓	✓
snomed_description_id	bigint	✓	✓
emis_term	varchar	✓	✓	term
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Hermes

The table below presents the flavour specific schema.

Column Name	Data Type
is_deleted	boolean
organisation	varchar
code_id	bigint
parent_code_id	bigint
term	varchar
read_term_id	varchar
readv2_code	varchar
snomed_concept_id	bigint
snomed_description_id	bigint
national_code_category_id	bigint
national_code_category	varchar
national_code	varchar
national_description	varchar
emis_code_category_id	bigint
emis_code_category_description	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 Types	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
emis_code_id	bigint	✓	✓	code_id
emis_term	varchar	✓	✓	term
emis_old_code	varchar	✓	✓	read_term_id
snomed_concept_id	bigint	✓	✓
snomed_description_id	bigint	✓	✓
national_code_category_id	bigint	✗	✓
national_code	varchar	✓	✓
national_code_category	varchar	✓	✓
national_description	varchar	✓	✓
emis_code_category_id	bigint	✗	✓
emis_code_category_description	varchar	✓	✓
parent_emis_code_id	bigint	✓	✓	parent_code_id
processing_id	bigint	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
transform_datetime	timestamp(6) with time zone	✗	✓
organisation	varchar	✓	✓
_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 Types	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
emis_code_id	bigint	✓	✓	code_id
emis_term	varchar	✓	✓	term
emis_old_code	varchar	✓	✓	read_term_id
snomed_concept_id	bigint	✓	✓
snomed_description_id	bigint	✓	✓
national_code	varchar	✓	✓
national_code_category_id	bigint	✗	✓
national_code_category	varchar	✓	✓
national_description	varchar	✓	✓
emis_code_category_id	bigint	✗	✓
emis_code_category_description	varchar	✓	✓
parent_emis_code_id	bigint	✓	✓	parent_code_id
processing_id	bigint	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

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 Types	Data in V1	Data in V2	Core Mapping	Comments
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
code_id	bigint	✓	✓
parent_code_id	bigint	✓	✓
term	varchar	✓	✓
read_term_id	varchar	✓	✓
snomed_ct_concept_id	bigint	✓	✓	snomed_concept_id
snomed_ct_description_id	bigint	✓	✓	snomed_description_id
national_code	varchar	✓	✓
national_code_category_id	bigint	✗	✓
national_code_category	varchar	✓	✓
national_description	varchar	✓	✓
emis_code_category_id	bigint	✗	✓
emis_code_category_description	varchar	✓	✓
processing_id	bigint	✓	✓		Always NULL as it is deprecated in v2. Refer changes in v2 for more details.
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Themis

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 Types	Data in V1	Data in V2	Core Mapping	Comments
_record_version	varchar	✓	✗
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
emis_code_id	bigint	✓	✓	code_id
emis_code_category_id	bigint	✗	✓
emis_codecategory_description	varchar	✓	✓	emis_code_category_description
national_code	varchar	✓	✓
national_code_category_id	bigint	✗	✓
national_code_category	varchar	✓	✓
national_description	varchar	✓	✓
parent_emis_code_id	bigint	✓	✓	parent_code_id
emis_old_code	varchar	✓	✓	read_term_id
readv2_code	varchar	✓	✓
snomed_concept_id	bigint	✓	✓
snomed_description_id	bigint	✓	✓
emis_term	varchar	✓	✓	term
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Zeus

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 Types	Data in V1	Data in V2	Core Mapping	Comments
_record_version	varchar	✓	✗
_ingest_time	varchar	✓	✗
is_deleted	boolean	✗	✓
emis_code_id	bigint	✗	✓	code_id
emis_code_category_id	bigint	✗	✓
emis_codecategory_description	varchar	✓	✓	emis_code_category_description
national_code	varchar	✓	✓
national_code_category_id	bigint	✗	✓
national_code_category	varchar	✓	✓
national_description	varchar	✓	✓
parent_emis_code_id	bigint	✓	✓	parent_code_id
emis_old_code	varchar	✓	✓	read_term_id
readv2_code	varchar	✓	✓
snomed_concept_id	bigint	✓	✓
snomed_description_id	bigint	✓	✓
emis_term	varchar	✓	✓	term
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓
data_filter	integer	✓	✗