Route Deviation

Deploy Route Deviation Analysis Demo

End-to-end deployment of a Route Deviation Analysis demo comparing actual GPS paths against expected routes to detect detours, delays, and anomalies. Data Studio generates both actual and planned routes in SYNTHETIC_DATASETS.UNIFIED, so no ORS batch routing is needed. Projection views must read active-dataset sources (V_FACT_VEHICLE_TELEMETRY_CURRENT, V_FACT_TRIPS_CURRENT, V_DIM_FLEET_CURRENT, V_DIM_POIS_CURRENT, V_DIM_TRIP_SCHEDULE_CURRENT). Vehicle-type agnostic -- works with trucks, taxis, e-bikes, e-scooters, or any fleet type via CONFIG table.

Prerequisites

CRITICAL: Verify these before starting:

• Synthetic fleet data generated by Data Studio in SYNTHETIC_DATASETS.UNIFIED (any vehicle type)
• CONFIG table set to desired VEHICLE_TYPE and REGION (created automatically during deployment)
• Active Snowflake connection with a role that has privileges listed in the Required Privileges section below
• Compute warehouse available

Required Privileges

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

Configuration

Error Logging

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

Execution Rules

1. One statement per snowflake_sql_execute tool call. Multi-statement blocks can silently fail. This rule applies to the snowflake_sql_execute tool only; snow sql -f and other CLI execution is fine.
2. Always use fully qualified object names. {TARGET_DB}.{TARGET_SCHEMA}.<table>.
3. Verify row counts after each CTAS. Catch silent failures early.
4. All CREATE statements must include a COMMENT tracking tag per AGENTS.md convention: COMMENT = '{"origin":"sf_sit-is-fleet","name":"oss-route-deviation",...}'. See references/sql-pipeline.md for tagged SQL.

Quick Start

The fastest path to a working demo. Creates projection views over SYNTHETIC_DATASETS.UNIFIED tables (loaded by install-fleet-apps Step 8), then runs ETL to produce analysis tables. No Data Studio generation needed.

Quick check

SELECT COUNT(*) FROM FLEET_INTELLIGENCE.ROUTE_DEVIATION.TRIP_DEVIATION_ANALYSIS;

If the table exists and has rows, data is already loaded. Skip to Step 5 (Verify End-to-End).

Create views and run ETL

Execute references/seed-data.sql. This creates CONFIG, 5 projection views, and 3 ETL Dynamic Tables (TRIP_DEVIATION_ANALYSIS, DRIVER_DEVIATION_SUMMARY, DAILY_DEVIATION_TRENDS) computed from the views (TRIP_DEVIATION_ANALYSIS with lag=DOWNSTREAM, the two summary tables with lag='1 hour') so they auto-refresh whenever CONFIG or upstream FACT_TRIPS change without burning idle credits on a short refresh cadence.

Workflow

Before executing SQL, consult references/sql-pipeline.md for the exact SQL statements. For dataset details, consult references/dataset-guide.md.

Step 1: Set Query Tag

Execute:

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

Step 2: Create Infrastructure

Execute:

CREATE DATABASE IF NOT EXISTS FLEET_INTELLIGENCE
    COMMENT = '{"origin":"sf_sit-is-fleet","name":"oss-route-deviation","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}';

CREATE SCHEMA IF NOT EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION
    COMMENT = '{"origin":"sf_sit-is-fleet","name":"oss-route-deviation","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}';

CREATE WAREHOUSE IF NOT EXISTS ROUTING_ANALYTICS
    WAREHOUSE_SIZE = 'XSMALL'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    COMMENT = '{"origin":"sf_sit-is-fleet","name":"oss-route-deviation","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}';

Step 3: Verify UNIFIED Data

Check that Data Studio has generated data in SYNTHETIC_DATASETS.UNIFIED:

SELECT 'FACT_VEHICLE_TELEMETRY' AS TBL, COUNT(*) AS ROW_CNT
FROM FLEET_INTELLIGENCE.ROUTE_DEVIATION.VW_VEHICLE_TELEMETRY
UNION ALL SELECT 'FACT_TRIPS', COUNT(*) FROM FLEET_INTELLIGENCE.ROUTE_DEVIATION.VW_TRIP_DEVIATION
UNION ALL SELECT 'DIM_FLEET', COUNT(*) FROM FLEET_INTELLIGENCE.ROUTE_DEVIATION.VW_FLEET
UNION ALL SELECT 'DIM_POIS', COUNT(*) FROM FLEET_INTELLIGENCE.ROUTE_DEVIATION.VW_POIS;

STOP if any view returns 0 rows. Generate data via Data Studio first.

Step 4: Run ETL Pipeline

Execute all 3 ETL steps in order. Each is a CREATE OR REPLACE DYNAMIC TABLE with refresh_mode='AUTO' (TRIP_DEVIATION_ANALYSIS uses lag=DOWNSTREAM, the two summaries lag='1 hour') so the Route Deviation pages reflect the active CONFIG.REGION/VEHICLE_TYPE without a manual rebuild and without a short idle-cost refresh cadence. Full SQL in references/sql-pipeline.md.

Run the verification query after all 3 steps to confirm row counts.

Step 5: Verify End-to-End

1. Check deviation distribution matches expected pattern (see references/dataset-guide.md)
2. Check daily trends show correct weekday/weekend patterns

Dashboard Schema Contract

The React ORS Control Center pages query these exact tables and columns. If the ETL pipeline changes column names, the React pages must be updated to match.

TRIP_DEVIATION_ANALYSIS

DAILY_DEVIATION_TRENDS

DRIVER_DEVIATION_SUMMARY

VW_VEHICLE_TELEMETRY (local projection view)

Examples

Example 1: Fresh deployment with seed data

User says: "Deploy route deviation analysis" Actions:

1. Run references/seed-data.sql to create projection views and ETL tables
2. Verify TRIP_DEVIATION_ANALYSIS has rows Result: Route deviation dashboard with deviation analysis, driver summaries, and daily trends (~5 min)

Example 2: Switch vehicle type after deployment

User says: "Show deviation analysis for trucks instead of e-bikes" Actions:

1. Update CONFIG table: UPDATE FLEET_INTELLIGENCE.ROUTE_DEVIATION.CONFIG SET VEHICLE_TYPE='hgv';
2. Re-run ETL Steps 4.1-4.3 from references/sql-pipeline.md
3. Verify TRIP_DEVIATION_ANALYSIS reflects HGV data Result: Dashboard shows truck fleet deviation metrics

Common Scenarios

• Fresh deployment: Run Steps 1-5 sequentially (~5 min total)
• Re-run after failure: All statements use CREATE OR REPLACE -- safe to re-run from failed step
• Change vehicle type: Update CONFIG table, re-run ETL Steps 4.1-4.3

Stopping Points

• Step 3: STOP if UNIFIED views return 0 rows
• Step 4.1: Verify TRIP_DEVIATION_ANALYSIS has rows
• Step 5: Verify deviation distribution matches expected pattern

Troubleshooting

UNIFIED views return 0 rows

Cause: Data Studio has not generated data for the configured VEHICLE_TYPE + REGION Solution: Run Data Studio to generate fleet data, or update CONFIG to match an existing vehicle type and region

Wrong vehicle type

Cause: CONFIG table has wrong VEHICLE_TYPE Solution: UPDATE FLEET_INTELLIGENCE.ROUTE_DEVIATION.CONFIG SET VEHICLE_TYPE='hgv', REGION='SanFrancisco'; then re-run ETL

Low TRIP_DEVIATION_ANALYSIS row count

Cause: Trips without planned routes or incomplete data Solution: Verify FACT_TRIPS has PLANNED_ROUTE_GEOG populated for the configured vehicle type

COUNT(*) AS ROWS syntax error

Cause: ROWS is a reserved word in Snowflake Solution: Use ROW_CNT as the column alias instead

DIM_TRIP_SCHEDULE has 0 rows

Cause: Data Studio may not have populated this table yet Solution: LEFT JOINs to VW_TRIP_SCHEDULE will return NULLs for ROUTE_VARIATION and TRIP_TYPE columns -- this is acceptable and does not affect deviation calculations

Recovery

All statements use CREATE OR REPLACE or IF NOT EXISTS, making re-runs safe. No manual cleanup needed -- fix the underlying issue and re-run from the failed step.

Output

Complete Route Deviation Analysis demo with:

• 5 projection views reading from SYNTHETIC_DATASETS.UNIFIED
• 3 ETL analytics tables (deviation analysis, driver summary, daily trends)
• React dashboard pages in ORS Control Center (Deviation Dashboard, Route Comparison, Route Inspector)

Cleanup

To remove all objects created by this skill:

DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION.DAILY_DEVIATION_TRENDS;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION.DRIVER_DEVIATION_SUMMARY;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION.TRIP_DEVIATION_ANALYSIS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION.CONFIG;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION.VW_VEHICLE_TELEMETRY;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION.VW_TRIP_DEVIATION;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION.VW_FLEET;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION.VW_TRIP_SCHEDULE;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION.VW_POIS;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION;

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