Dwell Analysis

Deploy Dwell & Congestion Analysis

Two deployment paths.

• SA + synapse app (preferred for the data-contract app): dwell ships as a self-building data-contract pack at install-fleet-apps/fleet_sa_app/app/packs/fleet/dwell/ (data-model.yaml + entity-mapping.yaml). Its derived primitives rebuild the full DAG (state-detection -> sessionization -> enrichment -> H3/SLA/facility/driver/daily) into FLEET_APP.DWELL.*, verified bit-for-bit against the pipeline below. Deploy it with the pack installer (packs/_lib/install.py), not by hand. To change the analytics, edit the pack's data-model.yaml and regenerate.
• Control-app demo / source-of-truth (below): the imperative 12-step Dynamic Table pipeline. This is the canonical reference the pack's derived SQL was distilled from, and the path the ORS Control App demo uses. Keep references/sql-pipeline.sql in sync when changing dwell logic - it is the spec the pack mirrors.

Deploys a 12-step Dynamic Table pipeline that transforms vehicle telemetry into actionable dwell analytics: state detection, session grouping, H3 congestion heatmaps, SLA breach alerts, facility utilization, and fleet-wide daily trends. Vehicle-type agnostic -- works with trucks, taxis, e-bikes, e-scooters, or any fleet type. All data must come from active-dataset projection views (V_FACT_VEHICLE_TELEMETRY_CURRENT, V_DIM_FLEET_CURRENT, V_DIM_POIS_CURRENT, V_DIM_TRIP_SCHEDULE_CURRENT).

Prerequisites

1. Synthetic fleet data in SYNTHETIC_DATASETS.UNIFIED (any vehicle type):
   • FACT_VEHICLE_TELEMETRY -- GPS pings with STATUS column
   • DIM_POIS -- POI locations (warehouses, stores, rest stops)
   • DIM_FLEET -- vehicle metadata and driver profiles
   • DIM_TRIP_SCHEDULE -- trip schedule with OD pairs
2. CONFIG table set to desired VEHICLE_TYPE and REGION (created automatically during deployment)
3. ROUTING_ANALYTICS warehouse available
4. A role with privileges listed in the Required Privileges section below

Required Privileges

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

Configuration

Pipeline Architecture

VW_VEHICLE_TELEMETRY (source, from SYNTHETIC_DATASETS.UNIFIED.FACT_VEHICLE_TELEMETRY)
    |
    v
DT_STATE_CHANGES (Layer 1: LAG-based state detection)
    |
    v
DT_DWELL_SESSIONS (Layer 2: CONDITIONAL_CHANGE_EVENT sessionization + H3)
    |
    v
DT_DWELL_ENRICHED (Layer 3: joins location + fleet metadata)
    |
    +---> DT_H3_CONGESTION (hourly H3 heatmap)
    +---> DT_SLA_ALERTS (WARNING/CRITICAL breach detection)
    |       +---> SLA_ALERT_LOG (Task MERGE, 5-min schedule)
    +---> DT_FACILITY_UTILIZATION (daily visit stats)
    +---> DT_DRIVER_DWELL_SUMMARY (per-driver breach counts)
    +---> DT_DAILY_TRENDS (fleet-wide daily aggregates)

Error Logging

Follow the Error Logging convention in AGENTS.md. Log file prefix: dwell-analysis.

Quick Start

The fastest path to a working demo. Creates projection views over SYNTHETIC_DATASETS.UNIFIED tables (loaded by install-fleet-apps Step 8), computes GEOFENCE_POLYGONS, and inserts SLA_THRESHOLDS.

Quick check

SELECT COUNT(*) FROM FLEET_INTELLIGENCE.DWELL_ANALYSIS.GEOFENCE_POLYGONS;

If the table exists and has rows, data is already loaded. Skip to Step 1 (Run SQL Pipeline) -- the seed only creates views and static tables, DTs must still be created.

Create views and tables

Execute references/seed-data.sql. This creates CONFIG, 5 projection views, GEOFENCE_POLYGONS (computed from views), and SLA_THRESHOLDS.

After loading, you must still create the Dynamic Tables by running references/sql-pipeline.sql Steps 5-13. Dynamic Tables cannot be pre-baked.

Generate data for other regions (optional)

To generate data for a region other than San Francisco, use the full pipeline starting at Step 1.

Or use the centralized provisioner:

CALL FLEET_INTELLIGENCE.CORE.PROVISION_REGION('<RegionName>', ARRAY_CONSTRUCT('dwell-analysis'));

Workflow

Step 1: Run SQL Pipeline

Execute the complete SQL pipeline from references/sql-pipeline.sql. Run each statement sequentially using snowflake_sql_execute. All CREATE statements in the referenced SQL include COMMENT tracking tags per AGENTS.md convention ("origin":"sf_sit-is-fleet","name":"oss-dwell-analysis").

IMPORTANT: Step 4 (SLA_THRESHOLDS) requires two separate SQL calls -- one CREATE TABLE and one INSERT.

Note: VW_REST_STOPS will return 0 rows with seed data because DIM_POIS contains no LOCATION_TYPE = 'REST_STOP' records. The dwell pipeline still works using geofence-based analysis from VW_DESTINATIONS. REST_STOP data appears when generating data via Data Studio with POI diversity enabled.

| 3 | GEOFENCE_POLYGONS | Table | Destinations + rest stops with buffer radii | | 4 | SLA_THRESHOLDS | Table + INSERT | WARNING/CRITICAL minutes per location type (2 calls) | | 5 | DT_STATE_CHANGES | Dynamic Table | LAG-based state change detection | | 6 | DT_DWELL_SESSIONS | Dynamic Table | CONDITIONAL_CHANGE_EVENT sessionization | | 7 | DT_DWELL_ENRICHED | Dynamic Table | Join location + fleet metadata | | 8 | DT_H3_CONGESTION | Dynamic Table | Hourly H3 heatmap | | 9 | DT_SLA_ALERTS | Dynamic Table | SLA breach detection | | 10 | DT_FACILITY_UTILIZATION | Dynamic Table | Daily facility visit stats | | 11 | DT_DRIVER_DWELL_SUMMARY | Dynamic Table | Per-driver dwell + breach counts | | 12 | DT_DAILY_TRENDS | Dynamic Table | Fleet-wide daily aggregates | | 13 | SLA_ALERT_LOG + Task | Table + Task | Schedule-based alert logging (5-min schedule, created SUSPENDED to avoid idle cost; resume manually for a live demo) |

Step 2: Verify Pipeline

SELECT 'DT_STATE_CHANGES' AS DT, COUNT(*) AS ROW_CNT FROM FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_STATE_CHANGES
UNION ALL SELECT 'DT_DWELL_SESSIONS', COUNT(*) FROM FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_DWELL_SESSIONS
UNION ALL SELECT 'DT_SLA_ALERTS', COUNT(*) FROM FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_SLA_ALERTS
UNION ALL SELECT 'DT_DAILY_TRENDS', COUNT(*) FROM FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_DAILY_TRENDS;

SLA Threshold Tuning

Default thresholds in SLA_THRESHOLDS table:

Demo note: The default thresholds above are tuned for synthetic seed data so that DT_SLA_ALERTS populates immediately. seed-data.sql and sql-pipeline.sql ship the SAME values (single source of truth) - keep them in sync if you change either. The IDLE row makes the long ghost-idle sessions surface as "asset idle too long" alerts; DT_SLA_ALERTS includes both DWELL_* and IDLE statuses. For production, increase to realistic values (e.g., WAREHOUSE: 60/120 min, DESTINATION: 30/60 min, IDLE: 240/480 min).

Update thresholds by modifying the SLA_THRESHOLDS table directly. DT_SLA_ALERTS will refresh automatically.

Examples

Example 1: Quick deploy with seed data

User says: "Deploy dwell analysis" Actions:

1. Run references/seed-data.sql to create projection views, geofences, and SLA thresholds
2. Run references/sql-pipeline.sql Steps 5-13 to create Dynamic Tables
3. Verify DT_SLA_ALERTS and DT_DAILY_TRENDS have rows Result: 12-step Dynamic Table pipeline with SLA alerts, congestion heatmaps, and facility utilization (~10 min)

Example 2: Deploy with Data Studio data

User says: "Set up dwell analysis for London truck fleet" Actions:

1. Generate truck fleet data via Data Studio for London region
2. Update CONFIG table with VEHICLE_TYPE='hgv', REGION='London'
3. Run full pipeline from Step 1 Result: Dwell analysis pipeline processing London HGV telemetry with region-specific geofences

Stopping Points

• Step 1 (after Step 4 SQL): Verify SLA_THRESHOLDS has 5 rows
• Step 1 (after Step 5): Verify DT_STATE_CHANGES is refreshing
• Step 2: Verify all DT row counts are non-zero

Troubleshooting

Cleanup

To remove all objects created by this skill:

ALTER TASK IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.LOG_SLA_ALERTS SUSPEND;
DROP TASK IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.LOG_SLA_ALERTS;
DROP STREAM IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.TELEMETRY_STREAM;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.SLA_ALERT_LOG;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_DAILY_TRENDS;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_DRIVER_DWELL_SUMMARY;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_FACILITY_UTILIZATION;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_SLA_ALERTS;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_H3_CONGESTION;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_DWELL_ENRICHED;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_DWELL_SESSIONS;
DROP DYNAMIC TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.DT_STATE_CHANGES;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.SLA_THRESHOLDS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.GEOFENCE_POLYGONS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.CONFIG;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.VW_VEHICLE_TELEMETRY;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.VW_VEHICLE_FLEET;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.VW_DESTINATIONS;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.VW_REST_STOPS;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS.VW_TRIP_SCHEDULE;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.DWELL_ANALYSIS;

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