Dataset schema in OpenSAFELY-TPP¶

This notebook displays the schema of the OpenSAFELY-TPP database. It is part of the technical documentation of the OpenSAFELY platform to help users understand the underlying data and guide analyses.

The schema information is read from the OpenSAFELYSchemaInformation table, which is refreshed at the same time as the core S1 SystemOne tables. There are some non-automated steps required to update the schema information when a new table is added to the database — if you can't see a table that you are expecting to see, speak to TPP.

If you would like to apply to use the OpenSAFELY platform please read our documentation, the principles of the platform, and information about our pilot programme for onboarding external users.

If you want to see the Python code used to create this notebook, you can view it on GitHub.

Data sources¶

The core SystmOne primary care datasets are held in the S1 tables in the OpenSAFELY-TPP database. Other externally-linked data sources are listed below, with the table name given in brackets:

All positive or negative SARS-CoV2 tests, from SGSS (SGSS_AllTests_Positive and SGSS_AllTests_Negative)
First-ever positive or negative SARS-CoV2 test, from SGSS (SGSS_Positive and SGSS_Negative)
A&E attendances, from SUS Emergency Care data (EC)
In-patient hospital admissions, from SUS Admitted Patient Care Spells data (APCS)
Out-patient hospital appointments, from SUS (OPA)
Covid-related ICU admissions, from ICNARC (ICNARC)
Covid-related in-hospital deaths, from CPNS (CPNS)
COVID-19 Infection Survey, from ONS (ONS_CIS)
All-cause registered deaths, from ONS (ONS_Deaths)
High cost drugs (HighCostDrugs)
Unique Property Reference Number, used for deriving household variables (UPRN)
Master Patient Index (MPI)
Health and Social Care Worker identification, collected at the point of vaccination (HealthCareWorker)

Some of these tables are accompanied by additional tables with further data. For instance, OPA contains the core out-patient appointment event data, and is supplemented by the OPA_Cost, OPA_Diag, OPA_Proc tables. See the data schema notebook for more information.

## Import libraries

%load_ext autoreload
%autoreload 2

import pyodbc
import os
import pandas as pd
import numpy as np
from datetime import date, datetime
from IPython.display import display, Markdown

import sys
sys.path.append('../lib/')
from functions import *

The autoreload extension is already loaded. To reload it, use:
  %reload_ext autoreload

# get the server credentials from environ.txt

dbconn = os.environ.get('FULL_DATABASE_URL', None).strip('"')

## Import schema data and date

with closing_connection(dbconn) as cnxn:
    table_schema = pd.read_sql("""select * from OpenSAFELYSchemaInformation""", cnxn)

today = date.today()

Notebook run date¶

display(Markdown(f"""This notebook was run on {today.strftime('%Y-%m-%d')}.  The information below reflects the state of the OpenSAFELY-TPP as at this date."""))

Table names by source¶

The table below lists all the data tables available in the OpenSAFELY-TPP database and where the data originate from.

table_names = table_schema[['DataSource', 'TableName']].drop_duplicates().sort_values(['DataSource', 'TableName'])
table_names = table_names[table_names['DataSource']!=""]
display(table_names.reset_index(drop=True).style.set_properties(**{'text-align': 'left'}))

Table Schema¶

The schema for each table contains the following info:

ColumnName, the column name.
ColumnType, the column type, for example integer, numeric or date — see SQL Server data types documentation for more details.
Precision, Scale and MaxLength — see SQL Server precision, scale, and length documentation for more details.
IsNullable, are Null values accepted.

The schema for each table is printed below.

pd.set_option('display.max_columns', None)

for source in table_names['DataSource'].unique():
    
    display(Markdown("\n"))
    display(Markdown(f"### {source}"))
    
    for table in table_names.loc[table_names['DataSource']==source, 'TableName']:
        tab = table_schema[table_schema['TableName']==table]
        tab = tab.drop(columns=['TableName', 'DataSource', 'ColumnId', 'CollationName'])
        display(Markdown(f"#### {table}"))
        display(tab.set_index('ColumnName'))

	DataSource	TableName
0	ICNARC	ICNARC
1	NHSD_HIGH_COST_DRUGS	HighCostDrugs
2	NHSE_APCS	APCS
3	NHSE_APCS	APCS_Cost
4	NHSE_APCS	APCS_Der
5	NHSE_CPNS	CPNS
6	NHSE_EC	EC
7	NHSE_EC	EC_AlcoholDrugInvolvement
8	NHSE_EC	EC_Comorbidities
9	NHSE_EC	EC_Cost
10	NHSE_EC	EC_Diagnosis
11	NHSE_EC	EC_Investigation
12	NHSE_EC	EC_PatientMentalHealth
13	NHSE_EC	EC_Treatment
14	NHSE_ECDS	ECDS
15	NHSE_ECDS	ECDS_EC_Diagnoses
16	NHSE_HEALTH_CARE_WORKER	HealthCareWorker
17	NHSE_MPI	MPI
18	NHSE_MPI	UPRN
19	NHSE_OPA	OPA
20	NHSE_OPA	OPA_Cost
21	NHSE_OPA	OPA_Diag
22	NHSE_OPA	OPA_Proc
23	NHSE_SGSS	SGSS_AllTests_Negative
24	NHSE_SGSS	SGSS_AllTests_Positive
25	NHSE_SGSS	SGSS_Negative
26	NHSE_SGSS	SGSS_Positive
27	NHSE_Therapeutics	Therapeutics
28	ONS_CIS	ONS_CIS
29	ONS_DEATHS	ONS_Deaths
30	OS_BUILD	BuildInfo
31	OS_BUILD	CodeCountIndicator
32	OS_BUILD	LatestBuildTime
33	OS_BUILD	OpenSAFELYSchemaInformation
34	OS_DERIVED	Household
35	OS_DERIVED	HouseholdMember
36	OS_DERIVED	MSOA_PopulationEstimates_2019
37	OS_DERIVED	PotentialCareHomeAddress
38	S1	Appointment
39	S1	CTV3Dictionary
40	S1	CTV3Hierarchy
41	S1	CodedEvent
42	S1	CodedEventRange
43	S1	Consultation
44	S1	DataDictionary
45	S1	ICD10Dictionary
46	S1	MedicationDictionary
47	S1	MedicationIssue
48	S1	MedicationRepeat
49	S1	MedicationSensitivity
50	S1	Organisation
51	S1	Patient
52	S1	PatientAddress
53	S1	QOFClusterReference
54	S1	RegistrationHistory
55	S1	UnitDictionary
56	S1	Vaccination
57	S1	VaccinationReference
58	S1	YCodeToSnomedMapping
59	UK_Renal_Register	UKRR

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
Patient_ID	bigint	8	19	0	False
ICNARC_ID	bigint	8	19	0	True
CalculatedAge	int	4	10	0	True
EstimatedAge	int	4	10	0	True
Sex	varchar	10	0	0	True
OriginalHospitalAdmissionDate	datetime	8	23	3	True
HospitalAdmissionDate	datetime	8	23	3	True
IcuAdmissionDateTime	datetime	8	23	3	True
TransferredIn	varchar	10	0	0	True
OriginalIcuAdmissionDate	datetime	8	23	3	True
HighestLevelFirst24Hours	int	4	10	0	True
Ventilator	int	4	10	0	True
AP2score	int	4	10	0	True
IMscore	int	4	10	0	True
pfratio	real	4	24	0	True
BasicDays_RespiratorySupport	int	4	10	0	True
AdvancedDays_RespiratorySupport	int	4	10	0	True
BasicDays_CardiovascularSupport	int	4	10	0	True
AdvancedDays_CardiovascularSupport	int	4	10	0	True
SupportDays_Renal	int	4	10	0	True
SupportDays_Neurological	int	4	10	0	True
SupportDays_Gastrointestinal	int	4	10	0	True
SupportDays_Dermatological	int	4	10	0	True
SupportDays_Liver	int	4	10	0	True
Level3days	int	4	10	0	True
Level2days	int	4	10	0	True
Level1days	int	4	10	0	True
Level0days	int	4	10	0	True
HRG	varchar	50	0	0	True
yusurv	int	4	10	0	True
IcuDischargeDateTime	datetime	8	23	3	True
TransferredOut	varchar	10	0	0	True
ausurv	int	4	10	0	True
UltimateIcuDischargeDate	datetime	8	23	3	True
yhsurv	int	4	10	0	True
HospitalDischargeDate	datetime	8	23	3	True
ahsurv	int	4	10	0	True
UltimateHospitalDischargeDate	datetime	8	23	3	True
DateOfDeath	datetime	8	23	3	True

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
Patient_ID	bigint	8	19	0	False
FinancialMonth	varchar	2	0	0	True
FinancialYear	varchar	6	0	0	True
PersonAge	int	4	10	0	True
PersonGender	int	4	10	0	True
ActivityTreatmentFunctionCode	varchar	100	0	0	True
TherapeuticIndicationCode	varchar	1000	0	0	True
HighCostTariffExcludedDrugCode	varchar	100	0	0	True
DrugName	varchar	1000	0	0	True
RouteOfAdministration	varchar	100	0	0	True
DrugStrength	varchar	1000	0	0	True
DrugVolume	varchar	1000	0	0	True
DrugPackSize	varchar	1000	0	0	True
DrugQuanitityOrWeightProportion	varchar	1000	0	0	True
UnitOfMeasurement	varchar	100	0	0	True
DispensingRoute	varchar	100	0	0	True
HomeDeliveryCharge	varchar	100	0	0	True
TotalCost	varchar	100	0	0	True
DerivedSNOMEDFromName	varchar	1000	0	0	True
DerivedVTM	varchar	1000	0	0	True
DerivedVTMName	varchar	1000	0	0	True

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
Patient_ID	bigint	8	19	0	False
APCS_Ident	bigint	8	19	0	False
Carer_Support_Indicator	varchar	1000	0	0	True
Ethnic_Group	varchar	2	0	0	True
Administrative_Category	varchar	2	0	0	True
Patient_Classification	varchar	2	0	0	True
Admission_Method	varchar	2	0	0	True
Discharge_Destination	varchar	2	0	0	True
Discharge_Method	varchar	2	0	0	True
Source_of_Admission	varchar	2	0	0	True
Admission_Date	date	3	10	0	True
Discharge_Date	date	3	10	0	True
Provider_Org_Code_Type	varchar	5	0	0	True
Duration_of_Elective_Wait	int	4	10	0	True
Spell_Core_HRG_SUS	varchar	10	0	0	True
Spell_HRG_Version_No_SUS	varchar	20	0	0	True
Hospital_Spell_Duration	int	4	10	0	True
Der_Spell_LoS	int	4	10	0	True
Der_Diagnosis_Count	int	4	10	0	True
Der_Procedure_Count	int	4	10	0	True
Der_Diagnosis_All	varchar	4000	0	0	True
Der_Procedure_All	varchar	4000	0	0	True
Der_Admit_Treatment_Function_Code	varchar	3	0	0	True
Der_Dischg_Treatment_Function_Code	varchar	3	0	0	True
Der_Pseudo_Patient_Pathway_ID	bigint	8	19	0	True
Der_Activity_Month	varchar	6	0	0	True
Der_Financial_Year	varchar	7	0	0	True

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
Patient_ID	bigint	8	19	0	False
APCS_Ident	bigint	8	19	0	False
Tariff_Initial_Amount	real	4	24	0	True
Tariff_Total_Payment	real	4	24	0	True
Grand_Total_Payment_MFF	real	4	24	0	True

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
Pseudo_uprn	varchar	200	0	0	True
Pseudo_parent_uprn	varchar	200	0	0	True
class	varchar	100	0	0	True
Total_Pop	int	4	10	0	True
_0to4	int	4	10	0	True
_5to9	int	4	10	0	True
_10to14	int	4	10	0	True
_15to19	int	4	10	0	True
_20to24	int	4	10	0	True
_25to29	int	4	10	0	True
_30to34	int	4	10	0	True
_40to44	int	4	10	0	True
_45to49	int	4	10	0	True
_50to54	int	4	10	0	True
_55to59	int	4	10	0	True
_60to64	int	4	10	0	True
_65to69	int	4	10	0	True
_70to74	int	4	10	0	True
_75to79	int	4	10	0	True
_80to84	int	4	10	0	True
_85Plus	int	4	10	0	True
Care_Home_Flag	varchar	100	0	0	True
ServiceType	varchar	100	0	0	True
Rural_Urban_Classification	varchar	100	0	0	True
property_type	varchar	100	0	0	True
private_outdoor_space	varchar	100	0	0	True
private_outdoor_space_area	varchar	100	0	0	True

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
BuildDesc	varchar	100	0	0	False
BuildDate	datetime	8	23	3	False
BuildNumber	int	4	10	0	False

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
CTV3Code	varchar	50	0	0	False
CodeCountIndicator	float	8	53	0	True

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
TableName	nvarchar	256	0	0	False
TableName	sysname	256	0	0	False
ColumnId	int	4	10	0	False
ColumnName	nvarchar	256	0	0	True
ColumnName	sysname	256	0	0	True
ColumnType	nvarchar	256	0	0	False
ColumnType	sysname	256	0	0	False
MaxLength	smallint	2	5	0	False
Precision	tinyint	1	3	0	False
Scale	tinyint	1	3	0	False
CollationName	nvarchar	256	0	0	True
CollationName	sysname	256	0	0	True
IsNullable	bit	1	1	0	True
DataSource	varchar	1000	0	0	True

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
Household_ID	bigint	8	19	0	True
MSOA	varchar	50	0	0	True
NFA_Unknown	bit	1	1	0	True
CareHome	bit	1	1	0	True
Prison	bit	1	1	0	True
HouseholdSize	int	4	10	0	True
MatchesUprnCount	bit	1	1	0	True
MixedSoftwareHousehold	bit	1	1	0	True
TppPercentage	int	4	10	0	True

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
HouseholdMember_ID	bigint	8	19	0	False
Patient_ID	bigint	8	19	0	False
Household_ID	bigint	8	19	0	False

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
MSOA_Code	varchar	50	0	0	False
LA_Code_2019	varchar	50	0	0	False
LA_Code_2020	varchar	50	0	0	False
Age_All	int	4	10	0	False
Age_0	int	4	10	0	False
...	...	...	...	...	...
Age_86	int	4	10	0	False
Age_87	int	4	10	0	False
Age_88	int	4	10	0	False
Age_89	int	4	10	0	False
Age_90_Plus	int	4	10	0	False

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
ParentCTV3Code	varchar	50	0	0	False
ParentCTV3Description	varchar	255	0	0	False
ChildCTV3Code	varchar	50	0	0	False
ChildCTV3Description	varchar	255	0	0	False
ChildToParentDistance	int	4	10	0	False

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
Table	varchar	1000	0	0	True
Type	varchar	255	0	0	True
Code	varchar	255	0	0	True
Description	varchar	1000	0	0	True

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
MultilexDrug_ID	varchar	767	0	0	True
ProductId	bigint	8	19	0	False
FullName	varchar	1000	0	0	True
RootName	varchar	100	0	0	True
PackDescription	varchar	50	0	0	True
Form	varchar	50	0	0	True
Strength	varchar	500	0	0	True
CompanyName	varchar	200	0	0	True
DMD_ID	varchar	50	0	0	True

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
Organisation_ID	bigint	8	19	0	False
GoLiveDate	datetime	8	23	3	False
STPCode	varchar	50	0	0	False
MSOACode	varchar	150	0	0	False
Region	varchar	255	0	0	False

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
ClusterType	varchar	50	0	0	False
ClusterName	varchar	100	0	0	False
CTV3Code	varchar	50	0	0	False
Description	varchar	255	0	0	False

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
Registration_ID	bigint	8	19	0	False
Organisation_ID	bigint	8	19	0	False
Patient_ID	bigint	8	19	0	False
StartDate	datetime	8	23	3	False
EndDate	datetime	8	23	3	False

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
UnitDictionary_ID	int	4	10	0	False
CTV3Code	varchar	50	0	0	False
Units	varchar	50	0	0	False
Minimum	real	4	24	0	False
Maximum	real	4	24	0	False
LowerNormalBound	real	4	24	0	False
UpperNormalBound	real	4	24	0	False
DecimalPlaces	int	4	10	0	False

	ColumnType	MaxLength	Precision	Scale	IsNullable
ColumnName
VaccinationName_ID	int	4	10	0	False
VaccinationName	varchar	100	0	0	False
VaccinationContent	varchar	50	0	0	False

Dataset schema in OpenSAFELY-TPP¶

Data sources¶

Notebook run date¶

Table names by source¶

Table Schema¶

ICNARC¶

ICNARC¶

NHSD_HIGH_COST_DRUGS¶

HighCostDrugs¶

NHSE_APCS¶

APCS¶

APCS_Cost¶

APCS_Der¶

NHSE_CPNS¶

CPNS¶

NHSE_EC¶

EC¶

EC_AlcoholDrugInvolvement¶

EC_Comorbidities¶

EC_Cost¶

EC_Diagnosis¶

EC_Investigation¶

EC_PatientMentalHealth¶

EC_Treatment¶

NHSE_ECDS¶

ECDS¶

ECDS_EC_Diagnoses¶

NHSE_HEALTH_CARE_WORKER¶

HealthCareWorker¶

NHSE_MPI¶

MPI¶

UPRN¶

NHSE_OPA¶

OPA¶

OPA_Cost¶

OPA_Diag¶

OPA_Proc¶

NHSE_SGSS¶

SGSS_AllTests_Negative¶

SGSS_AllTests_Positive¶

SGSS_Negative¶

SGSS_Positive¶

NHSE_Therapeutics¶

Therapeutics¶

ONS_CIS¶

ONS_CIS¶

ONS_DEATHS¶

ONS_Deaths¶

OS_BUILD¶

BuildInfo¶

CodeCountIndicator¶

LatestBuildTime¶

OpenSAFELYSchemaInformation¶

OS_DERIVED¶

Household¶

HouseholdMember¶

MSOA_PopulationEstimates_2019¶

PotentialCareHomeAddress¶

S1¶

Appointment¶

CTV3Dictionary¶

CTV3Hierarchy¶

CodedEvent¶

CodedEventRange¶

Consultation¶

DataDictionary¶

ICD10Dictionary¶

MedicationDictionary¶

MedicationIssue¶

MedicationRepeat¶

MedicationSensitivity¶

Organisation¶

Patient¶

PatientAddress¶

QOFClusterReference¶

RegistrationHistory¶

UnitDictionary¶

Vaccination¶

VaccinationReference¶

YCodeToSnomedMapping¶