Yorkshire Vitality Observatory Data Pipeline

An automated end-to-end Prefect v3 ETL pipeline that ingests, transforms, and warehouses socioeconomic, health, and environmental indicators for Yorkshire into PostgreSQL, powering the Yorkshire Vitality Suite dashboards.

---

Documentation

Full documentation is at yhoda-project.github.io/yhoda-pipeline, including:

• Onboarding - get set up on the pipeline
• Architecture - how the system works
• Runbooks - what to do when something goes wrong
• Reference - environment variables, ERD, glossary

---

Overview

flowchart LR
    A[Live APIs] --> C[Extract]
    B[Static Releases] --> C
    C --> D[Transform & Validate]
    D --> E[(PostgreSQL)]
    E --> F[Power BI\nDashboards]

Loading

---

Quickstart (for developers on the VM)

Prerequisites

• Python 3.11+
• uv package manager
• PostgreSQL 14+ with the target database created
• Self-hosted Prefect v3 server

Install

git clone https://github.com/yhoda-project/yhoda-pipeline.git
cd yhoda-pipeline
uv sync --extra dev
cp .env.example .env  # then fill in DATABASE_URL and DWP_API_KEY at minimum

Initialise the database

uv run alembic upgrade head
uv run python -m yhovi_pipeline.utils.seed_geo_lookup
uv run python -m yhovi_pipeline.utils.load_csv
uv run python -m yhovi_pipeline.utils.load_jobs
uv run python -m yhovi_pipeline.utils.load_industry
uv run python -m yhovi_pipeline.utils.load_neighbourhoods
uv run python -m yhovi_pipeline.utils.compute_correlations

Register deployments and verify

uv run prefect deploy --all --no-prompt
uv run python -c "from yhovi_pipeline.config import get_settings; print('ok')"
uv run pytest
uv run ruff check src/ tests/

---

Development

uv run ruff check --fix src/ tests/   # lint
uv run ruff format src/ tests/        # format
uv run mypy src/                      # type check
uv run pytest --cov=src/yhovi_pipeline  # tests with coverage

Unit tests require env vars but no real database:

export DATABASE_URL="postgresql+psycopg2://t:t@localhost/d"
export DWP_API_KEY="x"
uv run pytest tests/unit/

Pre-commit hooks (ruff, mypy, file hygiene) run automatically on every commit. Install them once with:

uv run pre-commit install

CI runs on every push and pull request. Deployments to Prefect are triggered automatically on merge to main.

---

Project Structure

src/yhovi_pipeline/
├── config.py          # pydantic-settings; always use get_settings()
├── db/                # SQLAlchemy models + Alembic migrations
├── flows/             # 15 Prefect flows across economy/, society/, environment/
├── tasks/
│   ├── extract/       # one module per source (nomis, fingertips, dwp, ons, ...)
│   ├── transform/     # validate, normalise, geo
│   └── load/          # upsert_indicators, write_metadata
└── utils/             # CSV loaders, geo lookup, email alerts

---

Licence

See LICENSE.