Schedule Integrity & Consistency Audit Guide

Document Overview

This guide provides the complete analytical framework, business logic, and natural language prompts for executing Schedule Integrity and Consistency Audits across airline flight schedules. It identifies critical operational anomalies—such as same-flight-number airborne overlaps, exact duplicate SSIM records, turnaround violations, leg sequence discontinuities, overlapping DOW validity windows, and invalid block times—ensuring database hygiene and operational feasibility before GDS publication or operational execution.

Natural Language Prompt Templates

1. Master Integrity Audit (Simultaneous Overlaps + Exact Duplicates)

PROMPT

Run a Schedule Integrity & Consistency Audit for carrier **AM** using schedule data from **the latest published schedule**. Limit scope to own-metal operating flights (operating_airline_disclosure IS NULL) and passenger scheduled services (service_type = 'J') over the **full published horizon**. Perform both the In-Flight Simultaneous Overlap Audit and the Exact Duplicate Record Audit. Present findings in formatted markdown tables and provide an executive summary.

2. Multi-Leg Continuity & Route Sequence Audit

PROMPT

Perform a **Leg Continuity and Sequence Audit** for carrier **DL** on dataset **ssim_published_2025w26** for passenger services (service_type = 'J'). Identify all multi-leg direct flight numbers where leg sequences are broken, intermediate stations do not match (e.g., Leg 1 arrives at station B but Leg 2 departs from station C), or leg sequence numbers skip. List all affected flight numbers, leg routes, and gap details.

3. Minimum Ground Turnaround Time Violation Audit

PROMPT

Run a Turnaround & Ground Time Violation Audit for carrier **LH** using **the latest published schedule** for station **FRA** across all own-metal passenger flights. Flag any sequential legs sharing the same flight number where the ground turnaround interval (Arrival UTC to Departure UTC) is less than the minimum connection/turn time of **45 minutes** (or station minimum MCT). Report the flight numbers, inbound/outbound legs, scheduled turn time, and deficit.

---

4. Overlapping Validity & Day-of-Week (DOW) Conflict Audit

PROMPT

Audit carrier **BA** schedule data in **ssim_published_2025w30** for **Overlapping Validity and DOW Conflicts**. Flag instances where the same flight number, leg sequence, and route have multiple active records whose operation_from to operation_to date ranges overlap and share active days of operation in days_operation, but contain conflicting departure times, arrival times, or equipment types.

5. Block Time & Negative Elapsed Time Audit

PROMPT

Run a **Block Time & Flight Duration Feasibility Audit** for carrier **AF** across all published schedules. Identify any flight records where: (1) block time is zero or negative (scheduled arrival UTC is earlier than or equal to departure UTC after applying date_variation), (2) flight duration is unrealistically short (less than 20 minutes), or (3) block time exceeds 20 hours. Report flight number, route, departure/arrival local times, UTC times, and calculated block duration.

6. Codeshare Synchronization & Mirror Discrepancy Audit

PROMPT

Perform a **Codeshare Mirror Audit** between operating carrier **QR** and marketing carrier **BA** on dataset **ssim_published_2025w28**. Identify: (1) marketing flights with no corresponding operating flight match, (2) timing discrepancies between operating and marketed legs greater than 5 minutes, and (3) operating flights where partner codeshare marketing records are missing.

Comprehensive Audit Suite Matrix

Audit Type	Primary Detection Objective	Key SQL / Filter Condition	Common Root Cause
In-Flight Simultaneous Overlap	Same flight number airborne simultaneously in UTC	(Dep_A < Arr_B) AND (Dep_B < Arr_A) for same flight number & date	Reused flight numbers across disparate route networks without temporal separation
Exact Duplicate Records	Identical redundant records in ssim_3 table	Group by all 40 SSIM Type 3 fields with COUNT(*) > 1	Double ingestion of SSIM files or overlapping snapshot delta loads
Leg Continuity & Discontinuity	Multi-leg direct flight station breaks	Leg_N.Arr_Station != Leg_(N+1).Dep_Station	Broken through-flight numbers after seasonal schedule adjustments
Turnaround Time Violations	Ground turn time below minimum station threshold	(Leg_(N+1).Dep_UTC - Leg_N.Arr_UTC) < Min_Turn_Time	Aggressive aircraft scheduling or unadjusted turn times post-schedule update
DOW & Date Validity Overlaps	Conflicting flight parameters on same operating day	Overlapping [op_from, op_to] with shared DOW bitmask bits	Unsynchronized schedule revisions or duplicate seasonal filings
Block Time Anomaly	Negative, zero, or unrealistic flight durations	(Arr_UTC - Dep_UTC) <= 0 OR Block_Mins < 20	Incorrect date_variation (+1/+2), missing station UTC variance, or timezone error

Standard Audit Scope & Parameters

Parameter	Type	Required	Description / Value
Target Carrier	String	Yes	IATA airline designator (e.g., AM, DL, LH, BA, AA).
Schedule Dataset	String	Yes	Dataset ID (ssim_published_YYYYwWW) or latest published snapshot.
Operation Scope	Filter	Yes	Own-metal operating records only (operating_airline_disclosure IS NULL or ='X').
Service Type	Char	Yes	Scheduled passenger services (service_type = 'J').
Time Horizon	Date Range	Yes	Date range (YYYY-MM-DD to YYYY-MM-DD) or full published horizon.

Analytical Logic & Step-by-Step Methodology

┌────────────────────────────────────────────────────────────────────────────────────────┐
│ Schedule Integrity Audit Engine │
│ │
│ ┌────────────────────────────────────────────────────────────────────────────────┐ │
│ │ SSIM Type 3 Schedule Dataset │ │
│ │ (Filter: Carrier, Service Type 'J', Own-Metal) │ │
│ └───────────────────────────────────────┬────────────────────────────────────────┘ │
│ │ │
│ ┌───────────────────────────────────┼───────────────────────────────────┐ │
│ ▼ ▼ ▼ │
│ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐│
│ │ MODULE 1: │ │ MODULE 2: │ │ MODULE 3: ││
│ │ In-Flight │ │ Exact SSIM │ │ Turnaround & ││
│ │ Overlaps │ │ Duplicates │ │ Leg Continuity││
│ └───────┬───────┘ └───────┬───────┘ └───────┬───────┘│
│ │ │ │ │
│ ▼ ▼ ▼ │
│ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐│
│ │ UTC Airborne │ │ 40-Field Hash │ │ Ground Turn & ││
│ │ Interval Test │ │ Group COUNT>1 │ │ Station Match ││
│ └───────┬───────┘ └───────┬───────┘ └───────┬───────┘│
│ │ │ │ │
│ └──────────────────────────────────┼──────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌────────────────────────────────────────────────────┐ │
│ │ Consolidated Integrity Report & Summary │ │
│ └────────────────────────────────────────────────────┘ │
└────────────────────────────────────────────────────────────────────────────────────────┘

Module 1: In-Flight Simultaneous Overlap Logic

1. Calendar Date Expansion: Expand active schedule records by matching operation_from, operation_to, and the 7-day day-of-week mask (days_operation).
2. UTC Timestamp Conversion: Dep UTC = Local Dep Time - Departure Station UTC Offset Arr UTC = Local Arr Time - Arrival Station UTC Offset + (date_variation × 24 hours)
3. Conflict Match: Flag record pairs sharing carrier, flight_number, and operating date where: (UTC_Dep_A < UTC_Arr_B) AND (UTC_Dep_B < UTC_Arr_A)
4. Overlap Calculation: Overlap Duration (Minutes) = min(UTC_Arr_A, UTC_Arr_B) - max(UTC_Dep_A, UTC_Dep_B)

Module 2: Exact Duplicate Record Logic

• Group records across all 40 SSIM Type 3 fields: HAVING COUNT(*) > 1

Module 3: Turnaround & Leg Continuity Logic

• For multi-leg flights (Leg_N and Leg_(N+1)):
  • Station Continuity: Check if Leg_N.Arrival_Station == Leg_(N+1).Departure_Station.
  • Ground Turn Feasibility: Check if (Leg_(N+1).Dep_UTC - Leg_N.Arr_UTC) >= Station Minimum Turn Time.

Standard Output Tables & Deliverables

Table 1: In-Flight Simultaneous Overlaps

Flight Number	Date	Leg 1 Route	Leg 1 Local Times	Leg 1 UTC Times	Leg 2 Route	Leg 2 Local Times	Leg 2 UTC Times	Overlap Duration
AM 0408	2025-07-15	MEX-JFK	07:00 / 13:15	13:00 / 17:15	NLU-MIA	11:30 / 16:00	17:30 / 20:00	No Overlap
AM 0612	2025-08-04	MEX-GDL	18:00 / 19:25	00:00 / 01:25	MTY-CUN	18:15 / 21:05	00:15 / 02:05	70 mins

Table 2: Exact Duplicate SSIM Records

Flight Number	Leg Seq	Route	Service Type	Date Range	Op Days	Local Dep / Arr	Duplicate Count
AM 0120	1	MEX-MTY	J	2025-06-01 - 2025-10-25	1234567	06:00 / 07:40	2

Table 3: Ground Turnaround Violations & Leg Discontinuities

Flight Number	Date	Inbound Leg	Inbound Arr (UTC)	Outbound Leg	Outbound Dep (UTC)	Scheduled Turn	Required Turn	Deficit
LH 0450	2025-06-10	MUC-FRA	09:15	FRA-LAX	09:40	25 mins	45 mins	-20 mins

Executive Summary Template

1. Integrity Score / Hygiene Rating: (e.g., Passed, Minor Warnings, Critical Action Required).
2. Conflict Breakdown: Total records analyzed vs. total conflicting pairs detected across each audit module.
3. Operational Impact: Summary of risks (GDS rejection, double-booking, infeasible aircraft rotations).
4. Remediation Plan: Recommended SSIM Type 3 record modifications or flight renumbering actions.