medicines-snowflake/docs/data_sources_and_join_patterns.md

Data Sources And Join Patterns

This repo assumes access to the Norfolk and Suffolk Snowflake environment used for medicines optimisation analysis.

Core Medicines Sources

REPORTING_DATASETS_ICB.SCRATCHPAD."MEDS__UnifiedPrescribingTable"

Use this for current prescribing analysis when available. It combines EMIS and TPP prescribing into a single shape with PersonKey, SNOMEDCode, DateMedicationStart, quantity, estimated price, source system, prescribing organisation, and current registered GP.

NATIONAL.GPMED."MedicinesDispensedInPrimarycare"

Use this for official dispensing activity. It is usually slower to refresh than prescribing but is better aligned to dispensing/payment concepts. Key columns include ProcessingPeriodDate, PatientPseudonym, PaiddmdCode, PaidBNFCode, CostCentreODSCode, ItemCount, PaidQuantity, and TotalPaidGross.

DATA_HUB.DWH."DimMedicineAndDevice"

Use this as the medicine reference table. It links SNOMED product codes to BNF, VTM, VMP, product descriptions, routes, strengths, and indicative price fields.

DATA_HUB.DWH."DimOrganisationAndSite"

Use this for practice names and hierarchy columns such as PCN, Place, Alliance, and INT. For Norfolk/Suffolk practice reports, the common filter is:

WHERE "OrganisationSubType" = 'GP Practice'
  AND "IsSiteActive" = 'Yes'
  AND "IsSiteNorfolkAndSuffolk" = 'Yes'
  AND "SiteCode" = "OrganisationCode"

DATA_HUB.DWH."DimPerson"

Use this for registered GP, age, demographic fields, and pseudonym-to-person links. For Suffolk-inclusive work, avoid old Norfolk-and-Waveney-only registration filters unless the report is explicitly Norfolk and Waveney only.

DATA_HUB.PHM."PrimaryCareClinicalCoding"

Use this for clinical coding events. Join on PatientPseudonym, then filter by SNOMEDCode and EventDateTime.

DATA_HUB.PHM."ClinicalCodingClusterSnomedCodes"

Use maintained clinical coding clusters where possible. This is usually safer than searching SNOMED descriptions with text matching.

Common Joins

Prescribing to medicine:

INNER JOIN DATA_HUB.DWH."DimMedicineAndDevice" med
    ON rx."SNOMEDCode" = med."ProductSnomedCode"

Prescribing to registered practice:

INNER JOIN DATA_HUB.DWH."DimOrganisationAndSite" gp
    ON rx."CurrentGeneralPractice" = gp."OrganisationCode"
   AND gp."SiteCode" = gp."OrganisationCode"

Dispensing to medicine:

INNER JOIN DATA_HUB.DWH."DimMedicineAndDevice" med
    ON gpm."PaiddmdCode" = med."ProductSnomedCode"

Clinical coding to maintained cluster:

INNER JOIN DATA_HUB.PHM."ClinicalCodingClusterSnomedCodes" c
    ON cc."SNOMEDCode" = c."SNOMEDCode"
   AND c."Cluster_ID" = 'REPLACE_WITH_CLUSTER_ID'

Choosing The Date Field

Use ProcessingPeriodDate for dispensing.

Use DateMedicationStart for prescribing activity windows.

Use DateEventRecorded as a conservative freshness marker for TPP prescribing extracts when checking whether the latest full month is safe to report.