| name | aviation-installer |
| description | Install and configure an airport analytics platform in Snowflake. Routes to sub-skills for base infrastructure setup, ADS-B real-time ingestion, flight schedule ingestion, TSA checkpoint throughput ingestion, and derived analytics pipelines. Use when: installing airport analytics, setting up a new airport, deploying aviation platform, provisioning airport database. Do NOT use for: deploying the Streamlit dashboard (use aviation-dashboard), cleaning up objects (use aviation-cleanup), viewing flight data. Triggers: install airport, setup airport, deploy aviation, provision airport, aviation installer, new airport setup, airport analytics platform. |
| metadata | {"author":"Snowflake SIT-IS","version":"1.0.0","category":"infrastructure"} |
Install Airport Analytics Platform
Routes installation requests to the correct sub-skills based on phase. Provisions a complete airport analytics platform in Snowflake: database infrastructure, real-time ADS-B ingestion from adsb.lol, optional flight schedules from Aviationstack, optional TSA checkpoint throughput from FOIA data, and derived Dynamic Table pipelines for gate analysis, traffic analytics, runway crossings, and operational KPIs.
Prerequisites
- Snowflake Account with ACCOUNTADMIN role (or equivalent privileges)
- Overture Maps Base dataset from Snowflake Marketplace (
OVERTURE_MAPS__BASE) — auto-installed in Step 1 if missing
- Warehouse available for installation and ongoing tasks
- Aviationstack API key (optional, for flight schedule ingestion)
Required Privileges
| Privilege | Scope | Reason |
|---|
| CREATE DATABASE | Account | Creates AIRPORT_{IATA} database |
| CREATE INTEGRATION | Account | Creates external access integrations for APIs |
| CREATE NETWORK RULE | Account | Creates network rules for adsb.lol, GitHub, Aviationstack |
| EXECUTE TASK | Account | Enables scheduled task execution |
| IMPORTED PRIVILEGES ON OVERTURE_MAPS__BASE | Database | Reads airport geometry, infrastructure, gates |
Configuration
| Parameter | Default | Description |
|---|
| AIRPORT | (user selects) | Target airport from Overture Maps inventory |
| WAREHOUSE | AVIA_{IATA}_WH | Dedicated warehouse (created automatically, XSMALL) |
| AVIATIONSTACK_KEY | (optional) | API key for flight schedule ingestion. Skip for a fully functional install without schedule matching. |
| TSA_THROUGHPUT | yes (default) | Enable TSA checkpoint throughput ingestion from FOIA data. No API key needed. |
| GIT_REPO_STAGE | @{TARGET_DB}.{SCHEMA}.AVIA_OPS_REPO/branches/main | Git repo stage for skill source files |
| BACKFILL_DAYS | 5 | Days of historical ADS-B data to backfill |
Re-installation Behavior
The installer detects existing airport databases before proceeding. When AIRPORT_{IATA} already exists, you will be prompted to choose:
- Update dashboard only -- redeploys the dashboard if a newer version is available; all data and pipelines remain untouched
- Skip -- leave the existing installation completely untouched
- Full reinstall -- destroys all accumulated data and recreates from scratch (irreversible)
Dashboard Location
The dashboard is deployed once and auto-discovers all AIRPORT_XXX databases. When installing a second (or third) airport, the installer detects the existing dashboard and updates it in-place rather than creating a duplicate. The dashboard always lives in whichever airport database was installed first.
Object Safety Reference
| Category | Pattern | Re-run Effect |
|---|
| Database, Warehouse, Schemas, Tags | IF NOT EXISTS | Safe -- no data loss |
| HELPER_INSTALL_AUDIT | IF NOT EXISTS + INSERT | Safe -- appends audit rows |
| PROPERTIES_*, ADSB_DATA, HELPER_ADSB_LOL_RAW | CREATE OR REPLACE | Destructive -- data wiped |
| 13 Dynamic Tables | CREATE OR REPLACE | Destructive -- analytics history lost |
| Procedures, Tasks, UDFs, Views | CREATE OR REPLACE | Safe -- stateless objects |
| Streamlit app, SPCS service | CREATE OR REPLACE / redeploy | Safe -- idempotent |
Error Logging
When any step fails or produces unexpected results, log the issue to .cortex/skills/logs/ following the format in .cortex/skills/logs/README.md. File name: aviation-installer_{YYYY-MM-DD}_{HH-MM}.md. Continue execution where possible.
Friction Logging
MANDATORY: After every execution (regardless of success or failure), generate a friction log in .cortex/skills/logs/. File name: friction-log_{YYYY-MM-DD}_{HH-MM}.md.
Follow the friction log template in .cortex/skills/logs/README.md. The log must capture:
- Exact wall-clock duration of each step (Step Timing table)
- Configuration parameters used
- Objects created counts and initial data row counts
- Any friction points with F1/F2/F3 numbering, each including: Step, Severity (High/Medium/Low), What happened, Resolution, Recommendation
- Verification checklist with pass/fail
- Summary with total execution time and overall outcome (SUCCESS / COMPLETED_WITH_ISSUES / FAILED)
If no friction was encountered, still create the log with "No friction points encountered." and all other sections filled.
Sub-skills executed via runSubagent must report friction points back to this parent skill for consolidation into the single friction log.
Workflow
Step 1: Set Query Tag and Record Start Time
Record the installation start time (store as {START_TIME}):
SELECT CURRENT_TIMESTAMP() AS install_start_time;
ALTER SESSION SET query_tag = '{"origin":"sf_sit-is-aviation","name":"oss-aviation-installer","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}';
Step 2: Install Marketplace Dependencies
Check if OVERTURE_MAPS__BASE exists. If not, install it from Snowflake Marketplace:
SHOW DATABASES LIKE 'OVERTURE_MAPS__BASE';
If no results:
CALL SYSTEM$ACCEPT_LEGAL_TERMS('DATA_EXCHANGE_LISTING', 'GZT0Z4CM1E9KV');
CREATE DATABASE IF NOT EXISTS OVERTURE_MAPS__BASE FROM LISTING GZT0Z4CM1E9KV;
Verify:
SELECT COUNT(*) FROM OVERTURE_MAPS__BASE.CARTO.INFRASTRUCTURE WHERE class ILIKE '%airport%' LIMIT 1;
Step 3: Select Airport
Goal: Help the user find and select their target airport from 22,000+ airports worldwide.
IMPORTANT: Do NOT run a full inventory query. Use a search-based flow instead.
3a. Ask the user which airport they want to install. Use the ask_user_question tool with a text input:
- Question: "Which airport do you want to install? Type an airport name, city, or IATA/ICAO code (e.g. 'San Diego', 'SAN', 'KSAN')."
- Default value: "" (empty)
3b. Run a filtered search using their input. Replace {SEARCH} with the user's text:
Read references/airport-search-query.sql for the full query. Replace {SEARCH} with the user's input before executing.
3c. Present matching airports to the user. Use the ask_user_question tool with options showing each airport's name, IATA/ICAO codes, and class (e.g. "San Diego International Airport (SAN / KSAN) — international_airport"). If no results, ask the user to try a different search term.
3d. From the selected airport, derive:
{TARGET_DB} = AIRPORT_{IATA} (prefer IATA; fall back to ICAO if IATA is empty){SCHEMA} = PUBLIC{IATA} = Airport IATA code (or ICAO as fallback for DB naming){ICAO} = Airport ICAO code{AIRPORT_ID} = Overture Maps record ID{AIRPORT_NAME} = Airport name
Step 3.1: Check for Existing Installation
After resolving {TARGET_DB}, check if this airport database already exists:
SHOW DATABASES LIKE '{TARGET_DB}';
If the database exists, run a quick health check to understand the current state:
SELECT 'PROPERTIES_AIRPORT' AS obj, COUNT(*) AS cnt FROM {TARGET_DB}.PUBLIC.PROPERTIES_AIRPORT
UNION ALL SELECT 'ADSB_DATA', COUNT(*) FROM {TARGET_DB}.PUBLIC.ADSB_DATA
UNION ALL SELECT 'HELPER_AIRLINE_DIM', COUNT(*) FROM {TARGET_DB}.PUBLIC.HELPER_AIRLINE_DIM;
Then prompt the user with ask_user_question (3 options):
- Option "Update dashboard only": "Keep all data and pipelines intact. Redeploy the dashboard if a newer version is available. Current data: {row counts from health check}."
- Option "Skip this airport": "Leave the existing installation completely untouched. No changes will be made."
- Option "Full reinstall (data loss)": "WARNING: This will destroy ALL accumulated ADS-B data, flight schedules, Dynamic Tables, and analytics history for {AIRPORT_NAME}. This cannot be undone. The airport will be rebuilt from scratch."
Handling each choice:
- Update dashboard only → Jump to Step 3.1a: Dashboard-Only Update (below).
- Skip this airport → Print "Skipping {AIRPORT_NAME} ({IATA}) — existing installation preserved." and stop.
- Full reinstall → Continue with Step 3.5 and the normal installation flow (Steps 4–9).
If the database does not exist, proceed normally with Step 3.5 (fresh install).
Step 3.1a: Dashboard-Only Update
This shortcut path skips all data pipeline sub-skills and only updates the dashboard.
-
Resolve {WAREHOUSE} from the existing installation:
SELECT WAREHOUSE FROM {TARGET_DB}.PUBLIC.HELPER_INSTALL_AUDIT
ORDER BY INSTALL_TS DESC LIMIT 1;
Fall back to AVIA_{IATA}_WH if no audit record exists.
-
Locate the existing dashboard. Check if a dashboard is already deployed in ANY airport database:
SHOW STREAMLITS IN ACCOUNT;
Filter results for rows where the comment column contains "sf_sit-is-aviation" and "oss-aviation-dashboard". Record the database where it was found as {DASHBOARD_DB}.
If no existing dashboard is found, set {DASHBOARD_DB} = {TARGET_DB}.
-
Ensure the Git Repository Stage is up to date:
ALTER GIT REPOSITORY {DASHBOARD_DB}.PUBLIC.AVIA_OPS_REPO FETCH;
If the Git Repository Stage does not exist in {DASHBOARD_DB}, create it (same as Step 5).
-
Invoke the dashboard skill: Read and follow .cortex/skills/aviation-dashboard/SKILL.md with {DASHBOARD_DB} as the target.
The dashboard skill's version check (Step 3.5 in that skill) determines whether to actually redeploy.
-
Print summary: "Dashboard check complete for {AIRPORT_NAME} ({IATA})." and stop.
Step 3.5: Create Dedicated Warehouse
CREATE WAREHOUSE IF NOT EXISTS AVIA_{IATA}_WH
WAREHOUSE_SIZE = 'XSMALL'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE
COMMENT = '{"origin":"sf_sit-is-aviation","name":"oss-aviation-base-setup","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}';
Set {WAREHOUSE} = AVIA_{IATA}_WH for all subsequent steps.
Step 4: Gather Configuration
Collect configuration from the user using structured prompts. Ask these one at a time.
4a. Flight Schedules (Aviationstack)
Use the ask_user_question tool to ask whether the user wants flight schedule ingestion:
- Option "Skip": "Install without flight schedules. Everything works: real-time aircraft tracking, ground activity, runway crossings, and traffic analytics. Flights just won't be matched to airline schedules (no flight numbers, gate assignments, or delay metrics)."
- Option "I have a key": "Enable flight schedule ingestion via Aviationstack. Adds: flight number matching, airline/gate assignments, on-time performance, and delay analytics. Requires a free or paid API key from aviationstack.com."
If user chooses "I have a key", ask them to provide the key using the ask_user_question tool with a text input.
Set {API_KEY} to the provided key, or leave empty if skipped.
4b. TSA Throughput
Use the ask_user_question tool to ask whether the user wants TSA checkpoint throughput data:
- Option "Yes" (default): "Enable TSA checkpoint throughput ingestion. Fetches weekly passenger throughput data from the TSA FOIA reading room. No API key needed. Adds: checkpoint passenger counts by hour, day, and checkpoint for the selected airport."
- Option "Skip": "Install without TSA throughput data. All other features remain fully functional."
Set {ENABLE_TSA} = true/false based on user response.
4c. Historical Backfill
Use the ask_user_question tool with a text input to ask how many days of historical ADS-B data to load:
- Question: "How many days of historical ADS-B data should we backfill? (0 = skip, max 30, default 5). More days = richer initial dataset but longer install time (~2-3 min per day)."
- Default value: "5"
Set {BACKFILL_DAYS} to the user's value.
4d. Warehouse
Use the ask_user_question tool to confirm the warehouse:
- Question: "We'll create warehouse
AVIA_{IATA}_WH (XSMALL, auto-suspend 60s). Confirm or provide a different warehouse name."
- Default value:
AVIA_{IATA}_WH
Set {WAREHOUSE} to confirmed name.
Step 5: Create Git Repository Stage
The airline CSV and skill files are loaded from a Git Repository Stage inside the airport database:
CREATE OR REPLACE GIT REPOSITORY {TARGET_DB}.{SCHEMA}.AVIA_OPS_REPO
API_INTEGRATION = (ask user or use existing)
ORIGIN = 'https://github.com/Snowflake-Labs/sfguide-aviation-ops-intelligence.git';
Set {GIT_REPO_STAGE_BASE} = @{TARGET_DB}.{SCHEMA}.AVIA_OPS_REPO/branches/main.
Note: If the Git Repository Stage already exists, skip this step.
Step 6: Route to Sub-Skills
Execute sub-skills in order:
-
Base Setup -- Read and follow .cortex/skills/aviation-installer/base-setup/SKILL.md
- Creates database, schemas, tags, airport properties, gates, runways, airline dimension
-
ADS-B Ingestion -- Read and follow .cortex/skills/aviation-installer/adsb-ingestion/SKILL.md
- Creates ADS-B tables, external access integrations, ingestion procedures, tasks, backfill
-
Flight Schedules (if API key provided) -- Read and follow .cortex/skills/aviation-installer/flight-schedules/SKILL.md
- Creates schedule tables, ingestion procedure, task
-
TSA Throughput (if enabled, default yes) -- Read and follow .cortex/skills/aviation-installer/tsa-throughput/SKILL.md
- Creates TSA PDF stages, throughput table, network rule, EAI, ingestion procedures, weekly tasks
-
Derived Analytics -- Read and follow .cortex/skills/aviation-installer/derived-analytics/SKILL.md
- Creates Dynamic Tables, monitoring views, task DAG, operational KPIs
-
Dashboard -- Before deploying, locate any existing dashboard across all airport databases:
SHOW STREAMLITS IN ACCOUNT;
Filter results for rows where the comment column contains "sf_sit-is-aviation" and "oss-aviation-dashboard".
- If a dashboard already exists in another airport database: set
{DASHBOARD_DB} to that database. The dashboard auto-discovers all AIRPORT_XXX databases, so one copy serves all airports. Do NOT create a duplicate.
- If no dashboard exists anywhere: set
{DASHBOARD_DB} = {TARGET_DB}.
Then read and follow .cortex/skills/aviation-dashboard/SKILL.md with {DASHBOARD_DB} as the target.
The dashboard skill's version check determines whether to redeploy or skip.
Step 7: Start Task DAG
Resume tasks in leaf-to-root order (avoids "Unable to update graph" errors).
First, ensure the root task is suspended (a sub-skill may have already resumed some tasks):
ALTER TASK {TARGET_DB}.{SCHEMA}.TASK_INGEST_ADSB SUSPEND;
Then resume children first, root last:
ALTER TASK {TARGET_DB}.{SCHEMA}.TASK_REFRESH_ANALYTICS RESUME;
ALTER TASK {TARGET_DB}.{SCHEMA}.TASK_REFRESH_DERIVED RESUME;
ALTER TASK {TARGET_DB}.{SCHEMA}.TASK_ENRICH_ADSB RESUME;
ALTER TASK {TARGET_DB}.{SCHEMA}.TASK_ENRICH_AIRCRAFT_META RESUME;
ALTER TASK {TARGET_DB}.{SCHEMA}.TASK_INGEST_ADSB RESUME;
If flight schedules were configured:
CALL {TARGET_DB}.{SCHEMA}.PROC_RESUME_OPTIONAL_TASK('TASK_FLIGHT_SCHEDULE');
If TSA throughput was enabled:
ALTER TASK {TARGET_DB}.{SCHEMA}.TASK_EXTRACT_TSA_PDF RESUME;
ALTER TASK {TARGET_DB}.{SCHEMA}.TASK_FETCH_TSA_PDF RESUME;
Step 8: Trigger Initial Data Load
EXECUTE TASK {TARGET_DB}.{SCHEMA}.TASK_INGEST_ADSB;
Optionally start historical backfill:
CALL {TARGET_DB}.{SCHEMA}.PROC_START_BACKFILL_HISTORY();
Step 9: Verify Installation
SELECT 'PROPERTIES_AIRPORT' AS OBJ, COUNT(*) AS CNT FROM {TARGET_DB}.{SCHEMA}.PROPERTIES_AIRPORT
UNION ALL SELECT 'PROPERTIES_GATES', COUNT(*) FROM {TARGET_DB}.{SCHEMA}.PROPERTIES_GATES
UNION ALL SELECT 'PROPERTIES_RUNWAYS', COUNT(*) FROM {TARGET_DB}.{SCHEMA}.PROPERTIES_RUNWAYS
UNION ALL SELECT 'HELPER_AIRLINE_DIM', COUNT(*) FROM {TARGET_DB}.{SCHEMA}.HELPER_AIRLINE_DIM;
Check task status:
SELECT NAME, STATE, LAST_COMMITTED_ON, NEXT_SCHEDULED_TIME
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(SCHEDULED_TIME_RANGE_START => DATEADD('hour', -1, CURRENT_TIMESTAMP())))
WHERE DATABASE_NAME = '{TARGET_DB}'
ORDER BY SCHEDULED_TIME DESC;
Record the installation end time and compute elapsed minutes: {ELAPSED} = TIMEDIFF(minute, {START_TIME}, CURRENT_TIMESTAMP()). Store the row counts from the verification query above as {GATE_COUNT}, {RUNWAY_COUNT}, {AIRLINE_COUNT} for the output summary.
Stopping Points
- After Step 3: Confirm airport selection with user
- After Step 3.1: If existing airport detected, confirm user choice (skip/update/reinstall)
- After Step 6.1 (base-setup): Verify PROPERTIES_AIRPORT has 1 row
- After Step 6.2 (adsb-ingestion): Verify EAIs and procedures exist
- After Step 7: Verify all tasks are STARTED
- After Step 8: Wait 2-3 minutes, then verify ADSB_DATA has rows
Examples
Example 1: Fresh install for San Diego International
User says: "Install airport analytics for San Diego"
Actions:
- Search Overture Maps for "San Diego" → find SAN / KSAN
- Confirm airport selection with user
- Gather config: skip flight schedules, enable TSA, 5-day backfill, default warehouse
- Run sub-skills in order: base-setup → adsb-ingestion → tsa-throughput → derived-analytics → dashboard
- Resume task DAG, trigger initial data load, verify
Result:
AIRPORT_SAN database with full analytics pipeline and dashboard link
Example 2: Add a second airport to existing installation
User says: "Set up LAX — I already have SAN installed"
Actions:
- Search for "LAX" → find Los Angeles International
- Create
AIRPORT_LAX database (new)
- Existing dashboard in
AIRPORT_SAN is detected and reused (no duplicate)
- Run all sub-skills for LAX
Result:
AIRPORT_LAX database, dashboard auto-discovers both SAN and LAX
Example 3: Update dashboard for existing airport
User says: "Update the dashboard for my SAN airport"
Actions:
- Detect
AIRPORT_SAN exists → prompt user
- User selects "Update dashboard only"
- Fetch latest Git repo, redeploy dashboard
Result: Dashboard updated, all data and pipelines untouched
Troubleshooting
| Error | Cause | Fix |
|---|
| Overture Maps query fails | Listing not installed | Step 2 auto-installs it; or manually: CREATE DATABASE IF NOT EXISTS OVERTURE_MAPS__BASE FROM LISTING GZT0Z4CM1E9KV; |
| Airport not found | Missing IATA code | Search by ICAO code; some airports lack IATA codes in Overture |
| EAI creation fails | Insufficient privileges | Requires ACCOUNTADMIN or CREATE INTEGRATION privilege |
| Tasks not running | Wrong resume order | Resume in leaf-to-root order; check warehouse is active |
| No ADS-B data after 5 min | Ingestion issue | Check CALL {TARGET_DB}.{SCHEMA}.PROC_INGEST_ADSB_REALTIME() manually |
| Backfill stuck | Failed days | Check HELPER_ADSB_BACKFILL_STATUS for failed days |
| Airport already installed | Database exists | Step 3.1 detects this automatically; choose update dashboard, skip, or full reinstall |
| Duplicate dashboards | Dashboard in multiple DBs | Step 6.6 auto-discovers existing dashboard and reuses it; remove duplicates via aviation-cleanup |
Output
After Step 9 verification completes, print the following summary to the user (substituting actual values from the installation):
Airport Analytics Platform installed for {AIRPORT_NAME} ({IATA} / {ICAO})
Installation time: {ELAPSED} minutes
Capabilities installed:
- Real-time ADS-B aircraft tracking (5-minute refresh from adsb.lol)
- Ground movement analysis (taxi paths, gate dwell times)
- Runway crossing detection
- Daily and hourly traffic analytics
- Airline-level traffic and delay metrics
- Gate utilization and airline dwell analysis
- Flight tracker with historical replay
- Operational KPI dashboard (V_AIR_OPS_DAILY_KPIS)
- {BACKFILL_DAYS}-day historical ADS-B backfill (running in background)
- Flight schedule matching via Aviationstack (include only if API key was provided; otherwise print: "Flight schedules: skipped (no API key)")
- TSA checkpoint throughput ingestion (include only if TSA was enabled; otherwise print: "TSA throughput: skipped")
Objects created:
- Database:
{TARGET_DB}
- Warehouse:
{WAREHOUSE}
- Schemas:
PUBLIC, TAGS
- Reference tables: PROPERTIES_AIRPORT (1 row), PROPERTIES_GATES ({GATE_COUNT} gates), PROPERTIES_RUNWAYS ({RUNWAY_COUNT} runways), HELPER_AIRLINE_DIM ({AIRLINE_COUNT} airlines)
- ADS-B pipeline: 3 network rules, 3 external access integrations, ingestion and enrichment stored procedures
- Dynamic Tables: 13 cascading DTs (traffic facts, gate analysis, runway crossings, flight tracker)
- Views: V_AIR_OPS_DAILY_KPIS, HELPER_MONITOR_LAST_REFRESH, HELPER_QA_COUNTS_DAILY
- Task DAG: TASK_INGEST_ADSB (root, 5-min schedule) with child tasks for enrichment, derived analytics, and optional flight schedule / TSA ingestion
- Dashboard: deployed (see link below)
Final Step: Open the Dashboard
If React dashboard (SPCS) was deployed:
Retrieve the dashboard endpoint URL:
SHOW ENDPOINTS IN SERVICE {TARGET_DB}.PUBLIC.AVIATION_DASHBOARD_SERVICE;
SELECT 'https://' || "ingress_url" AS dashboard_url
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "name" = 'dashboard';
Print this exact message to the user (substituting the actual URL):
Open this URL and log in with your Snowflake credentials to see the Airport Analytics Dashboard:
<url>
Then open it automatically:
open "<url>"
If Streamlit dashboard was deployed:
SELECT SYSTEM$GET_SNOWSIGHT_HOST() AS host;
Print this message (substituting actual values):
Open the Airport Analytics Dashboard in Snowsight:
https://<host>/api/streamlit/{DASHBOARD_DB}.{DASHBOARD_SCHEMA}.{APP_NAME}
Cleanup
To remove all objects created by this installation:
Tip: Use the aviation-cleanup skill to auto-discover all tagged objects via COMMENT tracking.
