Andrew Charlwood
83 lines
3.6 KiB
Markdown
83 lines
3.6 KiB
Markdown
# Snowflake Medicines Optimisation Query Templates
|
|
|
|
Curated Snowflake SQL templates for medicines optimisation analysis. The aim is to give a new analyst a clean starting point without copying the whole working query folder, including old experiments and one-off scripts.
|
|
|
|
The templates are written for analysts who already understand medicines datasets and reporting concepts, but who may not have pharmacy training. Clinical judgement, formulary interpretation, and patient-level action should still be checked with the relevant clinical lead.
|
|
|
|
## Start Here
|
|
|
|
1. Use `docs/data_sources_and_join_patterns.md` to understand the main tables and joins.
|
|
2. Use `01_medicine_lookups/medicine_reference_lookup.sql` to identify the right BNF, VTM, VMP, or product codes.
|
|
3. Read `docs/sql_style_and_validation_guardrails.md` when adapting a template for the first time.
|
|
4. Check `docs/template_validation_status.md` to see what was validated against Snowflake.
|
|
5. Pick the closest template folder below.
|
|
6. Replace the visible `SET` values and any `REPLACE_WITH...` placeholders.
|
|
7. Run a small test first, then check output columns before using the result in a report.
|
|
|
|
## Folder Guide
|
|
|
|
`00_copied_reference/`
|
|
|
|
Original files copied from the working repo because they were already template-like. Use them as reference, not as the preferred starting point.
|
|
|
|
`01_medicine_lookups/`
|
|
|
|
Lookup and extraction templates for medicines by VTM, VMP, BNF, dispensing product, or patient pseudonym.
|
|
|
|
`02_prescribing_analysis/`
|
|
|
|
Practice-level prescribing summaries, high-prescribing quintiles, and patient-level cost ranking.
|
|
|
|
`03_cohorts_and_clinical_coding/`
|
|
|
|
Templates for combining prescribing exposure with clinical coding, maintained SNOMED clusters, and month/practice event grids.
|
|
|
|
`04_rolling_and_pqs/`
|
|
|
|
Rolling-period, long-format, baseline-vs-evaluation, and latest-data templates. These are useful for PQS-style reporting and repeatable monthly outputs.
|
|
|
|
`05_audit_detail/`
|
|
|
|
Row-level audit extract pattern with medicine and organisation names added. Use this after an aggregate query identifies records worth checking.
|
|
|
|
`06_advanced_methods/`
|
|
|
|
More technical reference logic, currently including raw TPP quantity parsing and product price lookup. Prefer maintained tables unless you need to rebuild or audit the method.
|
|
|
|
`docs/`
|
|
|
|
Plain-language notes on data sources, joins, common medicines-analysis terms, SQL guardrails, and the copied introductory SQL/Snowflake training programme.
|
|
|
|
## Common Defaults
|
|
|
|
For Norfolk/Suffolk practice reporting, templates use:
|
|
|
|
```sql
|
|
WHERE "OrganisationSubType" = 'GP Practice'
|
|
AND "IsSiteActive" = 'Yes'
|
|
AND "IsSiteNorfolkAndSuffolk" = 'Yes'
|
|
AND "SiteCode" = "OrganisationCode"
|
|
```
|
|
|
|
Change this deliberately if the report is Norfolk and Waveney only, branch-site level, provider-level, or national.
|
|
|
|
## Practical Checks Before Sharing Outputs
|
|
|
|
- Confirm whether the question needs prescribing data or dispensing data.
|
|
- Check latest available dates before setting the reporting period.
|
|
- Keep denominator logic visible in the SQL.
|
|
- Use maintained coding clusters before text-searching SNOMED descriptions.
|
|
- Avoid `SELECT *` in shared audit extracts.
|
|
- Do not commit patient pseudonyms, row-level extracts, CSVs, or spreadsheets.
|
|
|
|
## Template Sources
|
|
|
|
The cleaned templates were derived from recurring patterns in the working query repo, including:
|
|
|
|
- VTM/VMP prescribing and dispensing checks.
|
|
- Practice-level high-prescribing analysis.
|
|
- Prescribing plus clinical coding cohorts.
|
|
- SMR/frailty-style monthly practice grids.
|
|
- PQS rolling-period and long-format outputs.
|
|
- Human-readable audit extracts using medicine and organisation dimensions.
|