NovaHB/Sub-Saharan-Healthcare-to-Facilities

An end-to-end, local ELT pipeline built with DuckDB and Python that ingests, cleans, and joins 98,623 health facility records across 50 Sub-Saharan African countries with World Bank 2024 population figures. # Sub-Saharan Africa Healthcare Infrastructure Analytics Pipeline An end-to-end, local ELT pipeline built with DuckDB and Python that ingests, cleans, and joins **98,623 health facility records** across 50 Sub-Saharan African countries with **World Bank 2024 population figures**. The pipeline produces per-capita healthcare infrastructure density indicators (`facilities_per_100k` residents) to identify regional coverage gaps and inform resource distribution. --- ## Architecture & Data Flow The project adheres to a **Medallion Architecture** pattern using DuckDB as the in-memory analytical query engine and Parquet as the primary storage format. | Layer | File / Artifact | Engine / Format | Description | | :--- | :--- | :--- | :--- | | **Bronze** | `country_population.csv` `health_facilities_cleaned.parquet` | Raw Ingestion | Raw World Bank WDI population indicator export and standardized health facility location points. | | **Silver** | `population_2024.parquet` | DuckDB SQL | Normalizes country name variants, maps *Zanzibar* to Tanzania (`TZA`), and applies multi-year backfills for missing data (e.g., *Somalia*). | | **Gold** | `country_facility_metrics.parquet` `country_facility_metrics.csv` | DuckDB Analytics | Joins facility counts with population baselines to calculate total facilities, primary care counts, hospital counts, and normalized `facilities_per_100k` density ratios. | --- ## Key Metrics & Gold Schema ```sql CREATE TABLE country_facility_metrics ( country VARCHAR, country_code VARCHAR, population_2024 BIGINT, total_facilities BIGINT, hospital_count BIGINT, primary_care_count BIGINT, facilities_per_100k DOUBLE ); ``` --- ## Data Architecture --- ## Key Regional Takeaways - Primary Care Backbone: 92.7% (91,389) of all logged facilities across the region operate at the primary care level. - Regional Baseline: The weighted infrastructure density across 1.35B residents sits at 7.25 facilities per 100,000 people. - Per-Capita Leaders: Botswana …