« Back to Index

Weather

Weather and Aviation

Weather is the #1 cause of flight delays globally, responsible for ~30–40% of all delay minutes (FAA data). Understanding weather patterns is critical for delay analysis.

Weather Variables Tracked

Weather Condition Categories

Weather Impact Scaling

Weather Data Model (dim_weather)

Adversity Classification Logic

def classify_adverse_weather(row):
    """Return True if weather is likely to cause delays."""
    return (
        row["visibility_km"] < 3.0
        or row["wind_speed_kmh"] > 50
        or row["precipitation_mm"] > 5
        or row["condition"] in ["Thunderstorm", "Snow", "Fog", "Blizzard"]
    )

This is_adverse flag is a derived column in Silver/Gold layers that makes weather-delay correlation queries simple:

SELECT
    is_adverse,
    AVG(delay_minutes) AS avg_delay,
    COUNT(*) AS flight_count
FROM fact_flights
JOIN dim_weather ON fact_flights.weather_key = dim_weather.weather_key
GROUP BY is_adverse;

Weather Data Sources

Recommended for this project: Open-Meteo — free, no API key, global coverage.

# Open-Meteo API example (no API key required)
import requests

url = "https://api.open-meteo.com/v1/forecast"
params = {
    "latitude": 40.6413,  # JFK
    "longitude": -73.7781,
    "hourly": ["temperature_2m", "relative_humidity_2m", "wind_speed_10m", "visibility"],
    "timezone": "UTC",
}
response = requests.get(url, params=params)
weather_data = response.json()

Temporal Joining Strategy

Weather data is typically hourly. Flights have specific departure/arrival timestamps. Strategy:

1. Store weather at hourly granularity in Silver
2. When building fact_flights, join the nearest hourly weather observation within ±1 hour of scheduled departure

# Polars temporal join: nearest weather for each flight
flights_with_weather = flights.sort("scheduled_departure").join_asof(
    weather.sort("observation_time"),
    left_on="scheduled_departure",
    right_on="observation_time",
    tolerance="1h",  # within 1 hour
    strategy="nearest",
)

Seasonal Weather Patterns

These patterns feed into seasonal trend Gold tables.

Weather Delay Quantification

-- DuckDB: Measure delay impact per weather condition
SELECT
    w.condition,
    COUNT(*) AS flights,
    ROUND(AVG(f.delay_minutes), 1) AS avg_delay,
    ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY f.delay_minutes), 1) AS p95_delay,
    ROUND(SUM(CASE WHEN f.status = 'C' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS cancellation_rate
FROM fact_flights f
JOIN dim_weather w ON f.weather_key = w.weather_key
GROUP BY w.condition
ORDER BY avg_delay DESC;

Data Quality Notes for Weather

• Missing observations: forward-fill from nearest station
• Extreme values: validate -80 <= temperature <= 60
• Station ↔ Airport mapping: manual lookup table
• Time zones: all weather timestamps must be UTC

# Weather validation rules
def validate_weather_row(row) -> bool:
    return (
        -80 <= row["temperature_c"] <= 60
        and 0 <= row["humidity_pct"] <= 100
        and 0 <= row["wind_speed_kmh"] <= 300
        and 0 <= row["visibility_km"] <= 100
        and 800 <= row["pressure_hpa"] <= 1100
    )

« Back to Index