Derived Analytics

Derived Analytics Pipeline

This subskill cannot be run independently. It must be invoked from the aviation-installer router after adsb-ingestion (and optionally flight-schedules) completes.

Deploys 13 Dynamic Tables, 4 views, monitoring tables, refresh procedures, and operational KPI placeholders. This is the analytics layer that powers all dashboard pages.

Pipeline Architecture

ADSB_DATA (Gold layer from adsb-ingestion)
  + FLIGHT_SCHEDULE (optional, from flight-schedules)
  + PROPERTIES_GATES / PROPERTIES_RUNWAYS (from base-setup)
    |
    v
ADSB_DATA_LOCAL (DT, DOWNSTREAM — bbox-filtered local positions)
    |
    +---> GATE_ANALYSIS_AIRCRAFT_GROUND_SESSIONS (DT, 1 HOUR)
    |         +---> GATE_ANALYSIS_ADSB_GROUND_POINTS (DT, DOWNSTREAM)
    |         +---> GATE_ANALYSIS_FLIGHT_GATE_TIME (DT, DOWNSTREAM)
    |         +---> GATE_ANALYSIS_GATE_UTIL_DAILY (DT, 1 HOUR)
    |         +---> GATE_ANALYSIS_GATE_AIRLINE_DWELL_DAILY (DT, 1 HOUR)
    |         +---> GATE_ANALYSIS_FLIGHT_DWELL_WITH_AIRLINE (DT, 1 HOUR)
    |
    +---> FLIGHT_TRAFFIC_FACT_ADSB_DAILY (DT, 1 HOUR)
    +---> FLIGHT_TRAFFIC_FACT_ADSB_HOURLY (DT, 1 HOUR)
    +---> FLIGHT_TRACKER_FLIGHT_LIST (DT, 1 HOUR)
    +---> FLIGHT_TRAFFIC_FACT_AIRLINE_TRAFFIC_DAILY (DT, 1 HOUR)
    +---> FLIGHT_TRAFFIC_FACT_AIRLINE_DELAY_DAILY (DT, 1 HOUR)
    +---> RUNWAY_CROSSINGS_DETAILED (DT, 1 HOUR)

Prerequisites

• adsb-ingestion completed (ADSB_DATA table exists, even if empty)
• PROPERTIES_AIRPORT, PROPERTIES_GATES, PROPERTIES_RUNWAYS all exist
• Variables from router: {TARGET_DB}, {SCHEMA}, {WAREHOUSE}, {IATA}

Required Privileges

Configuration

Friction Logging

Report all friction points (errors, warnings, workarounds, race conditions) back to the parent installer using the F1/F2/F3 format from .cortex/skills/logs/README.md. The parent writes the consolidated friction log. If executing standalone, write to .cortex/skills/logs/friction-log_{YYYY-MM-DD}_{HH-MM}.md.

Workflow

Read the references/ subfiles for complete SQL:

• references/01-adsb-data-local.md — Foundation DT with VEHICLE_CATEGORY
• references/02a-gate-sessions-dts.md — Gate analysis DTs 1-3 (sessions, ground points, flight gate time)
• references/02b-gate-utilization-dts.md — Gate analysis DTs 4-6 (utilization, airline dwell, flight dwell)
• references/03-traffic-dts.md — 5 traffic fact DTs + flight tracker
• references/04-runway-crossings.md — RUNWAY_CROSSINGS_DETAILED DT
• references/05-views-and-tables.md — HELPER_LANDING_LIVE_TIMETABLE, monitoring tables, placeholders
• references/06a-procedures.md — PROC_REFRESH_DERIVED, PROC_SMOKE_CHECK, PROC_REFRESH_ANALYTICS, PROC_RESUME_OPTIONAL_TASK
• references/06b-tasks.md — Task CREATE statements (no COMMENT on AFTER tasks) + ALTER TAG
• references/06c-operations.md — DT refresh, DT resume, install-time calls, verification
• references/07-tsa-checkpoint-geo.md — V_TSA_THROUGHPUT_CLEAN view (REQUIRED by TSA dashboard KPIs/charts) + V_TSA_CHECKPOINT_GEO view (TSA throughput mapped to terminal geometries)

Execute ALL SQL from each file in order. Do NOT skip or optimize away any queries.

Step 0: Set Query Tag

ALTER SESSION SET query_tag = '{"origin":"sf_sit-is-aviation","name":"oss-aviation-derived-analytics","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}';

Step 1: Create ADSB_DATA_LOCAL Dynamic Table

Foundation DT — filters ADSB_DATA to the airport bounding box using PROPERTIES_AIRPORT geometry. All other DTs depend on this.

CREATE OR REPLACE DYNAMIC TABLE {TARGET_DB}.{SCHEMA}.ADSB_DATA_LOCAL
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = {WAREHOUSE}
  INITIALIZE = ON_SCHEDULE
  COMMENT = '{"origin":"sf_sit-is-aviation","name":"oss-aviation-derived-analytics","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}'
  AS
  SELECT a.*
  FROM {TARGET_DB}.{SCHEMA}.ADSB_DATA a
  CROSS JOIN {TARGET_DB}.{SCHEMA}.PROPERTIES_AIRPORT p
  WHERE ST_CONTAINS(p.AIRPORT_BBOX, ST_MAKEPOINT(a.LON, a.LAT));

Step 2: Create Gate Analysis Dynamic Tables (6 DTs)

In dependency order:

1. GATE_ANALYSIS_AIRCRAFT_GROUND_SESSIONS — detects continuous on-ground periods per aircraft using LAG-based state change detection
2. GATE_ANALYSIS_ADSB_GROUND_POINTS — filters ground-phase ADS-B points with H3 index
3. GATE_ANALYSIS_FLIGHT_GATE_TIME — assigns nearest gate to each ground session
4. GATE_ANALYSIS_GATE_UTIL_DAILY — daily gate utilization metrics (occupancy %, flights, avg dwell)
5. GATE_ANALYSIS_GATE_AIRLINE_DWELL_DAILY — dwell minutes per gate per airline per day
6. GATE_ANALYSIS_FLIGHT_DWELL_WITH_AIRLINE — per-flight gate dwell enriched with airline name

Step 3: Create Traffic Fact Dynamic Tables (4 DTs)

1. FLIGHT_TRAFFIC_FACT_ADSB_DAILY — daily arrival/departure counts, on-time rates
2. FLIGHT_TRAFFIC_FACT_ADSB_HOURLY — hourly traffic volume by hour-of-day
3. FLIGHT_TRACKER_FLIGHT_LIST — deduplicated flight list for tracker page dropdown
4. FLIGHT_TRAFFIC_FACT_AIRLINE_TRAFFIC_DAILY — per-airline daily traffic breakdown
5. FLIGHT_TRAFFIC_FACT_AIRLINE_DELAY_DAILY — per-airline delay analytics (avg, median, p95)

Step 4: Create Runway Crossings Dynamic Table

RUNWAY_CROSSINGS_DETAILED — detects aircraft crossing runway polygons at taxi speeds (speed ≤ 45 kts, duration ≤ 120s, proximity ≤ 220m to runway centroid). Enriched with airline, direction, gate correlation.

Step 5: Create Monitoring Tables and Views

• HELPER_LANDING_LIVE_TIMETABLE — view for live timetable widget (joins ADSB_DATA_LOCAL + FLIGHT_SCHEDULE + gate data)
• H2H_CONFLICT_PAIRS — head-to-head conflict placeholder table
• V_AIR_OPS_TIMELINE — operational timeline view (placeholder structure)
• V_AIR_OPS_DAILY_KPIS — daily KPI view (placeholder structure for Performance page)
• V_TSA_THROUGHPUT_CLEAN — typed/cleaned TSA throughput (REQUIRED by the TSA dashboard page; airport-agnostic, parses date/hour/passengers, drops misaligned-extraction rows)
• V_TSA_CHECKPOINT_GEO — TSA checkpoint throughput mapped to terminal building geometries (fuzzy-matched from PROPERTIES_INFRASTRUCTURE)

Step 6: Create Refresh Procedures

• PROC_REFRESH_DERIVED() — calls ALTER DYNAMIC TABLE ... REFRESH on all 13 DTs
• PROC_REFRESH_ANALYTICS() — runs QA checks, updates HELPER_QA_COUNTS_DAILY and HELPER_MONITOR_LAST_REFRESH
• PROC_SMOKE_CHECK(STRING) — JavaScript procedure that runs validation queries and returns pass/fail summary
• PROC_RESUME_OPTIONAL_TASK(STRING) — helper to safely resume a task only if it exists

Step 7: Create Remaining DAG Tasks

CREATE TASK {TARGET_DB}.{SCHEMA}.TASK_REFRESH_DERIVED
  WAREHOUSE = {WAREHOUSE}
  COMMENT = '{"origin":"sf_sit-is-aviation","name":"oss-aviation-derived-analytics","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}'
  AFTER {TARGET_DB}.{SCHEMA}.TASK_ENRICH_ADSB
  AS CALL {TARGET_DB}.{SCHEMA}.PROC_REFRESH_DERIVED();

CREATE TASK {TARGET_DB}.{SCHEMA}.TASK_REFRESH_ANALYTICS
  WAREHOUSE = {WAREHOUSE}
  COMMENT = '{"origin":"sf_sit-is-aviation","name":"oss-aviation-derived-analytics","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}'
  AFTER {TARGET_DB}.{SCHEMA}.TASK_REFRESH_DERIVED
  AS CALL {TARGET_DB}.{SCHEMA}.PROC_REFRESH_ANALYTICS();

Step 8: Trigger Initial DT Refresh

ALTER DYNAMIC TABLE {TARGET_DB}.{SCHEMA}.ADSB_DATA_LOCAL REFRESH;
ALTER DYNAMIC TABLE {TARGET_DB}.{SCHEMA}.GATE_ANALYSIS_AIRCRAFT_GROUND_SESSIONS REFRESH;
ALTER DYNAMIC TABLE {TARGET_DB}.{SCHEMA}.FLIGHT_TRAFFIC_FACT_ADSB_DAILY REFRESH;
ALTER DYNAMIC TABLE {TARGET_DB}.{SCHEMA}.RUNWAY_CROSSINGS_DETAILED REFRESH;

Step 9: Verify Pipeline

SHOW DYNAMIC TABLES IN {TARGET_DB}.{SCHEMA};
SELECT "name", "scheduling_state", "data_timestamp"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY "name";

Expected: All DTs in SCHEDULED or EXECUTING state. data_timestamp will be NULL until first refresh completes.

Stopping Points

• After Step 1: Confirm ADSB_DATA_LOCAL is created (SHOW DYNAMIC TABLES LIKE 'ADSB_DATA_LOCAL' IN {TARGET_DB}.{SCHEMA})
• After Step 4: Confirm all 13 DTs exist
• After Step 8: Wait 2–5 minutes, then re-run Step 9 to confirm DATA_TIMESTAMP is populated

Troubleshooting

Return to Router

After completing all steps, return to the aviation-installer router to proceed with Step 7 (Start Task DAG).

Cleanup

Derived analytics creates 13 Dynamic Tables and multiple views/procedures. Use the aviation-cleanup skill which reads .cortex/skills/aviation-cleanup/references/drop-order.sql to tear down objects in the correct dependency order.

For manual cleanup, suspend tasks first:

ALTER TASK IF EXISTS {TARGET_DB}.{SCHEMA}.TASK_REFRESH_DERIVED SUSPEND;
ALTER TASK IF EXISTS {TARGET_DB}.{SCHEMA}.TASK_REFRESH_ANALYTICS SUSPEND;

Then drop objects in reverse dependency order — see .cortex/skills/aviation-cleanup/references/drop-order.sql for the full sequence.