Spreadsheets delayed route planning — Airflow unites OAG, PSS & Treasury in Snowflake

Overview

Route planning at a regional airline slowed because teams reconciled fuel costs, slot rights, and demand forecasts in spreadsheets. Treasury’s hedge positions, OAG schedules, and booking curves from the Passenger Service System (PSS) refreshed on different cadences, and meetings devolved into data triage. Intelligex orchestrated an Apache Airflow pipeline that blended OAG schedules, PSS booking curves, slot constraints, and fuel hedging data into a governed model in Snowflake, and exposed choices in a scenario app gated by Revenue Management approvals. Leadership made quicker route calls with fewer manual reconciles because every option drew from the same snapshot, slot rules were explicit, and assumptions were reviewed before publication.

Client Profile

• Industry: Regional air carrier
• Company size (range): Multi-base operation with short- and medium-haul networks
• Stage: Established carrier refining network strategy and fuel risk posture
• Department owner: Strategy, Analytics & Executive Leadership (Network Planning / Revenue Management)
• Other stakeholders: Treasury, Operations Control, Airport Affairs, Scheduling, Finance/FP&A, IT/Data Engineering, Legal & Compliance

The Challenge

Route and frequency decisions required a single view of schedule feasibility, slot rights, demand, and fuel exposure. In practice, schedules and competitor movements were pulled from OAG, demand from the PSS as booking curves and historical load factors, fuel hedges from Treasury, and station constraints from Airport Affairs. Each source used different identifiers and calendars. A slot window change in one airport could invalidate a planned rotation, while a hedge roll or tax change altered unit economics. Analysts stitched extracts together, and forecasts aged while teams reconciled basic facts.

Approvals arrived late. Revenue Management, Treasury, and Airport Affairs weighed in after scenarios had been circulated. Slot rights and curfew constraints were buried in PDFs or coordinator emails, and there was no consistent place to see whether a proposed time fell within permitted windows. Leadership wanted to keep core tools—scheduling systems, treasury models, and analytics—but needed an integrated, governed layer that aligned calendars and identities, encoded slot rules, and required approvals before scenarios reached executive forums.

Why It Was Happening

Signals and identities were fragmented. Flight numbers and tail rotations used one set of keys, OAG schedules used another, and booking curves referenced fare classes and markets without stable links to aircraft turns. Calendars mixed local time and UTC, and week-of-operation definitions varied by group. Treasury modeled hedges at product and period levels, while Network Planning used route-level unit economics. Without a mastered map and shared cut-offs, each team’s spreadsheet produced plausible but incompatible answers.

Governance sat at the end. Scenario decks lacked clear slot validation, fuel assumption stamps, or booking curve versions. Revenue Management approvals came via email, Treasury’s inputs were pasted as screenshots, and Airport Affairs added slot notes after conflicts were found. There was no change log binding a scenario to the exact inputs used, which made reversals and postmortems difficult.

The Solution

We built a governed network planning pipeline and scenario workspace. An Apache Airflow orchestration blended OAG schedules, PSS booking curves, fuel hedge positions from Treasury, and slot and curfew rules into a conformed model in Snowflake. dbt encoded identity mapping, calendar harmonization, fare cohort logic, and slot rule checks aligned with IATA’s Worldwide Slot Guidelines. A lightweight scenario app let planners toggle frequencies, times, aircraft types, and fare assumptions; each scenario was stamped with a snapshot ID and routed to Revenue Management for approval. Nothing was replatformed: OAG remained the schedules source, PSS remained the demand system, Treasury kept its models, and the new layer handled timing, rules, and workflow.

• Scheduled ingestion and transformation with Apache Airflow for OAG schedules, booking curves, and hedge positions
• Schedule and competitor context from OAG mapped to carrier flight, market, and aircraft identities
• Slot rules and curfew constraints encoded per station, aligned to IATA guidance (IATA Slots)
• Fuel hedging inputs and uplift assumptions joined to flights and markets with tax and uplift factors
• Conformed model and snapshots in Snowflake; definitions and tests in dbt for identity, calendars, and accepted values
• Scenario app with toggles for frequency, departure time, aircraft, and fare mix; built on a lightweight framework such as Streamlit with sourced tiles and warnings
• Approval workflow for Revenue Management and Treasury assumptions via ServiceNow with reason codes and comments
• Role-based views for Network Planning, Revenue Management, Treasury, Airport Affairs, and executives
• Audit trail capturing snapshot ID, slot checks, fuel assumptions, booking curve versions, and approvers
• Operational dashboard for data freshness, failed checks, and scenarios in review

Implementation

• Discovery: Mapped current data flows from OAG, PSS, and Treasury; documented slot windows, curfews, and local exceptions; inventoried identity keys and calendars; and reviewed recent route decision decks to pinpoint recurring reconciliations and late-stage edits.
• Design: Defined the conformed identity model across flights, markets, aircraft, and stations; set shared cut-offs and UTC/local handling; authored dbt models and tests for booking curves, fare cohorts, and slot checks; designed scenario toggles and guardrails; and documented approval paths with reason codes.
• Build: Implemented Airflow DAGs to land and transform schedules, bookings, and hedge inputs; built Snowflake tables and dbt transformations; encoded slot rules and warnings; developed the Streamlit scenario interface with sourced tiles and flags; and wired ServiceNow approvals and change logs.
• Testing and QA: Replayed recent season changes to reconcile outputs with prior decisions; validated identity joins, UTC/local conversions, and slot checks; spot-checked booking curve and hedge alignment; and exercised approval and rollback with Revenue Management and Treasury.
• Rollout: Launched read-only scenario views beside existing spreadsheets; after validation, enabled approvals for selected markets; expanded to the broader network with slot and curfew checks active. Maintained a manual override for urgent operational changes with post-review documentation.
• Training and hand-off: Delivered quick guides for planners on toggles and sourced tiles, for Revenue Management and Treasury on approvals and reason codes, and for Airport Affairs on slot rule stewardship. Established ownership for calendars, identity mappings, and rule updates on a set cadence.

Results

Route choices moved from reconciling extracts to weighing options with shared assumptions. Planners saw slot warnings in context, booking curves aligned to a common calendar, and fuel assumptions stamped on each scenario. Revenue Management and Treasury approvals occurred in the flow, which reduced late edits and back-and-forth.

Leadership conversations centered on trade-offs rather than data lineage. Conflicts with curfews and slot windows were flagged early, competitor schedules were visible in the same view, and decisions referenced an auditable snapshot. Teams spent less time stitching spreadsheets and more time shaping profitable, compliant schedules.

What Changed for the Team

• Before: Spreadsheets reconciled schedules, bookings, and hedge inputs. After: Airflow-fed snapshots in Snowflake powered a single scenario workspace.
• Before: Slot constraints were checked late in email threads. After: Encoded slot rules flagged issues inside the scenario app.
• Before: UTC and local calendars caused mismatches. After: A shared calendar model harmonized time handling across sources.
• Before: Assumptions lived in slides. After: Each scenario carried stamped booking curve versions, fuel assumptions, and approvals.
• Before: RM approvals were ad hoc. After: A routed workflow captured reason codes and sign-offs before executive review.

Key Takeaways

• Unify schedules, demand, slots, and fuel into a conformed model; decisions require consistent identities and calendars.
• Encode slot rules and curfews; policy-aware checks prevent late-cycle rework.
• Freeze inputs per cycle with snapshots and stamp assumptions; lineage builds trust in choices.
• Put approvals in the flow so Revenue Management and Treasury align before leadership meetings.
• Keep core systems; layer orchestration, transformations, and a scenario app to reduce reconciliation without replatforming.

FAQ

What tools did this integrate with?
Schedules and competitor context were sourced from OAG, booking curves from the PSS, and hedge inputs from Treasury’s existing models. Orchestration ran on Apache Airflow, the conformed model lived in Snowflake, transformations and tests ran in dbt, slot rules aligned to IATA Slot guidance, and the scenario app was delivered in Streamlit with approvals executed in ServiceNow.

How did you handle quality control and governance?
Shared cut-offs and a harmonized calendar prevented UTC/local drift. dbt tests enforced accepted values, identity joins, and slot rule checks. Each scenario carried a snapshot ID, booking curve version, and fuel assumptions. Revenue Management and Treasury approvals were required before scenarios advanced, and a change log captured reason codes, comments, and approvers for audit.

How did you roll this out without disruption?
We ran the scenario app in parallel with legacy spreadsheets for a cycle, reconciled differences, and tuned identities, calendars, and slot checks. After stakeholders were comfortable, we enabled approvals for a subset of markets and then expanded. Core systems remained; the orchestration layered consistency and workflow on top.

How were slot rights and constraints modeled?
Slot windows, curfews, and local restrictions were encoded per station with references to coordinator rules and internal agreements. The scenario app evaluated proposed times against these rules and displayed warnings with links to source clauses aligned to IATA guidance. Airport Affairs stewarded rule updates on a set cadence.

How did you handle fuel price volatility and hedge positions?
Treasury’s hedge ladders and product mixes were ingested on schedule and joined to flights and markets with uplift, tax, and burn assumptions. Scenarios stamped the hedge set used, and Treasury approved any assumption changes. Sensitivities could be toggled to show exposure bands without rewriting the base model.