# Route Deviation

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

- Skill: `snowflake-labs/route-deviation` (Agent Skill, multi-file: 4 files)
- Install (CLI): `npx skillmds@latest add snowflake-labs/route-deviation`
- Raw SKILL.md: https://api.skillmd.com/api/skills/snowflake-labs/route-deviation/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Web & Frontend
- Author: snowflake-labs (https://skillmd.com/u/snowflake-labs)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/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. 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

| 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 `install-fleet-apps` Step 8), then runs ETL to produce analysis tables. No Data Studio generation needed.

### Quick check

```sql
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:
```sql
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:
```sql
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:

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

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

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