Parcel Scans, Route Realignments, and a Reused Driver Key

The three questions have different grains and different temporal semantics, so a single enlarged fact will not solve them. Extend the star for presentation, and add a scan-level factless fact or bridge to capture route assignment at scan time. Data Vault is not the right first move: it is an integration and historization pattern, not a reporting layer, and an eight-week, two-engineer migration would consume the budget without fixing the as-of conflict.

The reasoning is grain-first. Parcel scans are the atomic event and support on-time measurement. Route realignment is a temporal many-to-many relationship: one scan can be associated with the route assignment at scan time and with the current assignment. A bridge table with effective dates, joined through a role-playing date dimension, lets each report state its as-of semantics instead of silently disagreeing.

Revenue per pallet is not additive at scan grain. Pallet coverage is only 63%, so multiplying or allocating revenue at scan level would fabricate precision. Model a pallet manifest or allocation fact at pallet grain and report revenue per pallet only on the covered population, with coverage percentage displayed. Keep the scan fact narrow and add conformed dimensions.

Driver productivity per shift needs a shift fact or an explicit shift dimension with start and end timestamps; it should not be inferred from scan timestamps alone. The star remains the semantic layer, the bridge fixes point-in-time route attribution, and the pallet and shift facts answer the other two questions at their correct grains.

Reusing an employee number means the natural key no longer identifies a person over time. If the dimension is Type 2, a new driver can inherit or collide with the history of a previous holder; productivity, tenure, and safety metrics then attach to the wrong person. If the dimension is Type 1, history is overwritten and past shifts are silently reassigned. Either way, the error is invisible in totals until someone audits an individual record.

Design a durable key:

• Introduce a surrogate driver_sk for the dimension and a durable employee_party_id from the HR master as the person identifier. The operational employee number becomes an attribute with validity dates, not the primary identifier.
• If legacy records lack a party ID, match deterministically on employee number plus start date, legal name, and hub, and mark unresolved matches rather than guessing. Never merge two people on employee number alone.
• Build Type 2 history on the durable key, with non-overlapping effective dates and a current-row flag. Add a constraint or test that fails if two rows for the same party overlap.
• For reporting, join facts to the dimension on the durable key and the event date. Operational lookups can still use employee number for convenience.

Include a data-quality check that flags any employee number associated with more than one party ID within the retention window. This converts an invisible attribution error into an actionable exception.

The 6.8% gap is expected if one report uses the route assignment at scan time and the other uses the latest assignment. The fix is to make time semantics explicit, not to force both reports onto one join.

Implement a route-assignment bridge with route_id, valid_from, valid_to, and a change reason. Join scans through the bridge on scan timestamp for the operational measure, and through the current-row flag for the management measure. Name the measures differently, for example 'on-time at scan route' and 'on-time at current route', and display the as-of date on each report. Reconcile by quantifying the 137 realignments: compare both measures before and after each realignment, and confirm that the difference reconciles to the bridge changes.

Add tests: every scan has exactly one route at scan time; route assignment periods do not overlap; and the sum of scans reconciles to source hub control totals.

For pallet coverage, do not impute pallet revenue into the 37% of scans without pallet data. Build a pallet-grain fact from the manifest and link it to scans where the relationship is known. Report revenue per pallet on the covered population only, and show the coverage rate alongside the metric. If leadership needs an all-in estimate, publish it as a separate modelled estimate with its assumptions, not as the base metric. This preserves trust in the reported number and prevents allocation from becoming a hidden source of bias.