snowflake-labs/fleet-intelligence-food-delivery

Food Delivery Fleet Intelligence

Food delivery fleet intelligence solution powered by Data Studio synthetic datasets.

Data Source

Data is generated using Data Studio in the ORS Control Panel, which writes to:

• SYNTHETIC_DATASETS.UNIFIED.FACT_VEHICLE_TELEMETRY
• SYNTHETIC_DATASETS.UNIFIED.FACT_TRIPS

Projection views in FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY filter the unified tables by vehicle type and region via a CONFIG table. See references/sql-projection-views.sql for the complete view definitions.

Objects created:

• CONFIG — single-row filter table (VEHICLE_TYPE + REGION)
• DELIVERIES + RESTAURANTS_ENRICHED — React Fleet Delivery page views

The San Francisco baseline is loaded by build-routing-solution Step 8 into UNIFIED tables.

---

Configuration

| Parameter | Default | Description | |-----------|---------|-------------| | DATABASE | FLEET_INTELLIGENCE | Target database for all objects | | SCHEMA | FLEET_INTELLIGENCE_FOOD_DELIVERY | Schema for projection views and CONFIG | | WAREHOUSE | ROUTING_ANALYTICS | Warehouse for queries | | VEHICLE_TYPE | ebike | Vehicle type filter applied to UNIFIED tables | | REGION | san_francisco | Region filter applied to UNIFIED tables |

Prerequisites

1. Snowflake Account with appropriate privileges
2. OpenRouteService Native App installed from Snowflake Marketplace (for Data Studio generation)
3. Data Studio dataset generated in ORS Control Panel (or seed data loaded by build-routing-solution Step 8)

Required Privileges

| Privilege | Scope | Reason | |-----------|-------|--------| | CREATE DATABASE | Account | Creates FLEET_INTELLIGENCE database | | CREATE WAREHOUSE | Account | Creates ROUTING_ANALYTICS warehouse | | CREATE SCHEMA | Database (FLEET_INTELLIGENCE) | Creates FLEET_INTELLIGENCE_FOOD_DELIVERY schema | | CREATE TABLE | Schema | Creates CONFIG table | | CREATE VIEW | Schema | Creates projection views | | SELECT ON SYNTHETIC_DATASETS.UNIFIED | Schema | Reads Data Studio output tables |

Note: ACCOUNTADMIN is NOT required. Create a custom role with the above privileges.

---

Error Logging

Follow the Error Logging convention in AGENTS.md. Log file prefix: fleet-intelligence-food-delivery.

Quick Start

The primary data path is Data Studio in the ORS Control Panel:

1. Open the ORS Control Panel > Data Studio page
2. Select the ebike (E-Bike Couriers) profile
3. Configure region, fleet size, and generation parameters
4. Run the generation job

This writes to SYNTHETIC_DATASETS.UNIFIED.FACT_VEHICLE_TELEMETRY and SYNTHETIC_DATASETS.UNIFIED.FACT_TRIPS. The projection views in Step 10 read from these tables.

Alternative: Load San Francisco Baseline from Seed Data

Seed data is pre-loaded by build-routing-solution Step 8 into SYNTHETIC_DATASETS.UNIFIED. To create the projection views:

Execute references/seed-data.sql. This creates CONFIG, DELIVERIES, and RESTAURANTS_ENRICHED views over UNIFIED data.

Workflow

Execute each step in order using snowflake_sql_execute. Substitute {PLACEHOLDER} values based on the user's chosen configuration before executing.

CRITICAL: Execution Rules

These rules MUST be followed to avoid silent failures:

1. One statement per snowflake_sql_execute tool call. Never combine multiple SQL statements (CREATE, INSERT, SET, USE) in a single tool call. Multi-statement blocks can silently fail -- tables may be created with 0 rows and no error is reported. 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. Use FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.<object> instead of relying on USE DATABASE / USE SCHEMA. Session context from USE statements does not persist across snowflake_sql_execute calls.

3. Never use SET session variables. Variables set with SET VAR = 'value' do not persist across calls. Instead, substitute literal values directly into the SQL before execution.

4. Verify row counts after each CTAS. Run SELECT COUNT(*) FROM <table> after every CREATE TABLE ... AS SELECT to catch silent failures early.

5. All CREATE statements must include a COMMENT tracking tag per AGENTS.md convention: COMMENT = '{"origin":"sf_sit-is-fleet","name":"oss-fleet-intelligence-food-delivery",...}'. See references/sql-projection-views.sql for tagged SQL.

Step 1: Set Query Tag for Tracking

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

Step 2: Configure Database, Warehouse, and Schema

Create FLEET_INTELLIGENCE database, ROUTING_ANALYTICS warehouse, and FLEET_INTELLIGENCE_FOOD_DELIVERY schema.

Step 3: Create Projection Views

Run references/sql-projection-views.sql to create CONFIG table + projection views from SYNTHETIC_DATASETS.UNIFIED. Creates 3 objects: 1 config table + 2 React views (DELIVERIES, RESTAURANTS_ENRICHED).

Step 4: Verify

SELECT 'CONFIG' AS TBL, COUNT(*) AS CNT FROM FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.CONFIG
UNION ALL SELECT 'DELIVERIES', COUNT(*) FROM FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.DELIVERIES
UNION ALL SELECT 'RESTAURANTS_ENRICHED', COUNT(*) FROM FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.RESTAURANTS_ENRICHED;

Expected: CONFIG=1, DELIVERIES and RESTAURANTS_ENRICHED > 0 (row counts depend on Data Studio generation or seed data).

---

Troubleshooting

| Issue | Solution | |-------|----------| | No data in projection views | Run Data Studio generation with ebike profile for your region | | CONFIG table missing | Run Step 3 to create CONFIG + projection views |

Cleanup

To remove all objects created by this skill:

-- Projection views
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.RESTAURANTS_ENRICHED;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.DELIVERIES;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.CONFIG;
-- Legacy objects (from deprecated Steps 3-9)
DROP STREAMLIT IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.SWIFTBITE_DELIVERY_DASHBOARD;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.DELIVERY_SUMMARY;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.DELIVERY_ROUTE_PLAN;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.DELIVERY_NAMES;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.ORDERS_ASSIGNED_TO_COURIERS;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.COURIER_LOCATIONS_V;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.VW_DELIVERY_SUMMARY;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.VW_COURIER_LOCATIONS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.COURIER_LOCATIONS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.DELIVERY_ROUTE_GEOMETRIES;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.DELIVERY_ROUTES_PARSED;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.DELIVERY_ROUTES;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.ORDERS_WITH_LOCATIONS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.DELIVERY_ORDERS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.ADDRESSES_NUMBERED;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.RESTAURANTS_NUMBERED;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.COURIERS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.CUSTOMER_ADDRESSES;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.RESTAURANTS;
DROP STAGE IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY.STREAMLIT_STAGE;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.FLEET_INTELLIGENCE_FOOD_DELIVERY;

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