Schema

Raw

Column Name	Data Type	Description	Example
_ingest_time	varchar	Timestamp the row was ingested into the Explorer Platform	20240917084800
organisation_id	bigint	The emis web organisation identifier, can be used to join to other models	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
organisation_guid	varchar	The GUID for this organisation	82E6FF36-87E2-11EF-B3A3-48DF37DF55D0
cdb	varchar	EMIS Customer Database ID	50002
name	varchar	The name of the organisation	The Dermatologist Practice
ods_code	varchar	The organisational identifier that the NHS assigns an organisation	V81999
open_datetime	timestamp(6) with time zone	Timestamp of when the organisation opened	2020-01-01T13:44:00+00:00
close_datetime	timestamp(6) with time zone	Timestamp of when the organisation closed	2024-12-17T13:44:00+00:00
is_open	boolean	Flag to indicate if the organisation is classed as open	TRUE
organisation_type_id	bigint	Emis Web organisation Type ID	2
organisation_type_description	varchar	Description type of organisation as defined on the source system	General Practice
ccg_ods_code	varchar	Commissioning group identifier from the NHS of the organisation	01V
house_name_flat_number	varchar	Number or name from the address	2
number_and_street	varchar	Address number or street name	Hill House
village	varchar	Village from the organisations address	Rawdon
town	varchar	Town from organisations address	Leeds
county	varchar	County from address	West Yorkshire
postcode	varchar	Organisations postcode	LS99 7BZ
main_location_guid	varchar	main Location GUID from emis web, can be used to join to location model	A3E962E4-CB3B-40B2-8D86-D850CB9BF1A4
is_this_organisation	boolean	Flag to indicate if the current rows data relates the organisation in the organisation column	TRUE

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
_record_version	varchar	✓	✗	Removed as it is deprecated in iPCV v2
_ingest_time	varchar	✓	✓
is_deleted	boolean	deleted	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
emis_organisation_id	bigint	✓	✓
organisation_guid	varchar	✓	✓
organisation_type_id	bigint	✗	✓
organisation_type_description	varchar	✓	✓
emis_main_location_guid	varchar	✓	✓
cdb	bigint	✓	✓
name	varchar	✓	✓
is_this_organisation	boolean	✗	✓
open_date	date	✓	✓
close_date	date	✓	✓
is_open	bigint	✓	✓
speciality_codes	array(bigint)	✗	✓
commissioner	varchar	✗	✓
ods_code	varchar	✓	✓
address_line_1	varchar	✓	✓
address_line_2	varchar	✓	✓
address_line_3	varchar	✓	✓
address_line_4	varchar	✓	✓
address_line_5	varchar	✓	✓
postcode	varchar	✓	✓
parent_emis_organisation_id	varchar	✓	✗	Removed as it is deprecated in v1
ccg_ods_code	varchar	✓	✗	Removed as it is deprecated in v2
ccg_emis_organisation_id	varchar	✓	✗	Removed as it is deprecated in v1
full_address	varchar	✓	✗	Removed as it is deprecated in v2
parent_organisation_type_description	varchar	✓	✗	Removed as it is deprecated in v2
releaseversion	varchar	✓	✗	Removed as it is deprecated in v2
nhs_role	integer	✓	✗	Removed as it is deprecated in v2
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
_record_version	varchar	✓	✗	Removed as it is deprecated in iPCV v2
_ingest_time	varchar	✓	✓
is_deleted	boolean	deleted	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
emis_organisation_id	bigint	✓	✓
organisation_guid	varchar	✓	✓
organisation_type_description	varchar	✓	✓
emis_main_location_guid	varchar	✓	✓
speciality_codes	array(bigint)	✗	✓
commissioner	varchar	✗	✓
is_this_organisation	boolean	✗	✓
cdb	bigint	✓	✓
name	varchar	✓	✓
ods_code	varchar	✓	✓
ccg_ods_code	varchar	✓	✗	Removed as it is deprecated in v2
open_date	date	✓	✓
close_date	date	✓	✓
is_open	bigint	✓	✓
address_line_1	varchar	✓	✓
address_line_2	varchar	✓	✓
address_line_3	varchar	✓	✓
address_line_4	varchar	✓	✓
address_line_5	varchar	✓	✓
postcode	varchar	✓	✓
ccg_emis_organisation_id	varchar	✓	✗	Removed as it is deprecated in v1
full_address	varchar	✓	✗	Removed as it is deprecated in v2
parent_emis_organisation_id	varchar	✓	✗	Removed as it is deprecated in v1
parent_organisation_type_description	varchar	✓	✗	Removed as it is deprecated in v2
releaseversion	varchar	✓	✗	Removed as it is deprecated in v2
nhs_role	integer	✓	✗	Removed as it is deprecated in v2
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	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_organisation_id	bigint	✗	✓
emis_organisation_guid	varchar	✓	✓
emis_main_location_guid	varchar	✓	✓
organisation_type_id	bigint	✗	✓
organisation_type_description	varchar	✓	✓
speciality_codes	array(bigint)	✗	✓
commissioner	varchar	✗	✓
is_this_organisation	boolean	✗	✓
cdb	bigint	✓	✓
name	varchar	✓	✓
ods_code	varchar	✓	✓
ccg_ods_code	varchar	✓	✗	Removed as it is deprecated in v2
open_date	date	✓	✓
close_date	date	✓	✓
nhs_role	integer	✓	✗	Removed as it is deprecated in v2
address_line_1	varchar	✓	✓
address_line_2	varchar	✓	✓
address_line_3	varchar	✓	✓
address_line_4	varchar	✓	✓
address_line_5	varchar	✓	✓
postcode	varchar	✓	✓
releaseversion	varchar	✓	✗	Removed as it is deprecated in v2
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	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
organisation_id	bigint	✗	✓
emis_organisation_guid	varchar	✓	✓
emis_main_location_guid	varchar	✓	✓
organisation_type_id	bigint	✗	✓
organisation_type_description	varchar	✓	✓
speciality_codes	array(bigint)	✗	✓
commissioner	varchar	✗	✓
is_this_organisation	boolean	✗	✓
cdb	bigint	✓	✓
ods_code	varchar	✓	✓
emis_parent_organisation_guid	varchar	✓	✗	Removed as it is deprecated in v2
emis_ccg_organisation_guid	varchar	✓	✗	Removed as it is deprecated in v2
ccg_ods_code	varchar	✓	✗	Removed as it is deprecated in v2
open_date	date	✓	✓
close_date	date	✓	✓
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 Type	Data Exists in v1	Data Exists in v2	Comments
_ingest_time	varchar	✓	✓
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
organisation_id	bigint	✗	✓
emis_organisation_guid	varchar	✓	✓
emis_main_location_guid	varchar	✓	✓
organisation_type_id	bigint	✗	✓
organisation_type_description	varchar	✓	✓
speciality_codes	array(bigint)	✗	✓
commissioner	varchar	✗	✓
is_this_organisation	boolean	✗	✓
cdb	bigint	✓	✓
ods_code	varchar	✓	✓
ccg_ods_code	varchar	✓	✗	Removed as it is deprecated in v2
emis_parent_organisation_guid	varchar	✓	✗	Removed as it is deprecated in v2
emis_ccg_organisation_guid	varchar	✓	✗	Removed as it is deprecated in v2
open_date	date	✓	✓
close_date	date	✓	✓
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	✓

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	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
organisation_id	bigint	✗	✓
organisation_guid	varchar	✓	✓
organisation_type_id	bigint	✗	✓
organisation_type	varchar	✓	✓
main_location_guid	varchar	✓	✓
cdb	bigint	✓	✓
speciality_codes	array(bigint)	✗	✓
commissioner	varchar	✗	✓
is_this_organisation	boolean	✗	✓
ods_code	varchar	✓	✓
open_date	date	✓	✓
close_date	date	✓	✓
parent_organisation_guid	varchar	✓	✓
ccg_organisation_guid	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	✓

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
_record_version	varchar	✓	✗	Removed as it is deprecated in iPCV v2
_ingest_time	varchar	✓	✓
is_deleted	boolean	deleted	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
emis_organisation_id	bigint	✓	✓
organisation_guid	varchar	✓	✓
organisation_type_id	bigint	✗	✓
organisation_type_description	varchar	✓	✓
emis_main_location_guid	varchar	✓	✓
speciality_codes	array(bigint)	✗	✓
commissioner	varchar	✗	✓
is_this_organisation	boolean	✗	✓
cdb	bigint	✓	✓
name	varchar	✓	✓
ods_code	varchar	✓	✓
ccg_ods_code	varchar	✓	✗	Removed as it is deprecated in v2
open_date	date	✓	✓
close_date	date	✓	✓
is_open	bigint	✓	✓
address_line_1	varchar	✓	✓
address_line_2	varchar	✓	✓
address_line_3	varchar	✓	✓
address_line_4	varchar	✓	✓
address_line_5	varchar	✓	✓
postcode	varchar	✓	✓
parent_emis_organisation_id	varchar	✓	✗	Removed as it is deprecated in v1
ccg_emis_organisation_id	varchar	✓	✗	Removed as it is deprecated in v1
full_address	varchar	✓	✗	Removed as it is deprecated in v2
parent_organisation_type_description	varchar	✓	✗	Removed as it is deprecated in v2
releaseversion	varchar	✓	✗	Removed as it is deprecated in v2
nhs_role	integer	✓	✗	Removed as it is deprecated in v2
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Zeus

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
_record_version	varchar	✓	✗	Removed as it is deprecated in iPCV v2
_ingest_time	varchar	✓	✓
load_datetime	timestamp(6) with time zone	✗	✓
is_deleted	boolean	deleted	✓	Exists in v1 with a different name
emis_organisation_id	bigint	✓	✓
emis_organisation_guid	varchar	✓	✓
emis_main_location_guid	varchar	✓	✓
cdb	bigint	✓	✓
ccg_emis_organisation_id	varchar	✓	✗	Removed as it is deprecated in v1
ccg_ods_code	varchar	✓	✗	Removed as it is deprecated in v2
parent_emis_organisation_id	varchar	✓	✗	Removed as it is deprecated in v1
parent_organisation_type_description	varchar	✓	✗	Removed as it is deprecated in v2
releaseversion	varchar	✓	✗	Removed as it is deprecated in v2
nhs_role	integer	✓	✗	Removed as it is deprecated in v2
name	varchar	✓	✓
ods_code	varchar	✓	✓
open_date	date	✓	✓
close_date	date	✓	✓
is_open	bigint	✓	✓
organisation_type_id	bigint	✗	✓
organisation_type_description	varchar	✓	✓
speciality_codes	array(bigint)	✗	✓
commissioner	varchar	✗	✓
address_line_1	varchar	✓	✓
address_line_2	varchar	✓	✓
address_line_3	varchar	✓	✓
address_line_4	varchar	✓	✓
address_line_5	varchar	✓	✓
full_address	varchar	✓	✗	Removed as it is deprecated in v2
postcode	varchar	✓	✓
is_this_organisation	boolean	✗	✓
data_filter	integer	✓	✗	Removed as it is deprecated in v2
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓