When evaluating airline performance, typical analytics dashboards rely blindly on passenger-facing "On-Time" arrival percentages. However, from an operational and financial engineering standpoint, these high-level metrics can easily mask millions of dollars in silent cash destruction.
Due to strategic schedule padding, a short-haul flight can sit idling its engines on a congested tarmac for 20 minutes, yet still be registered as "On-Time" to the customer. The passenger is happy, but the airline’s finance department just watched pure margin vaporize into thin air.
To expose these hidden friction layers, I engineered LCC-Ops: an end-to-end data warehouse auditing over 120,000 regional transaction legs modeled around the single-aisle network performance of flynas across key GCC hubs.
Here is the technical architecture and the forensic database methodologies behind the pipeline.
The Tech Stack & Architecture
- Database Layer: MySQL Server 8.0 (Advanced CTEs, Mathematical Window Functions, Schema Migrations)
- Business Intelligence: Power BI Desktop (Data Type Optimization, Cross-Table Iterators, UI Design)
- Core Fleet Constants: Engineered around the Airbus A320neo (CFM LEAP-1A26 engine) utilizing official ICAO Engine Emissions Databank metrics. At a standard 7% ground idle taxi thrust, this engine burns exactly 0.0940 kg of fuel per second, per engine
The Database Engineering Breakdown
Pillar 1: Isolating Tarmac Cash Destruction (Domain A)To find the true cost of airport ground bottlenecks, the ingestion engine isolates rows inside the transactions table (fact_flights) where the Out-Gate to Runway Takeoff latency (taxi_out) exceeded a standard 4-minute engine stabilization and warming buffer.
By pulling the real-world ICAO fuel coefficients down via relational keys, the calculation bypasses text-based padding and computes raw physical fuel destruction.
SELECT
f.origin AS departure_hub,
COUNT(f.tail_number) AS total_monitored_flights,
ROUND(AVG(f.taxi_out), 2) AS avg_taxi_out_minutes,
-- Formula: (Actual Taxi Time - 4 Min Buffer) * 60 Sec * ICAO Idle Flow Rate * 2 Engines
ROUND(SUM((f.taxi_out - 4) * 60 * e.fuel_flow_idle_kg_sec * 2), 2) AS total_wasted_fuel_kg
FROM fact_flights f
JOIN dim_aircraft a ON f.tail_number = a.tail_number
JOIN dim_engine e ON a.engine_identification = e.engine_identification
WHERE f.reporting_airline = 'XY' AND f.taxi_out > 4
GROUP f.origin;
Pillar 2: Tracking Cross-Border Delay Cascades (Domain D)
When an international flight arriving from an airport like Karachi (KHI) experiences customs or airspace delays, that latency risks cascading into the aircraft’s subsequent domestic legs (e.g., RUH ➔ JED).
To track this chronologically across individual daily airframe lifecycles, I utilized advanced LEAD() mathematical window functions
WITH ranked_daily_schedule AS (
SELECT
flight_date, tail_number, origin, dest, carrier_delay AS leg_1_delay,
LEAD(origin, 1) OVER (PARTITION BY tail_number, flight_date ORDER BY taxi_out DESC) AS leg_2_origin,
LEAD(carrier_delay, 1) OVER (PARTITION BY tail_number, flight_date ORDER BY taxi_out DESC) AS leg_2_delay
FROM fact_flights
WHERE reporting_airline = 'XY'
)
Pillar 3: Menu Logistics & Hangar Risk Automation (Domains B & C)
Catering Spoilage Optimization: Short flight windows on regional tracks mean cabin crews lack the time to complete complex perishable sales loops. The data models validate flynas’s lean, shelf-stable buy-on-board menu model, proving that switching to fresh profiles would trigger an immediate 54% inventory spoilage cash bleed
Automated Hangar Reorder Points (ROP): The warehouse tracks rolling airframe hours against specific tail numbers (HZ-NS1 to HZ-NS5) [Query-relevant Context]. If a component approaches its high-wear safety limit, the system triggers automated database alerts to protect the airline against catastrophic $25,000/day Aircraft on Ground (AOG) hangar penalties
UI Engineering in Power BI
To present these data layers to corporate stakeholders, I built an interactive application-style interface directly inside Power BI using flynas’s signature branding tones.
The biggest challenge during BI ingestion was data type conversions—empty strings or trailing spaces in the raw logs caused math functions to drop blank values (--). By implementing upfront string transformations (Uppercase/Trim) and embedding data validation wrappers like ISNUMBER(VALUE()) directly into row-level DAX iterators, the front-end dynamically scales across massive transaction volumes without data loss.
The entire production code schema and scripts are live on my GitHub Link. I'd love to hear how other data engineers manage real-time fuel-burn parameters or multi-leg network routing in your pipelines!