How (and why) we work with patient-level data in OpenSAFELY | Bennett Institute for Applied Data Science

Something that new OpenSAFELY users might find surprising is that when you extract the data for your analysis using ehrql, there is one important constraint: the resulting analytic dataset can only have one row per patient.

We refer to this as patient-level data (PLD). In contrast, event-level data (ELD) is where a patient can contribute multiple rows to a dataset, each corresponding to an individual event such as diagnoses, prescriptions, measurements, or hospital admissions.

This distinction affects how you approach your analysis in OpenSAFELY. Research questions that might typically involve working with a table of individual events need to be reformulated so that the relevant information is summarised at the patient level.

In this blog, we explain why OpenSAFELY works this way, what it means in practice, and some useful tips for working with event-based information using patient-level datasets.

Thinking patient-level rather than event-level

Suppose we are interested in atorvastatin prescriptions. In an event-level dataset, the data might look something like this:

patient	medication	prescription_date
A	Atorvastatin	2025-01-12
A	Atorvastatin	2025-06-19
A	Atorvastatin	2025-08-22
B	Atorvastatin	2025-04-11
B	Atorvastatin	2025-12-01

In an ehrQL dataset, each patient can only have one row. So, your patient-level dataset might look something like this:

patient	atorvastatin_first_date	atorvastatin_last_date	atorvastatin_count
A	2025-01-12	2025-08-22	3
B	2025-04-11	2025-12-01	2

ehrQL lets you query individual events by filtering them, and has functions that allow you to find the first or last event, count individual events, or even count episodes (e.g. groups of events in close proximity to each other). But before those data become variables in your dataset, the events must be reduced to a single value for each patient.

Working with patient-level data may require a change in perspective for analysts used to working directly with event tables. For example, rather than extracting every recorded blood-pressure measurement, you might write ehrql code to define:

Each of these produces something represented as a single value for a patient.

Why does OpenSAFELY work this way?

OpenSAFELY is designed to support research using very large volumes of sensitive health data while minimising analysts’ exposure to unnecessary information.

Patient-level extraction encourages an important discipline: users must decide what information their analysis actually needs, and only extract that information, rather than large volumes of individual event records. This has several advantages.

It supports data minimisation, by reducing the volume and granularity of sensitive data being extracted.

It makes the relationship between the raw electronic health record (EHR) data and the analytic dataset explicit. When decisions about how raw clinical events are filtered, selected, and manipulated are expressed in ehrQL, the dataset definition provides a clear record of how the analytic dataset and study variables were constructed. It can be reviewed, tested against dummy data, and reused by other researchers.

It also reduces computational load. The data in OpenSAFELY contain the consultations, diagnoses, prescriptions, procedures, and more for tens of millions of patients; reducing those events to the patient-level produces smaller datasets that are faster and less resource-intensive to store, process, and analyse.

What if an analysis does require individual events?

Sometimes individual events genuinely are of interest for a particular research question, such as constructing longitudinal patient trajectories. For example, an analyst might be interested in patterns of medicine prescribing, or changes in body mass index (BMI) over time.

Here, simply knowing a patient’s first, last, or number of measurements is not enough. The one-row-per-patient constraint does not necessarily mean that the analysis is impossible. One option is to define multiple patient-level variables corresponding to sequential events. This works best for a small number of events. For example, this table contains the patient-level dates for a patient’s COVID-19 vaccination dates that were converted to a PLD:

patient	covid_vax_date_1	covid_vax_date_2	covid_vax_date_3
A	2021-01-14	2021-04-12	2021-08-25

Another option is to extract separate patient-level datasets for different time periods. Rather than creating separate columns for every single measurement over the entire study period in one dataset, you might derive one measurement per patient per month using separate dataset definitions. These resulting datasets can then be combined as necessary.

For looking at aggregate measures over time, such as time series analysis, analysts can use the measures framework. Which approach makes sense depends on the research question.

But, some things are just too difficult without being able to extract ELD. We are currently trialling use of ELD for specific analyses with the plan to roll it out more widely - so watch this space.

Working effectively in OpenSAFELY

The key to working with PLD is to start by asking: “what patient-level information do I actually need to answer my research question?”

You can ask whether events occurred, count them, compare dates, and construct variables describing sequences of events. What you can’t do is extract a large number of event rows and defer these data processing decisions until later. This distinction is central to working effectively in OpenSAFELY.

ehrQL encourages researchers to specify the information required for their analysis and derive a purpose-built, patient-level dataset from the underlying EHR data. In this way, the one-row-per-patient approach is not an obstacle, but a useful principle for writing code for reproducibly defining and extracting a study dataset.