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.
Architecture
Bronze Layer
Raw data ingestion from 5 government sources: CSR spending, groundwater quality, educational institutions, LGD master codes, and population estimates. 245K+ rows preserved in original format.
Silver Layer
Data cleaning, standardization, and aggregation. State-year level consolidation with quality filtering, reducing to ~1,500 rows across 4 tables.
Gold Layer
Unified panel dataset with ~300 rows combining CSR, groundwater, and institutional metrics. Includes derived features like lagged CSR and per-capita normalizations.
Data Sources
CSR Spending (28,834 rows)
Ministry of Corporate Affairs data on Corporate Social Responsibility expenditure at district-year granularity. Tracks INR Crores and development sectors.
Groundwater Quality (188,209 rows)
Ministry of Jal Shakti station-level measurements including chemical parameters: Hardness, pH, Nitrate, Fluoride. Used to calculate contamination index (0-4 scale).
Educational Institutions (2,141 rows)
Ministry of Education data on approved intake and institution counts by state and institution type, tracking educational capacity over time.
Key Results
Data Reduction: 99.88%
From 245K+ raw rows to ~300 unified panel rows through intentional aggregation and quality filtering, creating a clean dataset for rigorous analysis.
Random Forest Outperforms OLS
Panel regression comparing OLS, Random Forest, and XGBoost models. Random Forest achieved best generalization (RMSE 0.811 on 5-fold CV), suggesting non-linear relationships between CSR and environmental outcomes.
Feature Importance Insights
Number of monitoring stations (51%) and lagged CSR spending (21%) emerged as top predictors of contamination index, with institutional capacity contributing 18.8%.
Research Question
“Does subsequent improvement in groundwater quality associate with state-level CSR spending?”
Using two-way fixed effects regression with state and year fixed effects to control for time-invariant state characteristics and common temporal trends.
Technical Implementation
Distributed Processing with Apache Spark
PySpark for distributed data transformation across Bronze → Silver → Gold layers, handling 245K+ rows efficiently with partitioned operations.
Lakehouse with Apache Iceberg
ACID transactions, schema evolution, and time travel capabilities on MinIO S3 storage. Enables reproducible analytics and audit trails.
Docker Compose Infrastructure
Local development environment with MinIO (S3-compatible storage) and Spark + Jupyter for interactive development and visualization.
Future Enhancements
Additional Data Sources
Integrate health outcomes, infrastructure development, and economic indicators to build a more comprehensive policy impact model.
Causal Inference Methods
Apply difference-in-differences and instrumental variables approaches to strengthen causal claims beyond correlation.
Related Projects
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.
Research project using Synthetic Control Method to analyze the causal impact of World Trade Organization accession on national economies.