healthcare-governance-pipeline

KO | EN

gitlite — search

#javascript #hacktoberfest #typescript #llm #golang #rust #linux #docker #cli #awesome-list #vue #pytorch #mcp #openai

healthcare-governance-pipeline

(★ 1)

End-to-end healthcare data pipeline: WHO GHO + NDAP India → Bronze→Silver→Gold with dbt, DuckDB, 31 automated tests

healthcare-governance-pipeline 최신버젼 다운로드

최종 버전 다운로드 (.zip)

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# Healthcare Governance Pipeline

### WHO Global Health Observatory × NDAP India — Bronze → Silver → Gold

> **Combining two government health datasets with zero alignment out of the box.**
> Corrupted state names. Mismatched schemas. Different grains. All fixed, tested, and orchestrated into a production-grade dbt + DuckDB pipeline.

---

## 📌 Executive Summary

Government health data is rarely clean enough to analyze immediately. This project ingests raw, messy public health data from two independent government sources, establishes a strict governance layer, normalizes schema inconsistencies, and produces tested analytical **gold-layer models** that answer critical healthcare infrastructure questions:

- **Which Indian states carry the highest disease burden relative to their health infrastructure?**
- **Is disease incidence getting better or worse year-over-year at the state level?**
- **Where is disease burden rising faster than facility capacity?**

---

## 🏗️ Architecture & Data Lineage

```mermaid
graph TB
 subgraph Sources ["Raw Data Sources"]
 WHO[WHO GHO API Global Mortality & Incidence]
 NDAP[NDAP India 36 States & UTs, District Disease Data]
 end

subgraph Bronze ["Bronze Layer (DuckDB / Parquet)"]
        RAW[Raw Source Parquet Files]
    end

subgraph Staging ["Staging Layer (dbt)"]
 S1[stg_ndap_disease State Name Normalization]
 S2[stg_ndap_phc Facility Counts, State & Year Grain]
 S3[stg_who_ghe Global Mortality Indicators]
 end

subgraph Silver ["Silver Layer (dbt Marts)"]
 M1[mart_disease_by_state_year Aggregated to State & Year]
 M2[mart_phc_by_state_year Facility Counts]
 M3[mart_india_health Full Outer Join + Audit Quality Flags]
 end

subgraph Gold ["Gold Layer (dbt Analytics)"]
 G1[gold_state_health_rankings Risk Tiers & Burden Rankings]
 G2[gold_yoy_trends Window Functions & YoY Change]
 end

WHO --> RAW
    NDAP --> RAW
    RAW --> S1
    RAW --> S2
    RAW --> S3
    S1 --> M1
    S2 --> M2
    S3 --> M3
    M1 --> M3
    M2 --> M3
    M3 --> G1
    M3 --> G2

style WHO fill:#0ea5e9,color:#fff,stroke:none
    style NDAP fill:#0ea5e9,color:#fff,stroke:none
    style RAW fill:#334155,color:#e2e8f0,stroke:none
    style S1 fill:#6366f1,color:#fff,stroke:none
    style S2 fill:#6366f1,color:#fff,stroke:none
    style S3 fill:#6366f1,color:#fff,stroke:none
    style M1 fill:#8b5cf6,color:#fff,stroke:none
    style M2 fill:#8b5cf6,color:#fff,stroke:none
    style M3 fill:#a855f7,color:#fff,stroke:none
    style G1 fill:#10b981,color:#fff,stroke:none
    style G2 fill:#10b981,color:#fff,stroke:none
```

### 🕸️ dbt DAG Lineage

![dbt Lineage Graph](https://github.com/user-attachments/assets/a17122f1-19b0-4150-9687-97739c6d3982)

---

## 🛠️ Data Quality & Governance Challenges Solved

| Challenge | Root Cause | Engineering Solution |
|---|---|---|
| 5,496 failing join rows | Corrupted state names — first character truncated at source (e.g. `hhattisgarh` instead of `Chhattisgarh`), affecting all 36 states/UTs | 36-state `CASE` mapping using substring pattern matching in `stg_ndap_disease` |
| Type cast errors | Year stored as free-text string (`"Calendar Year (Jan - Dec), 2024"`) | Extracted 4-digit integer using `regexp_extract(Year, '[0-9]{4}')` |
| Grain mismatch | Disease data recorded at **state + district** grain; PHC facility data recorded at **state + year** grain only, no district column | Aggregated disease data up to state + year in the silver layer so both sources share a joinable grain |
| Missing schema columns | Facility dataset lacked district-level metadata entirely | Standardized staging models to key joins explicitly on `state_year_key` |
| Silent data loss | Unmatched rows dropped under a traditional inner join | Implemented `FULL OUTER JOIN` in `mart_india_health` with explicit `join_quality` tracking (`complete`, `no_phc_data`, `no_disease_data`) |

---

## 📊 Verified Pipeline Metrics

| Metric | Verified Value |
|---|---|
| dbt models | 8 (3 staging, 3 silver, 2 gold) |
| Automated data tests | 31 passing, 0 failing |
| States & union territories covered | 36 total |
| Corrupted state names standardized | 36 mapped |
| Historical range | 8 years |
| Complete joins post-normalization | 141 / 285 rows (49.5%) |
| Rows recovered from join failure | 5,496 rows |
| CI build execution time | ~40 seconds |

*Verified directly against the DuckDB warehouse via `dbt build` + a verification query script — see output below.*

```
===================================
   README VERIFICATION RESULTS
===================================
1. Total distinct states in NDAP: 36
2. Total distinct years: 8
3. Complete Joins: 141 out of 285 (49.5%)
===================================
```

---

## ⚙️ Orchestration & CI/CD Pipeline

### 🕒 Apache Airflow (Docker Compose)

The pipeline is scheduled daily at 06:00 UTC using Apache Airflow. The DAG consists of three chained tasks:

`dbt_run` (BashOperator) ➔ `dbt_test` (BashOperator) ➔ `notify_success` (PythonOperator)

If any data quality test fails, execution halts immediately to keep downstream gold models reliable.

![Airflow DAG](https://github.com/user-attachments/assets/6a23a44f-7f4a-41b2-a1e0-7632e70af367)

### 🚀 GitHub Actions CI/CD

On every pull request or commit to `main`, GitHub Actions spins up an automated runner to execute `dbt build` against DuckDB, running all 31 tests and storing execution summaries as workflow artifacts.

![GitHub Actions CI Run](https://github.com/user-attachments/assets/b72de6d7-df3f-46fa-98a0-8b7130ebfaee)

---

## 💡 Key Analytical Insights (Gold Layer)

**Highest disease burden per health centre (`api_per_phc`):**
- Mizoram led India every year from 2018 to 2021, recording an Annual Parasite Incidence (API) reaching **9.29** across just 65 PHCs.
- Andaman & Nicobar Islands remained in the `CRITICAL` risk tier across 2018 and 2019.

**Fastest worsening YoY trends:**
- Sikkim: **+204%** increase in disease incidence in 2021 while classified `UNDER_RESOURCED`.
- Tripura: **+199.7%** increase in 2021.
- Maharashtra: **+181.8%** rise in 2020.

**Fastest improving states:**
- Chandigarh & Puducherry: **-100%** incidence reduction (zero reported cases) by 2021.
- Uttarakhand: **-97.4%** reduction in 2020.

---

## 🚀 How to Run Locally

**Prerequisites**
- Python 3.10+
- Virtual environment setup (`.venv`)

**Execution steps**

```powershell
# 1. Activate virtual environment
& .venv\Scripts\Activate.ps1

# 2. Navigate to dbt project directory
cd bronze_project

# 3. Build dbt models with local dataset variable
dbt build --vars "{bronze_path: 'C:/path/to/WHO/bronze'}"

# 4. Verify results directly from DuckDB
python ../query_gold.py

# 5. Generate and view dbt documentation & lineage
dbt docs generate
dbt docs serve
```

---

## 📁 Project Structure

```
WHO/
├── bronze/                            # Raw source parquet files
│   ├── Disease_incidence_dataset.parquet
│   ├── PHC_Facilities_dataset.parquet
│   └── who_ghe_full.parquet
├── bronze_project/                    # dbt project root
│   ├── models/
│   │   ├── staging/                   # Raw cleaning & normalization
│   │   │   ├── stg_ndap_disease.sql
│   │   │   ├── stg_ndap_phc.sql
│   │   │   └── stg_who_ghe.sql
│   │   ├── silver/                    # Grain alignment & joining
│   │   │   ├── mart_disease_by_state_year.sql
│   │   │   ├── mart_phc_by_state_year.sql
│   │   │   └── mart_india_health.sql
│   │   ├── gold/                      # Analytics & rankings
│   │   │   ├── gold_state_health_rankings.sql
│   │   │   └── gold_yoy_trends.sql
│   │   └── schema.yml                 # 31 data quality tests
│   ├── dbt_project.yml
│   └── profiles.yml
├── docs/                              # Screenshots & lineage diagrams
│   ├── lineage_graph.png
│   ├── airflow_dag.png
│   └── github_ci.png
├── query_gold.py                      # Verification & analytical querying script
└── README.md                          # Project documentation
```

---

## 💻 Tech Stack & Tools

| Component | Technology |
|---|---|
| Database engine | DuckDB 1.10.1 (columnar, OLAP) |
| Data transformation | dbt-core 1.11.8 & dbt-duckdb |
| Orchestration | Apache Airflow 2.9.1 (Docker Compose) |
| Continuous integration | GitHub Actions |
| Languages | SQL, Python 3.10, YAML, PowerShell |

---

## 📚 Data Sources

- **WHO Global Health Observatory (GHO):** https://www.who.int/data/gho
- **NDAP India (National Data & Analytics Platform — NITI Aayog):** https://ndap.niti.gov.in

---

## 👤 Author

**Farheen Sultana**
Analytics Engineer · Data Modeler · Data Engineer · Hyderabad, India

Building scalable, testable, and governed data pipelines.

healthcare-governance-pipeline

healthcare-governance-pipeline 최신버젼 다운로드

오늘의 추천 프로젝트

Was this content helpful?

Recent Feedback

healthcare-governance-pipeline

healthcare-governance-pipeline 최신버젼 다운로드

오늘의 추천 프로젝트

Was this content helpful?

Recent Feedback

Download README