Last-Mile Route Efficiency and SLA Deviation Data Model Specification

Role: Staff Logistics Analytics Engineer specialising in last-mile operational modeling and dimensional data architecture.

Context

• Logistics Network: {{logistics_network_name}}
• Warehouse Platform: {{target_data_warehouse}}
• SLA Margin: {{sla_threshold_minutes}}
• Event Feed: {{tracking_event_granularity}}
• Dwell Delay Threshold: {{dwell_time_outlier_threshold}}
• Driver History Schema: {{driver_assignment_schema_type}}

Task

Produce an enterprise SQL data modeling specification defining dimensional tables, fact tables, and incremental CTE transformations that calculate planned vs. actual route variances, stop-level dwell time outliers, and driver SLA compliance rates.

Method

1. Map the grain of the dimensional model at the individual delivery stop attempt level, linking orders, routes, and drivers.
2. Architect an incremental fact table tracking delivery lifecycle state transitions based on {{tracking_event_granularity}}.
3. Write robust window-function CTEs using LEAD and LAG to compute travel legs, actual stop arrival offsets, and stop duration.
4. Formulate the conditional logic defining SLA breach categorization against {{sla_threshold_minutes}} and root cause classification.
5. Design dwell time calculation logic that isolates valid deliveries from operational bottlenecks exceeding {{dwell_time_outlier_threshold}}.
6. Integrate {{driver_assignment_schema_type}} structures to ensure historical driver-vehicle assignments preserve accurate point-in-time attribution.
7. Provide testing and data quality assertions (uniqueness, referential integrity, non-negative interval checks) in dbt-compatible syntax.

Constraints

• MUST handle out-of-order and duplicated delivery event timestamps gracefully via deterministic window sorting.
• MUST NOT hardcode timezone adjustments; use dynamic UTC conversions relative to the delivery hub's local timezone attribute.
• SQL code must be production-ready and fully compatible with {{target_data_warehouse}}.
• Dimension surrogate keys must be generated using deterministic hashing rather than auto-incrementing integers.

Output format

• 1. Conceptual and Physical Entity-Relationship Specification
• 2. Staging and Transformation SQL Logic (Documented CTEs with inline comments)
• 3. SLA Breach and Dwell Time Metric Calculation Layer (Full SQL View Definitions)
• 4. Data Quality, Anomaly Handling, and Unit Test Assertions Length must be between 900 and 1300 words.

Self-review

• Confirm that stop dwell calculations exclude transit intervals between consecutive waypoints.
• Ensure the SLA breach classification correctly incorporates the {{sla_threshold_minutes}} tolerance window.
• Verify all surrogate keys conform to deterministic MD5 or SHA256 hashing standards.