Swiss Traffic Pipeline

Hi everyone! Excited to share that I've just wrapped up my Course Project for the Data Engineering Zoomcamp and what a ride it was! I spent hours building an end-to-end data pipeline on Swiss road traffic data, and seeing everything click into place at the end made it completely worth it. The goal was to take raw FEDRO sensor bulletin data and turn it into a clean, queryable analytics layer with a live dashboard on top. How I built it: Ingestion:Python reads the FEDRO annual CSV bulletin a tricky format with merged cells, German month names, and thousands-comma separators. Everything gets parsed and loaded into DuckDB locally. Staging & Transform:** Bruin orchestrates the full DAG — SQL assets clean station names, standardise road categories, tag missing sensor months, and aggregate canton-level KPIs. Asset tags (`raw` → `staging` → `transform` → `reporting`) keep the lineage clean. -Export Transformed tables land in Google BigQuery (`swiss_traffic` dataset) via pandas-gbq, overwriting on every run so the dashboard always reflects the latest pipeline output. What I visualised in Looker Studio (4 pages) - Canton-level traffic KPIs and seasonal patterns across Romandy (French-speaking Switzerland) - Station deep-dive: monthly ADT trends, weekday vs weekend gaps, metric comparisons - Freight corridors: which roads carry the heaviest HGV loads and how that shifts by season - Data quality: sensor coverage flags across all 403 monitoring stations :wrench: **Stack:** Python · DuckDB · Google BigQuery · Bruin · Looker Studio · SQL The dataset covers ~24,000 monthly traffic records across 403 stations and the most interesting finding was how dramatically seasonal Valais is compared to Geneva (45% vs 10% peak variation). Alpine tourism versus commuter traffic in action. Repo here if you want to dig in: :link: