holovata/africa-languages-analytics-pipeline

An end-to-end data engineering and business intelligence project analyzing the linguistic diversity of the African continent (covering over 2,000 languages). # 🌍 African Languages: Data Engineering & Analytics Pipeline ## 📌 Project Overview An end-to-end data engineering and business intelligence project analyzing the linguistic diversity of the African continent (covering over 2,000 languages). **Inspiration:** The core idea for this analysis was inspired by the TidyTuesday project (2026-01-13 dataset). However, instead of using the provided clean datasets, I built a custom ETL/ELT pipeline from scratch to extract the data directly from Wikipedia and Worldometers, process it via Python, load it into a local PostgreSQL instance, and serve it through an interactive dashboard. ## 📊 Dashboard Preview ## 🏗️ Architecture & Workflow ### 1. Extract (`src/extract.py`) * Scrapes unstructured HTML tables from Wikipedia and external linguistic sources. * Implements custom headers to respect robots policies. * Saves raw data locally to `data/raw_languages.csv` to avoid redundant API/web calls. ### 2. Transform (`src/transform.py`) * Cleans the raw text data using Pandas and Regular Expressions (RegEx). * Normalizes language families and extracts clean integer values for native speakers. * Explodes grouped countries into individual rows to prepare for relational modeling. * Saves the processed data to `data/clean_languages.csv`. ### 3. Load (`src/load.py` & `sql/init.sql`) * Spins up a PostgreSQL 16 database using **Docker**. * securely loads the cleaned CSV data into staging tables using `SQLAlchemy` and environment variables. * Distributes the staging data into a normalized Star/Snowflake schema (handling the Many-to-Many relationship between Languages and Countries). ### 4. Data Marts (`src/create_views.py`) * Executes complex SQL `VIEW`s to optimize Power BI performance: * Uses Window Functions (`RANK()`) for country stats. * Uses `STRING_AGG()` and `HAVING COUNT > 1` to identify cross-border languages. * Creates a flattened view designed specifically to prevent data duplication (fan-out) in BI hierarchical visuals. …