snowflake-labs/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. 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 TABLE | Schema (FLEET_INTELLIGENCE.ROUTE_DEVIATION) | Creates ETL output tables | | 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 tables (TRIP_DEVIATION_ANALYSIS, DRIVER_DEVIATION_SUMMARY, DAILY_DEVIATION_TRENDS) computed from the views.

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

Set the session query tag for tracking. See references/sql-pipeline.md > Query Tag.

Step 2: Create Infrastructure

Create target database, schema, and warehouse. See references/sql-pipeline.md > Infrastructure Setup.

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 TABLE. 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 TABLE IF EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION.DAILY_DEVIATION_TRENDS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.ROUTE_DEVIATION.DRIVER_DEVIATION_SUMMARY;
DROP 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.