# susai23/lincolnshire-adult-social-care-pipeline architecture

> Architecture diagram of the GitHub repository susai23/lincolnshire-adult-social-care-pipeline, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-09-30). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/susai23/lincolnshire-adult-social-care-pipeline
- Repository: https://github.com/susai23/lincolnshire-adult-social-care-pipeline

## Overview

This repository implements a self-serve Lincolnshire Adult Social Care intelligence report: public population, outcomes and provider-quality data are cleaned, joined and aggregated, then presented in Power BI. The README documents the pipeline and report capabilities; no source excerpts were provided, so relationships below are limited to those documented there.

Main entry points: [`SQL/02_build_dashboard_tables.sql`](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/SQL/02_build_dashboard_tables.sql) (Build dashboard tables), [`Python/Lincolnshire Adult Social Care Data Pipeline.ipynb`](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Python/Lincolnshire%20Adult%20Social%20Care%20Data%20Pipeline.ipynb) (Cleaning notebook), [`Power BI/Lincolnshire_Adult_Social_Care_Intelligence.pbix`](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Power%20BI/Lincolnshire_Adult_Social_Care_Intelligence.pbix) (Interactive report), [`Cleaned data`](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/tree/main/Cleaned%20data) (Tidy datasets)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_sources["Source data"]
  node_ascof["ASCOF outcomes<br/>[ascof_2024_25.ods]"]
  node_ons_current["Population estimates"]
  node_ons_projection["Population projections"]
  node_cqc["CQC directory"]
end

subgraph group_prep["Data preparation"]
  node_pipeline["Cleaning notebook"]
  node_cleaned["Tidy datasets"]
end

subgraph group_sql["SQL aggregation"]
  node_create_tables["Create tables"]
  node_build_tables["Build dashboard tables"]
  node_demand["Demand dashboard data"]
  node_outcomes["Outcomes dashboard data"]
  node_providers["Provider dashboard data"]
  node_provider_summary["Provider summary data"]
end

subgraph group_report["Power BI report"]
  node_report_file["Interactive report"]
end

node_user(("Report user"))

node_ascof -->|"reads"| node_pipeline
node_ons_current -->|"reads"| node_pipeline
node_ons_projection -->|"reads"| node_pipeline
node_cqc -->|"reads"| node_pipeline
node_pipeline -->|"writes tidy data"| node_cleaned
node_cleaned -->|"loads cleaned CSVs"| node_create_tables
node_create_tables -->|"creates tables"| node_build_tables
node_build_tables -->|"builds dashboard data"| node_demand
node_build_tables -->|"builds dashboard data"| node_outcomes
node_build_tables -->|"builds dashboard data"| node_providers
node_build_tables -->|"builds dashboard data"| node_provider_summary
node_demand -->|"imports"| node_report_file
node_outcomes -->|"imports"| node_report_file
node_providers -->|"imports"| node_report_file
node_provider_summary -->|"imports"| node_report_file
node_user -->|"views report"| node_report_file

click node_ascof "https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Raw%20data/ascof_2024_25.ods"
click node_ons_current "https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Raw%20data/ons_population_2024.csv"
click node_ons_projection "https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Raw%20data/ons_population_projections.xls"
click node_cqc "https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Raw%20data/cqc_lincolnshire_prefiltered.csv"
click node_pipeline "https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Python/Lincolnshire%20Adult%20Social%20Care%20Data%20Pipeline.ipynb"
click node_cleaned "https://github.com/susai23/lincolnshire-adult-social-care-pipeline/tree/main/Cleaned%20data"
click node_create_tables "https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/SQL/01_create_tables.sql"
click node_build_tables "https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/SQL/02_build_dashboard_tables.sql"
click node_demand "https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Cleaned%20data/dashboard_demand.csv"
click node_outcomes "https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Cleaned%20data/dashboard_outcomes.csv"
click node_providers "https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Cleaned%20data/dashboard_providers.csv"
click node_provider_summary "https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Cleaned%20data/dashboard_providers_summary.csv"
click node_report_file "https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Power%20BI/Lincolnshire_Adult_Social_Care_Intelligence.pbix"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_ascof,node_ons_current,node_ons_projection,node_cqc,node_user toneBlue
class node_pipeline,node_cleaned toneAmber
class node_create_tables,node_build_tables,node_demand,node_outcomes,node_providers,node_provider_summary toneMint
class node_report_file toneRose
```

## Components

### Source data

- [ASCOF outcomes](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Raw%20data/ascof_2024_25.ods): `Raw data/ascof_2024_25.ods`
- [Population estimates](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Raw%20data/ons_population_2024.csv): `Raw data/ons_population_2024.csv`
- [Population projections](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Raw%20data/ons_population_projections.xls): `Raw data/ons_population_projections.xls`
- [CQC directory](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Raw%20data/cqc_lincolnshire_prefiltered.csv): `Raw data/cqc_lincolnshire_prefiltered.csv`

### Data preparation

- [Cleaning notebook](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Python/Lincolnshire%20Adult%20Social%20Care%20Data%20Pipeline.ipynb): `Python/Lincolnshire Adult Social Care Data Pipeline.ipynb`
- [Tidy datasets](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/tree/main/Cleaned%20data): `Cleaned data`

### SQL aggregation

- [Create tables](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/SQL/01_create_tables.sql): `SQL/01_create_tables.sql`
- [Build dashboard tables](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/SQL/02_build_dashboard_tables.sql): `SQL/02_build_dashboard_tables.sql`
- [Demand dashboard data](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Cleaned%20data/dashboard_demand.csv): `Cleaned data/dashboard_demand.csv`
- [Outcomes dashboard data](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Cleaned%20data/dashboard_outcomes.csv): `Cleaned data/dashboard_outcomes.csv`
- [Provider dashboard data](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Cleaned%20data/dashboard_providers.csv): `Cleaned data/dashboard_providers.csv`
- [Provider summary data](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Cleaned%20data/dashboard_providers_summary.csv): `Cleaned data/dashboard_providers_summary.csv`

### Power BI report

- [Interactive report](https://github.com/susai23/lincolnshire-adult-social-care-pipeline/blob/main/Power%20BI/Lincolnshire_Adult_Social_Care_Intelligence.pbix): `Power BI/Lincolnshire_Adult_Social_Care_Intelligence.pbix`

### Other components

- **Report user**

## Connections

- ASCOF outcomes → Cleaning notebook: reads (evidence: `README.md`)
- Population estimates → Cleaning notebook: reads (evidence: `README.md`)
- Population projections → Cleaning notebook: reads (evidence: `README.md`)
- CQC directory → Cleaning notebook: reads (evidence: `README.md`)
- Cleaning notebook → Tidy datasets: writes tidy data (evidence: `README.md`)
- Tidy datasets → Create tables: loads cleaned CSVs (evidence: `README.md`)
- Create tables → Build dashboard tables: creates tables (evidence: `README.md`)
- Build dashboard tables → Demand dashboard data: builds dashboard data (evidence: `README.md`)
- Build dashboard tables → Outcomes dashboard data: builds dashboard data (evidence: `README.md`)
- Build dashboard tables → Provider dashboard data: builds dashboard data (evidence: `README.md`)
- Build dashboard tables → Provider summary data: builds dashboard data (evidence: `README.md`)
- Demand dashboard data → Interactive report: imports (evidence: `README.md`)
- Outcomes dashboard data → Interactive report: imports (evidence: `README.md`)
- Provider dashboard data → Interactive report: imports (evidence: `README.md`)
- Provider summary data → Interactive report: imports (evidence: `README.md`)
- Report user → Interactive report: views report (evidence: `README.md`)
