The full path, from six upstream sources to the dashboard. Open the image for the full-size animated version.

Most flight trackers show a map and stop there. We keep the receipts: we count aircraft movements ourselves, then place them beside the same month's official Eurostat passenger totals for each airport. The live numbers can be checked against an outside source instead of taken on faith.

Our collector runs on a two-core VPS and does nothing but collect. Positions, flight movements, schedules, weather and fuel estimates leave it as Kafka records, and every transformation (deduplication, the warehouse build, the published lake) happens in scheduled jobs. The dashboard stays current without anyone watching it.

What we run

• Live map. Around 2,000 aircraft with callsign, type, altitude, speed and vertical rate, route lines, METAR and TAF stations, and search by callsign, registration, type or airport.
• Live analytics. Airspace composition, altitude bands, operators, and carbon intensity from the OpenAP kinematic model with measured and estimated values side by side.
• Business analysis. Delays, punctuality, cancellations, an airport leaderboard, airline rankings, route performance, weather impact and seasonal trends.
• Official benchmark. Eurostat monthly passengers per airport, cross-checked against the movements we observed.
• SQL workbench. The serving copy queried in the browser through a read-only token.
• Ops. Pipeline step report, data-quality report, dbt lineage and freshness.

How a cycle runs

1. The collector polls each source on its own interval (positions every 15 seconds and published in five-minute batches, movements every 30 minutes, schedules rotating through hubs) and writes batched records keyed by aircraft or flight.
2. Every 15 minutes a scheduled job drains Kafka from its last consumer offset and lands the window in Bronze as Parquet.
3. Silver updates incrementally: records are deduplicated across providers, typed, and loaded into a star schema in DuckDB.
4. dbt build runs 103 models and tests and produces the marts the site reads.
5. The publisher pushes changed files to the Hugging Face dataset, the full warehouse to MotherDuck, and a bounded copy to Turso through a shadow table that swaps in atomically.
6. The dashboard reads Turso for history and the collector's live API for the last few minutes.

Two serving stores, on purpose

MotherDuck holds the full warehouse, but its user model cannot issue a browser a scoped read-only token. Turso can, so the site reads a derived copy there: small enough to hold cheaply, safe to expose, refreshed every cycle. Paging totals come from a precomputed site_summary row, so a browser poll never scans a fact table.

That copy can span several Turso databases, usually one per free-tier account. TURSO_TARGETS maps each table to its targets, and a table listed under more than one target is mirrored and versioned on each side. When an account is down or out of quota the publisher keeps going, and the browser fails over to a healthy copy while the failed one cools down. There is no Turso replication in the mix, so every target is an independent database that can be rebuilt from the warehouse at any time.

Budgets are enforced in code

Every upstream free tier has a hard limit. The pipeline treats those limits as part of the design instead of something to discover at 3am.

• Kafka, five topics per cluster. One topic per domain. Weather (METAR, TAF, forecast) and reference data share a topic with a _kind discriminator that the sink splits back into datasets.
• OpenSky, daily credit budget. /flights/all covers both ends of a flight in one request, plus live departures for four hubs and a nightly arrivals backfill, around 2.7k of 4k daily credits.
• AirLabs, 1,000 calls per month. Rotating hubs and a persisted monthly counter that stops at the cap. IATA to ICAO codes resolve from bundled data, with no extra calls.
• Turso, row read and write budget. Aggregates precomputed into a one-row lookup, static tables uploaded only when a content hash changes, growth watermark-synced, and tables the site no longer needs dropped from the copy.
• Object storage, commit budget. Silver is partitioned per source. Only changed files are pushed, and unchanged files are acknowledged as no-ops.
• CI runners, shared and ephemeral. Frontend-only pushes skip the lake, and the job fails fast instead of retrying silently.
• VPS, two cores. The box only collects; all transformation runs in CI.

Running unattended

Most of the work went into failure handling rather than the happy path. Each item below has a test.

• Silent success. Some steps logged an error and exited 0, including an upload rejected by a trailing space in a repo id and a publish that never wrote. Failures are loud now, and credentials that are set but empty raise.
• Fresh-boot assumptions. A cadence check compared monotonic() against 0.0, which only works when uptime is longer than the interval. Missing state now means the work is due.
• Destructive retries. A failed lake pull could push one window over the full Silver history, and publishers could wipe serving tables mid-run. Pulls now fail the job, and static tables load into a shadow table and swap atomically.
• Double counting. OpenSky movements and AirLabs schedules describe the same flight with different ids. The marts deduplicate on callsign, date and endpoint, and only average delays that are actually known.
• Timezone traps. Casting TIMESTAMPTZ to TIMESTAMP shifts by the session offset, which silently re-reads old rows and skips boundary ones.
• Queue starvation. Every push entered one single-writer concurrency group until frontend-only pushes were excluded.

Data sources

• OpenSky Network for flight movements (OAuth2 client credentials)
• adsb.lol for live positions, with airplanes.live and OpenSky as fallbacks
• AirLabs for schedules with planned times and delays
• aviationweather.gov for METAR and TAF, Open-Meteo for forecast
• Eurostat avia_paoa for official monthly passengers per airport
• OurAirports, OpenFlights and ICAO 8643 for reference data, built once by a script
• OpenAP (TU Delft) for the kinematic fuel-flow model, precomputed into a 37-type lookup grid

Stack

Part	Technology
Collection	Python, one module per source
Event bus	Apache Kafka, five topics
Lake	Parquet on the Hugging Face dataset hub
Warehouse	DuckDB and dbt, served from MotherDuck
Serving	Turso with read-only tokens
Dashboard	React and Vite on Cloudflare Pages
Deploy	systemd and Docker Compose on a VPS
CI	GitHub Actions, a 15-minute schedule
Tests	pytest plus dbt tests

Links

• Live dashboard: airtraffic.eu.cc
• Live API health: vps.swadhin.cv/health
• Dataset: swadhinbiswas/air-traffic, MIT licensed
• Source: github.com/swadhinbiswas/eu-air-traffic
• DOI: 10.5281/zenodo.22790201