Query 5: Fleet Health Dashboard

The most comprehensive federated query, combining all data sources into a single fleet health view using both federation methods simultaneously.

Federation Method: Hybrid using remote_query() + Spark Connector + Delta tables

Data Flow

Graph Traversal Metric (remote_query)

A standalone remote_query() call provides a fleet-wide graph metric:

SELECT * FROM remote_query('neo4j_uc_connection',
    query => 'SELECT COUNT(*) AS cnt
              FROM Flight f
              NATURAL JOIN DEPARTS_FROM r
              NATURAL JOIN Airport a')

Full Dashboard Query

Uses the neo4j_maintenance and neo4j_flights temp views loaded via Spark Connector in previous queries:

WITH aircraft_ref AS (
    SELECT `:ID(Aircraft)` AS aircraft_id, tail_number, model, manufacturer, operator
    FROM aircraft
),
sensor_stats AS (
    SELECT
        sys.aircraft_id,
        ROUND(AVG(CASE WHEN sen.type = 'EGT' THEN r.value END), 1) AS avg_egt,
        ROUND(AVG(CASE WHEN sen.type = 'Vibration' THEN r.value END), 4) AS avg_vib,
        ROUND(AVG(CASE WHEN sen.type = 'FuelFlow' THEN r.value END), 2) AS avg_fuel,
        COUNT(*) AS reading_count
    FROM sensor_readings r
    JOIN sensors sen ON r.sensor_id = sen.`:ID(Sensor)`
    JOIN systems sys ON sen.system_id = sys.`:ID(System)`
    GROUP BY sys.aircraft_id
),
maint AS (
    SELECT aircraft_id, COUNT(*) AS events,
           SUM(CASE WHEN severity = 'CRITICAL' THEN 1 ELSE 0 END) AS critical
    FROM neo4j_maintenance
    GROUP BY aircraft_id
),
flights AS (
    SELECT aircraft_id, COUNT(*) AS flight_count
    FROM neo4j_flights
    GROUP BY aircraft_id
)
SELECT
    a.tail_number,
    a.model,
    a.operator,
    COALESCE(f.flight_count, 0) AS flights,
    COALESCE(m.events, 0) AS maint_events,
    COALESCE(m.critical, 0) AS critical,
    s.avg_egt AS egt_c,
    s.avg_vib AS vib_ips,
    s.avg_fuel AS fuel_kgs,
    s.reading_count AS readings
FROM aircraft_ref a
LEFT JOIN flights f ON a.aircraft_id = f.aircraft_id
LEFT JOIN maint m ON a.aircraft_id = m.aircraft_id
LEFT JOIN sensor_stats s ON a.aircraft_id = s.aircraft_id
ORDER BY COALESCE(m.critical, 0) DESC, COALESCE(m.events, 0) DESC

What This Query Does

remote_query(): Graph traversal counting Flight→DEPARTS_FROM→Airport connections (fleet-wide metric)
Spark Connector temp views: Per-aircraft maintenance events (with severity breakdown) and flight counts from Neo4j
Delta tables: Per-aircraft sensor aggregates across all sensor types (EGT, vibration, fuel flow)
Federation: LEFT JOINs everything on aircraft_id, sorted by critical maintenance events descending

Actual Output (Databricks Runtime 17.3 LTS)

The following output was captured from a live Databricks cluster, combining remote_query() graph traversals, Spark Connector temp views, and Delta lakehouse tables in a single federated query:

Graph traversal (Flight)-[:DEPARTS_FROM]->(Airport): 800 connections

Fleet Health Dashboard
Delta: sensor_readings, sensors, systems, aircraft
Neo4j: MaintenanceEvent + Flight (Spark Connector), graph traversal (remote_query)
====================================================================================================
+-----------+--------+-----------+-------+------------+--------+-----+-------+--------+--------+
|tail_number|model   |operator   |flights|maint_events|critical|egt_c|vib_ips|fuel_kgs|readings|
+-----------+--------+-----------+-------+------------+--------+-----+-------+--------+--------+
|N53032E    |B737-800|ExampleAir |27     |25          |8       |671.6|0.266  |1.39    |17280   |
|N54980C    |A321neo |RegionalCo |34     |20          |8       |671.6|0.266  |1.39    |17280   |
|N76758N    |A320-200|SkyWays    |46     |26          |7       |671.6|0.2659 |1.39    |17280   |
|N30268B    |A320-200|SkyWays    |41     |18          |6       |671.6|0.2659 |1.39    |17280   |
|N86057G    |A321neo |RegionalCo |51     |18          |6       |671.6|0.2659 |1.39    |17280   |
|N84110I    |B737-800|ExampleAir |43     |17          |6       |671.6|0.2659 |1.39    |17280   |
|N37272D    |E190    |NorthernJet|30     |15          |6       |671.6|0.266  |1.39    |17280   |
|N81338F    |A320-200|SkyWays    |45     |14          |6       |671.6|0.2659 |1.39    |17280   |
|N13211H    |E190    |NorthernJet|39     |13          |6       |671.6|0.266  |1.39    |17280   |
|N26760M    |B737-800|ExampleAir |30     |13          |6       |671.6|0.2659 |1.39    |17280   |
|N44342Q    |B737-800|ExampleAir |41     |13          |5       |671.6|0.2658 |1.39    |17280   |
|N89365K    |A321neo |RegionalCo |48     |12          |5       |671.6|0.2659 |1.39    |17280   |
|N95040A    |B737-800|ExampleAir |47     |11          |5       |671.6|0.2659 |1.39    |17280   |
|N84703L    |E190    |NorthernJet|40     |10          |5       |671.6|0.2659 |1.39    |17280   |
|N46224T    |E190    |NorthernJet|40     |18          |4       |671.6|0.2661 |1.39    |17280   |
|N96107S    |A321neo |RegionalCo |41     |15          |3       |671.6|0.2659 |1.39    |17280   |
|N32276J    |A320-200|SkyWays    |33     |12          |3       |671.6|0.266  |1.39    |17280   |
|N65164O    |A321neo |RegionalCo |30     |9           |3       |671.7|0.2659 |1.39    |17280   |
|N15332P    |E190    |NorthernJet|46     |14          |2       |671.6|0.2658 |1.39    |17280   |
|N63098R    |A320-200|SkyWays    |48     |7           |1       |671.6|0.2658 |1.39    |17280   |
+-----------+--------+-----------+-------+------------+--------+-----+-------+--------+--------+

Why This Pattern Matters

This query demonstrates that both federation methods can coexist in a single analysis:

remote_query() for fleet-wide graph traversal metrics (no cluster library needed)
Spark Connector for row-level Neo4j data that needs GROUP BY aggregation
Delta tables as the foundation for time-series sensor analytics

The same dual-source pattern used in AgentBricks (Lab 6) works directly in SQL without AI agent routing.