# SQL Style And Validation Guardrails

These notes are extracted from repeated medicines optimisation query work. They are intended to prevent common Snowflake and medicines-data errors.

## Snowflake Syntax

- Double-quote table and column identifiers, especially mixed-case columns such as `"PatientPseudonym"` and `"ProcessingPeriodDate"`.
- Quote aliases that will be consumed by Excel, Power BI, Python, or another SQL layer: `COUNT(*) AS "PatientCount"`.
- Use `CURRENT_DATE()` or `CURRENT_TIMESTAMP()` rather than T-SQL functions such as `GETDATE()`.
- Use `LIMIT` for quick checks.
- Cast long-format output values to a consistent numeric type before `UNION ALL`.

## Medicines Table Choices

- Use `REPORTING_DATASETS_ICB.SCRATCHPAD."MEDS__UnifiedPrescribingTable"` for current prescribing analysis when available.
- Use `NATIONAL.GPMED."MedicinesDispensedInPrimarycare"` for official dispensed/paid activity.
- Do not treat prescribing and dispensing as interchangeable. They answer related but different questions.
- Map prescribing SNOMED codes to BNF through `DATA_HUB.DWH."DimMedicineAndDevice"`.
- Use `"ProductDescription"` for medicine names from `DimMedicineAndDevice`. Do not assume a `"ProductName"` column.

## Geography And Denominators

- Keep the practice CTE visible in each report.
- For Norfolk/Suffolk GP practice outputs, use `DATA_HUB.DWH."DimOrganisationAndSite"` with `"IsSiteNorfolkAndSuffolk" = 'Yes'`.
- Only use older Norfolk-and-Waveney flags when the report is explicitly Norfolk and Waveney only.
- Be explicit about whether you are using organisation registered population, a counted active patient denominator, or another denominator.

## Clinical Coding

- Prefer maintained clusters in `DATA_HUB.PHM."ClinicalCodingClusterSnomedCodes"` over text searching SNOMED descriptions.
- Use `DATA_HUB.PHM."PrimaryCareClinicalCoding"` for unified clinical coding across systems.
- Join clinical coding to patients through `PatientPseudonym`, then to `DimPerson` when person or practice fields are needed.
- Make lookback windows visible in `SET` variables or a date CTE.

## Validation Before Sharing

- Check latest data dates before selecting a period.
- Run a limited version first and inspect row counts.
- Check the output column list before handing results to someone else.
- Avoid `SELECT *` in audit/detail outputs.
- Do not commit patient pseudonyms, CSV exports, spreadsheets, images, or local tooling files.