Team of four · Data engineering · MMAI 5100, Schulich — DB Project 2
Weather API ingestion pipeline
A daily Extract-Transform-Load pipeline, built with three MMAI teammates, that pulls current weather for 15 representative Canadian postal codes from OpenWeather and stores it in a structured PostgreSQL table. It's designed to run unattended: every run adds to the history instead of overwriting it, and a composite key stops a rerun from creating duplicates.
per run, coast to coast
append-only
key blocks reruns
Overview
One provider, current, forecast and history
Built for analysts and researchers comparing financial institutions that each report in their own currency and on their own schedule.
The pipeline ingests current weather for 15 representative Canadian postal codes and stores it in a structured SQL table, following a standard Extract-Transform-Load design and built to run daily without overwriting existing history. We picked OpenWeather as the provider because it supplies current, forecast, and historical data from one source, accepts latitude/longitude directly, and its free tier is sufficient for a daily sample of this size.
The postal codes themselves aren't hardcoded — they're pulled from a shared class database
(uploads.ca_postal_codes) with a SQL query that lives in its own
postal_codes.sql file, so scaling the pipeline from 15 codes to all of Canada
means changing the query, not the code.
The four stages
What each step is actually doing
01 — Extract
Select the codes
A SQL query reads 15 postal codes from the class-wide uploads.ca_postal_codes table, returning province, region, postal code, and coordinates. The query lives in its own file, so the pipeline doesn't change if the selection does.
02 — Extract
Call the API
The pipeline loops over the 15 codes and calls OpenWeather's current-weather endpoint once per location, passing each lat/lon pair with units=metric so temperatures arrive in Celsius directly from the source.
03 — Transform
Flatten and clean
Each nested JSON response is flattened into one row per postal code. The Unix dt field becomes a timezone-aware measurement_timestamp; a second ingestion_timestamp marks when the pipeline ran.
04 — Load
Write to Postgres
An uploads schema is created in a personal database — leaving the shared class database untouched — and the DataFrame is written to uploads.current_weather with an explicit type for every column.
The schema
Every column, explicitly typed
Nothing lands as loosely-typed text — coordinates and measurements are DECIMAL, counts are INTEGER, both timestamps are timezone-aware.
| Column | Type | Notes |
|---|---|---|
| postal_code | VARCHAR(7) | Primary key (with timestamp below) |
| measurement_timestamp | TIMESTAMPTZ | Primary key — from the API's Unix dt |
| province, region | VARCHAR | Carried through from the postal-code source |
| latitude, longitude | DECIMAL(10,2) | Passed straight to the OpenWeather call |
| temp_c, temp_min, temp_max | DECIMAL(10,2) | Celsius — requested via units=metric |
| feels_like_c | DECIMAL(10,2) | OpenWeather's perceived temperature |
| pressure, sea_level, grnd_level | INTEGER | sea_level / grnd_level are null for some locations |
| humidity | INTEGER | Percent relative humidity |
| wind_speed | DECIMAL(10,2) | Metres per second |
| condition | VARCHAR | e.g. Clear, Clouds, Rain |
| ingestion_timestamp | TIMESTAMPTZ | When the pipeline ran, not the observation |
Running it daily
Built to run unattended, not just once
The script is scheduled the same way any daily job would be — cron on Linux/macOS, Task
Scheduler on Windows — with a trigger like 0 6 * * * python3 weather_data.py.
Three design choices make that safe to leave running:
- GuardedCREATE TABLE IF NOT EXISTS — later runs skip table creation instead of failing or rebuilding it.
- Append-onlyif_exists='append' — new rows extend the history instead of overwriting the table each run.
- De-duplicatedComposite primary key on (postal_code, measurement_timestamp) — a rerun for the same observation can't insert a duplicate.
Scaling from 15 codes to all of Canada would mean parallelising the request loop with rate limiting, de-duplicating coordinates to the API's grid resolution to cut redundant calls, and upgrading the OpenWeather plan to allow the higher call volume.
Challenge
Reading the nested JSON
- OpenWeather's response nests values inside objects (main, wind) and
arrays (weather[0]). We had to trace each field's path before we could flatten it
into flat columns — and a few fields, like
sea_levelandgrnd_level, aren't returned for every location, which the schema had to allow for.
Challenge
Holding the whole flow in view
- Keeping the full Extract-Transform-Load path clear while building it — which step reads from where, what shape the data takes at each stage, where the two database connections fit — was harder than any single step. Sketching it end to end, SQL source → API calls → DataFrame → SQL table, is what made the pieces click into place.
Team
Group 5, MMAI 5100
- Quan Nguyen, Rad, Zeshan, and Temesgen. My contribution spanned the extract and transform stages — the postal-code query, the OpenWeather loop, and flattening the JSON response into a clean DataFrame — plus the load-side schema design.
Stack
What it's built with
- Python — pipeline script, scheduled via cron
- pandas — flattening JSON into a DataFrame
- SQLAlchemy + PostgreSQL — typed load into a class-hosted server
- OpenWeather API — current-weather endpoint, metric units