How diegomonterocrc/prediccion_consumo_energia_costa_rica works
1. This repository is a data-integration and warehouse project for Costa Rica energy analysis, not an application server. The center of the architecture is an ETL pipeline that pulls data from external APIs, cleans and reconciles it, and loads it into a PostgreSQL data warehouse designed in catalog, staging, and fact/dimension layers.
2. The main external data sources are NASA POWER for climate and ARESEP for energy-related datasets. Those integrations live in src/api/cliente_api_clima.py and src/api/cliente_api_aresep.py, which act as source adapters that return pandas DataFrames. This is the first boundary in the system: external services are isolated from downstream cleaning and loading logic.
3. The raw and derived data lifecycle is explicit in the repository layout. data/raw/ stores source extracts, data/processed/ stores unified and final curated CSVs, and data/docs_apis/ contains source metadata dictionaries and parameter mappings. That separation matters architecturally because the project supports reproducible local processing without re-querying every source every time.
4. Exploratory cleaning is a distinct subsystem. src/eda/ProcesadorEDA.py provides reusable DataFrame transformations such as type conversion, null handling, outlier checks, and coordinate cleanup. The notebooks in notebooks/ and src/eda/EDA.ipynb are not just analysis artifacts; they define and validate the data-shaping rules that later feed the ETL layer.
5. There is a second integration layer that merges source domains into curated datasets before database load. src/datos/gestor_datos_aresep_clima.py builds combined outputs like aresep_unificado_2020_2025.csv and dataset_final_2020_2025.csv, which indicates a preprocessing stage that reconciles climate and ARESEP data into common analytical records.
6. The ETL subsystem is centered in src/datos/ETLs.py and src/datos/CargadorDatos.py. CargadorDatos is the shared base that keeps the active DataFrame, reads/writes CSV, and triggers the final DW load. ETLs extends the cleaning pipeline into database insertion, so the same object can clean, stage, and publish data. That creates a single operational flow from pandas into PostgreSQL.
7. Staging is a deliberate intermediate boundary. The src/datos/DataModels/ classes translate DataFrame rows into database-ready records for each staging table, with _BaseStgModel.py handling alias mapping, null normalization, timestamp/date conversion, and SQL parameter ordering. This is the layer where the repository decouples pandas column names from the warehouse schema.
8. Database connectivity and SQL execution are encapsulated in src/datos/GestorDBconn.py. The code assumes PostgreSQL and uses environment-based configuration rather than hard-coded credentials. This module is the only place that should know how to open connections, run SQL, and invoke stored functions.
9. The warehouse itself is defined by the SQL scripts in src/base_datos/. There are three schema groups: Esquema_Catalogos.sql for variable dictionaries, Esquema_Staging.sql for load tables, and Esquema_Fact_Dim.sql for the dimensional model. Supporting functions in Funciones_Staging.sql and Funciones_Fact_Dim.sql implement the actual insert/transform logic inside PostgreSQL.
10. The target model is a classic dimensional warehouse with shared dimensions and multiple facts. Core dimensions include time, company, tariff, location, central eléctrica, hydrocarbon product, hydrocarbon resolution, and concession zone. Facts include monthly climate, electricity tariffs, distribution tariffs, and hydrocarbons. A bridge table links companies to concession zones, showing a many-to-many relationship that cannot be modeled as a simple foreign key.
11. src/ETL_main.py is the orchestration entry point. Its flow is: clear staging, load catalogs, fetch ARESEP and NASA data, prepare each domain, load staging tables, and then call the function that populates the fact/dimension layer. This script is the best single place to understand runtime execution order and subsystem dependencies.
12. src/main.py appears to be a lighter support script for generating or validating local CSV artifacts, while src/visualizacion/ contains map-related outputs and helper scripts such as mapa_concesiones.py and streamlit.py. These are downstream consumer-facing artifacts, but they are not the core ETL path; they sit beside the warehouse as visualization support.
13. Overall, the architecture is layered and batch-oriented: source APIs and reference CSVs feed pandas-based cleaning, cleaned data is staged into PostgreSQL, and stored SQL procedures materialize the dimensional warehouse. The strongest architectural boundaries are between source extraction, DataFrame normalization, database staging, and dimensional modeling.
Main entry points: GestorDBconn.py DB Conn · ETLs.py ETL Orchestrator · ProcesadorEDA.py EDA Processor · gestor_datos_aresep_clima.py Domain Merge
Components 19
Sources
External APIs and reference dictionaries that feed the pipeline.
- NASA API · Fetches climate data from NASA POWER and returns DataFrames.
src/api/cliente_api_clima.py - ARESEP API · Fetches energy datasets from ARESEP and returns DataFrames.
src/api/cliente_api_aresep.py - Docs CSVs · Source dictionaries and parameter mappings used to normalize incoming datasets.
data/docs_apis
Preparation
Pandas cleaning, domain unification, and ETL orchestration.
- EDA Processor · Reusable DataFrame transformations for type conversion, null handling, outliers, and coordinates.
src/eda/ProcesadorEDA.py - Domain Merge · Combines ARESEP and climate data into curated unified datasets.
src/datos/gestor_datos_aresep_clima.py - Data Loader · Holds the active DataFrame and reads or writes CSV artifacts before load.
src/datos/CargadorDatos.py - ETL Orchestrator · Runs cleaning, staging, and final warehouse load in one operational flow.
src/datos/ETLs.py - ETL Main · Top-level batch entry point that coordinates extraction, staging, and dimensional loading.
src/ETL_main.py
Warehouse
PostgreSQL connection, staging models, schemas, and stored functions.
- DB Conn · Encapsulates PostgreSQL connections, SQL execution, and stored-function calls.
src/datos/GestorDBconn.py - Stg Base · Normalizes aliases, nulls, dates, timestamps, and SQL parameter ordering for staging models.
src/datos/DataModels/_BaseStgModel.py - Staging Models · Row-to-record translators for the staging tables.
src/datos/DataModels - Catalog Schema · Defines catalog dictionaries and variable metadata tables.
src/base_datos/Esquema_Catalogos.sql - Staging Schema · Defines the database load tables used as the intermediate boundary.
src/base_datos/Esquema_Staging.sql - DW Schema · Defines the fact and dimension model for the warehouse.
src/base_datos/Esquema_Fact_Dim.sql - Staging Functions · PostgreSQL functions that implement staging inserts and transforms.
src/base_datos/Funciones_Staging.sql - DW Functions · PostgreSQL functions that populate the fact/dimension layer.
src/base_datos/Funciones_Fact_Dim.sql
Outputs
Curated files and visualization consumers built from the warehouse data.
- Final Dataset · Unified analytical dataset produced before or alongside warehouse loading.
data/processed/dataset_final_2020_2025.csv - Map Output · Generated concession-zone map artifact.
data/processed/mapas/mapa_zonas_concesion.html - Visual Layer · Map and streamlit helpers that consume processed outputs.
src/visualizacion
Connections 21
- NASA API → EDA Processor: raw climate · Climate extracts are normalized in pandas before integration.
- ARESEP API → EDA Processor: raw ARESEP · ARESEP extracts follow the same cleaning boundary.
- Docs CSVs → EDA Processor: field maps · Reference dictionaries inform column and parameter normalization.
- EDA Processor → Domain Merge: clean frames · Cleaned DataFrames are merged into domain-level curated outputs.
- Domain Merge → Final Dataset: curated CSV · Unified datasets are written to processed artifacts.
- Domain Merge → Data Loader: prepared data · Prepared outputs are handed to the shared load layer.
- ETL Main → ETL Orchestrator: run batch · The main script invokes the ETL pipeline.
- ETL Orchestrator → NASA API: fetch climate · ETL pulls NASA data through the climate adapter.
- ETL Orchestrator → ARESEP API: fetch ARESEP · ETL pulls ARESEP data through the energy adapter.
- ETL Orchestrator → Data Loader: load CSV · ETL uses the shared loader for file-backed artifacts.
- ETL Orchestrator → DB Conn: db load · ETL sends staging and final data to PostgreSQL.
- Data Loader → DB Conn: persist/load · The loader ultimately delegates database publication through the connection layer.
- DB Conn → Catalog Schema: catalogs · Database execution relies on the catalog schema.
- DB Conn → Staging Schema: staging · Database execution relies on the staging schema.
- DB Conn → DW Schema: warehouse · Database execution relies on the dimensional warehouse schema.
- DB Conn → Staging Functions: stage funcs · Staging functions perform the intermediate load logic.
- DB Conn → DW Functions: dw funcs · Warehouse functions materialize facts and dimensions.
- Staging Models → Stg Base: inherits · All staging models share the base record-mapping behavior.
- ETL Orchestrator → Staging Models: map rows · ETL converts DataFrame rows into staging records.
- Visual Layer → Map Output: render map · Visualization helpers generate map artifacts from processed data.
- Visual Layer → Final Dataset: consume data · Consumer-facing visuals read curated processed datasets.