Airports
Airport Classification
By Passenger Volume
| Category | Annual Passengers | Examples |
|---|---|---|
| Mega-hub | >70M | ATL, DXB, PEK |
| Major hub | 30M–70M | JFK, LHR, SIN |
| Mid-size | 10M–30M | MUC, BCN, SEA |
| Regional | 1M–10M | BRS, OPO, MSY |
| Small | <1M | Most regional airports |
By Role
| Role | Description |
|---|---|
| Primary Hub | Airline's main base (ATL for Delta) |
| Secondary Hub | Secondary focus city (MIA for American) |
| Focus City | Significant but not hub-level (AUS for Southwest) |
| Destination | Served but not strategic |
| Alternate | Backup airport for diversions |
Airport Data Model (dim_airport)
| Field | Type | Description | Example |
|---|---|---|---|
airport_key |
INTEGER (SK) | Surrogate key generated by warehouse | 1234 |
airport_code |
VARCHAR(3) | IATA 3-letter code | JFK |
airport_name |
VARCHAR(100) | Full name | John F Kennedy International |
city |
VARCHAR(50) | City name | New York |
country |
VARCHAR(50) | Country name | United States |
latitude |
DECIMAL(9,6) | Latitude | 40.6413 |
longitude |
DECIMAL(9,6) | Longitude | -73.7781 |
timezone |
VARCHAR(50) | IANA timezone string | America/New_York |
elevation_ft |
INTEGER | Elevation above sea level | 13 |
Airport Codes Used in This Project
Major Hubs Tracked
| Code | Airport | City | Country | Role |
|---|---|---|---|---|
| ATL | Hartsfield-Jackson | Atlanta | US | Delta hub |
| DXB | Dubai International | Dubai | UAE | Emirates hub |
| LAX | Los Angeles | Los Angeles | US | Multi-airline |
| LHR | London Heathrow | London | UK | British Airways hub |
| HND | Haneda | Tokyo | Japan | JAL/ANA hub |
| CDG | Charles de Gaulle | Paris | France | Air France hub |
| FRA | Frankfurt | Frankfurt | Germany | Lufthansa hub |
| SIN | Changi | Singapore | Singapore | Singapore Airlines hub |
| JFK | Kennedy | New York | US | JetBlue hub |
| ORD | O'Hare | Chicago | US | United hub |
Airport Runway Configuration Impact
Flight delays correlate with runway count and layout:
| Configuration | Impact |
|---|---|
| 4+ parallel runways | Best capacity, least delays |
| 2 crossing runways | Reduced capacity in crosswind |
| Single runway | Severe capacity limits (e.g., LGW) |
| Mountainous terrain | Approach restrictions, turbulence |
Airport Weather Sensitivity
| Airport | Primary Weather Risk |
|---|---|
| ATL | Thunderstorms (summer) |
| ORD | Snow + wind (winter) |
| SFO | Fog (low visibility, year-round) |
| LHR | Fog + wind (year-round) |
| DXB | Sandstorms, extreme heat |
| DEN | Snow + high-density altitude |
This data feeds into the weather impact Gold table.
Airport Capacity Metrics (Useful KPIs)
| Metric | Definition |
|---|---|
| ATMs/hour | Air Transport Movements per hour (capacity) |
| Delay per operation | Average delay per arrival/departure |
| Cancellation rate | % of flights cancelled at this airport |
| Diversion count | Flights diverted to this airport |
| Delay hotspot | Airport with delay > national avg |
-- Example: Find airports with above-average delays
SELECT
dim_airport.airport_code,
dim_airport.airport_name,
AVG(fact_flights.delay_minutes) AS avg_delay
FROM fact_flights
JOIN dim_airport ON fact_flights.departure_airport_key = dim_airport.airport_key
GROUP BY dim_airport.airport_code, dim_airport.airport_name
HAVING AVG(fact_flights.delay_minutes) > (
SELECT AVG(delay_minutes) FROM fact_flights
)
ORDER BY avg_delay DESC;
Great-Circle Distance Calculation
For route efficiency analysis, we calculate distance between airports:
import math
def haversine(lat1, lon1, lat2, lon2):
"""Calculate great-circle distance between two airports in km."""
R = 6371 # Earth radius in km
phi1 = math.radians(lat1)
phi2 = math.radians(lat2)
delta_phi = math.radians(lat2 - lat1)
delta_lambda = math.radians(lon2 - lon1)
a = (
math.sin(delta_phi / 2) ** 2
+ math.cos(phi1) * math.cos(phi2) * math.sin(delta_lambda / 2) ** 2
)
c = 2 * math.atan2(math.sqrt(a), math.sqrt(1 - a))
return R * c
# Example: JFK → LHR
distance = haversine(40.6413, -73.7781, 51.4700, -0.4543)
# ≈ 5,541 km
Airport Spatial Analysis
For the Gold layer's airport dashboard:
- Delay heatmap: Plot airports by lat/lon, color by avg delay
- Traffic distribution: Bubble chart by flight count
- Regional patterns: Group airports by continent/timezone
# Polars example: Compute per-airport delay statistics
import polars as pl
airport_delays = (
pl.scan_parquet("silver/flights/*.parquet")
.group_by("departure_airport")
.agg(
[
pl.col("delay_minutes").mean().alias("avg_delay"),
pl.col("delay_minutes").quantile(0.95).alias("p95_delay"),
pl.len().alias("flight_count"),
(pl.col("status") == "C").sum().alias("cancellations"),
]
)
.sort("avg_delay", descending=True)
.collect()
)
Airport Reference Dataset
The airports ingestion module pulls from OurAirports/OpenFlights:
- ~9,300 airports worldwide
- Filter to commercial airports only (type: large_airport, medium_airport)
- Store full metadata in Hugging Face
# Airports ingestion: Filter to relevant airports
airports_df = pl.read_csv("airports.dat")
commercial = airports_df.filter(pl.col("type").is_in(["large_airport", "medium_airport"]))
Airport In SCD Tracking
Airport details change rarely but need tracking:
- Type 1 SCD: Overwrite on change (name change, ICAO code)
- Type 2 SCD: Track history (runway additions, category upgrades)
For this project: Type 1 SCD is sufficient (low change rate).