Route Optimization

Deploy Route Optimization Demo

Deploys the complete Route Optimization demo including Snowflake Marketplace data and the exploration notebook. The interactive VRP simulator is served via the shared React Demo Dashboard app.

Prerequisites

• OpenRouteService Native App deployed and activated
• Active Snowflake connection with a role that has privileges listed in the Required Privileges section below
• For preset-driven demos, run Data Studio for the target (region, vehicle_type) first. The Route Optimization page now reads FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.V_PLACES_CURRENT and the active dataset's UNIFIED V_*_CURRENT views.

Required Privileges

Note: ACCOUNTADMIN is NOT required. Create a custom role with the above privileges, or use any role that has them.

All snow stage copy commands use --connection <ACTIVE_CONNECTION>. Replace <ACTIVE_CONNECTION> with the name of your currently active Snowflake connection.

Optional Dependencies

Asset Velocity (Non-Moving Vehicle Detection & Action Engine)

This skill ships a second React page in the control app, Asset Velocity, that materialises an industry-standard ghost-trailer detection workflow. It is fully additive - the existing Route Optimizer (VRP) page is untouched.

It reuses the dwell-analysis Dynamic Tables (no new DTs) and adds four thin views in FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION:

Full use case framing, cross-vertical applicability, and risk mitigations live in references/asset-velocity-use-case.md.

Smart-reposition (v1.1)

The Asset Velocity page now uses real ORS + VROOM features instead of Euclidean math:

Known limitations (synthetic data):

• The wrapped ISOCHRONES(...) UDF does not yet pass profile_params through, so the polygon is profile-only (the matrix gate is the authoritative filter).
• DIM_POIS has no OPEN_FROM/OPEN_TO, so terminal time windows default to 06:00-22:00 UTC.
• LANE_PROFILE does not exist on terminals; skill-mismatch heuristics treat RESTAURANT POIs as REEFER-required and everything else as DRY-friendly.

Deploy the Asset Velocity views

This is a REQUIRED step of the standard workflow (see Step 5b below), not an optional aside. Without it the Asset Velocity page in the control-app sidebar renders empty. It is preset-agnostic: VW_IDLE_VEHICLES pulls VEHICLE_TYPE from ROUTE_OPTIMIZATION.CONFIG, so on the default SanFrancisco/ebike install it surfaces idle ebikes (no HGV data required).

Prerequisites: dwell-analysis skill deployed (the views reference FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_DWELL_ENRICHED, which Snowflake validates at CREATE VIEW time).

snow sql -f .cortex/skills/route-optimization/references/extend-dim-fleet-hgv.sql -c <connection>
snow sql -f .cortex/skills/route-optimization/references/asset-velocity-views.sql -c <connection>

Verify:

SELECT 'VW_IDLE_VEHICLES' AS V, COUNT(*) AS CNT FROM FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.VW_IDLE_VEHICLES
UNION ALL
SELECT 'VW_LANE_DEMAND',         COUNT(*) FROM FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.VW_LANE_DEMAND
UNION ALL
SELECT 'VW_VEHICLE_COST_OF_IDLENESS', COUNT(*) FROM FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.VW_VEHICLE_COST_OF_IDLENESS;

The page will gracefully render an empty state with deployment instructions if DWELL_ANALYSIS.DT_DWELL_ENRICHED is missing.

Configuration

Error Logging

Follow the Error Logging convention in AGENTS.md. Log file prefix: route-optimization.

Execution Rules

1. Never use bulk sed or replace_all on .ipynb files - notebooks are JSON with structured cell arrays. Use targeted replacements on specific cells identified by name.
2. Replace longer phrases before shorter ones when editing notebook prompts to avoid garbled text.
3. Replace complete prompt strings, not individual words.
4. Always validate JSON validity of modified .ipynb files before uploading.

Workflow

Step 1: Set Query Tag

Pre-check: If data already exists, skip to Step 6. Run:

SELECT COUNT(*) AS cnt FROM FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.PLACES;

If cnt > 0, the data pipeline has already run. Skip to Step 6 (Claude model check) or Step 7 (AISQL notebook) as needed.

Goal: Set session query tag for attribution tracking.

Execute:

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

Step 2: Verify ORS Services

Goal: Confirm all 4 ORS services are active (OPENROUTESERVICE, ROUTING_REVERSE_PROXY, VROOM, DOWNLOADER).

Execute:

SHOW SERVICES IN DATABASE OPENROUTESERVICE_APP;

If any services are SUSPENDED, resume them:

CALL OPENROUTESERVICE_APP.CORE.RESUME_ALL_SERVICES();
SELECT OPENROUTESERVICE_APP.CORE.CHECK_HEALTH();

STOP if ORS Native App is not installed.

Step 3: Read ORS Configuration and Gather Preferences

Goal: Detect current map region and routing profiles, gather customization preferences from the user.

1. Read and follow .cortex/skills/routing-customization/read-ors-configuration/SKILL.md
2. Determine the demo city:
   • City map (e.g., "SanFrancisco"): use that city name
   • Region/country map (e.g., "great-britain"): ask user which city
3. Ask: "Do you want to customize industries? Default industries are: Food, Healthcare, Cosmetics."
   • If YES: gather specs per references/industry-customization.md
   • If NO: use defaults
4. Store for later steps: <REGION_NAME>, <NOTEBOOK_CITY>, <ENABLED_PROFILES>, <CUSTOM_INDUSTRIES>

Output: Map Region, Demo City, Vehicle Profiles, Custom Industries (YES/NO) confirmed with user.

Step 4: Get Carto Overture Dataset

Goal: Acquire Overture Maps Places dataset for POI data.

Execute:

CALL SYSTEM$ACCEPT_LEGAL_TERMS('DATA_EXCHANGE_LISTING', 'GZT0Z4CM1E9KR');
CREATE DATABASE IF NOT EXISTS OVERTURE_MAPS__PLACES FROM LISTING GZT0Z4CM1E9KR;

Requires IMPORT SHARE privilege. If profile error occurs, update user profile with first/last name and email. Full details in references/sql-setup.md Step 4.

Output: OVERTURE_MAPS__PLACES database available.

Step 5: Run Seed Data Script

Goal: Create database, schema, warehouse, CONFIG, PLACES, JOB_TEMPLATE, and LOOKUP tables from Overture Maps.

1. If region is NOT SanFrancisco, update the SET variables at the top of references/seed-data.sql:
   • $REGION_GEOHASH: see geohash table in references/notebook-deployment.md
   • $REGION_NAME: the city/region name (e.g., 'NewYork', 'London')
2. If the user requested custom industries in Step 3, update the LOOKUP INSERT section in references/seed-data.sql per references/industry-customization.md.
3. Run:

   snow sql -f .cortex/skills/route-optimization/references/seed-data.sql -c <connection>
   

4. Verify:

   SELECT 'PLACES' AS TBL, COUNT(*) AS CNT FROM FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.PLACES
   UNION ALL SELECT 'LOOKUP', COUNT(*) FROM FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.LOOKUP
   UNION ALL SELECT 'JOB_TEMPLATE', COUNT(*) FROM FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.JOB_TEMPLATE;
   

   Expected: PLACES 50K-500K, LOOKUP 4, JOB_TEMPLATE 29. STOP if any table has 0 rows.

Output: Standing data populated for <REGION_NAME>.

Step 5b: Deploy Asset Velocity Page Views (required for the Asset Velocity page)

Goal: Create the Asset Velocity views (VW_IDLE_VEHICLES, VW_LANE_DEMAND, VW_VEHICLE_COST_OF_IDLENESS, VW_FLEET_VEHICLE_PROFILE) that power the Asset Velocity sidebar page. Preset-agnostic - shows idle vehicles of the active preset (e.g. ebikes on the default SF install).

Dependency: dwell-analysis must be deployed first - the views reference FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_DWELL_ENRICHED, which Snowflake validates at CREATE VIEW time. If Dwell Analysis is not yet deployed, skip this step and run it after Dwell Analysis; the page renders a friendly empty state with deployment instructions until then.

Runtime owner: the control-app init.ts is the source of truth for these four views (VW_IDLE_VEHICLES, VW_LANE_DEMAND, VW_FLEET_VEHICLE_PROFILE, VW_VEHICLE_COST_OF_IDLENESS) and CREATE OR REPLACEs them on every container boot. The .sql files below are the keep-in-sync source and the manual-repair path (run them when a boot recreate failed silently, e.g. DT_DWELL_ENRICHED was absent at boot). The active-preset pointer they filter on lives in ROUTE_OPTIMIZATION.CONFIG, seeded by datasets/load-seed-data.sql and by this skill's seed-data.sql.

snow sql -f .cortex/skills/route-optimization/references/extend-dim-fleet-hgv.sql -c <connection>
snow sql -f .cortex/skills/route-optimization/references/asset-velocity-views.sql -c <connection>

Verify (all should return > 0 on a seeded install):

SELECT 'VW_IDLE_VEHICLES' AS V, COUNT(*) AS CNT FROM FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.VW_IDLE_VEHICLES
UNION ALL SELECT 'VW_LANE_DEMAND', COUNT(*) FROM FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.VW_LANE_DEMAND
UNION ALL SELECT 'VW_VEHICLE_COST_OF_IDLENESS', COUNT(*) FROM FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.VW_VEHICLE_COST_OF_IDLENESS;

Output: Asset Velocity page views created and populated for the active preset.

Step 6: Check Claude Model

Goal: Verify latest Claude Sonnet model is available in Snowflake Cortex.

See references/notebook-deployment.md (Step 7) for the test SQL and update instructions.

Step 7: Deploy AISQL Notebook

Goal: Deploy the AISQL exploration notebook, customized for <NOTEBOOK_CITY>.

Required for full demo: The AISQL notebook provides the interactive route optimization experience in Snowsight. Without it, only the seed data tables are deployed and the Route Optimization page in the control app will have its VRP simulator but no notebook-driven workflow. If you skip this step now, you can add it later by following references/notebook-deployment.md.

See references/notebook-deployment.md (Step 8) for cell-by-cell update tables, text replacement rules, post-replacement validation, and upload/create commands.

If city references already match <NOTEBOOK_CITY>, skip modification and upload directly.

Output: AISQL notebook deployed with AI prompts customized for <NOTEBOOK_CITY>.

Dashboard Schema Contract

The React Demo Dashboard page queries these exact tables and columns. If the pipeline changes column names, the React page must be updated to match.

CONFIG

PLACES

JOB_TEMPLATE

LOOKUP

VW_IDLE_VEHICLES (Asset Velocity)

VW_LANE_DEMAND (Asset Velocity)

VW_VEHICLE_COST_OF_IDLENESS (Asset Velocity)

ORS Functions (cross-app)

Step 8: Verify

Goal: Confirm tables exist and Demo Dashboard shows the page.

1. Verify PLACES, LOOKUP, JOB_TEMPLATE tables have rows
2. Check Demo Dashboard loads Route Optimization page

Stopping Points

• Step 1: STOP if OpenRouteService Native App is not installed
• Step 2: Wait for user to activate app if services not running
• Step 3: Confirm detected region, city, and industry choices with user
• Step 4: Verify Marketplace dataset accessible
• Step 5: Verify PLACES, LOOKUP, JOB_TEMPLATE tables are populated
• Step 6: Verify Claude model is available
• Step 8: Verify Demo Dashboard shows the Route Optimization page

Troubleshooting

Recovery

Re-running is safe: all statements use IF NOT EXISTS or OR REPLACE, and snow stage copy uses --overwrite. No manual cleanup needed.

Output

Complete Route Optimization demo with:

• Carto Overture Places dataset for POI data
• Exploration notebook with AISQL examples
• React VRP simulator in Demo Dashboard
• 3 configurable vehicles with skills
• Real-world points of interest for routing scenarios

Cleanup

To remove all objects created by this skill:

DROP NOTEBOOK IF EXISTS FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.ROUTING_FUNCTIONS_AISQL;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.VW_VEHICLE_COST_OF_IDLENESS;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.VW_LANE_DEMAND;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.VW_IDLE_VEHICLES;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.VW_FLEET_VEHICLE_PROFILE;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.CONFIG;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.LOOKUP;
DROP STAGE IF EXISTS FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION.NOTEBOOK;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.ROUTE_OPTIMIZATION;

-- v1.1 also added HGV columns to DIM_FLEET. They are non-destructive (NULL for
-- non-trucking modes) and the column-add is idempotent, so cleanup is optional.
-- If a fully clean DIM_FLEET is required (e.g. before regenerating via Data
-- Studio), drop them with:
ALTER TABLE SYNTHETIC_DATASETS.UNIFIED.DIM_FLEET DROP COLUMN IF EXISTS WEIGHT_TONS;
ALTER TABLE SYNTHETIC_DATASETS.UNIFIED.DIM_FLEET DROP COLUMN IF EXISTS HEIGHT_M;
ALTER TABLE SYNTHETIC_DATASETS.UNIFIED.DIM_FLEET DROP COLUMN IF EXISTS LENGTH_M;
ALTER TABLE SYNTHETIC_DATASETS.UNIFIED.DIM_FLEET DROP COLUMN IF EXISTS WIDTH_M;
ALTER TABLE SYNTHETIC_DATASETS.UNIFIED.DIM_FLEET DROP COLUMN IF EXISTS AXLELOAD_T;
ALTER TABLE SYNTHETIC_DATASETS.UNIFIED.DIM_FLEET DROP COLUMN IF EXISTS HAZMAT;
ALTER TABLE SYNTHETIC_DATASETS.UNIFIED.DIM_FLEET DROP COLUMN IF EXISTS VEHICLE_SUBTYPE;

Tip: Use the cleanup skill to auto-discover all tagged objects via COMMENT tracking.