I wanted to answer one question: what does European airspace actually look like right now. Then I wanted to be able to prove the numbers, which turns a dashboard into a pipeline. It has run for months on free tiers, and the code is archived on Zenodo.
Repo: swadhinbiswas/eu-air-traffic · Live: airtraffic-eu.pages.dev · Data lake: Hugging Face
What it serves
The live map shows around 2,000 aircraft with callsign, type, altitude, speed, vertical rate and route lines, plus weather stations and search by callsign, registration, type or airport. Alongside that sit delays, punctuality, cancellations, airport and airline leaderboards, route performance and weather impact. Eurostat's official monthly passenger numbers sit next to the movements this platform counted itself, so a wrong number has somewhere to show up.
The shape of the pipeline
A collector on a small VPS polls the upstream APIs and publishes to a five-topic Kafka cluster. Every fifteen minutes a scheduled job drains Kafka, builds the Bronze, Silver and DuckDB layers, runs dbt, then publishes to three places: Hugging Face for the dataset lake, MotherDuck for the full warehouse, and Turso for the copy the browser is allowed to read.
The sources are all public: OpenSky for movements, adsb.lol for live positions with airplanes.live and OpenSky as fallbacks, AirLabs for schedules and delays, aviationweather.gov for METAR and TAF, Open-Meteo for forecast, and Eurostat avia_paoa for the monthly passenger benchmark. Reference data from OurAirports, OpenFlights and ICAO 8643 is built once, and the OpenAP kinematic model is precomputed into a 37-type fuel lookup.
Why there are two serving stores
MotherDuck holds the full warehouse, but its user model cannot hand a scoped read-only token to a browser. Turso can, so the site reads a derived, bounded copy there instead: small enough to hold cheaply and safe to expose. Paging totals come from a precomputed site_summary row, so a browser poll never scans a fact table.
Free-tier accounts carry their own read and write budgets, so the serving copy is spread across several Turso databases through TURSO_TARGETS. A table listed in more than one target is mirrored, each copy synced and versioned on its own. When one account runs out of quota or goes down, the publisher keeps going and the browser fails over to a healthy copy. No Turso replication is involved. Every target is an independent database.
Working inside provider budgets
The project is shaped as much by limits as by goals.
| Limit | What I did |
|---|---|
| Kafka allows five topics per cluster | One topic per domain; weather and reference data multiplex with a _kind discriminator the sink splits back into datasets |
| OpenSky meters credits per endpoint | /flights/all for both ends in one request, live departures for four hubs, a nightly arrivals backfill, around 2,700 of 4,000 daily credits |
| AirLabs gives 1,000 calls a month at 50 rows each | Rotating hubs and a persisted monthly counter that stops at the budget; IATA to ICAO resolved from bundled data |
| Turso meters reads and writes | Aggregates precomputed into one summary row, static tables uploaded only when a content hash changes, growing tables watermark-synced |
| Object storage meters commits | Silver partitioned per source, only changed files pushed, unchanged files treated as no-ops |
| The VPS has two cores | The box only collects; every transform runs in CI |
The collector is deliberately the only long-running process. If it dies, the pipeline still drains whatever Kafka retained.
What running unattended taught me
Most of the work went into failure handling rather than the happy path, and each item below has a test behind it.
A step once logged an error and exited 0, which meant a rejected upload looked like a success. Failures are loud now, and credentials that are set but empty raise instead of silently skipping. A failed lake pull could push a single window over the full Silver history, so pulls fail the job. Publishers now load static tables into a shadow table and swap, rather than wiping a serving table mid-run. OpenSky movements and AirLabs schedules describe the same flight with different ids, so marts dedupe on callsign, date and endpoint, and only average delays that are actually known. Casting a timestamp with a time zone to a plain timestamp shifted rows by the session offset and quietly re-read old data, which is the kind of bug that makes you distrust every date column you own.
The repository ships an AWS serverless analytics plane as code (Lambda, S3, Glue, Athena, QuickSight) to show the scale path. It is not provisioned. What runs today is the free pipeline, every cycle, at zero cost.
The full source, the 103 dbt models and the dataset are linked above. Air-traffic data is a good teacher because the upstream sources are messy in the same ways every real source is: rate limits, duplicate ids, and clocks that disagree.
Reach me at swadhinbiswas.cse@gmail.com or on GitHub and LinkedIn.
No comments yet.