Overview
SkyNYC answers one question two ways: how much does weather cost arrivals at JFK, LaGuardia, and Newark? The live path ingests aircraft position telemetry from the OpenSky Network (ADS-B state vectors over a NYC bounding box, polled every 30 seconds) alongside official NOAA/NWS observations and alerts, pushes both through Kafka into Spark Structured Streaming, and derives three operational events that no public feed publishes: arrivals, holding patterns, and go-arounds. The historical path lands 38 years of federal on-time performance data into a Delta Lake medallion on Databricks and answers the same question back to 1987.
A detected event is an inference, so the system measures its own inference error daily against an independent reference. A Dagster job pulls OpenSky's arrival records, and a dbt mart matches them against detections on aircraft, airport, and a 10-minute window, publishing precision and recall per airport per day. Two publication gates constrain the result: precision is withheld until a day's ground truth has landed, and recall is withheld unless the detector observed at least 20 of 24 hours, so an unscored day is reported as unscored rather than as poor performance.
Each component is sized to a specific constraint in the source data. Kafka carries roughly 5 messages per second, far below any throughput threshold; it is present because the upstream API serves at most an hour of history, which makes the 7-day retained log the only substrate available for detector tuning and replay. Spark's applyInPandasWithState carries per-aircraft state machines whose 90-second timeouts convert transponder coverage loss into a landing signal. dbt's tests and source-freshness contracts serve as the monitoring surface. Dagster orchestrates the finite, partitioned work while Docker restart policies supervise the continuously running streams, a split drawn along the boundary between jobs that terminate and processes that do not.
The Problem
The events the question depends on are published by no public feed. FAA programs report airport-level status, not per-aircraft behavior, so arrivals, holding, and go-arounds have to be derived from raw position telemetry. That derivation is stateful, per-aircraft, and order-sensitive, over a stream where latitude, longitude, altitude, and velocity are all nullable, transponder contact routinely drops below roughly 450 m on short final, and aircraft on the ground legitimately report null altitude. Derivation alone is insufficient: a detector that is never scored publishes claims with no measurable error rate.
A scheduled script fails against three properties of the source. First, the API retains about an hour of history and supports no backfill, so an outage longer than that is unrecoverable without a durable buffer in front of it, and any change to detector thresholds is untestable without replayable history. Second, detection requires ordered per-aircraft state carried across polls: ring buffers, cumulative turn unwrapping, and gap-aware timeouts. Third, re-runs have to converge; absent deterministic event identities and upsert-only writes, each replay corrupts the score the system exists to publish.
Derived events, queried directly from Postgres.
- Twelve arrivals in ninety minutes, each stamped with its confirmation method (on_ground contact inside 1.5 km), alongside running totals of 126 at JFK, 107 at LaGuardia, and 93 at Newark since collection started.
- The third block is the harder path: arrivals confirmed by coverage loss, where the transponder went silent at 30–91 m altitude within 3 km of the field and the 90-second state timeout closed the event.
- The last block shows holding detections with their supporting evidence: 944°, 364°, and 1,084° of cumulative unwrapped turn over 7 to 17 minutes. No public feed publishes these rows.
Key Features
- Stateful event detection with named, tunable thresholds — arrivals confirmed by ground contact within 4 km or by coverage loss below 450 m after 90 seconds of silence; holding as ≥340° cumulative unwrapped turn inside 8 minutes with net displacement under 15 km; go-arounds as a descent followed by two strong-climb samples regaining ≥200 m with no touchdown
- Published self-validation — a day × airport mart scores detections against OpenSky's independent arrival records under two publication gates: precision null until ground truth lands, recall null unless the detector observed ≥20 hours
- Data quality as monitoring — 59 dbt tests (every model's grain enforced by a uniqueness test) plus 3 source-freshness contracts run hourly, so a green build over stale sources still fails
- Push alerting on the one signal that summarizes the live path — seconds since the newest position row; noDataState set to Alerting so the alarm can't be starved by its own outage
- Idempotency end to end — deterministic event IDs, ON CONFLICT DO UPDATE on natural keys in every sink, Delta replaceWhere per month in bronze: every layer replays to convergence
- API credit governor — the producer logs remaining credits every poll and degrades 30s → 60s polling below 500, against a 2,880-call daily budget
- Immutable archive and recovery — bronze Parquet to ADLS Gen2 with lifecycle tiering and soft delete, plus a pg_dump asset in custom format so restores can pick tables
- Everything-as-files ops — Grafana provisioned from the repo, Terraform for all Azure resources, numbered SQL migrations, one rsync deploy script, CI on every push
Stack
- Python 3.11
- Apache Kafka
- Spark Structured Streaming
- Delta Lake
- Databricks
- dbt
- Dagster
- Postgres 16
- Azure ADLS Gen2
- Azure Data Factory
- Terraform
- Docker Compose
- Grafana
Architecture
Live path: two Python producers poll REST APIs, wrap responses in a typed envelope, and publish to Kafka keyed by transponder address, so per-aircraft ordering is guaranteed by the partition key. Three independent Spark queries read the states topic. Q1 parses with an explicit schema, deduplicates within a 2-minute watermark, and appends Parquet partitioned by date and hour directly to ADLS Gen2, which serves as the immutable archive and system of record because the API cannot backfill. Q2 upserts the latest position per aircraft for the map. Q3 is the product: groupBy(icao24).applyInPandasWithState over a pure-Python detector engine with a 20-sample ring buffer per aircraft. Weather moves through a plain Python consumer, since 3 messages per 5 minutes does not warrant distributed compute.
Historical path: Azure Data Factory lands monthly BTS archives and METAR history as immutable copies, then a four-task Databricks job builds the medallion. Bronze keeps every column as string with one Delta partition per source month written via replaceWhere so retries converge; silver casts, prunes, and deduplicates 181 M rows on the natural flight key; gold aggregates one row per airport-day joined with daily weather rollups. A Dagster asset triggers the job over the Jobs API using an OAuth service principal that mints one-hour tokens, avoiding long-lived credentials, then upserts the gold export into the same Postgres the live path serves from.
Delivery and failure behavior: the pipeline is at-least-once with idempotent sinks, so replays and crash recovery converge on the same rows, yielding effectively-once semantics at the serving tables. Spark tracks Kafka offsets in per-query checkpoints instead of consumer groups, which makes replay an explicit runbook: delete one query's checkpoint and set startingOffsetsByTimestamp, since either step alone is a no-op. Late and gapped data are handled in event time. The detector enforces the same 90-second coverage boundary on sample timestamps that the live timeout enforces on the wall clock, so a replayed batch emits identically to the live stream, a property the test suite asserts at three batch granularities.
Status and limitations as of capture: the stack has run continuously on a single 8 GB droplet across 9 containers since 2026-08-11, though the 7-day soak is on its first day and the written analysis milestone has not started. Headline precision and recall derive from a single scored day. The holding and go-around detectors are thinly exercised at 15 and 9 events respectively, and only arrivals have independent ground truth; go-arounds are validated against disclosed synthetic geometry together with real-arrival fixtures that assert no false positives. The live-window impact mart has so far observed only VFR conditions, so the current weather-impact result comes from the 38-year historical mart. Federal data has no files for 1990–1999, so the gold table jumps from 1989 to 2000 and the delay-history dashboard begins at 2000 by design.
One control plane over four asset groups.
- Fifteen assets across four groups: default (the dbt DAG), ground_truth (the daily pull and quality report), lakehouse (the Databricks run and its Postgres upsert), and ops. Every finite job in the system appears on one graph.
- Dashed nodes are external sources the streams write; solid nodes are what Dagster materializes. The boundary between orchestrated work and supervised work is visible in the graph itself.
- The lakehouse group is where the two paths meet: a Databricks job triggered over the Jobs API, then an asset that upserts the gold export into the same Postgres the live path serves from.
Skills Applied
- Stream Processing
- Three checkpointed Structured Streaming queries; custom per-aircraft state machines under applyInPandasWithState with processing-time timeouts, an event-time equivalent of that timeout for replay parity, and watermark-scoped deduplication that evicts state as the watermark advances.
- Data Engineering
- End-to-end flow from REST polling through Kafka, Spark, and Postgres to BI, with unit-aware parsing (NWS quantities converted by reading unitCode) and nulls treated as data: drops counted, gaps recorded as absence.
- Analytics Engineering
- A 10-model dbt DAG with a one-sentence grain contract per model enforced by tests, spine semantics so zero-activity hours are real rows, join-at-read weather enrichment, and marts that publish their own denominators so thin cells cannot present as stable rates.
- Orchestration
- Dagster as the single control plane for finite work: partitioned daily assets, dbt assets discovered from the manifest, and cross-system triggering of Databricks over the Jobs API, with supervision of the continuous streams delegated to Docker restart policies.
- Infrastructure & DevOps
- Terraform-provisioned Azure across two stacks (~39 resources including Data Factory, Databricks, Unity Catalog, and budget caps), Compose with health-gated dependencies, loopback-bound ports because Docker's iptables rules bypass host firewalls, and one idempotent deploy script.
- Testing & Reliability
- 53 pytest cases driving the production detector engine over recorded live sequences (a descent into JFK, a flight vanishing at 83 m on LaGuardia final, overflights, taxi traffic), with equivalence asserted sample-by-sample, in coarse batches, and across the serialized-state seam.
Spark Structured Streaming, 362 batches deep.
- The named query has been running continuously for 1 hour 23 minutes at capture, processing roughly 13–15 records/sec in and 150–180/sec out; the margin between input rate and process rate is the headroom that keeps batches from backing up.
- Batch duration holds near one second against a 10-second trigger, with a single 3-second spike, so the query runs well below saturation at this volume.
- This is the checkpointed query whose Kafka offsets live on disk instead of a consumer group, which is what makes the replay runbook possible.
The bronze archive on ADLS Gen2, inventoried from the CLI.
- 416 Parquet files across seven dt=/hr= partitions on a StorageV2 account with hierarchical namespace enabled and a TLS 1.2 minimum. This is the immutable system of record, since the upstream API cannot backfill.
- The hr=00 spike is annotated in the output: a re-archive replaying retained Kafka history through a fresh checkpoint, which is the replay path working as designed.
- Alongside it, a nightly pg_dump in custom format and a lifecycle rule tiering bronze blobs to Cool after 30 days: recovery and cost control expressed as configuration.
The live board: airspace, arrivals, and pipeline freshness.
- Pipeline freshness reads 15.4 seconds, the single metric that summarizes the live path and the one the push alert watches, with noDataState set to Alerting so the alarm cannot be starved by its own outage.
- Arrivals per 15 minutes run per airport beside NWS alert state, with a Coastal Flood Statement live on JFK at capture; airborne delay tracks holding minutes and go-arounds separately.
- Bottom right plots arrival rate against effective wind: the live-window form of the question the 38-year mart answers, computed on events the system derived itself.
The medallion build, four tasks on one ephemeral cluster.
- bronze_bts runs 2h57m, the two silver tasks fan out in parallel at 31m and 6m, and gold_marts closes in under 6 minutes, for 3h34m47s end to end with every task succeeded.
- Every task is single_node on a Standard_DC4as_v5, and the cluster shows terminated: it exists only for the duration of the run, so nothing idles between monthly builds.
- Bronze is per-archive by construction because ZIP archives are not splittable, which is what makes single-node the correct shape for this stage.
181 million rows landing, one monthly archive at a time.
- The log counts through [125/344] archives with per-month row counts and a running cumulative: 65 million rows at 1h10m, on the way to the full 181 M.
- Parameters point at two abfss:// paths, raw and lake: the job reads immutable landed copies and writes Delta, never touching the source data.
- Each month is one Delta partition written with replaceWhere, so a retry rewrites only that month and converges instead of duplicating rows.
Inside the run: 3,652 stages and 6.2 GiB of shuffle.
- The active stage is a WriteIntoDeltaCommand at 183 of 200 tasks, writing 1,989 MiB of output: the silver rebuild deduplicating on the natural flight key.
- Silver uses a full overwrite instead of an incremental merge. Minutes of single-node compute buy trivial idempotency, which is worth more than speed on a job that runs monthly.
- 3,652 stages completed and 6.2 GiB of shuffle read across the run, with fair scheduler pools isolating the job's tasks from the default pool.
Unity Catalog over the same Delta the jobs write.
- Three external tables under skynyc.lake (the gold airport-day mart and both silver layers), registered as External with Delta as the data source, so the catalog references the lake in place.
- The gold schema is the historical thesis in columns: scheduled and cancelled counts, weather-attributed cancellations, average and p90 arrival delay, plus the visibility, ceiling, and worst-category weather fields it joins against.
- A 2XS serverless warehouse fronts the catalog, so compute is billed only while a query runs.
The 38-year answer, in one query and twelve rows.
- Average arrival delay climbs from 2.0 minutes in VFR to 22.9 in LIFR at JFK, 2.1 to 29.7 at LaGuardia, and 4.1 to 31.1 at Newark: roughly an order of magnitude at all three airports.
- The p90 column shows a tail heavier than the average implies. JFK's 90th percentile moves from 30.6 to 80.0 minutes, and the share of arrivals more than 15 minutes late nearly doubles.
- Each row carries its own denominator in the days column, so a large average can be weighed against how often that weather category occurs: 5,428 VFR days against 1,669 LIFR days at JFK.
The dbt DAG, discovered from the manifest.
- Ten models flow from raw sources through staging views, an airport-hour spine, and last-known-value hourly weather into the facts and marts, each node carrying its grain contract in one sentence.
- Models become Dagster assets by reading the dbt manifest at image build, so dependencies arrive intact and nothing is registered by hand.
- The green check counts on every node are dbt tests surfaced as asset checks: the quality suite and the orchestration graph are the same object.
The daily ground-truth pull: eight seconds, fully logged.
- 601 arrivals at JFK, 418 at LaGuardia, and 573 at Newark for the previous UTC day, upserted as 1,592 rows, forming the independent reference the detector is scored against.
- Each API call logs its remaining credit balance (3970 → 3940 → 3910), so budget consumption is observable per step.
- The asset is daily-partitioned, so a backfill is a materialization of an older partition.
The scoring mart and its published contract.
- The description is a contract: one row per UTC day per airport, precision publishing only once ground truth lands, recall only for days the detector observed ≥20 of 24 hours.
- The SQL implements those gates directly: a matched-arrival EXISTS test run in both directions, and a UTC day bucket pinned explicitly so a non-UTC session cannot shift day boundaries.
- Comments record the reasoning behind the matching rule: 1:1 assignment across a few hundred arrivals per day would move the score by noise.
Quality checks as a monitored surface.
- Seven checks on this mart alone (grain uniqueness, accepted airport values, and not-null guards on every column the score depends on), each with its own pass history and execution plot.
- The failed-row-count series sits flat at zero across every hourly run, and 59 dbt tests plus 3 source-freshness contracts run project-wide, so staleness fails the build even when every row is individually valid.
- Because checks are attached to assets, a failure identifies the specific table that broke.
The daily scoring job, publishing its own error rate.
- A 44-second run rebuilds the dbt models the score depends on, then materializes the report; the subsetted rebuild ensures the mart includes the morning's ground-truth pull before scoring.
- The log lines are the product: precision 0.9457 at Newark, 0.9435 at JFK, and 0.9327 at LaGuardia, each printed with the detected and ground-truth counts behind it.
- Raw recall reads 0.15–0.20 because the detector observed only part of that day: 92 detections against 574 full-day reference arrivals at Newark. This is the partial-coverage case the ≥20-hour gate withholds from publication.
Grafana, provisioned from version control.
- Delay by flight category since 1987, weather versus national airspace delay hours per year, cancellations by month, and the worst weather days on record: the historical path in one view.
- The detector's precision and recall are plotted beside the analysis they support, so the dashboard reports each finding alongside the measured error rate of the machinery that produced it.
- Datasources, dashboards, and the alert rule are all file-provisioned from the repo: a UI edit that isn't exported back does not survive a redeploy.
Every airport-month since 2000, by weather category.
- Each point is one airport-month; the four colors are flight categories. The separation between categories persists across 26 years of monthly observations.
- The tooltip carries the spread for a single month: LIFR averaging 22.3 minutes against VFR's 6.2, with an LIFR maximum of 1.19 hours.
- Minimums go negative (VFR −9.6 minutes) because flights routinely arrive early in good weather, a detail an annual average would absorb.