Building an Enterprise Transportation Data Lakehouse for Operational Machine Learning

This page specifies the layers, the grains, and the access rules that keep training rows honest. Figures used as examples are illustrative. They are not a measured result from a named fleet.

Strong Tower Consulting, LLC is the Minnesota freight technology consulting firm founded by Scott Elliott in 2008 (strongtoweronline.com). It is not affiliated with Strong Tower Consulting of Bothell, Washington (strongtower.consulting or strongtower.ai).

Why the TMS transactional schema is the wrong training ground

The TMS database behind the live board is a transactional schema. It holds the current order, the current stop, and the current assignment, and it overwrites them as dispatch and billing edit the load. That is the right shape for a screen. It is the wrong shape for a model. A row read tonight includes the arrival someone backdated, the accessorial added after delivery, and the late reason typed the next morning. None of those values were on the board when the planner decided. Training on the current row teaches the cleanup, not the decision.

The practical failure is also operational. A training query that joins orders, stops, breadcrumbs, and settlements on the production database competes with the morning board. The extract belongs on a replica or in the lakehouse, with a timeout, and with a login that cannot update a load. Grain is the other failure. One row per order hides which stop was late on a multi-stop. Status codes that share a column across pickup and delivery teach a blended error. The readiness evaluation names that grain problem before any table is built here. This page is where the surviving history goes.

Raw, conformed, and mart layers in carrier language

Raw is the landing zone. Change data from the TMS, invoice and settlement extracts, EDI archives, and the telematics feed arrive here as the source sent them, defects included. Files and batches are immutable. A bad geocode stays in raw so a later fix can be compared with what arrived. Nobody trains on raw, and nobody points a dispatcher at it. Retention in raw follows the source: breadcrumbs are not kept as long as invoices. The stream that feeds the breadcrumb files is specified in the telematics event streaming architecture. When that log expires, raw is the archive only for what you chose to land, not a surprise copy of every fix forever.

Conformed is where a stop means one stop, a power unit id means one tractor, and clocks are named. Duplicates from a flickered geofence collapse. Time is stored in UTC with the stop's local zone beside it. Keys that the TMS recycles, such as a PRO that was reused, are not treated as eternal identifiers. The mart is a later table at one decision grain, with only the columns that decision needs. Lane contribution is a mart. On-time is a mart. A feature set for one model is a mart. They are not three names for a single wide table. Industry writing calls the same stack bronze, silver, and gold. The words this carrier team should use are raw, conformed, and mart, because those words say what the layer is for.

Grain: order, stop, breadcrumb, settlement line, invoice line

Every fact table declares its grain in the first line of its definition, and a column from a finer grain is not pasted on to "make the model smarter." An order grain row can carry order id, lane, and total revenue. It cannot honestly carry one arrival time when the order has three stops. A stop grain row can carry the appointment and the arrival. It cannot carry the whole order's margin without double counting when someone sums stops. Breadcrumbs stay at breadcrumb grain until a named job aggregates them. Settlement lines stay at settlement grain. Invoice lines stay at invoice grain.

The natural key is the stable id from the system of record, not a sequence number that changes when a planner inserts a stop. Stop sequence is a display attribute. Order id joins the invoice. Stop id joins events. A trip-level settlement that covers two orders is the wrong grain for contribution until an owner stores the split. Do not invent the split in the mart. Leave the row out of the model and list it as a known gap. The same rule the billing pipeline uses, that the TMS invoice is the source of truth, is why invoice lines land from the approved invoice and not from an extracted PDF. That boundary is the ledger synchronization guide.

Conformed dimensions

Conformed means the same customer, facility, driver, tractor, trailer, and lane keys appear on every fact that needs them, with one definition. Customer is not a free-text bill-to name. Bill-to and shipper stay separate when they are different parties. Facility is a location id with a timezone and a yard polygon reference, not a city centroid and not a pin at 0,0. Driver id stays stable when the person changes trucks. Tractor is the power unit id. Trailer is its own id, because a dropped trailer is still at the dock after the tractor leaves. Lane is the carrier's lane code, or origin facility to destination facility. A city pair typed two different ways is not a lane key.

Pins and names change. Store the dimension version, or store the coordinates on the stop fact at the time the stop was conformed, so a corrected pin does not rewrite last year's arrivals. A model that trains on rewritten history thinks dispatch saw a dock that was only surveyed later. Effective dates on the dimension are how you answer "what did we believe this facility was on that Tuesday." The live TMS can update the current pin for the next load. The lakehouse does not have to follow that update backward.

Facts for lane contribution and on-time

Lane contribution is an order-grain fact rolled up by lane. Revenue comes from approved invoice lines: linehaul, fuel, and approved accessorials. Cost is only what the controller defined as direct for this mart. An illustrative definition is allocated driver pay plus the fuel expense the controller already posts, subtracted from that revenue. The definition is illustrative. Replace it with the carrier's own policy and do not treat the illustration as a margin the fleet achieved. A settlement that is not split to the order is excluded, not guessed. The mart stores the allocation rule id so a later reader can see which policy built the number.

On-time is a stop-grain fact. Compare the appointment window at that stop with the arrival the carrier named as the system of record, usually yard-polygon dwell, not the EDI 214. The 214 is a message. Using it as the default label teaches the model when the clerk posted, which is the wrong clock. That distinction is the same one in the 214 milestone translation guide. An order is not "on time" in this mart. A reader who wants an order view counts stops under a rule they write down, such as every stop on time, and that rule is a query, not a silent average inside the fact.

Late-arriving telematics

Breadcrumbs and geofence events arrive after the stop row was first conformed. A tractor flushes a batch when it leaves a building. Raw appends those events. Conformed may correct corroborating times inside a written window. An illustrative window is 48 hours from the stop's planned day. Inside the window, a late fence enter can update the corroborating clock. It does not silently change an invoice that billing has already approved, and it does not move a settlement line. Outside the window, the event stays in raw and on an exception list. The 48 hour figure is illustrative. Safety and billing replace it with the window they will actually stand behind.

The feature mart does not use the same patience. A decision at time T may use only events whose ingest time is at or before T. A fix with an earlier device time that landed after T is hindsight. It can restate a historical on-time report after the window rules pass. It cannot appear as a feature in "what the dispatcher knew." Keep ingest time on the conformed event so this filter is a column, not a story someone remembers in a notebook. Rebuilding a mart without that filter is how next quarter's model looks brilliant and fails on the live board.

A feature mart that is point-in-time correct

Point-in-time means the features on a row are values that existed at the decision timestamp, and nothing later. For a tender decision, that timestamp is when the planner accepted or declined. Features can include the lane's recent on-time rate as it stood then, the driver's remaining hours as they stood then, and the tractor pin age as it stood then. They cannot include the actual transit time, the final invoice total, the detention dollars added after delivery, or the late reason typed the next day. Those are outcomes. Outcomes are labels. Labels live in a separate table, joined only inside the training job, and they are absent from the mart the live scorer reads.

Leakage is a future column sitting in the feature list. It makes offline scores look strong and live scores useless, because the live call does not have tomorrow's invoice. The table lists the leaks that show up in freight work. The fix is structural: decision time on the feature row, label table keyed by the same decision id, and a review that rejects any feature whose source timestamp is after the decision. A column "pulled from the current order row" fails that review even if the name sounds operational. The current row is a future row relative to last month's decision.

Rates and payroll are not one open table

Customer rates and driver payroll do not belong in one wide table that every analyst and every model can scan. A person building an ETA feature needs stop clocks and pin age. They do not need settlement amounts or the contracted rate. A payroll analyst needs settlement lines. They do not need a year of raw breadcrumbs. Lane contribution is the narrow mart where revenue and the controller's cost definition meet, and the role list for that mart is short: finance, and the pricing owner. Everyone else gets a refusal, not a join.

Enforce this with separate schemas or separate grants, not with a shared view that hides columns in the BI tool while the warehouse login can still select them. Raw breadcrumbs follow the location rules from the streaming design: current operations and a named safety case, not a general reporting login. Invoice lines follow the billing retention the controller set. The audit of a ledger post, including request hash and response id, can sit beside the invoice line for the roles who already see invoices. It is not a feature, and it is not public inside the company. Access reviews are a data-protection practice. The lakehouse makes them enforceable because the tables were split on purpose.

Models read the mart and write recommendations elsewhere

A model reads a feature mart or a serving snapshot built from it. It writes a recommendation row: decision id, decision time, feature version, model version, the suggestion, and the event ids or stop ids it used. That row lives in an audit mart. It is not an update to the order, the invoice, or the settlement. If a person accepts the suggestion, the acceptance is written through the TMS field owner, the same way any other edit is written. The lakehouse then receives that edit through the ordinary raw feed. It does not push the acceptance back by updating a conformed table and hoping the TMS notices.

This split keeps a bad model from becoming a bad system of record. Rebuilding a mart must not replay recommendations into billing or into a customer message. The service boundary is the one in the decoupled services architecture: readers of history do not take locks on the live grid, and writers of suggestions do not pretend the suggestion is a fact. EDI status and ledger posts stay on their own pipelines, traced by shipment id and invoice id, not by a column a training job filled in. The 204, 990, 214, and 210 documents remain messages, as the transaction lifecycle describes. The lakehouse remembers them. It does not become them.

What the mart refuses to invent

Invented values train the model on the invention. The conformed layer records the gap, the mart drops the decision or marks the feature null, and the readiness list gains a line. Coverage is a published number: share of stops with a real pin, share of orders with a split settlement, share of decisions whose features were all known at decision time. Those shares are how you know the lakehouse is ready for a model. A full table that was filled by guesses is not ready.

Rebuilds are repeatable. Given the same raw batch and the same map version, conformed and the mart come out the same, including the same nulls. A job that "cleans up" nulls differently every night is not a layer. It is another unowned writer, which is the defect the transactional schema already has. Version the map that resolves customers, facilities, and lanes. Store that version id on the fact. When the map changes, new rows use the new version and old rows keep the old one until someone deliberately restates them.

Frequently asked questions

These are the questions data owners and dispatch leaders ask when a project wants to "point the model at the TMS." The answers keep grain, decision time, and access in front of any training job.

If raw is still a copy of production tables with no ingest time and no grain, you do not have a lakehouse yet. You have a backup with a longer name.