Accepted shipments
18 required fields checked
Shipments on time
10,000 of 20,000 records
For delayed shipments
Based on 10,000 delayed records
Per kilometre travelled
Total fuel expense ÷ total distance
WHY THIS MATTERS
The Reporting Layer Must Agree Before Decisions Follow
I built the validation, transformation and PostgreSQL reporting path so the same shipment produces the same metric in Python and SQL. The included snapshot has 20,000 accepted records and no rejects. Its 50% on-time rate is an observed dataset result, not a measured improvement in a company’s delivery performance.
ENGINEERING CONTRIBUTION
Repeat Loads Preserve Facts and Reporting Consistency
Validate before publication
Check 18 required fields, ranges, IDs and status/duration agreement. Invalid rows keep their source row and failure reasons. The default quality gate allows zero rejected records.
Load in one transaction
Upsert shipment facts and dimensions, then refresh six materialized views. Unchanged inputs preserve fact counts and load timestamps; changed records update by shipment ID.
Verify the failure paths
Integration tests compare all six SQL views with Python KPIs and exercise rollback, repeat loads and changed records. CI also executes three notebooks on Python 3.11 and 3.12.
Transaction and publication design · Validation and test scope
FOUR VIEWS OF THE OPERATION
Delivery Reliability, Travel Time and Fuel Expense
Select a transport mode.
Compare time, expense and routes.
Half of Recorded Shipments Arrive on Time
Rail, ship and truck all sit close to 50% on time. The gap between the highest and lowest mode is less than one percentage point, so mode alone does not identify a strong reliability advantage in this snapshot.
Delivery reliability by transport mode
Trips Average 7.52 Hours Longer Than Planned
Actual duration averages 79.56 hours versus 72.04 hours planned: a 7.52-hour gap, calculated before rounding. This average includes on-time and delayed shipments; the 17.01-hour delay figure uses only the 10,000 delayed records.
Planned and actual shipment duration
Fuel Ratios Differ by Less Than $0.02 per Kilometre
Weighted fuel expense ranges from $1.695/km for ship to $1.714/km for rail; truck is $1.701/km. The ratio divides each mode’s total fuel expense by its total distance, rather than averaging individual trip ratios.
The small spread does not justify a mode-switch recommendation by itself. Review comparable routes, distance and shipment mix first. These figures cover fuel expense only, not total operating cost or profit.
Route Volume and Delay Rates Guide Review
Switch between shipment volume and delayed share to distinguish busy routes from high-rate routes. Every displayed route has at least 100 records; a high percentage alone does not show how many shipments are affected.
WHAT I BUILT
A Reproducible Reporting Pipeline, with a Clear Data Boundary
I built atomic Parquet publication, quarantined rejects, transaction-safe database loads and consistent Python/SQL metrics. The manifest records input checksum, row counts and whether the database phase ran. Source dates, carrier identities, revenue and leg-level sequence are absent, so the next extension requires better source fields before monthly trends or delay prediction.
Reviewed 22 October 2026 against the project README and linked evaluation reports. Figures describe the documented dataset or test run.