Routing Solution Cleanup

Cleanup / Teardown

Discovers and removes all Snowflake objects created by skills in this repository. Uses the COMMENT tracking tag sf_sit-is-fleet for tagged objects and explicit object lists for untagged resources (marketplace databases, external access integrations, network rules, compute pools).

How It Works

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

{"origin":"sf_sit-is-fleet","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.

Configuration

Error Logging

Follow the Error Logging convention in AGENTS.md. Log file prefix: cleanup.

Prerequisites

• Active Snowflake connection with ACCOUNTADMIN role (or equivalent DROP privileges)
• The compute pool and SPCS services must be in the same account

Complete Object Inventory

These are ALL object types created across all skills. The drop order reverses creation dependencies.

Step 1: Set Session Tag

ALTER SESSION SET query_tag = '{"origin":"sf_sit-is-fleet","name":"oss-routing-solution-cleanup","version":{"major":2,"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 and collect results. Some queries use SHOW + RESULT_SCAN patterns; these must be run as two consecutive statements in the same session or via snowflake_sql_execute.

Tip: If SHOW AGENTS fails with a syntax error, the account may not have Cortex Agents enabled - skip that object type.

Step 3: Generate DROP Statements

Based on discovery results, generate DROP statements in strict dependency order (most-dependent first). The canonical ordered teardown is in references/drop-order.sql; use it as the template and keep only the objects that discovery actually found.

Phase 1 - App (stops SPCS services)

DROP DATABASE IF EXISTS OPENROUTESERVICE_APP;

Phase 2 - Compute Pools (if not already dropped by CASCADE)

-- 3. Stop and drop any compute pools created by the app
--    Naming pattern: OPENROUTESERVICE_APP_COMPUTE_POOL or city-specific pools
ALTER COMPUTE POOL IF EXISTS OPENROUTESERVICE_APP_COMPUTE_POOL STOP ALL;
DROP COMPUTE POOL IF EXISTS OPENROUTESERVICE_APP_COMPUTE_POOL;

Phase 3 - External Access Integrations & Network Rules

-- 4. Drop external access integrations
DROP INTEGRATION IF EXISTS ORS_OSM_EAI;
DROP INTEGRATION IF EXISTS ORS_CARTO_EAI;

Phase 4 - Cortex Agents, Tasks, Dynamic Tables, Notebooks, Streamlits

-- 5. Drop Cortex agents
DROP AGENT IF EXISTS FLEET_INTELLIGENCE.ROUTING_AGENT.ROUTING_AGENT;

-- 6. Suspend and drop tasks
ALTER TASK IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.LOG_SLA_ALERTS SUSPEND;
DROP TASK IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.LOG_SLA_ALERTS;

-- 7. Drop dynamic tables (dwell-analysis pipeline)
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_DAILY_TRENDS;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_DRIVER_DWELL_SUMMARY;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_FACILITY_UTILIZATION;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_SLA_ALERTS;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_H3_CONGESTION;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_DWELL_ENRICHED;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_DWELL_SESSIONS;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_STATE_CHANGES;

-- 8. Drop notebooks (route-optimization)
DROP NOTEBOOK IF EXISTS FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.ADD_CARTO_DATA;
DROP NOTEBOOK IF EXISTS FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.ROUTING_FUNCTIONS_AISQL;

Phase 5 - Procedures, Functions, Views, Tables

For each tagged object found in discovery, generate the appropriate DROP. Use the full signature for procedures and functions.

-- 9. Drop procedures (example - actual list comes from discovery)
DROP PROCEDURE IF EXISTS FLEET_INTELLIGENCE.CORE.SET_ACTIVE_REGION(VARCHAR);
DROP PROCEDURE IF EXISTS FLEET_INTELLIGENCE.ROUTING_AGENT.TOOL_DIRECTIONS(VARCHAR, VARCHAR, VARCHAR);
DROP PROCEDURE IF EXISTS FLEET_INTELLIGENCE.ROUTING_AGENT.TOOL_ISOCHRONE(VARCHAR, FLOAT, VARCHAR);
DROP PROCEDURE IF EXISTS FLEET_INTELLIGENCE.ROUTING_AGENT.TOOL_OPTIMIZATION(VARCHAR, VARCHAR, VARCHAR);

-- 11. Drop views (from all schemas - discovery-driven)
-- Example:
-- DROP VIEW IF EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION.VW_VEHICLE_TELEMETRY;
-- DROP VIEW IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.VW_VEHICLE_TELEMETRY;

-- 12. Drop tables (from all schemas - discovery-driven)
-- Example:
-- DROP TABLE IF EXISTS FLEET_INTELLIGENCE.CATCHMENT.POIS;
-- DROP TABLE IF EXISTS SYNTHETIC_DATASETS.UNIFIED.FACT_VEHICLE_TELEMETRY;

-- 13. Drop stages
DROP STAGE IF EXISTS OPENROUTESERVICE_APP.CORE.SEED_DATA_STAGE;
DROP STAGE IF EXISTS OPENROUTESERVICE_APP.CORE.ORS_SPCS_STAGE;
DROP STAGE IF EXISTS OPENROUTESERVICE_APP.CORE.ORS_GRAPHS_SPCS_STAGE;
DROP STAGE IF EXISTS OPENROUTESERVICE_APP.CORE.ORS_ELEVATION_CACHE_SPCS_STAGE;

-- 14. Drop image repository
DROP IMAGE REPOSITORY IF EXISTS OPENROUTESERVICE_APP.CORE.IMAGE_REPOSITORY;

-- 15. Drop file formats
DROP FILE FORMAT IF EXISTS OPENROUTESERVICE_APP.CORE.PARQUET_FF;

Phase 6 - Schemas, Warehouses, Databases

-- 16. Drop schemas (CASCADE handles any remaining objects)
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.ROUTING_AGENT CASCADE;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION CASCADE;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.CATCHMENT CASCADE;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS CASCADE;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION CASCADE;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_EBIKE CASCADE;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_CAR CASCADE;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.CORE CASCADE;

-- 18. Drop warehouse. Do NOT `ALTER WAREHOUSE ... SUSPEND` first: on an
--     already-suspended warehouse that raises 090064 ("Invalid state") and,
--     under `snow sql -f`, aborts the whole script -- stranding later drops.
--     DROP WAREHOUSE suspends implicitly.
DROP WAREHOUSE IF EXISTS ROUTING_ANALYTICS;

-- 19. Drop marketplace databases (no tracking tag - match by name and origin).
--     install-fleet-apps acquires all six via CREATE DATABASE ... FROM LISTING.
DROP DATABASE IF EXISTS OVERTURE_MAPS__PLACES;
DROP DATABASE IF EXISTS OVERTURE_MAPS__ADDRESSES;
DROP DATABASE IF EXISTS OVERTURE_MAPS__TRANSPORTATION;
DROP DATABASE IF EXISTS OVERTURE_MAPS__BUILDINGS;
DROP DATABASE IF EXISTS OVERTURE_MAPS__DIVISIONS;
DROP DATABASE IF EXISTS SAFEGRAPH_OPEN_CENSUS_FREE;

-- 20. Drop project databases (CASCADE handles all contained objects)
DROP DATABASE IF EXISTS FLEET_INTELLIGENCE CASCADE;
DROP DATABASE IF EXISTS SYNTHETIC_DATASETS 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 the 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:
  Native apps: N
  App packages: N
  Compute pools: N
  Integrations: N
  Network rules: N
  Agents: N
  Tasks: N
  Dynamic tables: N
  Notebooks: N
  Streamlits: N
  Procedures: N
  Functions: N
  Views: N
  Tables: N
  Stages: N
  Image repositories: N
  File formats: N
  Schemas: N
  Warehouses: N
  Databases: N
  ─────────────────
  Total: N objects

Step 5: Verify Clean State

After execution, verify nothing remains:

-- Verify no tagged databases remain
SHOW DATABASES;
-- Expected: only SNOWFLAKE (system) and USER$<username> (personal)

-- Verify no tagged warehouses remain
SHOW WAREHOUSES;
-- Expected: only MY_WH (personal) and SYSTEM$STREAMLIT_NOTEBOOK_WH (system)

-- Verify no compute pools remain (except system pools)
SHOW COMPUTE POOLS;
-- Expected: only SYSTEM_COMPUTE_POOL_CPU and SYSTEM_COMPUTE_POOL_GPU

-- Verify no project integrations remain
SHOW INTEGRATIONS;
-- Expected: only SNOWFLAKE$LOCAL_APPLICATION (system)

-- Verify no marketplace listings attached
-- (Overture Maps databases being dropped detaches the listing)

Report results as a table:

Cleanup by Skill

To clean up objects from a single skill, set SKILL_FILTER to its tracking name and run discovery. Then drop only the objects tagged with that skill. This is useful for re-deploying a single skill.

Note: install-fleet-apps is the foundation skill. Dropping its objects (databases, warehouse) will cascade to all downstream skills. Only drop it when tearing down the entire environment.

Troubleshooting