Scenario

You are a data analyst at a major airline with three tables containing flight records, airport details, and plane specifications.

Task

Write a Snowflake SQL query that:

1. Joins {{ ref("flights") }} to {{ ref("airports") }} twice (once for the origin airport, once for the destination airport) using table aliases
2. Joins {{ ref("flights") }} to {{ ref("planes") }} on flight_id = plane_id
3. Computes origin_airport_name_length as the length of the trimmed origin airport name
4. Computes destination_airport_name_length as the length of the trimmed destination airport name
5. Computes plane_model_length as the length of the trimmed plane model name
6. Returns flight_id, origin_airport_name_length, destination_airport_name_length, and plane_model_length

Schema

flights

airports

planes

Example

flights:

airports:

planes:

Expected Output:

Note: Lengths are computed after trimming whitespace. For example, "John F Kennedy" has 14 characters, "Los Angeles" has 11, and "Boeing 787" has 10.

WITH joined AS (
    SELECT
        f.flight_id,
        LENGTH(TRIM(orig.airport_name)) AS origin_airport_name_length,
        LENGTH(TRIM(dest.airport_name)) AS destination_airport_name_length,
        LENGTH(TRIM(pl.plane_model))    AS plane_model_length
    FROM {{ ref("flights") }} f
    LEFT JOIN {{ ref("airports") }} orig
        ON f.origin_airport = orig.airport_id
    LEFT JOIN {{ ref("airports") }} dest
        ON f.destination_airport = dest.airport_id
    LEFT JOIN {{ ref("planes") }} pl
        ON f.flight_id = pl.plane_id
)
SELECT * FROM joined

Explanation

Step 1: Joining airports twice with aliases

FROM {{ ref("flights") }} f
LEFT JOIN {{ ref("airports") }} orig
    ON f.origin_airport = orig.airport_id
LEFT JOIN {{ ref("airports") }} dest
    ON f.destination_airport = dest.airport_id

When a table needs to be joined on two different columns, you join it twice using distinct aliases. Here orig resolves the origin airport and dest resolves the destination airport. LEFT JOIN preserves all flight rows even if an airport ID has no match.

Step 2: Joining planes on flight_id = plane_id

LEFT JOIN {{ ref("planes") }} pl
    ON f.flight_id = pl.plane_id

The flight_id in the flights table corresponds to the plane_id in the planes table. A LEFT JOIN ensures flights without a matching plane still appear in the output.

Step 3: Computing trimmed name lengths

LENGTH(TRIM(orig.airport_name)) AS origin_airport_name_length

TRIM strips leading and trailing whitespace from the string. LENGTH then counts the remaining characters. This two-function pattern is common when source data may contain extra spaces.

Step 4: Alternative without CTE

You can write the same logic as a flat query without a CTE:

SELECT
    f.flight_id,
    LENGTH(TRIM(orig.airport_name)) AS origin_airport_name_length,
    LENGTH(TRIM(dest.airport_name)) AS destination_airport_name_length,
    LENGTH(TRIM(pl.plane_model))    AS plane_model_length
FROM {{ ref("flights") }} f
LEFT JOIN {{ ref("airports") }} orig
    ON f.origin_airport = orig.airport_id
LEFT JOIN {{ ref("airports") }} dest
    ON f.destination_airport = dest.airport_id
LEFT JOIN {{ ref("planes") }} pl
    ON f.flight_id = pl.plane_id

Both approaches produce identical results. The CTE version is easier to extend if you need additional transformation steps.