delivery ops · lakehouse + route planner
RH-Pipe
Route optimization is easy until a real Tuesday gets involved. RH-Pipe plans multi-truck furniture delivery for a Bay Area operation where loading 29 orders takes 106 minutes before the wheels roll, a three-person crew stops for lunch at hour four, and a customer who ordered 20 pieces expects one delivery day, not three. The pipeline ingests raw order files, geocodes every address, plans routes inside the constraints the crew actually works under, and lands every number in a Power BI star schema.
Every figure on this page comes from one real solve: 159 orders, 43 SKUs, 372 addresses, and two 26-foot box trucks, benchmarked against the one-truck first-in-first-out schedule the operation previously ran by hand.
4 days
to clear 159 orders — FIFO took 11
0 min
overtime across 8 truck-days — FIFO logged 192
−74%
miles driven vs baseline — 335 vs 1,312
A delivery day is not a math problem
The textbook version of this job is the vehicle routing problem: minimize total distance, visit every stop once, done. The real version starts at a depot in San Francisco at 08:00 with a crew clocking in, 23 minutes of prep, and a truck that has to be loaded piece by piece, in reverse route order, before it moves an inch. It includes a lunch break that lands wherever the route happens to be, a customer window that closes at 17:00 sharp, and a warehouse charging rent on every item that did not ship today.
So the solver is built backwards from the operation, not forwards from the algorithm. Every rule below exists because the alternative failed a driver, a customer, or the ledger. The interactive day plan and every metric on this page are the planner's actual output on the real order book.
One day, as the system plans it
This is Day 1 of the benchmark solve, exactly as planned. Two trucks, 50 of 159 orders delivered. Truck 1 drew a short, bulky day: it maxed out on volume with just two addresses and was home by 13:26. Truck 2 drew the long day: five hours on the road, a coordinated drop, lunch at hour four, and two stops flagged for late-arrival risk. Tap any segment to see the rule that produced it.
08:23 – 10:08 · Loading 29 orders — 106 min
Loading is not free
Every product carries its own loading minutes, and the truck is packed in reverse route order so the next stop is always at the door. Twenty-nine orders held Truck 2 at the dock for 106 minutes, and the crew is on payroll from 08:00 the whole time — the BI layer reports hours on job and hours on the road as two separate clocks.
rule · load_sequence 1 = deepest = last delivery; wheels roll only after Σ load_min
Times are the planner's output for the real Day 1. Addresses are shown at neighborhood level.
The constraint catalogue
Twelve rules the planner refuses to break, grouped by who they protect.
The clock
Tuesday through Saturday
01Crews do not run Sundays or Mondays, so the planner cannot either. Rollovers skip to the next real delivery day.
rule · next delivery day ∈ {Tue…Sat}
The 17:00 wall
02No new deliveries at or after 17:00. A stop in progress gets finished; a stop not started waits until tomorrow.
rule · arrival ≥ 17:00 → refuse; return drive exempt
A 90-minute mercy rule
03A stop that would strand the crew deep into the evening is not worth the miles saved. It rolls to the next day.
rule · finish + return ≤ 17:00 + 90 min, else roll
The crew
Loading before rolling
04Twenty-nine orders held a truck at the dock for 106 minutes. Departure time is earned, not assumed.
rule · depart = 08:00 + 23 min + Σ load_min
Lunch at hour four
05Sixty minutes, wherever the route is. Every downstream ETA moves with it.
rule · break fires at +240 min elapsed
Unload from the door
06Trucks pack in reverse route order so nobody digs a bed frame out from behind six sofas.
rule · load_sequence 1 = deepest = last stop
The customer
One address, one day
07A twenty-piece order arriving across three random days is how you lose the account. Same-address orders ship together.
rule · address group never splits across days by choice
Two trucks, thirty minutes
08When an order overflows one truck, the second truck arrives within half an hour of the first, by staggered departure.
rule · coordinated arrivals ≤ 30 min apart
No address eats the day
09One mega-delivery cannot consume a whole shift while other customers wait. Past 60% of a truck's window, the remainder rolls.
rule · per-address cap = 60% of usable window
The money
Warehouse rent accrues nightly
10Every undelivered item pays holding cost tonight: $10 large, $5 small. The queue drains expensive-to-hold items first.
rule · sort: priority → holding $ → volume
Costs split honestly
11A crew hour costs $104 fully loaded: one driver at $30, two helpers at $25, times a 1.3 benefits multiplier. Mileage is planned at $1.205 from a modeled $0.74 to $1.67 range, 54% of it fuel. A truck that rolls at all costs $85 that day.
rule · cost = fuel + fixed + labor + holding
Carbon on the books
12Driving burns per mile; the dock, the door, and lunch burn per idle minute. Both are priced at $50 per tonne.
rule · CO₂ = miles·rate + idle·rate, at $0.05/kg
What we optimized, and what we refused to
The objective is simple: fewest miles and crew hours that still respect every rule above. What gets optimized is the order of attack. Pending orders queue priority first, then highest holding cost, then largest volume, so the items charging the most warehouse rent leave first. Orders are grouped by address, bin-packed onto trucks under volume and weight caps, and dealt round-robin so both crews get comparable days rather than one truck drawing every short straw.
Sequencing is a nearest-neighbour seed improved by 2-opt, capped at 500 iterations. That is a deliberate choice, not a shortcut: a dispatcher can trace why stop 7 follows stop 6, it runs in seconds inside a Fabric notebook, and it degrades gracefully. The same honesty applies to the road data. Distance matrices come from OpenRouteService's free tier, requested in 49-stop chunks with 1.6-second pauses to respect the 40-calls-per-minute quota, with a haversine fallback per failed chunk. Total routing API spend: zero dollars.
Day 1 proved the double cap matters: Truck 1 filled 100% of its volume at 75% of its weight; Truck 2 hit 99% of its weight at 91% of its volume. Same fleet, different binding constraint.
Against the schedule it replaced
The baseline is not a strawman. Naive mode replicates how scheduling was actually done: one truck, orders in the sequence they arrived, first in, first out. Same order book, same cost model, same time rules. The FIFO plan ran late-risk stops on all 11 days and finished in overtime on 9 of them. The optimized plan cleared the same 159 orders in 4 days without a single overtime minute.
Delivery days only — the calendar skips Sundays and Mondays.
One deliberately unheadlined number: booked delivery fees. The operation charges a flat zone rate per truck visit, so FIFO's repeat visits to the same address book more fees than consolidated routes do. Optimizing consolidation trades billable visits for cost, overtime, and customer goodwill. That tension is real, it is visible in the BI layer, and pretending it away would be exactly the kind of fiction this project exists to avoid.
The pipeline behind the planner
Planning is the last step of a lakehouse pipeline. Raw order, address, and product files land as CSVs in Fabric Lakehouse storage. Cleaning normalizes them and reconciles order lines against the product catalogue by fuzzy match at a 0.80 threshold, repairing the kind of mess real files carry: straight quotes standing in for inch marks, fractions written out, trailing symbols the export truncated. In the benchmark run that recovered 2 mismatches and left zero unmatched. Addresses geocode through OpenRouteService behind an MD5-keyed cache table, so each address is geocoded exactly once, ever. Everything lands in Delta tables, the solvers read and write Delta, and Power BI reads the star schema.
fact_stop
One truck visit to one address. Carries ETAs, delivery windows, and the flags a dispatcher actually reads.
fact_delivery
One order item. The manifest grain, including each item's LIFO position in the truck.
fact_truckload
One truck-day. Clock-in to clock-out, utilization, and the full cost split.
dim_solve
One solve run. The slicer anchor that lets Power BI put optimised and FIFO plans side by side.
dim_location
One customer address with zone and geocode status.
dim_product
One SKU with its physical reality: volume, weight, loading and unloading minutes, holding cost.
Delivery zones price by geography: local (CA) at $299 per visit, extended (OR/NV/AZ) at $399, national at $599, Alaska and Hawaii at $1,699.
The Power BI report reads like the driver's day, page by page.
01
Start the day
log in, pick your truck and date
02
Load Manifest
pick order in LIFO sequence, load time, departure estimate
03
Route & Unload
stop-by-stop schedule and map with arrival windows
04
Cost Summary
fuel, labor, fixed, and holding against revenue
05
CO₂ Summary
driving and idle emissions, priced
Every page carries a solve-mode slicer, so any view flips between the optimized plan and the FIFO baseline it replaced.
What it does not model yet
Realism includes being honest about the edges.
Traffic by time of day
ORS matrices are static. A 51-minute leg at 13:00 is treated the same as at 08:30. Time-dependent matrices are the next data upgrade.
Driver assignment
Truck-days carry a TBD driver until the assignment module lands; skill and familiarity with a route are not yet modeled.
Appointment windows
The schema carries promised-window fields on orders and stops, and the solver stamps a 30-minute window around its own ETA. It does not yet accept a customer-chosen window as a constraint.
Priority flag
The queue honors a priority flag end to end, but the ops process that sets it is still rolling out. The benchmark ran with zero priority orders.
Declared but not enforced
Config carries a 9-hour max shift and 15-minute payroll rounding the planner does not read yet. The 17:00 window plus the 90-minute ceiling bound the shift implicitly instead.
Heuristic, not exact
2-opt accepts a local optimum in exchange for explainability and speed. The gap to an exact CVRPTW solution is unmeasured, and for a two-truck fleet, acceptable.
Technologies
Next project
Coffee Futures Snowflake Warehouse