Unified Bharat: Cross-Sector Policy Analytics Lakehouse
Distributed Medallion Lakehouse integrating cross-ministry CSR, groundwater, and education datasets using Apache Spark and Iceberg for panel regression analysis of Indian state-level policy outcomes.
Problem
Indian state policy outcomes are scattered across ministry silos: CSR spending lives in Ministry of Corporate Affairs filings, groundwater quality in Jal Shakti station measurements, and educational capacity in Ministry of Education data. Each dataset uses different identifiers, granularities, and quality standards, so asking whether state-level CSR spending associates with groundwater outcomes requires assembling and reconciling them by hand.
Approach
Ingested five government sources — CSR spending (28,834 rows), groundwater quality (188,209 rows), educational institutions (2,141 rows), LGD master codes, and population estimates — into a raw Bronze layer.
Applied a Medallion architecture on Apache Iceberg (Bronze → Silver → Gold) with PySpark on Docker, aggregating to state-year granularity and filtering for quality.
Assembled a unified panel of ~300 rows with derived features (lagged CSR spending, per-capita normalizations, a 0–4 contamination index) for two-way fixed-effects regression.
Benchmarked OLS against Random Forest and XGBoost with 5-fold cross-validation instead of trusting a single model family.
Outcome
99.88%
Data reduction
245K+ raw rows distilled to ~300 unified panel rows through intentional aggregation and quality filtering
RMSE 0.811
Best model
Random Forest beat OLS and XGBoost on 5-fold CV — evidence of non-linear CSR–environment relationships
51%
Top predictor
monitoring-station count, with lagged CSR spending at 21% and institutional capacity at 18.8%
Pipeline
Evaluation
Random Forest's win over OLS (RMSE 0.811 on 5-fold CV) is the key finding: it suggests the relationship between CSR spending and environmental outcomes is non-linear, which a textbook fixed-effects specification would miss. Feature importance is best read as correlation given the observational design — monitoring-station count (51%) likely proxies enforcement intensity as much as monitoring itself.
What I'd Do Differently
The lakehouse was over-engineered for the final dataset — 99.88% reduction means most of the infrastructure existed to shrink a problem that a few well-written pandas pipelines could have handled. The Iceberg layer did pay off for reproducibility, but I would right-size the stack to the analysis earlier. I would also add explicit causal framing from day one: the panel answers association questions cleanly, but difference-in-differences would have taken the policy claim further.
Tech Stack
Links
Building something similar?
Let's talkRelated Projects
Enterprise-grade automated threat intelligence platform combining a FastText NLP classifier, structured ML models, and algorithmic distance rules to identify deceptive URLs and brand spoofing for client security teams at NetSTAR Global.
End-to-end ELT streaming pipeline that ingests real-time Twitch viewership data through Apache Kafka, warehouses in Snowflake, and transforms with dbt for Looker Studio dashboards.
Longitudinal analysis of IPUMS USA microdata to understand how wage trends influence shifts in U.S. college major popularity from 2009–2023.