Njokimary/kenya-food-prices-pipeline

End-to-end data pipeline for Kenyan food prices - PostgreSQL, Airflow, dbt, Metabase # Kenya Food Prices Pipeline End-to-end Medallion-style data pipeline for Kenyan food prices (WFP dataset, 2006–2025). **Stack:** PostgreSQL · Python (pandas, SQLAlchemy) · Apache Airflow · dbt Core · Metabase/Grafana ## Phase 1 — Bronze / Staging Layer - `sql/01_create_staging.sql` — staging table `raw_food_prices` with a 6-column natural-key unique index. Prices use `NUMERIC`, never `FLOAT`, to avoid precision drift in financial aggregation. - `src/load_raw.py` — incremental loader (pandas + SQLAlchemy). Uses `ON CONFLICT DO NOTHING` against the natural key, so re-runs and monthly re-downloads insert only the delta. Verified: run 1 inserts 26,745 rows; run 2 inserts 0. - `sql/02_validation_queries.sql` — required analytical checks (filtering, aggregation, quality density, temporal extraction). ## Data Quality Issues Observed (Bronze) **1. Missing geographic metadata.** 68 rows have NULL `admin1`, `admin2`, `latitude`, and `longitude` — all for the market "Hola (Tana River)". The county is recoverable from the market name; the Silver layer will backfill it. **2. Mixed units make raw prices non-comparable.** The same commodity is quoted in KG, 90 KG, 50 KG, 64 KG, and 126 KG bags. Averaging raw `price` across units is meaningless — the Silver layer must derive `price_per_kg`. **3. Inconsistent commodity granularity.** Maize appears as five variants ("Maize", "Maize (white)", "Maize (white, dry)", "Maize flour", "Maize flour (white)"). Trend analysis needs a standardised commodity dimension. **4. Mixed observation types in one column.** `priceflag` holds "actual" (17,861), "aggregate" (8,651), and the combined value "actual,aggregate" (233). Aggregates must not blend with actual observations in analytics. **5. Dates are monthly snapshots disguised as days.** Every record is stamped on the 15th of the month. The data is monthly, not daily — `dim_date` will model it at month grain. ## Setup ``` python3 -m venv venv && source venv/bin/activate pip install pan …