route-deviation

name	route-deviation
description	Deploy the Route Deviation Analysis demo: create projection views from SYNTHETIC_DATASETS.UNIFIED, run 3-step ETL pipeline, and register React dashboard pages. Works with any vehicle type configured via CONFIG table. Use when: setting up route deviation demo, detour analytics, fleet deviation analysis. Do NOT use for: general fleet tracking, real-time GPS monitoring, or non-deviation routing tasks. Triggers: deploy route deviation, deploy detour analytics, setup deviation analysis, route deviation demo.
depends_on	["build-routing-solution"]
metadata	{"author":"Snowflake SIT-IS","version":"2.0.0","category":"fleet-intelligence"}

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. 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

Privilege	Scope	Reason
CREATE DATABASE	Account	Creates FLEET_INTELLIGENCE database
CREATE WAREHOUSE	Account	Creates ROUTING_ANALYTICS warehouse
USAGE ON DATABASE SYNTHETIC_DATASETS	Database	Reads UNIFIED source data
USAGE ON SCHEMA SYNTHETIC_DATASETS.UNIFIED	Schema	Reads FACT_VEHICLE_TELEMETRY, FACT_TRIPS, DIM_FLEET, DIM_POIS, DIM_TRIP_SCHEDULE
CREATE SCHEMA	Database (FLEET_INTELLIGENCE)	Creates ROUTE_DEVIATION schema
CREATE DYNAMIC TABLE	Schema (FLEET_INTELLIGENCE.ROUTE_DEVIATION)	Creates ETL output tables (auto-refreshing)
CREATE VIEW	Schema (FLEET_INTELLIGENCE.ROUTE_DEVIATION)	Creates VW_ projection views

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

Configuration

Parameter	Default	Description
TARGET_DB	FLEET_INTELLIGENCE	Database for ETL output tables
TARGET_SCHEMA	ROUTE_DEVIATION	Schema for ETL output tables
WAREHOUSE	ROUTING_ANALYTICS	Warehouse for ETL execution

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 build-routing-solution 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 with lag='5 minutes' so they auto-refresh whenever CONFIG or upstream FACT_TRIPS change.

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 lag='5 minutes' and refresh_mode='AUTO' so the Route Deviation pages always reflect the active CONFIG.REGION/VEHICLE_TYPE without a manual rebuild. Full SQL in references/sql-pipeline.md.

Step	Table	Description
4.1	TRIP_DEVIATION_ANALYSIS	Actual vs expected comparison with deviation flags
4.2	DRIVER_DEVIATION_SUMMARY	Per-driver deviation statistics
4.3	DAILY_DEVIATION_TRENDS	Daily aggregated deviation metrics

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

Column	Type	Used By
TRIP_ID	VARCHAR	DeviationDashboard, RouteComparison, RouteInspector
DRIVER_ID	VARCHAR	DeviationDashboard
VEHICLE_ID	VARCHAR	RouteInspector (vehicle selector)
TRIP_DATE	DATE	RouteComparison
DISTANCE_DEVIATION_PCT	FLOAT	DeviationDashboard, RouteComparison
DISTANCE_DEVIATION_KM	FLOAT	DeviationDashboard, RouteComparison
ACTUAL_DISTANCE_KM	FLOAT	RouteComparison, RouteInspector
ACTUAL_PATH	GEOGRAPHY	RouteComparison (LATERAL FLATTEN ST_ASGEOJSON)
EXPECTED_PATH	GEOGRAPHY	RouteComparison (LATERAL FLATTEN ST_ASGEOJSON)
ORIGIN_NAME	VARCHAR	RouteComparison
DEST_NAME	VARCHAR	RouteComparison

DAILY_DEVIATION_TRENDS

Column	Type	Used By
TRIP_DATE	DATE	DeviationDashboard
TOTAL_TRIPS	NUMBER	DeviationDashboard
DEVIATION_RATE_PCT	FLOAT	DeviationDashboard

DRIVER_DEVIATION_SUMMARY

Column	Type	Used By
DRIVER_ID	VARCHAR	DeviationDashboard
TOTAL_TRIPS	NUMBER	DeviationDashboard
AVG_DISTANCE_DEVIATION_PCT	FLOAT	DeviationDashboard
MAX_DISTANCE_DEVIATION_PCT	FLOAT	DeviationDashboard
TOTAL_EXCESS_KM	FLOAT	DeviationDashboard

VW_VEHICLE_TELEMETRY (local projection view)

Column	Type	Used By
TRIP_ID	VARCHAR	RouteInspector (filter)
LATITUDE	FLOAT	RouteInspector (GPS track)
LONGITUDE	FLOAT	RouteInspector (GPS track)
SPEED_KMH	FLOAT	RouteInspector (speed chart)
POSTED_SPEED_KMH	FLOAT	RouteInspector (speed limit line)
GPS_ACCURACY_M	FLOAT	RouteInspector (accuracy chart, filter)
IS_DETOUR	BOOLEAN	RouteInspector (detour highlight)
IS_SPEEDING	BOOLEAN	RouteInspector
TS	TIMESTAMP	RouteInspector (ordering, teleport detection)
STATUS	VARCHAR	RouteInspector

---

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.