fredrickbernardonduru/kenya-food-prices-pipeline

# Kenya Food Prices — Production-Ready ETL Pipeline **Project title:** Cleaned & Enriched Food Prices with Visualization Prep & Production-Ready ETL Pipeline **Dataset:** WFP Kenya Food Prices (~17,000 rows, 2006–2025, monthly) --- ## Architecture ``` CSV / URL │ ▼ extract.py ──► clean.py ──► quality checks ──► load.py (psycopg2) │ PostgreSQL ┌────────────────┐ │ raw_food_prices│ │ dim_market │ │ dim_commodity │ │ dim_date │ │ fact_prices │ └────────────────┘ │ dbt Core (stg → marts → agg) │ Grafana / Metabase ``` All orchestration runs inside Docker via **Apache Airflow 2.9.0**. --- ## Project Structure ``` kenya-food-prices-pipeline/ ├── airflow/dags/ │ └── food_prices_dag.py # 4-task DAG: extract→clean→load→verify ├── etl/ │ ├── extract.py # Load CSV from disk (or URL) │ ├── clean.py # Pandas cleaning + price_per_kg derivation │ ├── pipeline.py # Orchestrator with QC + incremental logic │ └── load.py # psycopg2 bulk insert (bypasses to_sql) ├── sql/ │ ├── 02_create_raw_table.sql # Raw staging table DDL │ ├── 03_create_clean_tables.sql # Star schema DDL │ └── analysis_queries.sql # 8 analytical SQL queries ├── dbt/ │ ├── models/staging/ # stg_food_prices (view) │ └── models/marts/ # dim_*, fact_prices, agg_monthly_prices ├── docker/ │ ├── docker-compose.yml │ └── Dockerfile.airflow └── data/ └── sample_food_prices.csv ``` --- ## Quick Start ```bash cd docker docker compose build --no-cache docker compose up -d # Open localhost (admin / admin) # Trigger: kenya_food_prices_pipeline ``` To build the star schema after the ETL has run: ```bash docker exec -it kenya_db psql -U postgres -d kenya_food_prices \ -f /sql/03_create_clean_tables.sql ``` To run dbt (install dbt-postgres locally first): ```bash cd dbt dbt run dbt test ``` --- ## Data Issues Observed 1. **Missing geographic data** — ~3% of rows have nul …