Air-to-ocean candidates ranked by savings and CO₂

Freight Mode-Shift Optimization Engine

The engineering interest is in the checkpointing and the coverage model. Every stage writes two artefacts, a compact one for fast resume and a human-readable one for inspection, keyed by source snapshot and anchor date, so a run is both resumable and auditable by a planner who was not there when it ran. Coverage is computed across multiple horizons plus lead time, and the inclusion filter is explicit rather than implied, because 'which items are even candidates' turns out to be most of the answer.

2025–2026dataeconomicsml

What I owned

Sole author of the engine, its canonical schema and its deployment.

Item-site rows evaluated per run: ~75KcodeItem-site rows evaluated per run
Planning sites covered: ~33codePlanning sites covered
Coverage horizons: 13 / 26 / 52 weekscodeCoverage horizons

2decisions, argued

Built onSAP HANA and a document storeCloud Run service and jobAirflow on Cloud Composer

01Problem 02My role 03Decisions 04Impact

The problem

What was actually wrong

Air freight is fast and expensive. Ocean is slow and cheap. Which items can move depends on demand coverage over the transit horizon, and that calculation spans planning data, demand history, lane rates and spend across two different systems.

Doing it by hand meant it happened rarely and covered a fraction of the catalogue.

Constraints

The conditions the design had to hold under

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

Two source systems with different shapes: Planning and demand data live in the warehouse; other inputs live in a document store. The pipeline has to reconcile both into one canonical schema.
A weekly batch that must be resumable: A multi-stage pipeline over tens of thousands of item-site rows fails somewhere eventually, and re-running from the beginning wastes the extraction.

My role

Sole author of the engine, its canonical schema and its deployment.

Designed

The canonical schema and source catalogue that reconcile two source systems
Per-stage checkpointing with dual artefacts: one for machines, one for people
The coverage model across multiple horizons plus lead time

Built

The full pipeline, packaged self-contained with its contracts vendored so it has no external workspace dependency
Deployment as both a service and a scheduled job

Decisions

The calls I would defend

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

Decision

Each stage writes a compact format for fast resume and a spreadsheet for human inspection, keyed by source snapshot and anchor date.

Context

A planner asking why an item was excluded needs to see the intermediate state, and a re-run needs to load it quickly.

Alternatives, and why not

One machine format, with a viewer if anyone asksNobody asks. They just stop trusting the output, and the trust is the product.

Rationale

The two readers have genuinely different needs, and writing both costs almost nothing at this data size. Keying on snapshot and anchor date means a checkpoint is unambiguously attributable to a run.

What it cost

Duplicate storage per stage, which is trivial relative to the value of a planner being able to open the intermediate state.

Outcome

Exclusions are explainable without re-running anything.

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.

~75Kcode

Item-site rows evaluated per runAfter the inclusion filter.

~33code

Planning sites covered

13 / 26 / 52 weekscode

Coverage horizonsPlus lead time.

Business: Air-to-ocean candidates are ranked weekly by modelled freight savings and carbon impact, so both dimensions of the trade-off are visible at the point of decision.
Engineering: A canonical schema and documented source catalogue over two dissimilar source systems, with resumable staged execution.

Leadership and hindsight

What I influenced, and what I would change

Technical leadership

Surfaced the emissions dimension alongside the financial one rather than treating carbon as a reporting afterthought.

What I would do differently

Savings are modelled from cost differential and air share; they are not measured against realised outcomes. Closing that loop, comparing recommended shifts against what actually moved and what it actually cost, is the obvious next step and the one that would make the numbers defensible rather than indicative.

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 and a document store: Planning, demand, lane rate and spend inputs.Two source shapes reconciled into one canonical schema, documented in the repository rather than in someone's head.
Cloud Run service and job: Interactive and scheduled execution of the same pipeline.One implementation, two invocation modes, no drift between them.
Airflow on Cloud Composer: Weekly batch scheduling.Consistent with the other pipelines, so operational knowledge transfers.

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