The data we model

Two source families answer different questions

Most person-level work draws on two broad families. OLIDS provides detailed GP clinical records: observations, diagnoses, medication orders, encounters and registrations. Commissioning feeds record hospital activity, waiting lists, community and mental-health services, prescribing and other administrative activity. Reference data supplies organisations, geographies and clinical classifications.

These sources observe different parts of a person's care. A GP medication order and a hospital attendance describe different events. A provider submission may be corrected later. The absence of a record can mean no activity, incomplete coverage or a failed link. The source's purpose helps you decide what its records can support.

In the repository, GP source preparation is under models/staging/olids/. Commissioning source preparation is under models/staging/commissioning/. You do not need to remember every folder. The dataset and model directory keeps the detailed lists available when you need them.

A person can have several patient records

A patient record belongs to a source context, such as a practice registration. One person may have several such records. Counting patient records is therefore not necessarily counting people. In OLIDS, stg_olids_patient_personprovides the relationship between those records and the project's person identity.

A person spine is the starting list of people to which other information is attached. The reporting person models have already resolved the patient-to-person relationship. Start with the model whose included population matches the question, rather than constructing another person list from events.

The upstream dbt-olids project creates the local patient and person identities. person_id is not derived from an NHS number, so people without one can still be represented. Commissioning datasets do not share that OLIDS identifier.

Cross-dataset linkage can leave people unmatched

dim_person_demographics supplies sk_patient_id alongside person context such as practice and geography. dim_person_pseudo provides the identifier mapping when that is all you need. The following fictional rows show why that mapping is a separate relationship.

There are three patient records but two people. P1 can be matched to a commissioning record carrying X101. P2 cannot be linked through an NHS-number-based identifier here. That does not establish that P2 had no service activity.

An inner join to commissioning activity keeps matching people only. A left join can retain the starting population, with missing activity fields for unmatched people. Both joins can repeat a person if several activity rows match. The next chapter explains those row changes. For now, keep two questions separate: how many people linked, and what activity was recorded for those people?

Report linkage coverage for your chosen population and check the uniqueness of the keys used. Do not assume every commissioning feed supports person linkage. For example, the English Prescribing Dataset supports practice-level prescribing analysis; a practice total is not a person's medication history.

OLIDS models turn records into clinical concepts

OLIDS arrives from the upstream dbt project with identifiers, names and clinical terminology already prepared. Observation and medication-order models include mapped SNOMED codes and descriptions; medication orders also carry BNF classifications. In this handbook, those prepared tables are the source.

The analytical project then interprets those records. For example, int_hba1c_all contains qualifying HbA1c results, while int_hba1c_latest selects a result per person. A register such as fct_person_diabetes_register applies an agreed clinical definition. Its result has a different meaning from a list of diagnosis records.

That distinction matters when validating an answer. Finding a code is evidence; determining register membership can also require dates, exclusions and other criteria. The model description and retained evidence explain which interpretation was made. The model name helps you find it, but does not establish its suitability.

Commissioning models describe activity and pathways

SUS covers admitted care, outpatients and urgent and emergency care. CSDS covers community services; MHSDS covers mental-health activity. Waiting-list feeds record pathways at reporting dates. These feeds have their own identifiers, submission rules and units of activity.

A hospital spell, an appointment and a waiting-list pathway are different things to count. One person can have several of each. Models such as fct_person_sus_uec_recentsummarise activity at person level, while event models retain individual encounters. Choose according to whether you need people's activity totals or the events themselves.

int_wl_currentselects the most recent waiting-list census date present in its staging input. A recent date alone does not establish that every provider's submission is complete. Check the coverage and period represented before interpreting a movement as a change in demand.

Choose the data's time as well as its subject

dim_person_demographics gives current person context. dim_person_demographics_historicalretains change periods. A person's current practice may differ from the practice responsible for them during last year's activity. Neither shape is universally better; they answer different questions.

fct_person_condition_episodes describes condition episodes with start and end dates. person_month_analysis_base combines historical demographics and condition information into one row per active person per month. It is a useful candidate for monthly population trends because the time alignment is part of its purpose.

Read the documented population and period before using these models. A table describing people today cannot reconstruct a historical population merely by adding an earlier date to the output.

Products have an approved purpose

Published models assemble data for a named report, dashboard, extract or operational process. The project separates direct-care and secondary-use products so their purpose and audience are visible. The appropriate access, disclosure and exclusion rules follow the product's approved governance and its sources.

Where required, dim_person_secondary_use_allowedsupplies the project's allowed population for the relevant product. Its current definition considers National Data Opt-Out and Type 1 opt-out status. Using that model does not by itself establish that a new use is approved. Nor does pseudonymisation or a broad source-family label settle which controls apply.

The data layers chapter explains where shared definitions and product-specific requirements belong. First, Understanding analytical tables shows how to reason about the rows you will join.