NHS Prescribing Cost Intelligence Warehouse

A PostgreSQL analytics engineering portfolio project using 54.1M rows of NHS English Prescribing data to build a local cost intelligence warehouse for descriptive cost analysis.

Current status

The v1 warehouse is built and validated for three 2025 source months: January, February and March.

Validated layers:

• staging.stg_english_prescribing: 54,139,371 rows loaded from official monthly ZIP files.
• cleaned.prescribing_clean: 54,139,371 typed and standardised rows.
• fact.fact_prescribing: 54,139,371 reconciled fact rows.
• mart.*: analytical marts rebuilt from the validated fact table.
• Versioned evidence outputs exist under outputs/validation/ and outputs/analysis_tables/.

This is descriptive healthcare analytics, not clinical judgement. High cost does not prove inappropriate prescribing, waste or poor practice. Regional comparisons require denominators such as population, age profile, deprivation, disease burden and local service context.

Why this project matters

NHS prescribing data is large enough to expose real analytics-engineering problems: bulk loading, source header variation, warehouse modelling, reconciliation, query performance and cautious interpretation. The project demonstrates how to turn raw monthly prescribing files into validated cost intelligence outputs that could support finance, medicines optimisation or analytics review discussions.

Key validated results

Evidence files:

• Row counts: outputs/validation/warehouse_row_counts.txt
• Source coverage: outputs/validation/source_coverage.txt
• Fact reconciliation: outputs/validation/fact_reconciliation.txt
• Mart reconciliation: outputs/validation/mart_reconciliation.txt
• Analysis tables: outputs/analysis_tables/

Validated totals for the loaded period:

• Prescribing rows: 54,139,371
• Items: 308,336,612
• Actual cost: £2,490,569,936.13
• Loaded months: 2025-01, 2025-02, 2025-03

Monthly actual cost:

• 2025-01: £874,176,855.13
• 2025-02: £779,984,033.62
• 2025-03: £836,409,047.37

Top medicine by actual cost in the loaded period:

• Dapagliflozin 10mg tablets: £80,702,858.43 actual cost across 2,249,675 items.

These are descriptive findings only. They do not imply inappropriate prescribing or clinical priority without further context.

Visual evidence

The charts below are generated from the committed analysis outputs, not directly from the raw data. The text outputs remain the source of truth for exact values.

Direct link: visualizations/monthly_actual_cost.svg

Direct link: visualizations/top_medicines_by_actual_cost.svg

Direct link: visualizations/bnf_chapter_cost_profile.svg

Regenerate visualisations after refreshing analysis outputs:

python3 scripts/generate_visualizations.py

Technical highlights

The main engineering issue was not only data volume. The original direct fact load design was too expensive for 54M rows because it combined:

• active primary key, unique constraint, foreign keys and secondary indexes during insert;
• ON CONFLICT on the initial bulk load;
• IS NOT DISTINCT FROM joins that led PostgreSQL to choose very expensive nested-loop plans.

The implemented v1 fact-load strategy is:

cleaned.prescribing_clean
-> fact.fact_prescribing_load without PK/UNIQUE/FKs/secondary indexes
-> hash-join-friendly bulk insert
-> reconciliation checks
-> create indexes and constraints after loading
-> validate foreign keys
-> ANALYZE
-> rename to fact.fact_prescribing

The revised 54.1M-row fact insert completed in approximately 69.5 seconds in the local Docker PostgreSQL environment, followed by index/constraint finalisation and exact reconciliation to the cleaned layer.

See docs/methodology.md for the detailed ingestion, modelling, validation and performance-debugging approach.

Repository structure

nhs-prescribing-cost-intelligence-sql/
  data/raw/                 # local raw NHS ZIP files, ignored by Git
  sample_data/              # small committed sample files
  sql/00_setup/             # schemas/extensions
  sql/01_staging/           # raw file landing tables
  sql/02_cleaning/          # typed cleaned layer
  sql/03_dimensions/        # dimension tables
  sql/04_facts/             # optimised fact load/finalisation scripts
  sql/05_quality/           # validation and reconciliation checks
  sql/06_marts/             # analytical marts
  sql/07_analysis/          # final analysis queries
  scripts/                  # loaders, pipeline and output export helpers
  outputs/validation/       # versioned validation evidence
  outputs/analysis_tables/  # versioned analysis evidence
  visualizations/           # generated SVG charts from committed outputs
  docs/                     # methodology, metrics, caveats and interview notes

Data layout

Full raw data is local-only and ignored by Git:

data/raw/EPD_SNOMED_202501.ZIP
data/raw/EPD_SNOMED_202502.ZIP
data/raw/EPD_SNOMED_202503.ZIP

The loader streams ZIP contents directly and does not require extracting multi-GB CSV files into the repository.

Small review samples live in sample_data/ and may be committed.

Reproduce the current workflow

Default local connection:

export PGHOST=127.0.0.1
export PGPORT=5434
export PGDATABASE=nhs_prescribing_warehouse
export PGUSER=postgres
export PGPASSWORD=postgres

Load raw ZIPs into staging:

python3 scripts/load_raw_prescribing.py --months 2025-01 2025-02 2025-03

Run the full modelling pipeline from existing staged data:

./scripts/run_pipeline.sh

If the fact table is already validated and only marts/outputs need refreshing:

./scripts/run_marts.sh
./scripts/run_validation_outputs.sh
./scripts/run_analysis.sh
python3 scripts/generate_visualizations.py

Main evidence outputs

Validation:

• warehouse_row_counts.txt
• source_coverage.txt
• cleaning_quality_checks.txt
• fact_reconciliation.txt
• mart_reconciliation.txt
• fact_null_summary.txt

Analysis:

• 001_monthly_cost_trend.txt
• 002_top_medicines_by_actual_cost.txt
• 003_icb_cost_variation.txt
• 004_medicine_month_on_month_change.txt
• 005_high_cost_low_volume_items.txt
• 006_high_volume_cost_drivers.txt
• 007_net_ingredient_vs_actual_cost.txt
• 008_bnf_chapter_cost_profile.txt
• 009_icb_medicine_outlier_signals.txt
• 010_cost_review_priority_list.txt

Limitations

• The v1 analytical window is only three months; it should not be used to infer long-term seasonality.
• ICB comparisons are not adjusted for population, age profile, deprivation, disease burden or service configuration.
• Some source cost fields are null and are reported in quality outputs rather than hidden.
• BNF chapter labels are incomplete for some products based on the available source/dimension mapping.
• The project is local PostgreSQL analytics, not a production deployment.

Next improvements

1. Extend from three months to the full 2025 calendar year after v1 is fully documented.
2. Improve BNF chapter labelling with an approved reference source.
3. Add a safer backup/rename swap for fact finalisation before replacing the current final table.