# Derived Analytics

> Create the airport analytics Dynamic Table pipeline: gate analysis (6 DTs), traffic facts (4 DTs), runway crossings, flight tracker, monitoring views, operational KPIs, and the full task DAG. Subskill of aviation-installer — must be invoked from the router after ADS-B ingestion completes. Use when: deploying derived analytics layer as part of installation. Do NOT use for: standalone execution, base setup, ADS-B ingestion. Triggers: derived analytics, deploy derived analytics, dynamic tables, setup dynamic tables, gate analysis, runway crossings, runway crossings detection, traffic analysis, airport analytics pipeline, DT pipeline.

- Skill: `snowflake-labs/derived-analytics` (Agent Skill, multi-file: 11 files)
- Install (CLI): `npx skillmds@latest add snowflake-labs/derived-analytics`
- Raw SKILL.md: https://api.skillmd.com/api/skills/snowflake-labs/derived-analytics/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: snowflake-labs (https://skillmd.com/u/snowflake-labs)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/snowflake-labs/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

| Privilege | Scope | Reason |
|-----------|-------|--------|
| CREATE DYNAMIC TABLE | Schema | Creates 13 Dynamic Tables |
| CREATE VIEW | Schema | Creates 3 operational views |
| CREATE TABLE | Schema | Creates monitoring and placeholder tables |
| CREATE PROCEDURE | Schema | Creates PROC_REFRESH_DERIVED, PROC_REFRESH_ANALYTICS, PROC_SMOKE_CHECK |
| CREATE TASK | Schema | Creates TASK_REFRESH_DERIVED and TASK_REFRESH_ANALYTICS |
| USAGE ON WAREHOUSE | Warehouse | All Dynamic Tables use {WAREHOUSE} |

## Configuration

| Parameter | Default | Description |
|-----------|---------|-------------|
| DT_LAG_GATE | 1 HOUR | Refresh lag for gate analysis DTs |
| DT_LAG_TRAFFIC | 1 HOUR | Refresh lag for traffic fact DTs |
| DT_INIT | ON_SCHEDULE | Initialization mode for all DTs |
| CROSSING_MAX_SPEED | 45 | Max knots for runway crossing detection |
| CROSSING_MAX_SEC | 120 | Max seconds for a runway crossing event |
| CROSSING_MAX_DIST_M | 220 | Max meters proximity to runway centroid |

## 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

```sql
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.

```sql
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

```sql
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

```sql
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

```sql
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

| Error | Cause | Fix |
|-------|-------|-----|
| DT creation fails with "insufficient privileges" | Missing privilege | Ensure role has CREATE DYNAMIC TABLE on schema |
| ADSB_DATA_LOCAL empty after refresh | No source data | Check ADSB_DATA has rows; verify bbox in PROPERTIES_AIRPORT |
| Gate analysis DTs empty | Upstream dependency | GATE_ANALYSIS depends on ADSB_DATA_LOCAL; wait for initial data load |
| RUNWAY_CROSSINGS_DETAILED empty | Missing runway config | Requires PROPERTIES_RUNWAYS to have rows; verify runway detection thresholds |
| DTs stuck in EXECUTING | Warehouse too small | Check warehouse size — large airports may need MEDIUM or LARGE |
| V_AIR_OPS_DAILY_KPIS returns no data | Placeholder views | These are placeholder views; data populates once pipeline has multiple days of history |

## 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:

```sql
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.