95M-row source · a fanout gate · an incumbent to beat

Transit Time Prediction Service

Three things here are worth more than the model. There is a grain-and-fanout gate between extraction and modelling that fails the run if a join has multiplied rows, the single most under-practised stage in applied machine learning. The target definition was taken from the production ETL rather than from a column name, with the extracted SQL committed as evidence. And there is an incumbent: an existing percentile-statistics table that the model is measured against, so the improvement is a comparison rather than an assertion.

2026mldataevaluation

What I owned

Owner. Data understanding, pipeline, model, validation gates and the agent integration.

Rows in the source fact table: 95.2McodeRows in the source fact table
Training extract: 2.49M rowscodeTraining extract
Scored backlog: 113.8K rowscodeScored backlog

3decisions, argued

Built onSAP HANA with a parameterised read-only helperAirflow and Cloud RunPython modelling stack

01Problem 02My role 03Decisions 04Impact

The problem

What was actually wrong

Transit padding, the buffer added to a shipment's expected duration, was set from a static percentile table per lane and shipping method. It did not adapt, and it treated a lane's behaviour eighteen months ago as equal in weight to last month's.

The data to do better existed, spread across eight cross-referenced warehouse sources including lane pad masters and contracted service times per lane and rate service.

Before this existed

A static percentile-statistics table of lane and method transit times.

Constraints

The conditions the design had to hold under

Constraints are the interesting part of an architecture. Without them any diagram looks reasonable.

The source table is very large: The order fact table runs to tens of millions of rows, so extraction has to be windowed and validated rather than trusted.
Joining eight sources invites silent row multiplication: A many-to-many join that inflates the row count produces a model trained on duplicated evidence, and nothing downstream will tell you.
There is an incumbent in production: A model that is not measured against what it replaces is not measured.

My role

Owner. Data understanding, pipeline, model, validation gates and the agent integration.

Designed

The eight-stage numbered pipeline with a validation gate between extraction and modelling
Recency-weighted training on closed orders, scored against the open backlog
The read-only, parameterised data-access layer

Built

The extraction, validation, target construction, training and prediction stages, and around twenty numbered exploration scripts that write versioned reports
Baseline comparison and coverage analysis against the incumbent table
Integration as an additional data source in the conversational agent platform

Decisions

The calls I would defend

Each one with the alternatives I rejected, what the choice cost, and how it turned out.

Decision

Between extraction and modelling, a dedicated stage asserts the expected grain and fails the run if the row count has multiplied.

Context

Eight cross-referenced sources, several of them one-to-many.

Alternatives, and why not

Check row counts by eye during developmentIt catches the problem the day you introduce it and never again. A join that starts fanning out six months later is invisible.

Rationale

Row multiplication is a silent correctness failure: the model trains happily on duplicated evidence and reports plausible metrics. The only reliable defence is an assertion in the pipeline.

What it cost

A stage that can fail a run for a data-shape reason rather than a code reason, which needs an operator who understands the distinction.

Outcome

Grain violations became a failed run rather than a quietly wrong model.

Impact

What changed, and how it is known

Every figure carries its basis. Nothing here is rounded up, and nothing modelled is presented as a result.

95.2Mcode

Rows in the source fact table

2.49M rowscode

Training extractEighteen months of closed orders.

113.8K rowscode

Scored backlogOpen and in-flight orders.

8code

Cross-referenced warehouse sources

314.4K rowscode

Incumbent baselineThe existing lane-and-method percentile statistics table the model is compared against.

Business: Planners get a transit estimate that reflects recent lane behaviour rather than a static percentile, exposed through the conversational platform they already use.
Engineering: A staged, numbered pipeline where exploration writes versioned reports rather than living in notebooks, with a validation gate that can fail the run.

Leadership and hindsight

What I influenced, and what I would change

Technical leadership

Made exploration a versioned artefact: around twenty numbered scripts that each write a report, rather than notebooks whose outputs nobody can reproduce.

What I would do differently

Recency weighting is a single decay applied uniformly. Lanes differ in how fast their behaviour changes, and a per-lane weighting would probably beat one global choice.
The model predicts a duration, not a distribution. Planners are really asking about risk, and a predicted interval would serve that better than a point estimate.

Stack

What it is built on, and why that

A technology list without reasons is a list of things I have heard of.

SAP HANA with a parameterised read-only helper: All extraction, via safe builders for the warehouse's placeholder syntax.No string interpolation into SQL, and no write capability, the same posture as the agent platform.
Airflow and Cloud Run: Scheduled batch and service deployment of the same pipeline.Consistent with the other pipelines, and the service form is what allowed the agent integration.
Python modelling stack: Recency-weighted training per lane and shipping method.The problem is tabular and the model is not the interesting part; the data verification around it is.

3 more sections are written and hidden: constraints, leadership, stack.