Schema

Raw

Column Name	Data Type	Description	Example
_ingest_time	VARCHAR	The datetime that the record was ingested, format YYYYMMDDHHMMSS	’20230512143015’
location_id	BIGINT	PK Unique identifier for location	123456
organisation	VARCHAR	PK An identifier for the source of data (an organisation) this relates to a given GP practice	’CDB-12345’
extract_datetime	TIMESTAMP	The datetime that the record was last extracted from the source database with a relevant change	’2023-05-10 09:30:45.000’
load_datetime	TIMESTAMP	The datetime that the record was last upserted in to the EXA data lake with a relevant change	’2023-05-10 10:15:22.000’
is_deleted	BOOLEAN	If this record should be considered soft deleted	TRUE
transform_datetime	TIMESTAMP	The datetime that the data was made available in the model with a relevant change	’2023-05-12 14:30:15.000 UTC’
location_guid	VARCHAR	Unique identifier for the location	’12a3b456-7c89-0d1e-234f-5gh6i789jk01’
location_name	VARCHAR	The name of the location	’Main Surgery’
parent_location_id	BIGINT	FK Unique identifier of the parent location	123455
email_address	VARCHAR	The email address of the location	’info@surgeryname.nhs.uk’
phone_number	VARCHAR	The phone number of the location	’01234 567890’
fax_number	VARCHAR	The fax number of the location	’01234 567891’
main_contact_name	VARCHAR	The name of the main contact associated with the location	’Dr. Jane Smith’
open_date	DATE	The date the location can be used	’2010-03-15’
close_date	DATE	The date the location can no longer be used	’2022-12-31’
location_type_description	VARCHAR	EMIS description of the location type e.g. Primary Care Trust	’GP Practice’
code_id	BIGINT	EMIS code Id used to link location type back to MKB database	987654
house_name_flat_number	VARCHAR	The house name or number of an address can sometimes be house number and street name	’House’
number_and_street	VARCHAR	House number and street, could be either or	’45 Example Street’
village	VARCHAR	Used to display village names or in larger cities boroughs	’Exampleford’
town	VARCHAR	This is the town or city if the address	’Exampleford’
county	VARCHAR	County where the address resides	’West Exampleshire’
postcode	VARCHAR	Postcode with spaces removed properly	’AB11 1CD’
_execution_date	VARCHAR	Formatted transform datetime for versioning	’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	_base_ingest_time	✓	Exists in v1 with a different name
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_location_id	bigint	✓	✓
emis_location_guid	varchar	✓	✓
parent_location_id	bigint	✗	✓
emis_parent_location_guid	varchar	✓	✓
location_name	varchar	✓	✓
location_type_description	varchar	✓	✓
open_date	date	✓	✓
close_date	date	✓	✓
main_contact_name	varchar	✓	✓
email_address	varchar	✓	✓
phone_number	varchar	✓	✓
fax_number	varchar	✓	✓
house_name_flat_number	varchar	✓	✓
number_and_street	varchar	✓	✓
village	varchar	✓	✓
town	varchar	✓	✓
county	varchar	✓	✓
postcode	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	_base_ingest_time	✓	Exists in v1 with a different name
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_location_id	bigint	✓	✓
emis_location_guid	varchar	✓	✓
location_name	varchar	✓	✓
location_type_description	varchar	✓	✓
parent_location_id	bigint	✗	✓
emis_parent_location_guid	varchar	✓	✓
email_address	varchar	✓	✓
phone_number	varchar	✓	✓
fax_number	varchar	✓	✓
main_contact_name	varchar	✓	✓
open_date	date	✓	✓
close_date	date	✓	✓
house_name_flat_number	varchar	✓	✓
number_and_street	varchar	✓	✓
village	varchar	✓	✓
town	varchar	✓	✓
county	varchar	✓	✓
postcode	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	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_location_id	bigint	✓	✓
emis_location_guid	varchar	✓	✓
location_name	varchar	✓	✓
parent_location_id	bigint	✗	✓
emis_parent_location_guid	varchar	✓	✓
email_address	varchar	✓	✓
phone_number	varchar	✓	✓
fax_number	varchar	✓	✓
main_contact_name	varchar	✓	✓
open_date	date	✓	✓
close_date	date	✓	✓
location_type_description	varchar	✓	✓
house_name_flat_number	varchar	✓	✓
number_and_street	varchar	✓	✓
village	varchar	✓	✓
town	varchar	✓	✓
county	varchar	✓	✓
postcode	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar(14)	✓	✓

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	✗	✓
emis_location_id	bigint	✓	✓
emis_location_guid	varchar	✓	✓
location_type_description	varchar	✓	✓
parent_location_id	bigint	✗	✓
emis_parent_location_guid	varchar	✓	✓
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	deleted	✓	Exists in v1 with a different name
load_datetime	timestamp(6) with time zone	✗	✓
emis_location_id	bigint	✓	✓
emis_location_guid	varchar	✓	✓
location_type_description	varchar	✓	✓
parent_location_id	bigint	✗	✓
emis_parent_location_guid	varchar	✓	✓
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	✓	Exists in v1 with a different name

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	✗	✓
location_id	bigint	✓	✓
location_guid	varchar	✓	✓
parent_location_id	bigint	✗	✓
parent_location_guid	varchar	✓	✓
open_date	date	✓	✓
close_date	date	✓	✓
location_type_description	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	✓	Exists in v1 with a different name

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	_base_ingest_time	✓	Exists in v1 with a different name
is_deleted	boolean	✗	✓
load_datetime	timestamp(6) with time zone	✗	✓
emis_location_id	bigint	✓	✓
emis_location_guid	varchar	✓	✓
parent_location_id	bigint	✗	✓
emis_parent_location_guid	varchar	✓	✓
location_type_description	varchar	✓	✓
location_name	varchar	✓	✓
main_contact_name	varchar	✓	✓
open_date	date	✓	✓
close_date	date	✓	✓
email_address	varchar	✓	✓
phone_number	varchar	✓	✓
fax_number	varchar	✓	✓
house_name_flat_number	varchar	✓	✓
number_and_street	varchar	✓	✓
village	varchar	✓	✓
town	varchar	✓	✓
county	varchar	✓	✓
postcode	varchar	✓	✓
organisation	varchar	✓	✓
transform_datetime	timestamp(6) with time zone	✗	✓
_execution_date	varchar	✓	✓

Zeus

Not used in this schema