Definition

Overview

mart_patient is a wide analytical patient data mart combining demographics, dispensing activity, service preferences, pharmacy context, contact details, and deprivation data into a single patient-level record. It is the primary model for patient-facing analytical workloads across the Community Pharmacy platform.

The model is incremental and updates are triggered by:

• New data ingested into source tables (stg_pharmacy_patient, patient_additional_service_type, last_patient_dispensing_date)
• Time-driven recalculations where values depend on the modelled date (patient_age, is_active, is_new)

Key Data Fields

Field	Description
unique_patient_id	Globally unique patient identifier, used as the primary key across systems.
patient_full_name	Formatted patient name including title, e.g. “COLE, Molly Gary (Mr)“.
patient_age_bucket	Named age group derived from birth date, e.g. “Older adults (65 to 78)“.
last_dispensing_date	Most recent date the patient had a pharmacy item dispensed.
is_active	True when the patient has had dispensing activity within the last year and is not deceased.
is_new	True when the patient was registered within the last 6 months.
additional_service_type	How the patient receives products: Collection, Delivery, Collection & Delivery, or Walk-in.
imd_decile	Index of Multiple Deprivation decile derived from the patient’s address postcode.

Source

The lineage for this model is: proscript_connect patient, patient contact, patient identifier, ethnicity, pharmacy, patient property, nomination, and address source tables -> stg_pharmacy_patient, stg_patient_contact, stg_patient_identifier, stg_ethnicity, active_pharmacy, patient_additional_service_type, patient_eps_registration_details, patient_imd_decile, and last_patient_dispensing_date -> patient -> mart_patient.

mart_patient is materialized as incremental with merge strategy and unique key (unique_patient_id). Incremental updates are driven by the mart incremental filter and row hash comparison (row_id), so changed or new records are inserted or updated while unchanged rows are skipped.