flight-schedules

name: flight-schedules description: "Set up Aviationstack flight schedule ingestion: create schedule tables, network rule, external access integration with API key secret, Python ingestion procedure, ETL procedure, and scheduled task. Conditional subskill of aviation-installer — only runs when user provides an Aviationstack API key. Use when: configuring flight schedule data as part of installation with API key. Do NOT use for: standalone execution, ADS-B ingestion (use adsb-ingestion), derived analytics (use derived-analytics). Triggers: flight schedules, Aviationstack setup, schedule ingestion, flight timetable." depends_on: - aviation-installer - adsb-ingestion metadata: author: Snowflake SIT-IS version: 1.0.0 category: infrastructure

Flight Schedules Setup

This subskill cannot be run independently. It must be invoked from the aviation-installer router.

This subskill is conditional — only execute if the user provided an Aviationstack API key.

Creates the Aviationstack flight schedule ingestion pipeline: network rule, EAI, API key secret, schedule tables, Python ingestion procedure, ETL procedure, backfill procedures, and the TASK_FLIGHT_SCHEDULE task (chained after TASK_INGEST_ADSB).

Without this sub-skill, ADS-B tracks are still captured but won't be enriched with airline/route/gate metadata. The dashboard will show aircraft positions but flight details will be unavailable.

Prerequisites

• adsb-ingestion completed
• Aviationstack API key (free tier supports up to 1,000 requests/month)
• Variables from router: {TARGET_DB}, {SCHEMA}, {IATA}, {ICAO}, {WAREHOUSE}, {API_KEY}, {BACKFILL_DAYS}

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-network-and-tables.md — Network rule, EAI, secret
• references/02-procedures.md — All 5 ingestion/ETL/backfill procedures
• references/03-task-and-ops.md — Task definition + initial backfill call

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-flight-schedules","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}';

Step 1: Create Network Rule and EAI

CREATE OR REPLACE NETWORK RULE {TARGET_DB}.{SCHEMA}.{SCHEMA}_aviationstack_rule
  TYPE = HOST_PORT
  MODE = EGRESS
  VALUE_LIST = ('api.aviationstack.com:80')
  COMMENT = '{"origin":"sf_sit-is-aviation","name":"oss-aviation-flight-schedules","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}';

CREATE OR REPLACE SECRET {TARGET_DB}.{SCHEMA}.aviationstack_key
  TYPE = GENERIC_STRING
  SECRET_STRING = '{API_KEY}'
  COMMENT = '{"origin":"sf_sit-is-aviation","name":"oss-aviation-flight-schedules","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}';

Create EAI {EAI_AVIATIONSTACK} (e.g. AIRPORT_SAN_PUBLIC_AVIATIONSTACK_EAI) referencing the network rule and secret.

Step 2: Create Schedule Tables

• HELPER_FLIGHT_SCHEDULE_RAW — raw JSON responses from Aviationstack (Bronze layer), one row per API call
• FLIGHT_SCHEDULE — canonical flight schedule (Silver layer) with departure/arrival times, airline, route, status

Step 3: Create Ingestion Procedures

• PROC_INGEST_FLIGHT_SCHEDULE(VARCHAR, VARCHAR) — Python procedure that calls Aviationstack API for {IATA} airport, inserts raw JSON into HELPER_FLIGHT_SCHEDULE_RAW
• PROC_ETL_SCHEDULE_TO_FLIGHT_SCHEDULE() — SQL procedure that parses raw JSON → FLIGHT_SCHEDULE (deduplication by flight_date + flight_iata)
• PROC_BACKFILL_FLIGHT_SCHEDULE(INT) — backfills N days of schedule history
• PROC_BACKFILL_FLIGHT_SCHEDULE_WINDOW(INT, INT) — backfills a specific date range (days ago start/end)
• PROC_FLIGHT_SCHEDULE_INGEST_AND_ETL() — orchestrates ingest + ETL in sequence

Step 4: Create Task

CREATE TASK {TARGET_DB}.{SCHEMA}.TASK_FLIGHT_SCHEDULE
  WAREHOUSE = {WAREHOUSE}
  COMMENT = '{"origin":"sf_sit-is-aviation","name":"oss-aviation-flight-schedules","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}'
  AFTER {TARGET_DB}.{SCHEMA}.TASK_INGEST_ADSB
  AS CALL {TARGET_DB}.{SCHEMA}.PROC_FLIGHT_SCHEDULE_INGEST_AND_ETL();

Note: Do NOT resume this task here. The router uses PROC_RESUME_OPTIONAL_TASK to resume it after all tasks are ready.

Step 5: Trigger Initial Backfill

CALL {TARGET_DB}.{SCHEMA}.PROC_BACKFILL_FLIGHT_SCHEDULE({BACKFILL_DAYS});

Step 6: Verify

SELECT COUNT(*) AS RAW_ROWS FROM {TARGET_DB}.{SCHEMA}.HELPER_FLIGHT_SCHEDULE_RAW;
SELECT COUNT(*) AS SCHEDULE_ROWS FROM {TARGET_DB}.{SCHEMA}.FLIGHT_SCHEDULE;

Expected: > 0 rows if API key is valid and airport has scheduled service.

Stopping Points

• After Step 1: Confirm EAI exists (SHOW INTEGRATIONS LIKE '%AVIATIONSTACK%')
• After Step 5: Verify FLIGHT_SCHEDULE has rows (SELECT COUNT(*) FROM FLIGHT_SCHEDULE)

Troubleshooting

Return to Router

After completing all steps, return to the aviation-installer router and continue with derived-analytics.

Cleanup

Use the aviation-cleanup skill for automated tag-based teardown. Manual cleanup:

ALTER TASK IF EXISTS {TARGET_DB}.{SCHEMA}.TASK_FLIGHT_SCHEDULE SUSPEND;

DROP TASK IF EXISTS {TARGET_DB}.{SCHEMA}.TASK_FLIGHT_SCHEDULE;
DROP TABLE IF EXISTS {TARGET_DB}.{SCHEMA}.FLIGHT_SCHEDULE;
DROP TABLE IF EXISTS {TARGET_DB}.{SCHEMA}.HELPER_FLIGHT_SCHEDULE_RAW;
DROP PROCEDURE IF EXISTS {TARGET_DB}.{SCHEMA}.PROC_FETCH_FLIGHT_SCHEDULE();
DROP PROCEDURE IF EXISTS {TARGET_DB}.{SCHEMA}.PROC_FLIGHT_SCHEDULE_INGEST_AND_ETL();