| 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
| Privilege | Scope | Reason |
|---|
| CREATE NETWORK RULE | Database | Creates rule for api.aviationstack.com |
| CREATE INTEGRATION | Account | Creates EAI for Aviationstack API |
| CREATE SECRET | Schema | Stores API key securely |
| CREATE PROCEDURE | Schema | Creates 5 ingestion/ETL procedures |
| CREATE TASK | Schema | Creates TASK_FLIGHT_SCHEDULE |
Configuration
| Parameter | Default | Description |
|---|
| API_ENDPOINT | http://api.aviationstack.com/v1/flights | Aviationstack REST endpoint |
| BACKFILL_DAYS | 5 | Days of schedule history to backfill |
| TASK_PARENT | TASK_INGEST_ADSB | Parent task for DAG chaining |
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, secretreferences/02-procedures.md — All 5 ingestion/ETL/backfill proceduresreferences/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 callFLIGHT_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_RAWPROC_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 historyPROC_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
| Error | Cause | Fix |
|---|
| EAI creation fails | Insufficient privileges | ACCOUNTADMIN required for CREATE INTEGRATION |
| PROC_INGEST fails with 403 | Invalid API key | API key may be invalid or rate-limited; check Aviationstack dashboard |
| FLIGHT_SCHEDULE empty after backfill | Limited coverage | Some airports have limited Aviationstack coverage; try major IATA codes (SAN, LAX, JFK) |
| HTTP 80 blocked | Wrong port in network rule | Check network rule uses PORT 80 not 443 (Aviationstack uses plain HTTP) |
| Secret value redacted in logs | Expected behavior | Installer masks SECRET_STRING literals in UI display |
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();
