How are the TPMAs generated?

This should include a map of all the different data layers and what gets cleaned/added at each stage from hes_apc > raw_data > default > inputs-data / model-data parquet files, with links to the specific files where the cleaning happens at each stage.

Step (1): UDAL HES data to hes_* tables

• Raw data is recieved from NHS England via UDAL (a secure NHS data platform), and is accessed for pipeline development via Databricks.
• Raw NHSE HES data requires restructuring before it can be used, and this step normalises it into a structured table called HES_apc.
• The resulting HES_apc tables are only available to Strategy Unit (SU) colleagues and sits upstream of the open GitHub repo.

Link: https://github.com/The-Strategy-Unit/hes_processing [outdated - @tomjemmett to update]

Step (2): hes_* tables to raw_data tables

• Still at episode level, but many useful columns are added to support downstream processing.
• New columns include:

1. Maternity episode type (re-derived, as UDAL does not include the HES-derived version)
2. Primary diagnosis
3. Primary procedure
4. Treatment specialty groupings (TRESPEF)
5. Delivery/Birth flags

• There is also a simple true/false flag for whether a procedure was administered in the episode or not, which makes it easier to filter activity later on in the pipeline.
• Key filters applied at this step are:

1. Mental health providers are removed (using the ERIC dataset) to prevent extremely Length-of-Stay (LoS) records skewing results.
2. Well baby episodes are removed (minimal medical intervention).
3. Unfinished episodes are removed (patient still admitted at the time the data was submitted to SUS).

• Independent sector providers are retained at this step.
• This is also where Types of Potentially Mitigatable Activity (TPMAs) are flagged on individual rows.

Episode vs. Spell explained

• A spell= full hospital stay from admission to discharge. But a spell can contain multiple episodes (one per consultant/care change)
• The pipeline uses the last episode in the spell because:

1. It should contain the most complete ICD-10 diagnosis coding.
2. Length-of-Stay (LoS) is only known at discharge.
3. Modelling at admission-avoidance level requires spell-level thinking, not individual episode-level.
4. Data integrity issues (e.g. hospitals changing EPR systems, breaking spell ID continuity) making the joining of first and last episodes unreliable.
5. Known limitation: primary diagnosis at last episode may differ from the reason for original admission. This is acknowledged as a known trade-off.
6. Inpatients remain at individual record (unaggregated) level throughout.

Link: https://github.com/The-Strategy-Unit/nhp_data/tree/main/src/nhp/data/raw_data

Step (3): raw_data tables to aggregated_ tables

• Outpatients and A&E data are aggregated (grouped by characteristics such as age, sex, ethinicty, ICB, and with activity counts summed). This is to reduce data volume and memory requirements.
• Individual-level detail is lost at this point (meaning things like appointment dates).
• Inpatients are never aggregated, and always remain at record level.

Link: https://github.com/The-Strategy-Unit/nhp_data/tree/main/src/nhp/data/aggregated_data

Step (4): raw_data tables to default tables

• Filters to acute NHS providers only, and excludes independent sector providers.
• This is the recommended table for most TPMA-related analysis work.
• Raw data tables (which retain the independent sector providers) are available for more granular (in-depth) or research uses cases.

Link: https://github.com/The-Strategy-Unit/nhp_data/tree/main/src/nhp/data/default

Step (5): default tables to inputs data parquet files

https://github.com/The-Strategy-Unit/nhp_data/tree/main/src/nhp/data/inputs_data

default tables to model data parquet files

https://github.com/The-Strategy-Unit/nhp_data/tree/main/src/nhp/data/model_data