The data we model

The project has two main data families

Most person-level work in the project draws from one or both of two broad families. OLIDS provides the detailed GP clinical record. Commissioning data provides hospital activity, waiting lists, prescribing, community and mental-health activity, referrals and other administrative flows. Shared reference models add organisations, geographies, deprivation and clinical classifications to both.

The distinction is visible in the repository. OLIDS staging models live in models/staging/olids/. National and local commissioning feeds live under models/staging/commissioning/, with a folder for each source. Their downstream models remain separated where provenance matters and are joined where the project has a supported person-level relationship.

You rarely need to begin in either staging directory. The project already contains reporting models for common clinical populations, demographics, recent activity and waiting-list measures. Understanding the source families helps you interpret those models; it should not send every new question back to the source.

What the project creates from those sources

The dbt project does more than clean the incoming tables. It creates named, tested relations in the modelling, reporting and published databases. Each family removes a different piece of work that would otherwise be repeated in individual analyses.

These outputs are the project's accumulated domain knowledge. Staging makes the inputs dependable enough to model; modelling and reporting create reusable concepts; published models assemble those concepts for a named use. The rest of this page shows how the two source families feed that estate.

OLIDS supplies the GP clinical record

OLIDS delivers the GP record as a set of related entities: a patient and person spine, clinical events such as observations, medication orders, encounters and referrals, appointment and practitioner records, and the concept tables that resolve source codes. The feed arrives already conformed and tested upstream, so the staging models — stg_olids_observation and its neighbours — are deliberately thin: the project's names and types, and nothing more.

That preparation is our own work, done upstream in a separate dbt project, dbt-olids, which conforms and tests the OneLondon feed before it lands in this project's data lake. It mints the patient and person identifiers used throughout these models — a local identity, not the one OneLondon supplies — and maps every observation and medication order to its SNOMED concept (mapped_concept_code, mapped_concept_display), with medication orders additionally carrying the full BNF hierarchy: code, chapter, section and product name. Filtering by clinical concept or drug class therefore needs no terminology join; the codes are usable columns from the first query. This handbook does not cover dbt-olids itself — from here, its outputs are simply the source.

The investment sits in the modelling and reporting layers, where several hundred models turn those events into reusable clinical meaning. Unlike most domains, which occupy one folder, OLIDS is split into a folder per family — and because folder paths become schemas, this table is also a map of the warehouse:

The measurement models also handle screening and standardisation: implausible values are filtered out, HbA1c results are resolved to IFCC or DCCT, and results are categorised where thresholds are agreed. The medication models do the equivalent — diabetes medication orders are grouped by type and drug class, with GLP-1, SGLT2 and DPP-4 drugs resolved to active ingredients. Polypharmacy is counted against the NHSBSA 5+ and 10+ thresholds.

fct_person_bp_control is a typical composition of these blocks. It scores each person's latest reading against their NICE NG136 target, selected by age, Type 2 diabetes, CKD and ACR status from the register models — home and ambulatory readings are scored against the lower thresholds that apply to them — then stages hypertension and flags elevated readings in people not on the hypertension register.

Because these families follow the naming grammar, what OLIDS already covers can be enumerated by search:

Each register applies its qualifying codes, dates and exclusions in one shared model, with the supporting evidence retained for inspection.

OLIDS is delivered in current, historical and analytical shapes

The same clinical concepts are delivered in several shapes, each making a different claim about time. dim_person_demographics gives the supported current person view. dim_person_demographics_historical keeps one row per person per change period, recording when practice registration, ethnicity or geography changed. Use it when an analysis needs the attributes that applied at an earlier point rather than today's values.

Conditions also have more than a current-register shape. fct_person_condition_episodes creates one row per clinical condition episode, with start and end dates and repeated cycles where the condition can resolve and recur. It is not restricted to the current QOF register definition. This makes it suitable for analysing onset, duration, resolution and a person's clinical history.

person_month_analysis_base composes those histories into a wide person-month mart. It contains one row per actively registered person per month for the latest five years, with the demographics that applied in that month, calendar and financial-period fields, active-condition flags and new-episode flags. It is the practical starting point for population trends, prevalence, incidence and inequalities analysis that would otherwise have to rebuild the same temporal joins for every measure.

These shapes serve different questions. Use the current dimension for a current population, the historical dimension for change periods, the episode fact for condition journeys, and the person-month mart when the analysis itself is monthly. Choosing among them is usually more important than writing another transformation.

Commissioning models cover activity and pathways

Commissioning feeds are commonly submission-based. Providers submit files on a reporting schedule and may later send corrections, while each national collection has its own grain and vocabulary. The staging models make each feed consistent for downstream use; modelling and reporting models then express the analytical concepts we use repeatedly.

These are the most-used feeds; the staging layer covers thirteen source folders in all. Above their source-specific preparation, the modelling and reporting layers settle the concepts the feeds only imply:

The folder names are useful search terms. If work concerns emergency attendances, search for sus_uec; if it concerns current waiting, search for wl_current. That normally leads to a reporting model before it leads to staging. The Finding models lesson turns this into a repeatable search process.

OLIDS uses person_id; cross-dataset linkage uses sk_patient_id

One person can hold several patient records across practice registrations; stg_olids_patient_person resolves them, and the dim_person_* models are already at person grain.

To cross into commissioning data, join dim_person_demographics: it carries sk_patient_id alongside active status, practice, PCN, neighbourhood and borough, so the identifier crossing and the person context arrive in one join (dim_person_pseudo is the bare mapping if that is all you need). People without an NHS number sit outside that bridge — report the linkage coverage your join achieves, and test uniqueness at your model's grain.

Use the taxonomy to find a more specific model

The overview above describes the main kinds of output. Within them, model names let you search for the exact question the project has already settled:

The table is deliberately a search guide rather than a complete catalogue. This is why model names matter so much in this project. The prefix tells you the model's role; the entity and subject tell you what it describes; suffixes such as _latest or _current tell you which shape of the concept it offers. The model taxonomy lesson explains the grammar, while Finding models shows how to use it as the project's index.

A new question should compose these models

Suppose the request is for people on the diabetes register, their latest HbA1c and their recent emergency-care use. The project already provides the three main blocks: fct_person_diabetes_register, int_hba1c_latest and fct_person_sus_uec_recent. The register and HbA1c model join within OLIDS on person_id. dim_person_demographics then supplies sk_patient_id for the people who can be linked to the SUS activity model — along with the practice and status context the output will want anyway.

It should not return to stg_olids_observation to derive diabetes or HbA1c again, nor to the SUS emergency-care source to recount attendances. Doing that would fork definitions the project already owns. If an existing model is missing evidence or expresses the wrong contract, improve that shared model deliberately; otherwise, reuse it.

The result is the practical experience this project is designed to create: source cleaning, clinical coding, identity resolution and activity deduplication have already been handled upstream. Most downstream work should feel like composing named domain concepts rather than rebuilding the warehouse from raw records.

The data layers lesson explains where each of those responsibilities is settled. When answering a new question, use the model-discovery method to begin from these supported outputs rather than tracing back to source tables.

Published products apply the required controls

Models used for a specific report, dashboard, extract or operational process are assembled under models/published/. The project separates direct_care/ and secondary_use/ products so that purpose and audience are visible in the repository rather than left to a dashboard filter.

Where an approved secondary-use product needs the project's opt-out population, it can use dim_person_secondary_use_allowed when composing that product. The precise controls follow the product's approved governance and data sources; they should not be inferred simply from the fact that data is pseudonymised or belongs to a broad source family. Shared reporting models remain reusable, while the published model owns the requirements of the product it serves.