Project Overview
End-to-end Google Cloud pipeline: a Cloud Run Job (Python) fetches yesterday’s KEF departures from kefairport.is/flug/brottfarir, cleans the data, standardizes timestamps, and appends to a date-partitioned BigQuery table every morning at 06:00 Europe/Zurich. An enriched BigQuery view derives delay and cancellation metrics that power the dashboard below.
- KPIs: Punctuality (≤16 min), average delay (delayed flights only) and cancellations.
- Filters: Date range and airline slicer.
- Performance: Partition pruning + caching; idempotent loads to avoid duplicates.
Pipeline & Data Model
How the pipeline runs daily
- Cloud Run service (Python): Calls
/api/flightDatawith a rolling “yesterday” parameter, flattens/normalizes the JSON, writes a daily CSV to GCS, then loads into BigQuery viaload_table_from_uriwithWRITE_APPEND. - Cloud Scheduler: Triggers the job every day at 06:00 Europe/Zurich.
- BigQuery: Table
keflavik-flights.KefAirport.flights_history; an enriched view computes new columns such as delay minutes, punctuality for the dashboard.
How punctuality & delays are computed
- Delay (min):
raw_delay_min = DATETIME_DIFF(actual_dt, sched_dt, MINUTE) - Delayed flag:
Delayed = (raw_delay_min > 0)(only flights with both times are measured) - Punctual (≤16): on-time when
status != 'CNL'andDATETIME_DIFF(actual_dt, sched_dt, MINUTE) ≤ 16 - Cancellations:
status = 'CNL'→ treated as not punctual (excluded from on-time numerator) - Midnight rollover fix: if actual time is just after midnight while scheduled is late evening, shift
actual_dtto next day.
BigQuery view definition showing time normalization, cancellation logic, delay calculation, and ≤16-minute punctuality rule.
Live Dashboard
Interact with the embedded Looker Studio report. For full-screen view, open it in a new tab.
Stack & Data Sources
Stack: Google Cloud Run (Jobs), Cloud Scheduler, BigQuery (partitioned), Python (requests, pandas, numpy, google-cloud-bigquery, pyarrow), Looker Studio.
Source: KEF Airport API (/api/flightData) with rolling “yesterday” parameter; enriched KPIs in a BigQuery view.