Route-level aggregations and the final transform

Route metrics use the same window shape as carrier metrics. Same partition pattern, different keys: (year, month, route) and (year, hour, route). Reusing the pattern means the aggregation function is just a copy with the keys swapped.

transform.py
python
def route_analysis(df: DataFrame) -> DataFrame:
    """Aggregate by year/month/hour/route with all delay + flight metrics."""
    return df.groupBy("year", "month", "hour", "route").agg(
        mean("arr_delay").alias("route_year_month_hour_avg_arr_delay"),
        mean("route_year_month_avg_arr_delay").alias("route_year_month_avg_arr_delay"),
        mean("route_year_hour_avg_arr_delay").alias("route_year_hour_avg_arr_delay"),
        mean("dep_delay").alias("route_year_month_hour_avg_dep_delay"),
        mean("route_year_month_avg_dep_delay").alias("route_year_month_avg_dep_delay"),
        mean("route_year_hour_avg_dep_delay").alias("route_year_hour_avg_dep_delay"),
        mean("total_delay").alias("route_year_month_hour_avg_total_delay"),
        mean("route_year_month_avg_total_delay").alias("route_year_month_avg_total_delay"),
        mean("route_year_hour_avg_total_delay").alias("route_year_hour_avg_total_delay"),
        count("flight").alias("route_year_month_hour_total_flights"),
    )

The route aggregation collapses to (year, month, hour, route) and aliases each window mean. The naming convention encodes the partition key, which makes the downstream Athena queries readable.

The full pipeline composes four functions: coerce types, fill nulls, add derived columns, add window features. Then split into carrier and route aggregations. Each stage is independently testable and the order is intentional.

transform.py
python
def transform(df: DataFrame) -> tuple[DataFrame, DataFrame]:
    """Run the full transformation chain. Returns (carrier_df, route_df)."""
    df = coerce_types(df)
    df = fill_nulls(df)
    df = add_derived_columns(df)
    df = add_window_features(df)
    return carrier_analysis(df), route_analysis(df)

The single public function the Glue job calls. Notice the return is a tuple: carrier and route DataFrames. The caller decides where to write each.

Side effects belong in the entry point, not the transform. The transform is pure: same input, same output, no I/O. That makes it testable, runnable locally, and importable from both main.py and main_local.py without dragging awsglue along.

Ordering exercise: Order the pipeline stages

Loading practice…

Checkpoint: Transform module checkpoint

Loading practice…