BTS Airline On-Time Performance — Fabric Lakehouse

Report

Architecture

Pipeline decisions

• Explicit schema, not inference. Inference reads the file twice and produces inconsistent types between months — a column entirely null in one month infers as string and as int in another, and the append then fails partway through a backfill. The schema is generated once from a reference month, hardened, and cached to a 20 KB JSON file rather than making two passes over 260 MB of CSV.
• The phantom column. BTS ends every row with a trailing comma, producing a 110th unnamed field. Declaring it as _phantom and dropping it by name avoids Spark flagging every row as containing discarded data — a false "corrupted records" warning on 600,000 rows trains you to ignore warnings. It is also faster: 13 seconds against 24.
• Keep-list, not drop-list. Silver reduces 109 fields to 56 using an explicit keep-list, so a new column in a future BTS vintage is ignored by default rather than flowing through uninvited.
• Arrival timestamps take the date from arithmetic and the clock from BTS. Adding elapsed minutes to the departure timestamp produced a 48% mismatch against the reported arrival time. Every mismatch was a whole number of hours and tracked direction of travel — ActualElapsedTime is gate-to-gate in local clock time at each end, so pure arithmetic yields origin-local time while BTS reports destination-local. The hybrid resolves it without requiring an airport-to-timezone reference dataset.
• The `2400` rule differs between departure and arrival, deliberately. On the departure side the base date has not moved, so a day is added; on the arrival side the arithmetic has already crossed midnight, so it is not. Applying the departure rule to arrivals put 532 rows two days out.
• Null-safe equality in the merge condition. Standard = returns null when either side is null, so a row with a null key column would never match itself and would insert a duplicate on every run.
• Natural keys throughout, not generated surrogates. monotonically_increasing_id() is not stable across reruns, and a proper key-assignment step adds pipeline machinery this project does not need.
• Airports are keyed on `airport_id`, not the IATA code. BTS reuses IATA codes across different airports over time. Carriers join on DOT ID for the same reason — BTS defines a unique airline by its certificate regardless of code or holding company.
• Dimensions rebuild in full every run. This is what handled late-arriving members without special casing: dim_airport grew from 367 to 387 and dim_carrier from 17 to 18 as periods outside the development slice introduced airports and carriers that had not appeared before.
• Grain is one row per scheduled flight leg, including cancelled and diverted. Filtering cancellations out is the common mistake with this dataset — it destroys cancellation-rate analysis and biases every delay average, because the worst operational days are the ones with the most cancellations. April 2020 would lose two fifths of its rows.

Validation

Every layer boundary has a check that fails the run rather than warning. When the pipeline runs unattended, a bad month must fail the run rather than quietly write wrong data. validate_timestamps() runs two independent checks: that the derived clock matches the BTS reported time of day, and that the arrival date is the flight date or the day after. The 2400 bug passed the first at 100% and was caught only by the second. One passing check is not validation.

• Source archives CRC-verified before ingestion — 77 of 77 valid, 1.92 GB
• Explicit schema held across all 78 periods, 2020 to 2026
• Derived clock matched the BTS reported time at 100%, every period
• Arrival date within 0 to 1 days of flight date — 100%, zero outside range
• Business key unique — zero duplicates, every period
• Silver merge idempotent — row count unchanged on a full re-run at 41.2M rows
• Every delayed flight appears in delay attribution — 82,285 = 82,285
• Referential integrity across five relationships — zero orphans across 41.2M rows
• Assume referential integrity. All semantic model relationships use Assume referential integrity, which generates INNER rather than OUTER joins on a 41M-row fact. That is a promise, not a check — it is only safe because the gold notebook verifies exactly these five relationships with left_anti joins on every load and raises on failure.

Results

• 41,829,828 rows, identical across bronze, silver and fact_flight — no drift through the chain.
• Full backfill of 71 periods ran unattended in 7 hours 6 minutes with zero failures. Retries were configured on each activity but never fired.
• One period end to end takes about 6.5 minutes on F4 trial capacity — 53 seconds to fetch and extract, 13 seconds to bronze, 1m 22s through silver, 2m 25s to gold.
• Incremental load demonstrated. June 2026 was deliberately held back from the backfill and loaded afterwards through the same unchanged pipeline: 77 periods to 78, and exactly 607,577 new rows — June's count as recorded in the watermark. No double-counting, no manual intervention.
• The failure path works. A later attempt at July 2026 returned HTTP 404 because BTS had not yet published it. Landing failed, and on-success chaining meant bronze, silver and gold never ran — they show as unstarted rather than skipped or failed. No partial data, no false success.
• Late aircraft leads on total delay minutes despite fewer occurrences than carrier delay — the cascade effect, where one delayed aircraft makes its subsequent flights late. Weather is the rarest cause and by far the most severe per occurrence at 70.4 minutes average.
• Attribution covers 91.3% of arrival delay minutes. The gap is flights delayed 1 to 14 minutes, for which BTS reports no cause. The unattributed share moves with severity — 11.7% when delays were mild, 5.2% when they were long. Correct behaviour, not a data quality issue.

Notes on the Fabric platform

Three defaults that cost time and are not obvious from the error messages.

• GitHub sync is disabled tenant-wide by default. The setting lives in the Admin portal, not workspace settings. With it off, GitHub simply does not appear in the workspace Git provider list — it looks like a missing feature rather than a permission.
• High concurrency for pipelines is off by default. Four notebook activities in sequence each request their own Spark session, and on F4 the second fails with TooManyRequestsForCapacity. Session tags are the mechanism, but they are ignored for pipelines unless the setting is enabled. The equivalent toggle for interactive notebooks is on by default, which makes the asymmetry easy to miss.
• A notebook parameter cell must be toggled, not merely present. Untoggled, pipeline values are silently ignored and the notebook runs its defaults — and the failure surfaces two activities downstream. A zero-row guard converted a silent misconfiguration into a named, actionable error.
• HTTP 200 does not mean success. TranStats returns 200 with an HTML body for URLs it does not recognise, which produced two false positives during the build. Both fetch paths assert on content — the zip download checks magic bytes, the lookup checks the CSV header — rather than trusting the status code.