DMRL Full Calculation Diagram

Both Unit (Reefer Machinery) and Box (Container Structure) paths · with data sources at each step

Phase 1 — Container Data & Classification

Kafka / COMET

topic: cometdmrldata

container_id, equipment_type, equipment_group, equipment_dimension, equipment_manufacturer, equipment_reefer_type, location_id, move_timestamp, production_year

1 Container Base Data Ingestion

Raw container snapshot consumed from Kafka COMET topic
age = current_year − production_year
move_timestamp tracks latest movement event
equipment_type ∈ {DRY, REEF, TANK, OPEN…} drives all downstream branching

→ Produces: base container DataFrame used by every subsequent step

↓

Delta / Pipedream

watercooled-carriers

equipment_number → matches first 7 chars of container_id

2 Container Type Classification

HIGH cube mapping: 40DRY + 9'6" → 40HIGH, etc.
REEF type includes OEM manufacturer: 40REEF_CARRIER, 40REEF_CA, 40REEF_SUPER
Watercooled / Magnum detection → appends MAGNUM suffix

container_type = equipment_group + manufacturer (REEF) | equipment_group (DRY)

container_type_BOX = container_type + "_BOX" ← used only in location override step

→ Produces: container_type, container_type_BOX

↓

PostgreSQL

container_dmrl.v_cluster_loc_for_mrl

shop_location_code, loc (regional cluster)

Delta / Pipedream

v-cluster-loc

Materialised view of above PostgreSQL table

3 Location Cluster (loc_id)

JOIN ON: SUBSTRING(location_id, 1, 5) = SUBSTRING(shop_location_code, 1, 5)

loc_id = loc → e.g. "NAM", "EUR", "ASC", "AFS"

→ loc_id is used to look up region-specific positioning, evacuation costs and location overrides

↓

PostgreSQL (AEMS)

aems_extended_master_data.mrl_sales_locations

sales_location_code, equipment_group_sales

Delta / Pipedream

sales-loc-exploded

is_surplus, is_sales flags (rank=1 latest entry)

4 Surplus & Sales Status

JOIN ON: location_id = sales_location_code AND equipment_group = equipment_group_sales

is_surplus = true → container in surplus location → lower MRL path
is_sales = true → container is for sale → evacuation cost deducted

→ Produces: is_surplus, is_sales

Phase 2 — Cost Lookups & Repair Limit Curves

PostgreSQL (AEMS)

aems_extended_master_data.positioning_costs

container_type, loc_id → positioning_cost

PostgreSQL (AEMS)

aems_extended_master_data.evacuation_costs

container_type, loc_id → evacuation_cost

5 Positioning & Evacuation Costs

JOIN ON: container_type + loc_id (LEFT join; defaults to $0 if no match)

positioning_cost: cost to move a deficit container to demand location → added to DEFICIT MRL
evacuation_cost: cost to move a surplus+sales container → deducted from surplus MRL

→ Produces: positioning_cost, evacuation_cost

↓

PostgreSQL (AEMS)

aems_extended_master_data.costs_and_life_time

container_type, age → repair_limit_age, minimum_repair_limit, minimum_repair_limit_no_sale, effective_lifetime

PostgreSQL (AEMS)

aems_extended_master_data.assumptions_original

Original/baseline repair limit curves (historic reference)

6 Repair Limit Curves by Age

Unit (Reefer Machinery)

repair_limit_age

minimum_repair_limit

minimum_repair_limit_no_sale

effective_lifetime_reef

Lookup: container_type + age

Box (Container Structure)

repair_limit_age_box

minimum_repair_limit_box

minimum_repair_limit_no_sale_box

effective_lifetime_box

Lookup: container_type + age (separate curve)

Phase 3 — Base MRL Calculation

Uses outputs from
Steps 4, 5, 6

7 Base Maersk Repair Limit — add_repair_limits()

Unit → maersk_repair_limit

SURPLUS + SALES:
MAX(repair_limit_age − evacuation_cost, minimum_repair_limit)

SURPLUS + NO SALES:
MAX(repair_limit_age, minimum_repair_limit_no_sale)

DEFICIT:
repair_limit_age + positioning_cost

→ maersk_repair_limit = ROUND(value/25) × 25

Box → maersk_repair_limit_box

SURPLUS + SALES:
MAX(repair_limit_age_box − evacuation_cost, minimum_repair_limit_box)

SURPLUS + NO SALES:
MAX(repair_limit_age_box, minimum_repair_limit_no_sale_box)

DEFICIT:
repair_limit_age_box + positioning_cost

→ maersk_repair_limit_box = ROUND(value/25) × 25

Both values rounded to nearest $25. For non-REEF containers, maersk_repair_limit_box_original falls back to maersk_repair_limit_original.

Phase 4 — Adjustments (adjust_mrl_container_final)

PostgreSQL (AEMS)

aems_extended_master_data.features_properties_final

shared_data_join_key (DRY/REEF/OTHER) → all multipliers

Delta / Pipedream

features-properties-final

food_grade_multiplier, major_reefer_components_multiplier, power_consumption_multiplier, fleet_optimization_multiplier, safety_mrl_multiplier

8 Load Adjustment Multipliers

shared_data_join_key = equipment_type [DRY / REEF → as-is; anything else → "OTHER"]

JOIN ON: shared_data_join_key (LEFT join)

food_grade_multiplier — upward adjustment for food-grade repairs
major_reefer_components_multiplier — upward adjustment for major reefer repairs REEF only
power_consumption_multiplier — downward adjustment for excessive power draw REEF only
fleet_optimization_multiplier — downward adjustment for outfleet candidates
safety_mrl_multiplier — OVERRIDE adjustment for safety flags

↓

Delta / Pipedream

container-food-grade-repairs

container_id → food_grade flag (containers with food-grade repairs in past year)

9a Adjustment 1 — Food Grade Both

Unit

food_grade_adjustment =
food_grade_multiplier × maersk_repair_limit

0 if container_id not in food-grade list

Box

food_grade_adjustment_box =
food_grade_multiplier × maersk_repair_limit_box

0 if container_id not in food-grade list

↓

Delta / Pipedream

containers-with-expensive-repairs

container_id → major_reefer_component flag (expensive reefer component jobs)

9b Adjustment 2 — Major Reefer Component REEF only

Unit

major_reefer_components_adjustment =
IF(equipment_type = "REEF" AND has_major_reefer_component)
THEN major_reefer_components_multiplier × maersk_repair_limit
ELSE 0

Box

major_reefer_components_adjustment_box =
IF(equipment_type = "REEF" AND has_major_reefer_component)
THEN major_reefer_components_multiplier × maersk_repair_limit_box
ELSE 0

↓

Delta / Pipedream

reefer-machinery-power-consumption

equipment_reefer_type → reason_power, power_consumption_age_reef

9c Adjustment 3 — Power Consumption REEF only

Unit

power_consumption_adjustment =
IF(reason_power IS NOT NULL AND age < power_consumption_age_reef)
THEN power_consumption_multiplier × maersk_repair_limit
ELSE 0

Triggered by excessive power draw flag on reefer type

Box

power_consumption_adjustment_box =
IF(reason_power IS NOT NULL AND age < power_consumption_age_reef)
THEN power_consumption_multiplier × maersk_repair_limit_box
ELSE 0

↓

Delta / Pipedream

profiles-outfleet

equipment_profile → outfleet_reason (containers planned for outfleet)

9d Adjustment 4 — Outfleet Profile Both

Unit

outfleet_profiles_adjustment =
IF(outfleet_reason IS NOT NULL)
THEN fleet_optimization_multiplier × maersk_repair_limit
ELSE 0

Box

outfleet_profiles_adjustment_box =
IF(outfleet_reason IS NOT NULL)
THEN fleet_optimization_multiplier × maersk_repair_limit_box
ELSE 0

↓

Delta / Pipedream

profiles-safety-without-duplicates

equipment_profile → safety_reason (safety inspection flags)

9e Adjustment 5 — Safety Flag Both ⚠️ OVERRIDE

Unit

safety_adjustment =
IF(safety_reason IS NOT NULL)
THEN safety_mrl_multiplier × maersk_repair_limit
ELSE 0

Box

safety_adjustment_box =
IF(safety_reason IS NOT NULL)
THEN safety_mrl_multiplier × maersk_repair_limit_box
ELSE 0

⚠️ Safety is an OVERRIDE:

IF safety_adjustment ≠ 0 THEN adjusted_MRL = base − safety (ALL other adjustments ignored)

Phase 5 — Final MRL Assembly & Rules

Pure computation — no new data sources

10 Combine Adjustments → adjusted_MRL & adjusted_MRL_box

Unit

IF safety_adjustment ≠ 0:
adjusted_MRL = maersk_repair_limit − safety_adjustment

ELSE:
adjusted_MRL = maersk_repair_limit
+ food_grade_adjustment
+ major_reefer_components_adjustment
− power_consumption_adjustment
− outfleet_profiles_adjustment
− safety_adjustment

→ adjusted_MRL = MAX(adjusted_MRL, 0)

Box

IF safety_adjustment_box ≠ 0:
adjusted_MRL_box = maersk_repair_limit_box − safety_adjustment_box

ELSE:
adjusted_MRL_box = maersk_repair_limit_box
+ food_grade_adjustment_box
+ major_reefer_components_adjustment_box
− power_consumption_adjustment_box
− outfleet_profiles_adjustment_box
− safety_adjustment_box

→ adjusted_MRL_box = MAX(adjusted_MRL_box, 0)

↓

Uses effective_lifetime from Step 6 cost curves

11 Age Limit Rule — forces both to $0 when container is too old

Unit

IF equipment_type = "DRY" AND age ≥ effective_lifetime_box
→ adjusted_MRL = 0

IF equipment_type = "REEF" AND age ≥ effective_lifetime_reef
→ adjusted_MRL = 0

Thresholds: DRY ≈ 18 yrs, REEF ≈ 16 yrs (from cost curves)

Box

IF equipment_type = "DRY" AND age ≥ effective_lifetime_box
→ adjusted_MRL_box = 0

IF equipment_type = "REEF" AND age ≥ effective_lifetime_reef
→ adjusted_MRL_box = 0

Same thresholds apply to both Unit and Box

↓

Business rule — no new data source

12 REEF-Only Rule — Unit path only

IF equipment_type ≠ "REEF" → adjusted_MRL = NULL

Unit (Machinery) DMRL is only meaningful for reefer containers. DRY/TANK/OPEN containers get NULL for adjusted_MRL. Their Box DMRL (adjusted_MRL_box) is still set.

Phase 6 — Location Override (override_mrl_per_location)

PostgreSQL (AEMS)

aems_extended_master_data.sitelevel_overrides

container_type, location_id, mrl_bonus, min_age, max_age, valid_to

Delta / Pipedream

mrl-sitelevel-overrides

Materialised; separate entries for container_type and container_type_BOX

13 Site-Level Location Override

Unit → mrl_bonus

JOIN ON: container_type + location_id + valid_to check

CONDITION: min_age ≤ age ≤ max_age AND safety_reason IS NULL

adjusted_MRL = MAX(adjusted_MRL + mrl_bonus, 0)

IF equipment_type ≠ "REEF" → adjusted_MRL = NULL (re-applied)

Box → mrl_bonus_box

JOIN ON: container_type_BOX + location_id + valid_to check

CONDITION: min_age ≤ age ≤ max_age AND safety_reason IS NULL

adjusted_MRL_box = MAX(adjusted_MRL_box + mrl_bonus_box, 0)

No NULL rule on Box — applies to all container types

The _BOX suffix in container_type_BOX links to separate override entries in sitelevel_overrides, allowing independent bonuses for the box structure vs. the reefer machinery.

Step	Dataset / Source	Type	DB Table	Key Fields
1	cometdmrldata	Kafka	—	container_id, equipment_type, location_id, production_year
2	watercooled-carriers	Delta	carrier_registrations	equipment_number, description (Magnum detection)
3	v-cluster-loc	PostgreSQL	container_dmrl.v_cluster_loc_for_mrl	shop_location_code, loc → loc_id
4	sales-loc-exploded	PostgreSQL	aems_extended_master_data.mrl_sales_locations	is_surplus, is_sales
5	positioning-costs-exploded	PostgreSQL	aems_extended_master_data.positioning_costs	positioning_cost
5	evacuation-costs-exploded	PostgreSQL	aems_extended_master_data.evacuation_costs	evacuation_cost
6	repair-limits-yearwise	PostgreSQL	aems_extended_master_data.costs_and_life_time	repair_limit_age, repair_limit_age_box, minimum_*, effective_lifetime
8	features-properties-final	PostgreSQL	aems_extended_master_data.features_properties_final	food_grade_multiplier, major_reefer_components_multiplier, power_consumption_multiplier, fleet_optimization_multiplier, safety_mrl_multiplier
9a	container-food-grade-repairs	Delta	gate_repair_jobs (food-grade filter)	container_id → food_grade repair flag
9b	containers-with-expensive-repairs	Delta	gate_repair_jobs (major reefer component filter)	container_id → major_reefer_component flag
9c	reefer-machinery-power-consumption	Delta	aems.power_consumption_profiles	equipment_reefer_type → reason_power, power_consumption_age_reef
9d	profiles-outfleet	Delta	fleet management outfleet profiles	equipment_profile → outfleet_reason
9e	profiles-safety-without-duplicates	Delta	safety inspection profiles	equipment_profile → safety_reason
13	mrl-sitelevel-overrides	PostgreSQL	aems_extended_master_data.sitelevel_overrides	container_type / container_type_BOX, location_id, mrl_bonus, mrl_bonus_box, min_age, max_age

DMRL Calculation — Full Flow