Meshack132/sa-worldbank-pipeline

End-to-end ETL pipeline extracting live World Bank economic data for South Africa, validated, transformed with Python/SQL, loaded into BigQuery, orchestrated with Apache Airflow # 🇿🇦 South Africa World Bank Data Pipeline An end-to-end data pipeline that extracts real South African economic indicators from the **World Bank's public API**, transforms them with Python and SQL, loads them into a **Postgres warehouse**, and schedules the whole thing with **Apache Airflow**. ## Why this exists Most portfolio ETL projects either hardcode a static CSV or fabricate sample data. This pipeline hits a **live, public, no-auth-required API** every run — the extract step is a real integration, not a canned demo, and the numbers you see in the warehouse are genuinely current World Bank data for South Africa. ## Architecture ``` World Bank API (live, public, no auth) | Python: requests + pagination handling v extract() -> raw per-indicator JSON records | Python: pandas -- reshape nested JSON to tidy long format v transform() -> tidy DataFrame (country, indicator, year, value) | Load via SQLAlchemy v raw.indicators (Postgres warehouse, long format) | SQL: pivot + window functions (LAG for YoY growth) v analytics.yearly_summary (Postgres warehouse, wide format, ready for BI) ``` Orchestrated by an **Airflow DAG** (`dags/sa_worldbank_pipeline_dag.py`) using the modern TaskFlow API, scheduled weekly — World Bank development indicators update a few times a year at most, so daily runs would just repeat unchanged extracts. ## Indicators pulled | Code | Description | |---|---| | `NY.GDP.MKTP.CD` | GDP (current US$) | | `SP.POP.TOTL` | Population, total | | `SL.UEM.TOTL.ZS` | Unemployment (% of labor force) | | `FP.CPI.TOTL.ZG` | Inflation, consumer prices (annual %) | | `SP.DYN.LE00.IN` | Life expectancy at birth (years) | ## Why split Python and SQL transforms - **Python (pandas)** handles the irregular, nested JSON the API returns — parsing per-record `indicator.id`, `country.value`, handling null-valued years — the kind of messy, string/dict-heavy reshaping Python is naturally suited for. - **SQL** handles the relational pivot (long to …