When the data vendor changes but the report can’t

A paratransit agency I worked with runs weekly productivity reporting on top of their scheduling and dispatch platform—one row per driver run per day, 29 columns, going back years. It's a Python notebook reading from the vendor's tables. Then, mid-year, the agency replaced that vendor with a new platform. The notebook stopped working. The report still had to go out.

That's the surface problem. The interesting one is what "the same report" even means when the underlying system has changed.

Same names, different measurements

The new vendor divides a driver's shift into different categories than the old one did. A lot of the column names carried over—ServiceHours, DeadHeadHours, SlackHours—but the numbers behind them shifted, and in one case dramatically.

The new platform's DeadHeadHours came out to 4.7% of on-duty time. The old one had been running at 13.6%. That's the kind of gap that makes ops distrust the whole report, so I had to figure out where the time went before shipping anything.

It turned out the new vendor only records one leg of deadhead—the drive back to the depot at end of shift. The drive out to the first pickup, which the old system had labelled as deadhead, was sitting inside SlackHours instead. Two legs, same total driving, different bucket. So the "gap" was mostly a labelling change:

-- pull-out leg reconstructed from the vendor's own state timers
CAST(
    ISNULL(first_accept_deadhead_time,  0)
  + ISNULL(first_arrival_deadhead_time, 0)
  AS decimal(10,4)
) / 3600.0 AS PullOutHours

Adding pull-out back in brought deadhead to 11.1%—still not quite 13.6%, and the remainder turned out to be stationary post-service time (drivers still logged on after their last dropoff with the vehicle parked). That last piece became an ops decision rather than a technical one, so I documented it as three options with the trade-offs and left it to them to choose.

Two identities that had to hold

The whole thing lives or dies on reconciliation. Every hour and mile the vendor records has to land somewhere in the report and add up:

TotalHours = ServiceHours + DeadHeadHours + SlackHours
           + BreakHours   + LunchHours    + RefuelHours + OtherHours

TotalMiles = ServiceMiles + DeadHeadMiles + SlackMiles + Blo

If either of these breaks by more than a rounding error, something's wrong with the data, not the formula. I built those checks into the query so a bad day surfaces immediately instead of quietly filing.

Reconciling to the vendor's own report

The new platform publishes a Provider NTD dashboard—the same figures the agency files to the Federal Transit Administration. So I wrote a second query that rolls the productivity report up to provider-day and compares against it:

SELECT
    Provider,
    Date,
    SUM(ServiceHours + SlackHours)              AS RevenueHoursExclLayover,
    SUM(ServiceHours + SlackHours + OtherHours) AS RevenueHours,
    SUM(ServiceHours)                           AS DR_RevenueHours,
    SUM(ServiceMiles)                           AS RevenueMiles,
    SUM(SlackMiles + Blo)                       AS OffFareMiles,
    SUM(ServiceMiles + SlackMiles + Blo)        AS ServiceMiles,
    SUM(PassengerMiles)                         AS PassengerMiles
FROM dbo.ProductivityReport
GROUP BY Provider, Date;

All nine columns match the vendor's dashboard across the sample week. The largest disagreement is 0.006 hours on a day with roughly 130 runs—three-decimal rounding, not a real gap.

What shipped

• The productivity report—37 columns, verified against the vendor's own dashboard on nine NTD figures and against the legacy report on 34 days / 6,498 runs across all providers.
• An NTD parity query, separate from the report, that reproduces the vendor's dashboard from the same tables—an independent way to defend a number if it's ever questioned.
• Five ops-facing documents: a plain-language glossary, a one-page calculation reference, a deadhead decision handout, the parity dictionary, and an open-issues list.

The most useful realization on the documentation side was that I'd been mixing three things—definitions, formulas, and stuff-we're-still-figuring-out—into a single eight-page Word doc, and ops was getting stuck on the "still figuring out" parts. Pulling each into its own file made all of them shorter, and only one of them has to change week to week.