Analytics engineering · Python, GCP, BigQuery

Ten years of weather, 96,453 readings, turned into something you can actually ask questions of.

Raw hourly readings do not answer questions. A trustworthy pipeline and a few well chosen features do. This project builds the second thing from the first.

Open to data engineering & analytics roles
96,453hourly readings, 2006 to 2016
5orchestrated pipeline phases
2load targets, SQLite & BigQuery
47.3send to end, logged
The data

A decade of one city's sky, recorded every hour.

Weather feels random when you live inside it. Across ten years and ninety six thousand rows, it stops being random and starts being pattern.

The source is a decade of hourly meteorological readings from Szeged, Hungary, covering 2006 through 2016: temperature and apparent temperature, humidity, wind speed and bearing, visibility, pressure, and a text summary of conditions, ninety six thousand four hundred and fifty three rows in all. On its own it is an undifferentiated wall of numbers. The work is turning that wall into something queryable, trustworthy, and legible.

That means two disciplines working together. First, the engineering: a pipeline that cleans, validates, and lands the data somewhere it can be queried at scale. Second, the analysis: deriving the handful of features that let a decade of hourly noise resolve into the daily and seasonal rhythms that were there all along. The rest of this page follows the data through both.

A single day, averaged across ten years

The most important feature was not in the data. I had to build it.

Raw timestamps are almost useless for seeing a daily rhythm. So the transformation step derives three features from each reading: its Hour, its Weekday, and its Part of Day, morning, afternoon, evening, or night. That single engineered feature, Part of Day, is what turns a flat table into the shape below: the average temperature of one Szeged day, learned from ten years of them.

Morning
16.4°C
5 AM to 11 AM, warming
Afternoon
22.8°C
12 to 4 PM, the daily peak
Evening
20.9°C
5 to 8 PM, holding warmth
Night
14.6°C
9 PM to 4 AM, the floor

Roughly eight degrees separate the afternoon peak from the overnight floor, a swing you feel every day but rarely quantify. Feature engineering is what made it measurable: without Part of Day, this rhythm stays buried in timestamps. This is the quiet heart of the project, the point where data engineering becomes data analysis.

The decade

What ten years of sky actually say.

With the data clean and the features in place, the patterns surface quickly. Monthly averages breathe from roughly eight degrees in deep winter to the mid twenties at the height of summer, the long seasonal wave underneath the daily one.

Line chart of daily temperature trend across Szeged from 2006 to 2016
Ten years of daily average temperature. The seasonal wave is remarkably regular, year over year.

Underneath the seasons, three sharper findings emerge from the engineered features, each one a small piece of atmospheric physics the data confirms on its own.

Bar chart of average temperature by part of day and weekday
Temperature by part of day and weekday. The daily cycle dwarfs any day of week effect.
Line chart of monthly humidity and wind speed trends
Humidity against wind speed. As afternoon wind rises, humidity falls, an inverse diurnal pattern.
Heatmap of average temperature across part of day and weekday
The full grid: part of day against weekday. Night is coldest across the board, afternoon warmest, weekday barely matters.
Wind peaks between two and six in the afternoon; humidity moves the opposite way. That is thermal mixing from surface heating, and the decade shows it plainly.
The build

None of that analysis is trustworthy if the pipeline underneath it is not.

Every number above depends on the data being clean, complete, and correctly loaded. So the pipeline is built the way production data work actually gets built: five isolated phases, one orchestrator, structured logging at every step, and validation that fails loudly rather than silently.

Phase one

Extract

  • Read source CSV
  • Validate required fields
  • Confirm row count
96,453 rows in
Phase two

Transform

  • Deduplicate, handle nulls
  • Datetime normalization
  • Feature engineering
  • Regex schema sanitization
641 bad rows removed
Phase three

Load

  • Write to SQLite
  • Upload to BigQuery
  • Dual target, one run
2 destinations
Phase four

Verify

  • Row count reconciliation
  • Query validation
  • Table existence checks
95,812 confirmed
Phase five

Visualize

  • Looker Studio dashboard
  • Matplotlib exports
  • Four analysis views
live & shareable

The calls that were not on the happy path

The last one is the one worth defending in an interview: when the local infrastructure could not carry the plan, I changed the architecture instead of forcing it.

BigQuery rejected column names with spaces and special characters
Regex sanitization on every column before load, re.sub(r'[^a-zA-Z0-9_]', '_', name), so the schema is always legal without hand editing.
Datetime strings had to survive temporal analysis
Parsed with pd.to_datetime(errors='coerce'), so malformed timestamps become handled nulls, never a crash mid run.
Secure cloud authentication inside a notebook
GCP OAuth through Colab's built in flow, so credentials are never hard coded into the pipeline or the repository.
Docker container specs could not support the planned local Postgres
Rather than fight the environment, I pivoted to a SQLite plus BigQuery dual target design, which came out cleaner: lighter locally, stronger in the cloud. The constraint improved the architecture.
In one line
The weather was always patterned. The work was building something that could see it.

A validated pipeline, a few deliberate features, and a live dashboard, the full path from raw hourly readings to a decade you can question in a glance.

Open the live dashboard View the code
cmutuaa@gmail.com LinkedIn GitHub
Python 3.10pandasSQLiteBigQueryGCPLooker StudioMatplotlib