Bulk Data Extracts

Extract Schema

Each query in an extract is written to its own CSV file, and all CSV files for a run are bundled into a single ZIP.

File structure

• Each query is saved as a CSV file, named after the entity (for example coded_entry.csv). Table files are prefixed with the organisation's ODS code, for example YGMYW-patient.csv.
• CSVs are comma-separated, use " for enclosure and \ for escaping, and use \n as the end-of-line character.
• The first row is a header row of the field names selected in the query.
• All CSV files for a run are added to one ZIP. Any reasonable ZIP naming convention is supported (for example {odsCode}-medicus-test-extract-{yyyy-mm-dd}.zip).
• The ZIP filename is prefixed with full- or delta- for convenience.

For example, a full extract for organisation YGMYW run on 2026-01-15 produces a ZIP named full-YGMYW-medicus-test-extract-2026-01-15.zip, containing:

full-YGMYW-medicus-test-extract-2026-01-15.zip
├── YGMYW-patient.csv
├── YGMYW-patient-registration.csv
├── YGMYW-coded-entry.csv
├── YGMYW-prescription.csv
├── YGMYW-prescription-issue.csv
├── YGMYW-appointment.csv
├── YGMYW-appointment-practitioner.csv
├── YGMYW-appointment-service.csv
├── YGMYW-appointment-type.csv
├── YGMYW-site.csv
└── YGMYW-room.csv

A delta run of the same extract instead produces delta-YGMYW-medicus-test-extract-2026-01-15.zip, with the same CSV files inside.

Delta extracts

Delta extracts minimise transfer and processing by sending only what changed since the previous run.

• The initial extract is always a full extract, with no change_type column.
• Subsequent extracts are delta extracts: Medicus compares against the previous day's full extract and outputs only the changes, adding a change_type column with one of added, updated, or removed.
• If a query includes a uid column, it is used as the unique identifier to decide added/updated/removed. Otherwise Medicus compares all column values.
• If there are no changes for a query, the CSV is still produced with headers and no data rows.
• The primary key of each row is a record UUID. If an extract definition changes, Medicus must restart with a fresh full initial extract.

Common formats used below: UUIDs (for example ac552fad-8100-4044-90b9-e39930ac7d85), dates as YYYY-MM-DD, and date-times as YYYY-MM-DD HH:MM:SS.

Identifier types (used by practitioner, GP, and organisation columns): practitioner/GP *_identifier_type is one of medicus-staff-id, medicus-referral-provider-practitioner-id, gb-nhs-gp-connect-practitioner-id, gb-gmc-number, gb-gphc-number, gb-hcpc-number, gb-nmc-pin, sds-user-id. Organisation organisation_identifier_type is one of medicus-tenant-id, nhs-england-ods-code, third-party-system-organisation-id, unknown-identifier-type.

Patient

{odsCode}-patient.csv

Column	Format	Description
id	UUID	Unique identifier for the patient.
nhs_number	String, no spaces	The patient's NHS number.
date_of_birth	YYYY-MM-DD	The patient's date of birth.
official_name_prefix	String	The patient's prefix, for example Mr.
official_given_name	String	The patient's first name.
official_family_name	String	The patient's surname.
preferred_given_name	String	The patient's preferred first name.
clinical_sex	male / female / unknown	Derived: sex at birth, otherwise gender identity.
deceased	Boolean	Whether the patient is deceased.
is_test_patient	Boolean	Whether this is a test patient record.
named_gp_name	String	The name of the patient's named GP.
named_gp_identifier_type	String (identifier type)	Identifier type for the named GP (most commonly a GMC number).
named_gp_identifier_value	String	The named GP identifier.
home_address_line_1	String	The patient's home address.
home_address_line_2	String
home_address_line_3	String
home_address_locality	String
home_address_administrative_area	String
home_address_postal_code	String
home_address_country	String
date_of_death	YYYY-MM-DD	Date of death, if deceased.
communication_email_address	String	Email address for communicating with the patient.
communication_home_phone_number	String	Home phone number.
communication_mobile_phone_number	String	Mobile phone number.
guernsey_social_insurance_number	String, no spaces	Commonly referred to as the "GY Number".

Patient Registration

{odsCode}-patient-registration.csv

Column	Format	Description
id	UUID	Unique identifier for the registration record.
patient_id	UUID	Foreign key for the related patient.
start_date	YYYY-MM-DD	Registration start date.
end_date	YYYY-MM-DD	Registration end date, if the patient has deregistered.
registration_type	String	For example permanent, temporary, immediate-necessary-treatment. Can be customised by the organisation.
prescriptions_dispensed_by_practice	Boolean	Whether the patient has medication dispensed by the practice.
care_home_resident	Boolean	Whether the patient is a care-home resident. May also be recorded as a coded entry.

Coded Entry

{odsCode}-coded-entry.csv

Column	Format	Description
id	UUID	Unique ID for the record.
care_record_id	UUID	Identifier of the underlying care record entry.
patient_id	UUID	Unique identifier for the patient.
entity_type	String	The record type. Useful for filtering, but avoid assuming coded data always falls into neat buckets.
code	String	The SNOMED CT concept ID.
code_description	String	The SNOMED CT description.
clinically_relevant_date	YYYY-MM-DD	The record entry date (for example the observation date).
clinically_relevant_time	HH:MM:SS
care_record_sorting_date_time	YYYY-MM-DD HH:MM:SS	Timestamp used to order care record entries.
created_in_original_system_date_time	YYYY-MM-DD HH:MM:SS	Created date-time in the system the record originated from, if migrated.
created_date_time	YYYY-MM-DD HH:MM:SS	Created date-time in the original system.
encounter_id	UUID	Useful for de-duplicating codes in the same encounter (for example for activity counts).
value_1	Number	Optional value for numeric entries, for example height.
value_1_unit_code	String	SNOMED CT concept ID.
value_1_unit_description	String	The SNOMED CT description.
value_2	Number	Only populated for blood pressures.
value_2_unit_code	String	SNOMED CT concept ID.
value_2_unit_description	String	The SNOMED CT description.
body_site_code	String	Optional SNOMED CT body site (for example for an immunisation or procedure).
body_site_description	String	The SNOMED CT description.
route_code	String	Optional SNOMED CT route (for example 255560000 Intravenous).
route_description	String	The SNOMED CT description.
batch_number	String	Usually only populated for immunisations.
expiry_date	YYYY-MM-DD	Usually only populated for immunisations.
status	Active / Ended	Only populated for certain records, for example allergies.
end_date	YYYY-MM-DD	Only populated for certain records, for example allergies.
practitioner_name	String	The responsible practitioner.
practitioner_identifier_type	String (identifier type)	Identifier type for the responsible practitioner.
practitioner_identifier_value	String	The responsible practitioner's identifier.
organisation_identifier_type	String (organisation identifier type)	Identifier type for the responsible organisation.
organisation_identifier_value	String	The responsible organisation's identifier.
performed_at_local_organisation	Boolean	Whether the activity was performed at the local organisation (used by some payment extracts).
organisation_name	String	The responsible organisation.
encounter_type_code	String	The SNOMED CT encounter type. Commonly 1269515004 (Face to face), 439708006 (Home visit), 386472008 (Phone), 149971000000103 (Online), 37351000000107 (Administration).
encounter_type_description	String	The SNOMED CT description.

Prescription

{odsCode}-prescription.csv

Column	Format	Description
id	UUID	Unique ID for the record.
patient_id	UUID	Unique identifier for the patient.
type	String	One of acute, repeat-prescribing, repeat-dispensing, variable-repeat.
status	String	One of authorised, reauthorised, discontinued, requires-reauthorisation.
product_code	String	The SNOMED CT / dm+d concept ID for the drug product.
product_description	String	The SNOMED CT description.
dosage_instruction	String	Free-text dosage instructions.
start_date	YYYY-MM-DD	Date the prescription is authorised to start from.
expected_end_date	YYYY-MM-DD	Derived estimate of when the supply ends.
authorised_date	YYYY-MM-DD	Date the prescription was authorised.
authorised_by_practitioner_name	String	The authorising practitioner.
authorised_by_practitioner_identifier_type	String (identifier type)	Identifier type for the authorising practitioner.
authorised_by_practitioner_identifier_value	String	The authorising practitioner's identifier.
last_issue_date	YYYY-MM-DD	Date the prescription was last issued.
discontinued_date	YYYY-MM-DD	Date the prescription was discontinued, if applicable.

Prescription Issue

{odsCode}-prescription-issue.csv

Column	Format	Description
id	UUID	Unique ID for the record.
patient_id	UUID	Unique identifier for the patient.
prescription_id	UUID	Unique identifier for the parent prescription.
product_code	String	The SNOMED CT / dm+d concept ID for the drug product.
product_description	String	The SNOMED CT description.
expected_days_supply	Integer	Days the supply is expected to last.
supply_end_date	YYYY-MM-DD	Date the supply is expected to end.
quantity_value	Number	The quantity issued to the patient.
quantity_unit_code	String	SNOMED CT concept ID for the quantity unit.
quantity_unit_description	String	The SNOMED CT description (for example tablet).
issue_date	YYYY-MM-DD	Date the issue was issued to the patient.
issue_method	String	One of electronic-prescription-service, fp10ss-printed-script, handwritten-script, private-handwritten-script, private-printed-script, fp10-personally-administered, gg-ps2016-printed-script, gg-private-printed-script, unknown.
issued_by_practitioner_identifier_type	String (identifier type)	Identifier type for the issuing practitioner.
issued_by_practitioner_identifier_value	String	The issuing practitioner's identifier.
issued_by_practitioner_name	String	The issuing practitioner.
issued_by_organisation	String	The issuing organisation.
issue_number	Integer	The issue number in the authorised sequence (for example 3 of 12).
issue_status	String	not-issued or issued.

Appointment

{odsCode}-appointment.csv

Column	Format	Description
id	UUID	Unique ID for the record.
patient_id	UUID	Unique identifier for the patient.
status	String	One of pending, started, seen, rescheduled, cancelled, did-not-attend.
appointment_type_id	UUID	A practice-defined appointment type.
nhs_national_slot_type_category	String	GPAD category (for example "General Consultation Acute").
intended_start_date_time	YYYY-MM-DD HH:MM:SS	Planned start.
intended_end_date_time	YYYY-MM-DD HH:MM:SS	Planned end.
intended_duration_seconds	Number	Planned duration in seconds.
appointment_container_id	UUID	The session/clinic the appointment belongs to.
delivery_mode	String	One of face-to-face, home-visit, video, phone.
linked_encounter_id	UUID	The encounter created when the appointment was completed, if any.
arrived_date_time	YYYY-MM-DD HH:MM:SS	When the patient was marked as arrived.
booked_date_time	YYYY-MM-DD HH:MM:SS	When the appointment was booked.
cancelled_date_time	YYYY-MM-DD HH:MM:SS	When the appointment was cancelled.
rescheduled_date_time	YYYY-MM-DD HH:MM:SS	When the appointment was rescheduled.

Appointment Practitioner

{odsCode}-appointment-practitioner.csv

Column	Format	Description
id	UUID	Unique ID for the record.
practitioner_name	String	The practitioner's name.
practitioner_identifier_type	String (identifier type)	Identifier type for the practitioner.
practitioner_identifier_value	String	The practitioner's identifier.
appointment_container_id	UUID	The session/clinic this practitioner is associated with.

Appointment Service

{odsCode}-appointment-service.csv

Column	Format	Description
id	UUID	Unique ID for the record.
name	String	The name of the service.

Appointment Type

{odsCode}-appointment-type.csv

Column	Format	Description
id	UUID	Unique ID for the record.
name	String	The name of the appointment type.
appointment_service_id	UUID	Foreign key for the related appointment service.

Site

{odsCode}-site.csv

Column	Format	Description
id	UUID	Unique ID for the record.
name	String	The name of the site.

Room

{odsCode}-room.csv

Column	Format	Description
id	UUID	Unique ID for the record.
name	String	The name of the room.
site_id	UUID	Foreign key for the related site.