End-to-end ETL pipeline analyzing water consumption and copper production data from chilean mining (2014–2024)
Data sourced from COCHILCO 🇨🇱 (Chilean Copper Commission) is extracted via Python, transformed into a star schema in SQL Server, and loaded into BigQuery as the analytical data warehouse. A Power BI dashboard connects via DirectQuery to expose six analytical views across production, water intensity, regional breakdown, and demand projections to 2034.
COCHILCO → Python ETL → Docker / SQL Server → BigQuery → Power BI
- ETL: Python · pandas · SQLAlchemy · google-cloud-bigquery
- OLTP: SQL Server 2022 (Docker Compose) · T-SQL · star schema · 7 tables
- DWH: Google BigQuery · 6 analytical views · southamerica-west1
- BI: Power BI Service · DirectQuery · DAX
- Web: Astro · TypeScript · SCSS · Vercel
- Environment: Debian 12 · conda · lazydocker
- Tools: DBeaver · Obsidian · VSCode
# 1. Start SQL Server
docker compose up -d
# 2. Run the pipeline (idempotent)
conda run -n copperprint python pipeline/main.py
(Requires .env with SQL Server credentials and a BigQuery service account JSON)