| name | backload-matching |
| description | Deploy the Backload Matching Engine demo: a fleet-wide VRP solve over idle-bound trailers + internal volumes + external freight-exchange offers, anchored on the OPENROUTESERVICE_APP.CORE.OPTIMIZATION function. The page picks one or many trailers, calls OPTIMIZATION once, and renders empty/loaded legs, KPI savings, and a Cortex rationale. Use when: setting up the DHL Freight backload demo, asset velocity / trailer rotation use cases, freight-exchange aggregation, internal-first vs external-second proposals, multi-trailer joint dispatch. Do NOT use for: route optimization VRP from PLACES (use route-optimization), route deviation analysis (use route-deviation), retail catchment (use retail-catchment), fleet intelligence car/e-bike demos, or single-leg directions tests (use FunctionTester). Triggers: backload, backload matching, empty mile, empty leg, asset velocity, trailer rotation, freight exchange, freight exchanges, idle trailer, idle-bound trailer, Timocom, WTransnet, Teleroute, B2P, DHL, DHL Freight, dispatcher proposal, internal-first match, supply chain action engine, NTBO, line-haul VRP, drop-and-hook. |
| depends_on | ["install-fleet-apps","route-optimization"] |
| metadata | {"author":"Snowflake SIT-IS","version":"1.0.0","category":"demo"} |
Deploy Backload Matching Engine
Adds a parallel page to the ORS Control App that solves the backload problem for any line-haul fleet with imbalanced lanes: trailers reaching the continent and waiting up to three days for a return load. The page issues a single OPENROUTESERVICE_APP.CORE.OPTIMIZATION(...) call that jointly assigns N idle-bound trailers to a pool of internal volumes (own waiting shipments) and external offers (synthesized in the style of Timocom, WTransnet, Teleroute, B2P), minimizing total empty kilometres. Internal-first preference is encoded as VROOM priority; ADR/equipment gating uses VROOM skills; direction-to-home bias is encoded in each vehicle's end location. Accepted plans are written back to PROPOSAL_DECISIONS to close the Action Engine loop.
The existing Route Optimization and Asset Velocity pages are not modified - Backload is an additive, parallel page.
Use Case Narrative
See references/use-case-narrative.md for the full story. Summary anchored in the May 5 NTBO call with DHL Freight (Volker Nachtsheim / Martin Ahleff) and the Asset Velocity Case 4 slide:
- ~2,500 trailers, ~100 Nordic dispatchers, ~20 new orders/min across Europe.
- Trailers reach the continent and wait up to 3 days in Paris for backloads.
- Today: manual portal-hopping across Timocom, WTransnet, Teleroute, B2P.
- Desired: fleet-wide "give me a structural plan for tomorrow" - internal-first, external-second.
- Generalises 1:1 to Maersk Inland, K+N Road, DSV, XPO, Geodis, Dachser, FedEx Freight, Schneider, J.B. Hunt - anyone with imbalanced lanes.
Prerequisites
install-fleet-apps deployed (OPENROUTESERVICE_APP database with all ORS services running). The demo runs against whatever region/vehicle preset is currently active in the Control App - no specific region required.route-optimization deployed.- Synthetic datasets seeded under
SYNTHETIC_DATASETS.UNIFIED.* (DIM_FLEET, FACT_TRIPS) - not strictly required for the page, but kept as a dependency since this skill was scoped against that dataset.
- Run Data Studio for the target
(region, vehicle_type) first so V_DIM_FLEET_CURRENT, V_DIM_POIS_CURRENT, and V_FACT_FREIGHT_OFFERS_CURRENT are populated. The page no longer has an in-page "Generate seed data" action.
Required Privileges
| Privilege | Scope | Reason |
|---|
| USAGE ON DATABASE FLEET_INTELLIGENCE | Database | Demo database |
| CREATE SCHEMA | Database (FLEET_INTELLIGENCE) | Creates BACKLOAD_MATCHING schema |
| CREATE TABLE | Schema (FLEET_INTELLIGENCE.BACKLOAD_MATCHING) | CONFIG, TRAILERS, INTERNAL_VOLUMES, EXTERNAL_OFFERS, PROPOSAL_DECISIONS |
| CREATE VIEW | Schema (FLEET_INTELLIGENCE.BACKLOAD_MATCHING) | VW_TRAILERS, VW_BACKLOAD_CANDIDATES |
| USAGE ON DATABASE OPENROUTESERVICE_APP | Database | Calls OPTIMIZATION + DIRECTIONS + ISOCHRONES for the active region's routing profile |
| USAGE ON SCHEMA OPENROUTESERVICE_APP.CORE | Schema | Same |
| USAGE ON FUNCTION OPENROUTESERVICE_APP.CORE.OPTIMIZATION(VARIANT, VARCHAR) | Function | Solver entry point (challenge, region) |
| USAGE ON WAREHOUSE ROUTING_ANALYTICS | Warehouse | Powers the page queries |
| USAGE ON DATABASE SNOWFLAKE | Database | Calls SNOWFLAKE.CORTEX.COMPLETE for "Why this assignment?" |
Note: ACCOUNTADMIN is NOT required.
Configuration
| Parameter | Default | Description |
|---|
| DATABASE | FLEET_INTELLIGENCE | Database for demo objects |
| SCHEMA | BACKLOAD_MATCHING | Schema for backload tables and views |
| WAREHOUSE | ROUTING_ANALYTICS | Warehouse for queries |
| REGION | (active preset) | Auto-derived from BACKLOAD_MATCHING.CONFIG, which mirrors the active Control App region/vehicle. No hardcoded city. |
| VEHICLE_CLASS_PROFILE | OPENROUTESERVICE_APP.CORE.VEHICLE_CLASS_PROFILE | Single source of truth for per-vehicle-class capacity (PAYLOAD_KG_TYP), shipment-weight band, ORS profile, costs (€/km, €/hr), and UI label. The skill is transport-type agnostic - no HGV-specific constants. Seeded with 8 classes (bicycle, ebike, foot, motorcycle, car, van, hgv, truck). Unknown vehicle_type → bootstrap and React both fail loudly so a custom preset never silently runs with wrong-class defaults. |
| TRAILER_COUNT | up to ~80 (driven by Data Studio dataset) | Idle-bound trailers for the active preset |
| INTERNAL_VOLUMES_COUNT | 120 | Internal waiting loads (most-recent FACT_TRIPS) |
| EXTERNAL_OFFERS_COUNT | 300 | Synthetic external offers per region |
| INTERNAL_PRIORITY | 100 | VROOM priority on internal jobs |
| EXTERNAL_PRIORITY | 10 | VROOM priority on external offers |
| TIME_WINDOW_TOLERANCE_HRS | 4 | Pickup-window slack added to jobs |
| MAX_VEHICLES_PER_SOLVE | 30 | Solver caps vehicles per call to keep ORS responsive |
| EUR_PER_EMPTY_KM | 1.20 | Used for KPI ("EUR/day reclaimed") |
| IDLE_COST_EUR_PER_DAY | 650 | Used for KPI ("EUR/day reclaimed") |
Error Logging
Follow the Error Logging convention in AGENTS.md. Log file prefix: backload-matching.
Workflow
Step 1: Set Query Tag
ALTER SESSION SET query_tag = '{"origin":"sf_sit-is-fleet","name":"oss-backload-matching","version":{"major":1,"minor":0},"attributes":{"is_quickstart":1,"source":"sql"}}';
Step 2: Verify Prerequisites
SHOW SERVICES IN DATABASE OPENROUTESERVICE_APP;
SELECT COUNT(*) FROM SYNTHETIC_DATASETS.UNIFIED.V_FACT_FREIGHT_OFFERS_CURRENT;
DESC FUNCTION OPENROUTESERVICE_APP.CORE.OPTIMIZATION(VARIANT, VARCHAR);
If any step fails, deploy the upstream skill first.
Step 3: Run Bootstrap
Run references/bootstrap.sql from the active connection:
snow sql -f .cortex/skills/backload-matching/references/bootstrap.sql -c <ACTIVE_CONNECTION>
This creates FLEET_INTELLIGENCE.BACKLOAD_MATCHING.{CONFIG, VW_TRAILERS, VW_INTERNAL_VOLUMES, VW_EXTERNAL_OFFERS, PROPOSAL_DECISIONS} as projection views over SYNTHETIC_DATASETS.UNIFIED.* filtered by the active Data Studio preset (CONFIG row).
The active preset is auto-synced when the user switches in DatasetPicker (or runs a new Data Studio job) - same pattern that fleet-intelligence-ebike, dwell-analysis, and route-deviation use.
Step 3b (one-time, existing accounts only): Backfill freight offers
Data Studio jobs created BEFORE the v1.0.199 control app rollout did not generate SYNTHETIC_DATASETS.UNIFIED.FACT_FREIGHT_OFFERS. Backfill them once:
snow sql -f .cortex/skills/backload-matching/references/backfill-freight-offers.sql -c <ACTIVE_CONNECTION>
Idempotent: skips regions that already have offers. New presets generated after v1.0.199 deploy will populate offers natively, so this script is a no-op on greenfield deployments.
Step 4: Interactive page (retired host)
The legacy Vite control app (ors_control_app) that hosted the Backload Matching page has been removed from this repo, and the page was not ported to the current fleet_sa_app/fleet_admin_app. Step 2's bootstrap.sql (plus the fleet_admin_app boot init.ts) still creates the full BACKLOAD_MATCHING.* projection views, so the objects are queryable and the OPTIMIZATION-based solve can be driven from SQL, but there is currently no bundled interactive page in the fleet apps. Take the page source from another repo if you need the UI. The verification steps below describe that retired page.
Step 5: Verify
In the app:
- Confirm the region picker is on whatever region/vehicle preset you want to demo (default: SanFrancisco/ebike on a fresh install). The page reads from
BACKLOAD_MATCHING.CONFIG, which is auto-synced to the active preset.
- Click Backload Matching in the sidebar (under Solution Accelerators).
- Verify the map shows ~80 trailer markers + ~120 internal volume circles + ~300 external offer circles.
- Adjust
Internal Priority slider if desired (default 100). Click Solve Backloads.
- Within ~10-30 sec the page should render colored loaded legs + gray empty legs + a per-trailer assignment table on the right rail with KPIs:
- % trailers assigned
- Total empty km
- % internal coverage (assigned-to-internal / assigned)
- EUR/day reclaimed (rough =
idle_days_saved * IDLE_COST_EUR_PER_DAY + empty_km_saved * EUR_PER_EMPTY_KM)
- Click any trailer in the table -> "Why this assignment?" card -> Cortex returns a 2-sentence rationale.
- Click Confirm Plan - assignments land in
FLEET_INTELLIGENCE.BACKLOAD_MATCHING.PROPOSAL_DECISIONS.
Step 6: AISQL Notebook (optional)
Upload assets/notebooks/backload-matching-aisql.ipynb to a notebook stage and walk through the AI_FILTER / AI_AGG / AI_CLASSIFY / AI_EXTRACT / Cortex Complete cells. The notebook re-uses the same tables and shows the AISQL parity to the page, plus the raw VROOM JSON the page sends to OPTIMIZATION.
Cleanup
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.BACKLOAD_MATCHING.VW_EXTERNAL_OFFERS;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.BACKLOAD_MATCHING.VW_INTERNAL_VOLUMES;
DROP VIEW IF EXISTS FLEET_INTELLIGENCE.BACKLOAD_MATCHING.VW_TRAILERS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.BACKLOAD_MATCHING.PROPOSAL_DECISIONS;
DROP TABLE IF EXISTS FLEET_INTELLIGENCE.BACKLOAD_MATCHING.CONFIG;
DROP SCHEMA IF EXISTS FLEET_INTELLIGENCE.BACKLOAD_MATCHING;
Note: this skill does NOT delete from SYNTHETIC_DATASETS.UNIFIED.FACT_FREIGHT_OFFERS since that table is owned by install-fleet-apps (Data Studio output) and is shared. Use the cleanup script in install-fleet-apps if you also want to remove the freight-offer data per preset.
The Control App image rollback is handled by re-deploying the previous image tag from the registry; the new page becomes inaccessible automatically when the schema is dropped (the page surfaces an empty state).
Out of Scope
- Live Timocom / WTransnet / Teleroute / B2P API integration (synthetic only - productisation note in
references/optimization-vrp-mapping.md).
- Asset Velocity 7-day idle alerting / email engine (the existing
Asset Velocity tab covers KPIs; this skill stays focused on the solver).
- DGF / myDHLI POD-map use case.
- Real-time streaming pipeline (we ship a polled view first; productisation: Snowpipe Streaming for
EXTERNAL_OFFERS).
