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

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:

Maternity episode type (re-derived, as UDAL does not include the HES-derived version)
Primary diagnosis
Primary procedure
Treatment specialty groupings (TRESPEF)
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:

Mental health providers are removed (using the ERIC dataset) to prevent extremely long-of-stay (LoS) records skewing results.
Well baby episodes are removed (minimal medical intervention).
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: