MGNREGA Employment Analytics: ETL, Data Warehouse and Dashboard

MGNREGA Employment Analytics is a data-engineering project that turns the Mahatma Gandhi National Rural Employment Guarantee Scheme's public progress reports into a clean, queryable data warehouse and an interactive dashboard. MGNREGA guarantees up to 100 days of paid work per rural household every year, and the Ministry of Rural Development publishes monthly progress data — person-days generated, households demanded and worked, wages paid, works taken up — for every state, district and financial year through its NIC public data portal.

The catch is that the reports are published for humans, not for analysis: one file per state × district × financial-year × indicator combination, with merged headers, Indian digit grouping, - and NA placeholders, lakh/crore units and district names that change over time. Answering a simple question — which districts met less than half of the work demanded last year? — takes days of clicking and pasting into spreadsheets.

This project builds a three-stage pipeline: an ingestion layer that registers report extracts downloaded from the portal; a quality layer whose data-quality gates (schema, type, completeness, referential and outlier checks) quarantine bad rows with reasons; and a MySQL warehouse in a star schema at district-month-indicator grain. A Streamlit dashboard (an industry-standard Python dashboarding extra on top of the VTU-taught Python/MySQL stack) gives state and district drilldowns, and a scikit-learn linear-regression module forecasts next-quarter person-days. Four team members each own one module end to end.

MGNREGA's performance data is public, but it is not usable: it lives in hundreds of separately downloaded portal reports that each need manual cleaning, and every analyst rebuilds that cleaning from scratch in personal spreadsheets. Indian number formats, -/NA placeholders, unit columns and renamed districts make silent errors easy — a wrong sum can turn a performing district into a failing one, and nothing records which rows were altered or dropped.

There is a need for a reproducible data pipeline that ingests the raw report extracts exactly as downloaded, applies explicit and documented data-quality gates before any analysis, keeps rejected rows in a quarantine with reasons, loads a properly modelled warehouse at district-month grain, and answers real questions — demand met, person-day trends, wage outflow, seasonal patterns, next-quarter forecast — through a dashboard any stakeholder can open.

Abstract

This project designs and builds an end-to-end analytics pipeline for MGNREGA, India's largest public works programme. The pipeline extracts progress reports from the Ministry of Rural Development's public data portal, cleans and validates them through a catalogue of data-quality gates, loads a MySQL star-schema warehouse, and presents the results in a Streamlit dashboard with a lightweight forecasting module. The deliverable is a reproducible system: one command refreshes the warehouse from raw files, and a data-quality scorecard reports exactly how much of the source data passed each gate.

Introduction

MGNREGA generates more than 250 crore person-days of employment every year across all states. Researchers, journalists, district officials and students who want to study its performance must work with the NIC public data portal, which lets users generate and download progress reports for any state, district and financial year. The data is complete and authoritative — but it is fragmented into hundreds of downloadable report combinations, and every file needs manual cleaning before it can be compared with another.

Existing System

• Analysts generate reports one state or district at a time and paste them into Excel workbooks.
• Cleaning is repeated by hand for every new financial year; there is no shared, versioned dataset.
• Numbers use mixed formats (12,345, 12,345.67, -, NA), and district renames (e.g. after bifurcation) silently split a district's time series into two.
• Limitations: no single source of truth, no audit trail of what was changed, no record of rows dropped, and dashboards built on one person's spreadsheet cannot be reproduced.

Proposed System

• An ingestion module that treats every downloaded extract as an immutable raw file with metadata (source report, financial year, download date) stored in a file inventory.
• A data-quality gate engine: schema check, type parsing, completeness threshold, referential checks (district must belong to its state) and range/outlier gates (e.g. person-days per household above 365 are impossible). Rows that fail go to a quarantine table with a reason code; a scorecard summarises pass rates per file.
• A MySQL warehouse modelled as a star schema: fact_employment at the district × month × indicator grain, with dim_state, dim_district (with validity dates to handle renames), dim_date and dim_indicator.
• A Streamlit dashboard: state and district drilldowns, demand-met ratio, wage and person-day trends, district league tables, CSV export.
• A forecast module using scikit-learn linear regression on monthly person-days, with train/test split and reported MAE.

Feasibility Study

• Technical: Python, pandas and MySQL are all part of the VTU curriculum (BCS403 DBMS, BCSL606 ML Lab); Streamlit and Plotly are free, documented extras. The bundle ships with downloaded extracts for three financial years, so the demo runs fully offline.
• Economic: ₹0 — open data, open-source stack, a laptop with 8 GB RAM is enough for district-level data.
• Operational: the pipeline is a set of commands documented in the README; refreshing a financial year is a single command, so even a non-programmer can maintain it with the user guide.

In scope

• MGNREGA monthly progress indicators published on the NIC public data portal (person-days, households demanded/worked/completed-100-days, wages, works) for 5 selected states × all their districts × 3 financial years.
• Batch ingestion of locally downloaded extracts (CSV/Excel saved from the portal); no login, no scraping of authenticated pages.
• Data-quality gates, quarantine with reason codes, DQ scorecard report.
• MySQL star schema, idempotent reloads, SQL views for dashboard queries.
• Streamlit dashboard (state/district/trend/league-table views) and a linear-regression forecast with MAE.

Out of scope (v1)

• Work-level (individual muster roll) data and beneficiary-level records — only aggregated monthly indicators.
• Live scheduled crawling of the portal; refreshes run on saved extracts.
• Real-time streaming, geospatial maps beyond simple state outlines, and role-based user management.
• Machine-learning models beyond linear regression (kept deliberately simple so results are explainable in the viva).

The project follows the Incremental SDLC model, mapped onto VTU's two-phase Major Project: Phase-I (BCS685, Sem 6) delivers analysis and design; Phase-II (BCS786, Sem 7) delivers the working, tested system. Each increment ends with something runnable to show the guide.

Testing approach: reconciliation tests that sum warehouse facts and compare against the original portal report totals; unit tests for each gate (bad file in → expected quarantine reason); boundary tests for number parsing; a small UAT script of ten analysis questions the dashboard must answer correctly.

The system is a layered batch-analytics architecture.

1. Ingestion layer — a Python module that reads report extracts saved from the NIC portal, records each file in a raw_files inventory (path, financial year, report type, checksum) and copies it into an immutable raw zone.
2. Quality layer — the DQ gate engine parses files into typed tables, applies the five gates, and routes rows either to the clean zone or to a quarantine table with a reason code; a scorecard view reports pass rates per file and per gate.
3. Data tier — MySQL holds staging, quarantine and the star-schema warehouse; loaders are idempotent (a file re-loaded never duplicates facts).
4. Analytics & presentation layer — SQL views aggregate facts for the Streamlit dashboard (Plotly charts, drilldowns, CSV export) and feed the scikit-learn forecast module.

flowchart TD
  A["MGNREGA public data portal<br/>(dynamic reports, CSV/Excel extracts)"] --> B["Ingestion layer<br/>register + checksum + raw zone"]
  B --> C["Parser + normaliser<br/>Indian numbers, units, month columns"]
  C --> D{"Data-quality gates<br/>schema · type · completeness · referential · outlier"}
  D -->|pass| E["Clean zone"]
  D -->|fail| Q["Quarantine store<br/>row + reason code"]
  E --> F[("MySQL warehouse<br/>star schema")]
  Q --> S["DQ scorecard report"]
  F --> G["SQL views + aggregations"]
  G --> H["Streamlit dashboard<br/>drilldowns, league tables, CSV export"]
  G --> I["Forecast module<br/>linear regression, MAE report"]

Warehouse (star schema)

erDiagram
  DIM_STATE ||--o{ DIM_DISTRICT : contains
  DIM_DISTRICT ||--o{ FACT_EMPLOYMENT : "is measured for"
  DIM_DATE ||--o{ FACT_EMPLOYMENT : "keys"
  DIM_INDICATOR ||--o{ FACT_EMPLOYMENT : "keys"
  RAW_FILE ||--o{ DQ_CHECK_LOG : "is checked by"

  FACT_EMPLOYMENT {
    int sk_district FK
    int sk_date FK
    int sk_indicator FK
    int raw_file_id FK
    decimal value
    string unit
  }
  DIM_DISTRICT {
    int sk_district PK
    string district_name
    int sk_state FK
    date valid_from
    date valid_to
  }
  DIM_INDICATOR {
    int sk_indicator PK
    string indicator_name
    string unit
  }
  DQ_CHECK_LOG {
    int check_id PK
    int raw_file_id FK
    string gate_name
    string status
    string reason_code
  }

The grain of fact_employment is one indicator value for one district in one month of one financial year. Every fact row carries the raw_file_id it came from, so any number on the dashboard can be traced back to the exact downloaded report — the audit trail the spreadsheet workflow never had.

• Scheduling refreshes with a task scheduler or Apache Airflow for multi-year unattended loads.
• Geospatial layer — district choropleth maps of demand-met ratio using GeoJSON boundaries.
• Better forecasts — seasonal (SARIMA) or gradient-boosted models compared against the linear baseline.
• Public deployment — hosting the dashboard so panchayat researchers can use it directly.
• Cross-scheme analytics — joining with PM-Kisan or housing-scheme datasets in the same warehouse to study welfare overlap.