Aviation Cleanup

Cleanup / Teardown

Discovers and removes all Snowflake objects created by skills in this repository. Uses the COMMENT tracking tag sf_sit-is-aviation for tagged objects and explicit name matching for account-level objects (network rules, external access integrations) that cannot carry COMMENT fields.

How It Works

Every CREATE statement in every skill includes a COMMENT with JSON metadata:

{"origin":"sf_sit-is-aviation","name":"<skill-tracking-name>","version":{"major":1,"minor":0},...}

This skill queries INFORMATION_SCHEMA, SHOW commands, and ACCOUNT_USAGE views to discover all tagged objects, then generates DROP statements in dependency-safe order.

IMPORTANT: External access integrations and network rules are account-level objects created during ADS-B ingestion, flight schedules, and TSA throughput. They are matched by name pattern (AIRPORT_*_EAI, AIRPORT_*_RULE) rather than COMMENT tag, since those object types may not support COMMENT.

Configuration

Friction Logging

Always write a friction log to .cortex/skills/logs/friction-log_{YYYY-MM-DD}_{HH-MM}.md following the template in .cortex/skills/logs/README.md. Include configuration, objects discovered/dropped, and any friction points using the F1/F2/F3 format. If no friction points, write "No friction points encountered."

Execution Rules

1. Always dry-run first. Never execute DROP statements without user confirmation.
2. Suspend tasks before dropping. Every TASK must be SUSPENDED before DROP.
3. Drop in dependency order. Follow the phase sequence (Streamlit → Tasks → DTs → Views → Procedures → Tables → EAIs → Database).
4. Verify after cleanup. Run SHOW DATABASES LIKE 'AIRPORT_%' and SHOW INTEGRATIONS LIKE 'AIRPORT_%' to confirm clean state.
5. All discovery queries must use the tracking tag sf_sit-is-aviation — never drop objects that do not carry this tag.

Prerequisites

• Active Snowflake connection with ACCOUNTADMIN role
• Review generated DROP statements before executing

Complete Object Inventory

Drop order reverses creation dependencies (most-dependent objects first).

Workflow

Step 1: Set Session Tag

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

Step 2: Discover All Objects

Run the discovery queries from references/discovery-queries.sql.

Execute each query block and collect results. SHOW + RESULT_SCAN patterns must be run as two consecutive statements in the same session.

Tip: If filtering to a specific airport, replace LIKE 'AIRPORT_%' with = 'AIRPORT_{IATA}' in all discovery queries.

Step 3: Generate DROP Statements

Based on discovery results, generate DROP statements in strict dependency order:

Phase 1 — Streamlit Apps

The dashboard lives inside each airport database:

DROP STREAMLIT IF EXISTS {TARGET_DB}.PUBLIC.AIRPORT_ANALYTICS_DASHBOARD;

Phase 2 — Tasks (Suspend First)

For each airport database, suspend and drop tasks in leaf-to-root order:

ALTER TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_REFRESH_ANALYTICS SUSPEND;
DROP TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_REFRESH_ANALYTICS;
ALTER TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_REFRESH_DERIVED SUSPEND;
DROP TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_REFRESH_DERIVED;
ALTER TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_EXTRACT_TSA_PDF SUSPEND;
DROP TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_EXTRACT_TSA_PDF;
ALTER TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_FETCH_TSA_PDF SUSPEND;
DROP TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_FETCH_TSA_PDF;
ALTER TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_FLIGHT_SCHEDULE SUSPEND;
DROP TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_FLIGHT_SCHEDULE;
ALTER TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_ENRICH_ADSB SUSPEND;
DROP TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_ENRICH_ADSB;
ALTER TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_ENRICH_AIRCRAFT_META SUSPEND;
DROP TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_ENRICH_AIRCRAFT_META;
ALTER TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_ADSB_BACKFILL_ONCE SUSPEND;
DROP TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_ADSB_BACKFILL_ONCE;
ALTER TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_ADSB_BACKFILL_RETRY SUSPEND;
DROP TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_ADSB_BACKFILL_RETRY;
ALTER TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_INGEST_ADSB SUSPEND;
DROP TASK IF EXISTS {TARGET_DB}.PUBLIC.TASK_INGEST_ADSB;

Phase 3 — Dynamic Tables

DROP DYNAMIC TABLE IF EXISTS {TARGET_DB}.PUBLIC.FLIGHT_TRAFFIC_FACT_AIRLINE_DELAY_DAILY;
DROP DYNAMIC TABLE IF EXISTS {TARGET_DB}.PUBLIC.FLIGHT_TRAFFIC_FACT_AIRLINE_TRAFFIC_DAILY;
DROP DYNAMIC TABLE IF EXISTS {TARGET_DB}.PUBLIC.FLIGHT_TRACKER_FLIGHT_LIST;
DROP DYNAMIC TABLE IF EXISTS {TARGET_DB}.PUBLIC.FLIGHT_TRAFFIC_FACT_ADSB_HOURLY;
DROP DYNAMIC TABLE IF EXISTS {TARGET_DB}.PUBLIC.FLIGHT_TRAFFIC_FACT_ADSB_DAILY;
DROP DYNAMIC TABLE IF EXISTS {TARGET_DB}.PUBLIC.RUNWAY_CROSSINGS_DETAILED;
DROP DYNAMIC TABLE IF EXISTS {TARGET_DB}.PUBLIC.GATE_ANALYSIS_FLIGHT_DWELL_WITH_AIRLINE;
DROP DYNAMIC TABLE IF EXISTS {TARGET_DB}.PUBLIC.GATE_ANALYSIS_GATE_AIRLINE_DWELL_DAILY;
DROP DYNAMIC TABLE IF EXISTS {TARGET_DB}.PUBLIC.GATE_ANALYSIS_GATE_UTIL_DAILY;
DROP DYNAMIC TABLE IF EXISTS {TARGET_DB}.PUBLIC.GATE_ANALYSIS_FLIGHT_GATE_TIME;
DROP DYNAMIC TABLE IF EXISTS {TARGET_DB}.PUBLIC.GATE_ANALYSIS_ADSB_GROUND_POINTS;
DROP DYNAMIC TABLE IF EXISTS {TARGET_DB}.PUBLIC.GATE_ANALYSIS_AIRCRAFT_GROUND_SESSIONS;
DROP DYNAMIC TABLE IF EXISTS {TARGET_DB}.PUBLIC.ADSB_DATA_LOCAL;

Phase 4 — Views, Procedures, Functions, Tables

Use discovery results to drop in order. See references/drop-order.sql for the complete templated DROP sequence.

Phase 5 — External Access Integrations and Network Rules

DROP INTEGRATION IF EXISTS {TARGET_DB}_PUBLIC_ADSB_LOL_EAI;
DROP INTEGRATION IF EXISTS {TARGET_DB}_PUBLIC_GITHUB_EAI;
DROP INTEGRATION IF EXISTS {TARGET_DB}_PUBLIC_AVIATIONSTACK_EAI;
DROP INTEGRATION IF EXISTS {TARGET_DB}_PUBLIC_TSA_GOV_EAI;
DROP INTEGRATION IF EXISTS {TARGET_DB}_PUBLIC_PYPI_ACCESS_INTEGRATION;

Network rules (qualified names inside the airport database):

DROP NETWORK RULE IF EXISTS {TARGET_DB}.PUBLIC.PUBLIC_ADSB_LOL_RULE;
DROP NETWORK RULE IF EXISTS {TARGET_DB}.PUBLIC.PUBLIC_GITHUB_RULE;
DROP NETWORK RULE IF EXISTS {TARGET_DB}.PUBLIC.PUBLIC_AVIATIONSTACK_RULE;
DROP NETWORK RULE IF EXISTS {TARGET_DB}.PUBLIC.PUBLIC_TSA_GOV_RULE;
DROP NETWORK RULE IF EXISTS {TARGET_DB}.PUBLIC.PUBLIC_PYPI_NETWORK_RULE;

Phase 6 — Schemas and Database

DROP SCHEMA IF EXISTS {TARGET_DB}.TAGS CASCADE;
DROP DATABASE IF EXISTS {TARGET_DB} CASCADE;

Step 4: Review and Execute

Action: Present the generated DROP statements to the user grouped by phase.

• If DRY_RUN = true: Display all statements and stop. Ask user to confirm before executing.
• If DRY_RUN = false: Execute each DROP statement sequentially and report results.

Always confirm before executing. Show a summary count:

Objects to drop:
  Streamlit apps: N
  Tasks: N
  Dynamic tables: N
  Views: N
  Procedures: N
  Functions: N
  Tables: N
  Stages: N
  Integrations: N
  Network rules: N
  Schemas: N
  Databases: N
  ─────────────────
  Total: N objects

Step 5: Verify Clean State

SHOW DATABASES LIKE 'AIRPORT_%';
SHOW INTEGRATIONS LIKE 'AIRPORT_%';

Expected: no results.

Cleanup by Skill

To clean up objects from a single skill only:

Examples

Example 1: Clean up a single airport

User says: "Remove the SAN airport installation" Actions:

1. Set AIRPORT_FILTER = AIRPORT_SAN
2. Discover all tagged objects in AIRPORT_SAN
3. Generate DROP statements (dry run), present summary
4. User confirms → execute all DROPs
5. Verify clean state Result: AIRPORT_SAN database, all EAIs, network rules, and warehouse removed

Example 2: Dry-run teardown of all airports

User says: "Show me what would be cleaned up for aviation" Actions:

1. DRY_RUN = true (default)
2. Discover all AIRPORT_XXX databases and tagged objects
3. Present summary: "Found 3 databases, 42 tasks, 39 DTs, 15 EAIs..."
4. Do NOT execute — user reviews list Result: DROP statements printed, nothing executed

Example 3: Remove everything after a demo

User says: "Tear down the entire aviation demo environment" Actions:

1. Discover all tagged objects across all AIRPORT_XXX databases
2. Generate and present DROP statements
3. User confirms → execute in dependency order
4. Verify no AIRPORT_XXX databases or EAIs remain Result: Full environment cleaned up

Troubleshooting