Causal Forecast Computation Trace: Item 10, Site 100, Pipeline 4

This document traces how the batch process computes the weekly causal demand of 37.0117 for item 10 (Stem Ritchey WCS Carbon 100 mm) at site 100 (Central Warehouse Brussels) under pipeline 4.

The result is stored in ClickHouse PIPE_causal_forecast as a flat weekly value that is constant across all weeks in the forecast horizon.

---

1. Pipeline & Scenario Configuration

Parameter	Value	Source
Pipeline ID	4	config_scenario_pipeline
Pipeline name	Default	config_scenario_pipeline
Causal scenario ID	3	config_scenario_pipeline.causal_scenario_id
Maintenance scenario ID	2	config_scenario_pipeline.maintenance_scenario_id
Usage mode	fleet_total	scen_causal_scenarios.usage_mode
Granularity	weekly	scen_causal_scenarios.granularity

SELECT pipeline_id, name, causal_scenario_id, maintenance_scenario_id
FROM config_scenario_pipeline WHERE pipeline_id = 4;

SELECT scenario_id, name, usage_mode, granularity
FROM scen_causal_scenarios WHERE scenario_id IN (1, 3);

Key: Why scenario 3 uses the base fleet (sid=1)

The engine always starts from the base fleet (scenario_id = 1) and applies fleet_overrides from the target scenario. Scenario 3 ("Fleet Expansion 2026") has empty fleet_overrides (NULL), so the patched fleet is identical to the base fleet. This matches the production sql_engine and demand_generator behavior.

SELECT fleet_overrides, usage_overrides
FROM scen_causal_scenarios WHERE scenario_id = 3;
-- Both are NULL

---

2. Bill of Materials (BOM)

Item 10 appears in 5 asset types, all with qty_per_asset = 1.0 and usage_type_id = 1 (km_total):

Asset Type ID	Asset Type Name	Code	Usage Type	qty_per_asset
1	Canyon Ultimate CFR Disc	CAN-ULT	km_total	1.0
2	Canyon Aeroad CFR Di2	CAN-AER	km_total	1.0
3	Specialized Tarmac SL8 Pro	SPZ-TAR	km_total	1.0
4	Trek Madone SLR 9	TRK-MAD	km_total	1.0
5	Cervelo P5 Disc	CER-P5	km_total	1.0

SELECT b.asset_type_id, at2.name, at2.code, b.usage_type_id, ut.name, b.qty_per_asset
FROM master.causal_bom b
LEFT JOIN master.causal_asset_type at2 ON at2.asset_type_id = b.asset_type_id
LEFT JOIN master.causal_usage_type ut ON ut.id = b.usage_type_id
WHERE b.item_id = 10
ORDER BY b.asset_type_id;

Effective BOM (after fleet join)

The effective BOM is the inner join of BOM × fleet_plan. Since AT5 (Cervelo P5 Disc) has no fleet plan entries at all, it is dropped. The effective BOM for item 10 has 4 rows:

Asset Type ID	effective_qty
1	1.0
2	1.0
3	1.0
4	1.0

---

3. Base Fleet Plan (scenario_id = 1)

Only the fleet rows active on 2026-06-15 matter (the fleet range must overlap the FR period). The base fleet has 10 active rows across 4 asset types and 6 customers:

AT ID	Asset Type	Customer ID	Customer	asset_qty	Fleet Period
1	Canyon Ultimate CFR Disc	2	Visma-Lease a Bike	50	2025-11-28 → 2026-11-28
1	Canyon Ultimate CFR Disc	4	Equipe Amateurs Tour	8	2025-11-28 → 2026-11-28
2	Canyon Aeroad CFR Di2	1	UAE Team Emirates	45	2025-11-28 → 2026-11-28
2	Canyon Aeroad CFR Di2	2	Visma-Lease a Bike	40	2025-11-28 → 2026-11-28
2	Canyon Aeroad CFR Di2	5	Berlin Cycling Club	18	2025-11-28 → 2026-11-28
3	Specialized Tarmac SL8 Pro	1	UAE Team Emirates	45	2025-11-28 → 2026-11-28
3	Specialized Tarmac SL8 Pro	3	Ineos Grenadiers	45	2025-11-28 → 2026-11-28
3	Specialized Tarmac SL8 Pro	4	Equipe Amateurs Tour	7	2025-11-28 → 2026-11-28
3	Specialized Tarmac SL8 Pro	6	Barcelona Riders Club	12	2025-11-28 → 2026-11-28
4	Trek Madone SLR 9	3	Ineos Grenadiers	45	2025-11-28 → 2026-11-28

SELECT fp.asset_type_id, at2.name, fp.customer_id, c.name, fp.asset_qty,
       fp.period_start::date, fp.period_end::date
FROM scen_causal_fleet_plan fp
LEFT JOIN master.causal_asset_type at2 ON at2.asset_type_id = fp.asset_type_id
LEFT JOIN master.customer c ON c.customer_id = fp.customer_id
WHERE fp.scenario_id = 1
  AND fp.period_start <= '2026-06-15'::date
  AND fp.period_end   >= '2026-06-15'::date
ORDER BY fp.asset_type_id, fp.customer_id;

Customer → Asset Type mapping (for item 10)

Since usage_mode = 'fleet_total', fleet_qty = 1 (the fleet quantity is NOT a multiplier). The number of asset types per customer determines the row count in the bom_fleet cross product:

Customer ID	Customer	Asset Types in Fleet	# ATs
1	UAE Team Emirates	AT2, AT3	2
2	Visma-Lease a Bike	AT1, AT2	2
3	Ineos Grenadiers	AT3, AT4	2
4	Equipe Amateurs Tour	AT1, AT3	2
5	Berlin Cycling Club	AT2	1
6	Barcelona Riders Club	AT3	1

This produces 10 bom_fleet rows for item 10, which the demand generator sums per customer.

---

4. Usage (Merged: Scenario 3 replaces Base)

Anti-join logic

merge_scenario_usage() drops all base usage rows for (customer_id, usage_type_id) combos that exist in the scenario's own usage, and replaces them with the scenario rows. Since scenario 3 has usage for all 6 customers with usage_type_id = 1, all base usage is replaced.

Scenario 3 usage (km_total, yearly granularity)

Customer ID	Customer	factor_qty (2026)	Period	factor_qty (2027)	Period
1	UAE Team Emirates	175,004,360	2026-01-01 → 2026-12-31	171,557,760	2027-01-01 → 2027-12-31
2	Visma-Lease a Bike	176,375,316	2026-01-01 → 2026-12-31	162,932,728	2027-01-01 → 2027-12-31
3	Ineos Grenadiers	179,686,644	2026-01-01 → 2026-12-31	166,504,588	2027-01-01 → 2027-12-31
4	Equipe Amateurs Tour	9,873,124	2026-01-01 → 2026-12-31	9,229,448	2027-01-01 → 2027-12-31
5	Berlin Cycling Club	11,309,180	2026-01-01 → 2026-12-31	10,886,240	2027-01-01 → 2027-12-31
6	Barcelona Riders Club	7,644,096	2026-01-01 → 2026-12-31	7,484,164	2027-01-01 → 2027-12-31

SELECT au.customer_id, c.name, au.factor_qty,
       au.period_start::date, au.period_end::date
FROM scen_causal_asset_usage au
LEFT JOIN master.customer c ON c.customer_id = au.customer_id
WHERE au.scenario_id = 3 AND au.usage_type_id = 1
ORDER BY au.customer_id, au.period_start;

---

5. Failure Rate (Waterfall Resolved)

The failure rate table for item 10, scenario 3 has 208 weekly rows (2023-12-01 → 2027-11-25), all with the same value:

Parameter	Value
failure_rate	2.91386997619275 × 10⁻⁶
customer_id	NULL (global — no customer specificity)
site_id	NULL
asset_id	NULL
position	NULL
Example period	2026-06-12 → 2026-06-18

The waterfall resolver (vectorized_resolve) picks this global rate because there are no more-specific FR rows (specificity scores: asset=8, site=4, position=2, customer=1; this row has all NULLs → score=0, but it's the only candidate).

SELECT failure_rate, period_start, period_end, customer_id, site_id
FROM master_causal_failure_rate
WHERE scenario_id = 3 AND item_id = 10 AND usage_type_id = 1
  AND period_start = '2026-06-12'::date;

---

6. Overlap-Prorated Range-Join

The usage periods are yearly while FR periods are weekly. The range-join prorates usage into each FR week by computing the day overlap.

For the week of 2026-06-12 → 2026-06-18:

	FR period	2026 usage period	2027 usage period
Period	2026-06-12 → 2026-06-18	2026-01-01 → 2026-12-31	2027-01-01 → 2027-12-31
Overlap start	—	max(2026-01-01, 2026-06-12) = 2026-06-12	max(2027-01-01, 2026-06-12) = 2027-01-01
Overlap end	—	min(2026-12-31, 2026-06-18) = 2026-06-18	min(2027-12-31, 2026-06-18) = 2026-06-18
Overlap days	—	6	0 (start > end)

Proration formula: prorated_usage = factor_qty × (overlap_days / usage_period_days)

Where usage_period_days = (period_end - period_start).days = (2026-12-31 - 2026-01-01).days = 364 (pandas datetime subtraction, exclusive of end date).

Customer ID	factor_qty (2026)	Overlap days	Usage period days	Fraction	Prorated usage
1	175,004,360	6	364	6/364	2,884,687.253
2	176,375,316	6	364	6/364	2,907,285.429
3	179,686,644	6	364	6/364	2,961,867.758
4	9,873,124	6	364	6/364	162,743.802
5	11,309,180	6	364	6/364	186,415.055
6	7,644,096	6	364	6/364	126,001.582

-- Verify: 6/364 = 0.016483516...
SELECT 6.0 / 364;

-- Verify: prorated usage for C1
SELECT 175004360.0 * 6.0 / 364;

---

7. Demand Computation

Formula (fleet_total mode)

asset_unscheduled = effective_qty × fleet_qty × failure_rate × prorated_usage

Since effective_qty = 1.0 and fleet_qty = 1 (fleet_total mode), this simplifies to:

asset_unscheduled = failure_rate × prorated_usage

Per-asset-type demand (each bom_fleet row)

For each (item, asset_type, customer) row in the bom_fleet cross product, the unscheduled demand is:

1.0 × 1 × 2.91386997619275e-06 × prorated_usage[customer]

Per-customer aggregation (sum over asset types)

The demand generator sums asset_unscheduled across all asset types for each (item_id, customer_id, period_start). A customer with N asset types in the fleet gets its prorated usage counted N times:

Customer ID	# ATs	Prorated usage	Per-AT demand	Customer demand (= #ATs × per-AT)
1	2	2,884,687.253	0.008406	16.8112
2	2	2,907,285.429	0.008471	16.9429
3	2	2,961,867.758	0.008630	17.2610
4	2	162,743.802	0.000474	0.9484
5	1	186,415.055	0.000543	0.5432
6	1	126,001.582	0.000367	0.3672

Total unscheduled demand (all sites): 52.8739

-- Verify per-AT demand for C1
SELECT 2.91386997619275e-06 * (175004360.0 * 6.0 / 364);
-- = 0.008406

-- Verify customer demand for C1 (2 ATs)
SELECT 2 * 2.91386997619275e-06 * (175004360.0 * 6.0 / 364);
-- = 0.016811

---

8. Coverage Spreading (Site Split)

Coverage table (scenario 3)

Asset Type ID	Site ID	Site Name	coverage_pct
1	100	Central Warehouse Brussels	0.7
1	201	France Country Warehouse	0.3
2	100	Central Warehouse Brussels	0.5
2	202	Germany Country Warehouse	0.5
3	100	Central Warehouse Brussels	0.6
3	203	Spain Country Warehouse	0.4

SELECT asset_type_id, site_id, coverage_pct
FROM scen_causal_asset_coverage
WHERE scenario_id = 3
ORDER BY asset_type_id, site_id;

One-to-one mapping rule

spread_by_coverage() picks one asset type per item to avoid fan-out. It prefers the smallest asset_type_id that has coverage defined. For item 10, the candidates with coverage are AT1, AT2, AT3. The smallest is AT1.

Coverage merge (global, customer_id = NULL)

Since all coverage rows have customer_id = NULL (global coverage), the merge is on asset_type_id only. AT1 coverage maps to:

Site	coverage_pct	Normalized
100	0.7	0.7 / (0.7 + 0.3) = 0.7
201	0.3	0.3 / (0.7 + 0.3) = 0.3

Final split

site_100 = total_demand × 0.7 = 52.8739 × 0.7 = 37.0117
site_201 = total_demand × 0.3 = 52.8739 × 0.3 = 15.8622

Site ID	Site Name	Coverage	Demand
100	Central Warehouse Brussels	0.7	37.0117129789257
201	France Country Warehouse	0.3	15.8621627052539

---

9. Verification Against ClickHouse

SELECT unique_id, scenario_id, forecast_week, qty
FROM thebicycle.PIPE_causal_forecast
WHERE pipeline_id = 4
  AND unique_id = '10_100'
  AND forecast_week = toMonday(toDate('2026-06-15'))

Returns: qty = 37.0117129789257 — exact match.

All 99 weekly values for 10_100 are identical (the same FR, usage, fleet, and coverage apply every week in the horizon).

---

10. Complete Formula Summary

demand(item=10, site=100, week) =

    Σ_customers [  #asset_types(customer)
                   × effective_qty(1.0)
                   × fleet_qty(1)
                   × failure_rate(2.91387e-06)
                   × Σ_usage_periods [ factor_qty × overlap_days / usage_period_days ]
                 ]

    × coverage_pct(site=100, asset_type=1) / Σ_sites coverage_pct

= 52.8739 × 0.7

= 37.0117

Key assumptions

1. Base fleet (sid=1) is used, not scenario 3's own fleet plan, because fleet_overrides is NULL
2. Scenario 3's usage replaces all base usage (anti-join: scenario 3 covers all 6 customers for usage_type_id=1)
3. fleet_qty = 1 in fleet_total mode — asset_qty from the fleet plan is NOT a multiplier
4. The number of ATs per customer acts as an implicit multiplier (each AT in the bom_fleet cross generates a demand row that is summed)
5. Coverage uses AT1 (smallest AT with coverage entries) for the one-to-one item→asset_type mapping
6. Proration denominator is (period_end - period_start).days = 364 (pandas exclusive-end convention), not 365 calendar days
7. The failure rate is global (all specificity columns NULL) — no waterfall override needed

---

11. All Input Numbers at a Glance

Number	Value	Table.Column
Item 10 qty_per_asset (all ATs)	1.0	master.causal_bom.qty_per_asset
Failure rate	2.91386997619275 × 10⁻⁶	master_causal_failure_rate.failure_rate
C1 usage (2026)	175,004,360	scen_causal_asset_usage.factor_qty
C2 usage (2026)	176,375,316	scen_causal_asset_usage.factor_qty
C3 usage (2026)	179,686,644	scen_causal_asset_usage.factor_qty
C4 usage (2026)	9,873,124	scen_causal_asset_usage.factor_qty
C5 usage (2026)	11,309,180	scen_causal_asset_usage.factor_qty
C6 usage (2026)	7,644,096	scen_causal_asset_usage.factor_qty
FR overlap days	6	computed: min(fr_end,usage_end) - max(fr_start,usage_start)
Usage period days	364	computed: (2026-12-31 - 2026-01-01).days
#ATs for C1	2	AT2(CAN-AER) + AT3(SPZ-TAR) in fleet
#ATs for C2	2	AT1(CAN-ULT) + AT2(CAN-AER) in fleet
#ATs for C3	2	AT3(SPZ-TAR) + AT4(TRK-MAD) in fleet
#ATs for C4	2	AT1(CAN-ULT) + AT3(SPZ-TAR) in fleet
#ATs for C5	1	AT2(CAN-AER) in fleet
#ATs for C6	1	AT3(SPZ-TAR) in fleet
Coverage site 100 (AT1)	0.7	scen_causal_asset_coverage.coverage_pct
Coverage site 201 (AT1)	0.3	scen_causal_asset_coverage.coverage_pct
Selected coverage AT	1 (CAN-ULT)	smallest AT with coverage entries

---

12. Step-by-Step Arithmetic

Proration fraction = 6 / 364 = 0.016483516483516...

C1 prorated = 175,004,360 × 0.016483516... = 2,884,687.2527
C2 prorated = 176,375,316 × 0.016483516... = 2,907,285.4286
C3 prorated = 179,686,644 × 0.016483516... = 2,961,867.7582
C4 prorated =   9,873,124 × 0.016483516... =   162,743.8022
C5 prorated =  11,309,180 × 0.016483516... =   186,415.0549
C6 prorated =   7,644,096 × 0.016483516... =   126,001.5824

FR = 2.91386997619275e-06

Per-AT demand:
  C1: 2.91387e-06 × 2,884,687.2527 = 8.4056
  C2: 2.91387e-06 × 2,907,285.4286 = 8.4715
  C3: 2.91387e-06 × 2,961,867.7582 = 8.6310
  C4: 2.91387e-06 ×   162,743.8022 = 0.4742
  C5: 2.91387e-06 ×   186,415.0549 = 0.5432
  C6: 2.91387e-06 ×   126,001.5824 = 0.3672

Per-customer demand (× #ATs):
  C1: 2 × 8.4056 = 16.8112
  C2: 2 × 8.4715 = 16.9429
  C3: 2 × 8.6310 = 17.2610
  C4: 2 × 0.4742 =  0.9484
  C5: 1 × 0.5432 =  0.5432
  C6: 1 × 0.3672 =  0.3672

Total (all sites) = 52.8739

Coverage split (AT1: site100=0.7, site201=0.3):
  site 100 = 52.8739 × 0.7 = 37.0117129789257  ✓
  site 201 = 52.8739 × 0.3 = 15.8621627052539