Weather ELT Pipeline
An automated pipeline that collects weather data for 20 Indonesian cities every day and prepares it for analysis.
- Python
- Airflow
- dbt
- PostgreSQL
- Docker
The problem
Weather data from public APIs arrives as raw, nested JSON. Analysts cannot query it directly, and collecting it by hand does not scale or stay reliable.
What I built
A daily job fetches hourly weather for 20 cities across six island groups, retries when the API fails, and saves the raw responses to a database.
A transformation step cleans the data and reshapes it into a star schema, a layout built for fast analytical questions. Automated tests check the data, and the whole system runs in containers.
The result
- Each run produces 480 hourly records (20 cities, 24 hours).
- 18 automated data tests check uniqueness, missing values, and links between tables.
My role
Designed and built the pipeline: extraction, loading, dimensional modeling, tests, orchestration, and containerization.
Technical details
Tech stack
- Ingestion
- Python
- Open-Meteo REST API
- Storage
- PostgreSQL (JSONB raw layer)
- Transformation
- dbt Core
- Kimball star schema
- Orchestration
- Apache Airflow (LocalExecutor)
- Docker Compose
Architecture
- Open-Meteo APISource of hourly weather for 20 cities.
- Python extract & loadUses retries with exponential backoff.
- PostgreSQL raw layerKeeps untouched JSON payloads.
- dbt staging + star schemaCleans, de-duplicates, and models facts and dimensions.
- Airflow DAGRuns load, dbt run, and dbt test daily.
Key decisions
Kimball dimensional modeling
A central fact table with dimension tables keeps analytical queries to simple joins, instead of many-table joins.
Surrogate keys
Generated keys decouple the analytics tables from upstream identifier changes.