A Business Intelligence pipeline connecting academic data with real labor market demand to measure and reduce the digital skills gap in Bolivian technical higher education.
📄 Read this in: English | Español
Academic Project
Universidad Privada Domingo Savio — Computer Systems Engineering
Course: Business Intelligence — 2026
- Live Demo
- Purpose & Results
- What It Does
- Dashboard Pages
- Architecture
- Tech Stack
- Data Pipeline
- Database ER Diagrams
- Snowflake Schema
- Team
- Installation
- Environment Variables
- Streamlit Cloud Deployment
Goal: Build a BI system that makes the digital skills gap in Bolivian IT education visible and measurable — giving academic directors an evidence-based tool to align curricula with real labor market demand, directly contributing to ODS 4 (Quality Education) and ODS 8 (Decent Work).
Was it achieved?
| Objective | Result |
|---|---|
| End-to-end data pipeline (Bronze → Silver → Gold) | Fully implemented and operational |
| 4+ KPIs calculated from real data | 9 KPIs across 4 dashboard pages |
| Skill gap identified between academia and market | Gap measured across all 5 IT careers with fuzzy matching |
| Regional benchmark against Latin America | 17 countries via CEPALSTAT ODS 4.4.1 |
| AI assistant for natural language queries | Live via Groq API (LLaMA 3.1 8B) |
| Public deployment accessible to evaluators | Deployed at brecha-digital-bolivia-bi.streamlit.app |
Concrete finding: The skill gap analysis — using real job postings extracted by LLM — shows that the most demanded technical skills (Docker, cloud platforms, modern CI/CD frameworks) have significantly low academic coverage across all 5 IT programs, confirming the hypothesis that the digital gap is real and measurable. This is exactly the kind of evidence that can drive curriculum reform.
brecha-digital-bolivia-bi.streamlit.app
The application is deployed on Streamlit Cloud and connects live to the Groq API for the AI assistant and uses pre-processed CSV files for academic and labor market data.
Bolivian technical education institutions generate large amounts of academic data but lack the tools to connect it with real labor market demand. This project bridges that gap.
Before: Decision-making based on intuition, fragmented data silos, no visibility into graduate employability.
After: A unified BI pipeline that integrates internal academic records with external labor market data, exposing actionable KPIs through an interactive dashboard — accessible to any academic director without technical knowledge.
Core capabilities:
- End-to-end ELT pipeline: SQL Server (Bronze) → Python transformation (Silver) → Snowflake schema warehouse (Gold)
- IT-focused analysis: Exclusively covers 5 programs — Software Engineering, Systems Engineering, Data Science, Telecommunications, and Cybersecurity
- Labor market intelligence: Real job vacancy data from Adzuna API (US, Spain, Mexico, Brazil) with AI-powered skill extraction via Groq LLM
- KPI monitoring: Graduate employability rate, dropout risk, skill gap analysis, salary benchmarking
- Regional benchmark: CEPALSTAT ODS 4.4.1 indicator — digital skills competency across 17 Latin American countries
- AI assistant: Natural language queries over the full dataset, powered by LLaMA 3.1 (Groq API)
| Page | URL | Description |
|---|---|---|
| Overview | / |
Hero KPIs, navigation, data sources |
| KPIs | /kpis |
Employment rate, dropout risk gauge, CEPALSTAT regional benchmark |
| Labor Insertion | /insercion |
Insertion rate by career, salary distribution, temporal trends, city heatmap |
| Skill Gap | /skill_gap |
Market skills vs academic curriculum, coverage analysis, LLM-extracted skills |
| AI Assistant | /chatbot |
Natural language BI queries — LLaMA 3.1 8B via Groq API |
SQL Server BrechaDigitalDB CEPALSTAT API Adzuna API
[Bronze — Source] [Macro Data] [Employment Data]
│ │ │
└────────────────────────┴────────────────────┘
│
src/ingestion/ (Python)
sqlserver.py · cepalstat.py · empleos.py
│
data/raw/
[Bronze — CSV]
│
src/transform/ (Python)
clean.py · normalize.py
│
data/processed/
[Silver — CSV]
│
┌────────────┴────────────┐
│ │
src/ingestion/skill_extraction.py src/schema/ (Python)
Groq LLM → skills_extracted.csv facts.py · dimensions.py
│ │
└────────────┬────────────┘
│
SQL Server DW_BrechaDigital
[Gold — Snowflake Schema T-SQL]
Fact_InsercionLaboral · DIM_* tables
│
src/dashboard/ (Streamlit)
KPIs · Labor Insertion · Skill Gap · AI Chatbot
| Category | Technology | Version |
|---|---|---|
| Language | Python | 3.11+ |
| Data Manipulation | Pandas | 2.0+ |
| Bronze Database | SQL Server (T-SQL) — BrechaDigitalDB |
2019+ |
| Gold Warehouse | SQL Server (T-SQL) — DW_BrechaDigital |
2019+ |
| Dashboard | Streamlit | 1.32+ |
| Charts | Plotly | 5.20+ |
| AI Skill Extraction | Groq API (LLaMA 3.1 8B) | 0.9+ |
| AI Assistant | Groq API (LLaMA 3.1 8B Instant) | 0.9+ |
| Macro Data | CEPALSTAT REST API | — |
| Employment Data | Adzuna REST API | — |
| DB Connectivity | PyODBC + SQLAlchemy | 5.0+ / 2.0+ |
| Environment | python-dotenv | 1.0+ |
| Step | Module | Input | Output |
|---|---|---|---|
| 1. Extract Academic | src/ingestion/sqlserver.py |
BrechaDigitalDB (SQL Server) |
data/raw/*.csv |
| 2. Extract Macro | src/ingestion/cepalstat.py |
CEPALSTAT REST API | data/raw/cepalstat/*.csv |
| 3. Extract Jobs | src/ingestion/empleos.py |
Adzuna REST API | data/raw/empleos/*.csv |
| 4. Extract Skills | src/ingestion/skill_extraction.py |
Adzuna job descriptions | data/processed/empleos/skills_extracted.csv |
| 5. Clean | src/transform/clean.py |
data/raw/*.csv |
data/processed/*.csv |
| 6. Normalize | src/transform/normalize.py |
data/processed/*.csv |
data/processed/*.csv |
| 7. Load Dimensions | src/schema/dimensions.py |
data/processed/*.csv |
DW_BrechaDigital — DIM_* tables |
| 8. Load Facts | src/schema/facts.py |
data/processed/*.csv |
DW_BrechaDigital — Fact_InsercionLaboral |
Job descriptions from Adzuna are processed by skill_extraction.py using a two-stage approach:
- Groq LLM (primary): Sends raw job descriptions to
llama-3.1-8b-instantand extracts structured skill lists in JSON format - Regex fallback: If LLM fails or rate-limits, a regex pattern bank covers the most common tech keywords
The output skills_extracted.csv is committed to the repository for reproducibility and to avoid runtime LLM costs on every dashboard load.
Operational database that stores raw academic records. Normalized relational model.
┌─────────────────┐ ┌──────────────────────┐
│ Carreras │ │ CompetenciasDigitales│
├─────────────────┤ ├──────────────────────┤
│ PK CarreraID │◄────────│ FK CarreraID │
│ NombreCarrera│ │ PK CompetenciaID │
│ Facultad │ │ NombreHabilidad │
└────────┬────────┘ │ NivelRequerido │
│ └──────────────────────┘
│
│ ┌──────────────────┐
│ │ Estudiantes │
│ ├──────────────────┤
└──►│ PK EstudianteID │
│ Nombre │◄──────────────┐
│ FechaIngreso │ │
│ Genero │ │
│ Ciudad │ │
└──────────────────┘ │
│
┌──────────────────────────┐ ┌───────────────┴──────────┐
│ Inscripciones │ │ SeguimientoEgresados │
├──────────────────────────┤ ├──────────────────────────┤
│ PK InscripcionID │ │ PK EgresadoID │
│ FK EstudianteID ─────────┼───►│ FK EstudianteID │
│ FK CarreraID ─────────┼───►│ TieneEmpleoFormal │
│ NotaFinal │ │ SalarioMensualUSD │
│ SemestreActual │ │ TrabajaEnAreaDeEstudio│
└──────────────────────────┘ └──────────────────────────┘
5 tables — 4 foreign key relationships
SeguimientoEgresados is the key table: it records whether each student got formal employment after graduation and whether they work in their field of study.
Analytical warehouse optimized for BI queries. Each row in the fact table is one graduate's employment event.
┌───────────────────┐
│ DIM_CARRERA │
├───────────────────┤
│ PK SK_Carrera │
│ CarreraID (BK) │
│ nombrecarrera │
│ area │
└────────┬──────────┘
│
┌──────────────────┐ │ ┌──────────────────────┐
│ DIM_ESTUDIANTE │ │ │ DIM_HABILIDAD │
├──────────────────┤ │ ├──────────────────────┤
│ PK SK_Estudiante │ │ │ PK SK_Habilidad │
│ EstudianteID │ │ │ NombreHabilidad │
│ nombre │ │ │ FK SK_Categoria ──►┐ │
│ Genero │ │ └──────────┬──────────┘ │
│ ciudad_ │ │ │ │
│ residencia │ │ ┌──────────▼──────────┐ │
└────────┬─────────┘ │ │ DIM_CATEGORIA_SKILL │ │
│ │ ├─────────────────────┤ │
│ ┌─────────▼──────────────────────────┐ │ │
└────────►│ FACT_INSERCION_LABORAL │ │ │
├────────────────────────────────────┤ │ │
│ FK SK_Estudiante │ │ │
│ FK SK_Carrera │ │ │
│ FK SK_Tiempo │ │ │
│ FK SK_Region │ │ │
│ FK SK_MercadoLaboral │ │ │
│ EstaEmpleado (INT) │ │ │
│ SalarioMensualUSD (DECIMAL) │ │ │
│ TrabajaEnAreaEstudio (BIT) │ │ │
└──────┬──────────────┬───────────────┘ │ │
│ │ SK_Categoria│ │
▼ ▼ PK──────────┘ │
┌───────────────┐ ┌──────────────────────┐ │
│ DIM_TIEMPO │ │ DIM_MERCADO_LABORAL │ │
├───────────────┤ ├──────────────────────┤ │
│ PK SK_Tiempo │ │ PK SK_MercadoLaboral │ │
│ anio │ │ Ubicacion │ │
│ trimestre │ │ FK SK_Region ──►┐ │ │
│ mes │ └──────────────────┼─────┘ │
│ Semestre │ │ │
└───────────────┘ ┌──────────▼────────┐ │
│ DIM_REGION │ │
├───────────────────┤ │
│ PK SK_Region │ │
│ Ciudad │ │
│ Region │ │
└───────────────────┘ │
│
NombreCategoria ◄─────────────────────┘
Básico / Intermedio / Avanzado
8 tables — 1 fact + 7 dimensions (2 sub-dimensions)
The snowflake structure normalizes DIM_HABILIDAD → DIM_CATEGORIA_SKILL and DIM_MERCADO_LABORAL → DIM_REGION to eliminate data redundancy.
Normalized dimension model chosen over star schema for technical correctness. Sub-dimensions reduce redundancy and improve referential integrity at the cost of additional JOINs.
DIM_CARRERA
▲
DIM_CATEGORIA_SKILL DIM_ESTUDIANTE
▲ ▲
DIM_HABILIDAD │
▲ │
└── FACT_INSERCION_LABORAL ──► DIM_TIEMPO
│
└────────► DIM_MERCADO_LABORAL
▲
DIM_REGION
Full schema documentation: docs/esquema_copo_nieve.md
| Member | Role | GitHub |
|---|---|---|
| Abraham Flores Barrionuevo | Bronze Lead — Data Ingestion | @AFB-9898 |
| Juan Nicolás Flores Delgado | Silver Lead — Transformation | @Juan7139nf |
| Micaela Pérez Vásquez | Gold Lead — Schema Design | @Sam24p |
| Mayra Villca Méndez | Analysis Lead — Notebooks & KPIs | @MayVillca |
| Diego Vargas Urzagaste | Dashboard Lead — Integration & Deployment | @temps-code |
Progress tracked on the GitHub Kanban Board.
# 1. Clone
git clone https://github.com/temps-code/brecha-digital-bi.git
cd brecha-digital-bi
# 2. Virtual environment
python -m venv .venv
source .venv/bin/activate # Linux / macOS
.venv\Scripts\activate # Windows
# 3. Dependencies
pip install -r requirements.txt
# 4. Configure environment variables (see below)
# 5. Seed the Bronze database
# Execute database/seed.sql in SQL Server Management Studio
# 6. Run the full pipeline (single command)
python -m src.run_pipeline
# Optional: skip ingestion if raw CSVs already exist
python -m src.run_pipeline --skip-ingestionThe pipeline orchestrator (src/run_pipeline.py) runs all 4 stages in order:
| Stage | What it does |
|---|---|
| 1 — Ingestion | Extracts from SQL Server + Adzuna API + CEPALSTAT API + LLM skill extraction |
| 2 — Clean | Cleans and validates all raw CSVs |
| 3 — Normalize | Standardizes cities, careers, dates; creates unified view |
| 4 — Schema | Loads dimensions and fact table into DW_BrechaDigital |
Copy .env.example and fill in your values:
cp .env.example .env# SQL Server — Bronze Source
DB_SERVER=localhost,1433
DB_NAME=BrechaDigitalDB
DB_USER=sa
DB_PASSWORD=your_password
# SQL Server — Gold Warehouse
DW_SERVER=localhost,1433
DW_NAME=DW_BrechaDigital
DW_USER=sa
DW_PASSWORD=your_password
# Groq API — AI assistant + skill extraction
GROQ_API_KEY=your_groq_api_key
# CEPALSTAT (public API — no key required)
CEPALSTAT_BASE_URL=https://api-cepalstat.cepal.org/cepalstat/api/v1
# Adzuna Employment API
ADZUNA_APP_ID=your_app_id
ADZUNA_APP_KEY=your_app_keyThe
.envfile is already in.gitignore. Never commit it.
The pipeline auto-detects auth mode: SQL Auth when credentials are set, Windows Auth otherwise.
The app is deployed at brecha-digital-bolivia-bi.streamlit.app.
For your own Streamlit Cloud deployment:
- Fork or push to GitHub
- Connect the repository in share.streamlit.io
- Set Main file path to
src/dashboard/app.py - Add secrets in Settings → Secrets (TOML format):
GROQ_API_KEY = "gsk_your_key_here"
ADZUNA_APP_ID = "your_id"
ADZUNA_APP_KEY = "your_key"
DB_SERVER = "your_server"
DB_NAME = "BrechaDigitalDB"
DB_USER = "your_user"
DB_PASSWORD = "your_password"
DW_SERVER = "your_server"
DW_NAME = "DW_BrechaDigital"
DW_USER = "your_user"
DW_PASSWORD = "your_password"Note: Without SQL Server access, the dashboard automatically falls back to the pre-processed CSV files in
data/processed/— no data loss for the deployed version.
