Andrew Charlwood
63 lines
2.6 KiB
Markdown
63 lines
2.6 KiB
Markdown
# Template Validation Status
|
|
|
|
Last reviewed: 2026-05-12
|
|
|
|
## Scope
|
|
|
|
The reusable SQL templates in folders `01_medicine_lookups` through
|
|
`06_advanced_methods` were checked with the Snowflake MCP against live table
|
|
metadata and representative parameter values. Templates that use `SET`
|
|
variables were validated by substituting safe example literals into the final
|
|
`SELECT` statement before running `describe_query`.
|
|
|
|
The `00_copied_reference` folder and `docs/training_intro_snowflake_sql` are
|
|
historic/reference material. They are retained for learning and comparison,
|
|
not as the preferred starting point for new analysis.
|
|
|
|
## Live Metadata Checked
|
|
|
|
- `REPORTING_DATASETS_ICB.SCRATCHPAD."MEDS__UnifiedPrescribingTable"`
|
|
- `NATIONAL.GPMED."MedicinesDispensedInPrimarycare"`
|
|
- `DATA_HUB.DWH."DimMedicineAndDevice"`
|
|
- `DATA_HUB.DWH."DimOrganisationAndSite"`
|
|
- `DATA_HUB.DWH."DimPerson"`
|
|
- `DATA_HUB.PHM."ClinicalCodingClusterSnomedCodes"`
|
|
- `DATA_HUB.PHM."PrimaryCareClinicalCoding"`
|
|
- `PRIMARY_CARE.TPP."SRPrimaryCareMedication"`
|
|
- `PRIMARY_CARE.TPP."SRPatient"`
|
|
|
|
## Query Compilation Checks
|
|
|
|
All files below passed Snowflake MCP `describe_query` validation:
|
|
|
|
- `01_medicine_lookups/medicine_reference_lookup.sql`
|
|
- `01_medicine_lookups/prescribing_by_vtm.sql`
|
|
- `01_medicine_lookups/prescribing_by_vmp.sql`
|
|
- `01_medicine_lookups/dispensing_by_vtm_or_vmp.sql`
|
|
- `01_medicine_lookups/prescribing_for_patient_pseudonym.sql`
|
|
- `02_prescribing_analysis/practice_level_bnf_prescribing_summary.sql`
|
|
- `02_prescribing_analysis/high_prescribing_practices_quintile_template.sql`
|
|
- `02_prescribing_analysis/prescribing_spend_by_patient_template.sql`
|
|
- `03_cohorts_and_clinical_coding/cluster_code_lookup.sql`
|
|
- `03_cohorts_and_clinical_coding/monthly_clinical_event_count_by_practice.sql`
|
|
- `03_cohorts_and_clinical_coding/prescribing_plus_clinical_code_cohort.sql`
|
|
- `04_rolling_and_pqs/rolling_period_generator.sql`
|
|
- `04_rolling_and_pqs/latest_data_dates.sql`
|
|
- `04_rolling_and_pqs/baseline_vs_evaluation_template.sql`
|
|
- `04_rolling_and_pqs/dual_source_long_format_measure_template.sql`
|
|
- `05_audit_detail/human_readable_prescribing_detail.sql`
|
|
- `06_advanced_methods/product_price_and_quantity_parsing_template.sql`
|
|
|
|
## Freshness Probe
|
|
|
|
The `latest_data_dates.sql` query was also executed with Snowflake MCP. At the
|
|
time of review it returned:
|
|
|
|
- `NATIONAL.GPMED."MedicinesDispensedInPrimarycare"` latest
|
|
`ProcessingPeriodDate`: `2025-07-01`
|
|
- `PRIMARY_CARE.TPP."SRPrimaryCareMedication"` latest `DateEventRecorded`
|
|
within the recent-window probe: `2026-03-28`
|
|
- `REPORTING_DATASETS_ICB.SCRATCHPAD."MEDS__UnifiedPrescribingTable"` latest
|
|
non-future `DateMedicationStart`: `2026-05-12`
|
|
|