LogisticsLT Applied — Standalone

## The scenario You are a Network Data Analyst at a UK national parcel carrier. The Operations Director needs a Network Data Quality Audit before the next quarterly review — focusing on depot-hub assignment integrity, driver-vehicle continuity, shipment-event chain completeness, and SLA breach attribution. ## Dataset The full LogisticsLT schema — Region, Hub, Depot (self-referencing), Vehicle, Driver, Customer, Route, Shipment, ShipmentEvent, DeliveryAttempt. Realistic flaws are planted: orphan depots on inactive hubs, drivers assigned to retired vehicles, shipment events out of sequence, delivery attempts with no resolution. ## Deliverables Twelve outputs across six themes: **A. Joins & enrichment** — active route directory with driver + vehicle, hub-region rollup, depot self-join. **B. Orphan detection** — depots on deactivated hubs, routes on retired vehicles. **C. Duplicate detection** — duplicate active shipments by tracking ID, duplicate driver assignments on the same shift. **D. Missing relationships** — shipments with no scan events, delivery attempts with no resolution, drivers with no active route. **E. Reconciliation** — SLA breach attribution FULL OUTER JOIN, event-sequence completeness check. **F. View** — `vw_ShipmentAuditCard` per-shipment quality flags + three queries against it. Every query carries the four-line Investigation Framework header. ## Acceptance criteria (summary) Same rigour as the other Applied capstones — Framework header on every query, correct JOIN type chosen for the cardinality, NULL handling deliberate, expected row-count ranges met, view F1 reusable. Full brief, ERD, and expected row-count ranges appear inside the lesson once enrolled.