event_detail_investigation.md

Investigation: How event_detail changed in ft_wms_events

1. How event_detail is built in ft_wms_events

Source → DWH data flow:

1. WMS service emits operational log events (e.g. replenish_stock, start_replenishment_round) with raw JSON fields like fromLocation, toLocation, object, load_carrier_barcode, etc.

2. picnic_wms__operational_log_parsed (dbt evt model) normalizes these raw events into a unified JSON structure via a large CASE WHEN event_name = ... block. For each event type, it maps raw fields into standardized keys:

   • replenish_stock: source_hu_id ← event_raw:"fromLocation", target_hu_id ← coalesce(event_raw:"toLocation", event_raw:"object")
   • start_replenishment_round: target_hu_id ← event_raw:"load_carrier_barcode"

3. wms_events (dbt intgr model) reads from the parsed model and:

   • Computes h_source_hu_dv_hashkey = md5(site_id || '-' || source_hu_id) and similarly for target
   • Left joins to dv.hs_handling_unit on those hashkeys (filtered to latest version: r_timestamp_end = '9999-12-31')
   • Builds event_detail as:

     initcap(
       replace(
         event_type
         || iff(source_hu_id is not null, ' > ' || coalesce(source_hshu.subtype, source_hshu.type_desc, 'Unknown'), '')
         || iff(target_hu_id is not null, ' > ' || coalesce(target_hshu.subtype, target_hshu.type_desc, 'Unknown'), ''),
         '_', ' '
       )
     )

Formula: event_type [> source HU type] [> target HU type]

Each segment is only appended when the corresponding source_hu_id / target_hu_id is non-null. The type label comes from coalesce(subtype, type_desc, 'Unknown') in hs_handling_unit.

---

2. How dv.hs_handling_unit is built

Not from the CSV export (/admin/handlingunits, which only exports PL and AREA types).

Instead, the Data Vault domain wms_handling_units uses the OpsController REST API:

1. GET /api/1/ops/handling_units/types — returns all HandlingUnitType enum values mapped to their display names (e.g. LP → "License Plate", PL → "Pick Location", AREA → "Area")
2. For each type, GET /api/1/ops/handling_units?type={type} — returns all handling units of that type

The ETL code (wms_handling_units/__init__.py) iterates over all types and yields a Datum per handling unit, setting type_desc from the display name. This flows through:

API response → raw table → flow.sql (staging) → Data Vault (h_handling_unit hub + hs_handling_unit satellite)

For LP (License Plate) handling units:

• type = LP
• type_desc = License Plate (from HandlingUnitType.LP.getDisplayName())
• subtype = NULL (LicensePlate class has no subtype override)

So coalesce(subtype, type_desc, 'Unknown') → License Plate for LP handling units.

The DV load runs on a cron: Sundays and Wednesdays at 22:20.

---

3. What changed to produce the new event_detail values

Before (~2025-11-27)	After
Replenish Stock > Shelf Pick Location	Replenish Stock > License Plate > Shelf Pick Location
Start Replenishment Round	Start Replenishment Round > License Plate

The change could mean:

• For replenish_stock: event_raw:"fromLocation" went from NULL to populated (with an LP barcode), causing source_hu_id to become non-null
• For start_replenishment_round: event_raw:"load_carrier_barcode" went from NULL to populated, causing target_hu_id to become non-null

The code responsible is in picnic-ws-replenishment:

• StockServiceImpl.replenishStockToPickLocation() — .setFromLocation(stockIdentifier.locationPicnicId()) (commit 36914aea6, June 2024)
• RoundReplenishmentServiceImpl.startReplenishmentRound() — .setLoadCarrierBarcode(round.assignedMobileLocationPicnicId()) (commit d162948a8, April 2024)

---

4. How to verify by querying raw events

To confirm the root cause, query the raw operational log events around the change date:

select
    event_raw:"fromLocation"::varchar as from_location,
    min(event_ts) as first_seen,
    max(event_ts) as last_seen,
    count(*) as event_count
from picnic_wms__operational_log
where lower(event_raw:"action"::varchar) = 'replenish_stock'
  and site_id = '<WAREHOUSE_ID>'
  and event_ts between '2025-11-01' and '2025-12-15'
group by 1
order by 1;

If the hypothesis is correct, you should see:

• from_location = NULL rows before the change date
• from_location = <LP barcode> rows after the change date

Similarly for start_replenishment_round:

select
    event_raw:"load_carrier_barcode"::varchar as load_carrier_barcode,
    min(event_ts) as first_seen,
    max(event_ts) as last_seen,
    count(*) as event_count
from picnic_wms__operational_log
where lower(event_raw:"action"::varchar) = 'start_replenishment_round'
  and site_id = '<WAREHOUSE_ID>'
  and event_ts between '2025-11-01' and '2025-12-15'
group by 1
order by 1;

https://gist.github.com/irbis22/e5109f35ac370ab177b62d8c60409c44