TG-Mandla/End-to-End-Project

An automated, zero-cost ETL data pipeline and 4-page interactive Looker Studio framework for tracking STEM mentorship impact # End-to-End STEM Mentorship Data Pipeline & Analytics Framework ## 📌 Project Overview 👉 **Click here to explore the Live Interactive Looker Studio Dashboard!** This project establishes a low-cost, production-grade Data Engineering and Business Intelligence pipeline for a regional STEM Mentorship Program in South Africa. Facing realistic budget constraints, I bypassed expensive cloud warehouse tools in favor of an optimized Google Workspace & Cloud ecosystem. The pipeline automates the extraction of raw session logs, programmatically injects localized demographic data, cleans critical formatting anomalies via Python, and serves a live, interactive 4-page dashboard to departmental stakeholders. ### Key Impact Highlights: * **Zero-Cost Infrastructure:** Built entirely using free-tier cloud resources, proving scalability under tight budget constraints. * **Algorithmic Data Enrichment:** Programmatically injected localized geographical and institutional profiles (Pretoria, Joburg, WITS, UP, etc.) to evaluate regional D&I impact. * **Stakeholder-Driven Design:** Delivered custom views tailored specifically for Operations, Curriculum, Finance, and Outreach executives. --- ## 🛠️ System Architecture The pipeline follows a classic ETL (Extract, Transform, Load) pattern: 1. **Ingestion (Data Lake/Staging):** Raw CSV data is ingested into a non-converting staging layer in Google Sheets (`Raw_Data`) to preserve formatting anomalies. 2. **Processing & Transformation (ETL Engine):** A Python pipeline built in Google Colab handles secure OAuth2 authentication, reads the staging layer into Pandas, and applies critical business logic. 3. **Data Warehousing (Production):** Cleaned, standardized data is programmatically written to the production database layer (`Clean_Set`). 4. **Analytics & Visualization (BI Layer):** Looker Studio consumes the production layer, offering cross-filtering capabilities for end-users. --- ## 🐍 Data Transformation & Business Logic (Python) The ET …