How felidb/etl-movilidad-interdepartamental-postgres works
Project Type and Purpose
• This is an ETL‐and‐analytics project packaged with Docker Compose.
• It loads open CSV datasets (departamentos.csv, provincias.csv, movilidadinterdepartamental.csv) into a PostgreSQL database via an initialization script.
• It then exposes two UIs—pgAdmin (for DB administration) and Apache Superset (for BI dashboards and SQL queries)—all running as Docker services.
Main Components
A. Data Sources (Host)
• CSV files under /datos
B. Initialization Layer
• init/init.sql script (mounted into the Postgres container’s /docker-entrypoint-initdb.d)
• init.sh / init.bat scripts (entrypoint to docker-compose up)
C. Docker Compose Orchestrator
• docker-compose.yml defines 3 services and two named volumes
D. PostgreSQL Service
• Image: postgres:15
• Mounted volumes:
• ./datos → /datos (holds CSVs)
• ./init → /docker-entrypoint-initdb.d (executes init.sql on startup)
• Exposed port: 5432
E. pgAdmin Service
• Image: dpage/pgadmin4
• Named volume: pgadmin-data → /var/lib/pgadmin
• Exposed port: 8080
F. Apache Superset Service
• Built from superset/Dockerfile
• Named volume: superset-data → /app/superset_home
• Exposed port: 8088
• Depends on PostgreSQL (ensures DB is up before Superset initializes)
G. Host OS / Developer
• Runs init.sh/init.bat to bring containers up
• Connects via browser to pgAdmin and Superset
Relationships & Data Flows
• On docker-compose up, Postgres container reads init.sql, creates tables (provincia, departamento, movilidadinterdepartamental), and bulk‐loads CSVs from /datos.
• Once DB is ready:
• pgAdmin connects to postgres service on port 5432 for CRUD and schema inspection.
• Superset connects via SQLAlchemy URI (postgresql+psycopg2://postgres:postgres@postgres:5432/movilidad) for analytics.
• Developer/Analyst interacts with UIs through host browser.
Architectural Patterns & Design Principles
• Containerization: each major component runs in its own Docker container with minimal coupling.
• Volume mounts used for both persistence (named volumes) and data injection (host directories).
• Entrypoint scripts handle schema creation and data loading (pattern: immutable infrastructure on init).
• Separation of concerns:
• ETL/import at DB init
• DB administration via pgAdmin
• Analytics/visualization via Superset