Skip to content

Forecast Netting — Isolated Test Guide

Date: 2026-06-21 Tenant: thebicycle Scope: Forecast netting / consumption logic — three individually-debuggable scenarios on isolated SKUs Script: files/scripts/seed_netting_isolated.py

TOC

  1. Goal & design rationale
  2. Netting mechanics recap
  3. Environment facts
  4. The three test scenarios
  5. Seeded data
  6. Expected netting results
  7. Actual verified results
  8. How to drive the test
  9. Cleanup
  10. Design decisions (Q&A)
  11. Code review findings & fixes
  12. Risks & guards
  13. Out of scope

Goal & design rationale

The goal is to isolate three netting situations so each can be reviewed and debugged individually, without the noise of blending percentages, return forecasts, multi-source carry, or 500+ sibling series.

Three SKUs, one dedicated test pipeline, one situation per SKU:

# Situation SKU (uid) Item What consumes the statistical forecast
1 stat + causal forecast (netted, no maintenance) 13_333 Crankset Shimano DA FC-R9200 causal forecast
2 stat + a few firm orders 18_205 Wheelset Zipp 303 Firecrest TL Disc firm demand orders
3 stat + maintenance forecast (netted) 19_302 Carbon Road Fork maintenance demand

Why these SKUs? Items 13, 18, 19 have no RETURN route in master.route (verified), so return_forecast_qty stays 0 — no return-forecast noise. Site 205 for item 18 was chosen specifically because no other active pipeline (1/4/85) forecasts 18_205, so the global firm orders in master.demand_actuals cannot contaminate another pipeline's netting run.

The statistical forecast is overridden to a clean constant 10 units/week on aligned Monday weeks so the netting consumption arithmetic is trivial to verify by hand.

Netting mechanics recap

Netting lives in files/netting/forecast_netting.py. The core function is compute_and_persist(), which for each series calls _compute_series_netting_rows().

Configuration sources

  • config_scenario_pipeline per pipeline: causal_netting_mode, maintenance_netting_mode, netting_sequence, blend %s (series_pct, causal_pct, maintenance_pct).
  • config_forecast_parameter_set.params['netting'] (pipeline default): consumption_enabled, direction, forward_fence, backward_fence, forward_carry_periods, backward_carry_periods.

Test pipeline configuration

  • causal_netting_mode = 'net' — causal demand acts as firm-like demand that consumes the statistical forecast.
  • maintenance_netting_mode = 'net' — maintenance demand consumes the statistical forecast.
  • netting_sequence = 'single' — all firm-like demands (causal / maintenance / firm orders) consume the stat forecast in one pass.
  • Blend %s = 0 / 0 / 0 → weights s_w = c_w = m_w = 1.0 → no scaling.
  • Netting params: consumption_enabled=true, direction=forward, forward_fence=4, backward_fence=0, carry=0.

How consumption works (net mode, single sequence)

  • soft_f = stat_by_week (causal/maintenance in net mode are NOT added to the soft forecast).
  • firm_like = firm_orders + maintenance(net) + causal(net).
  • apply_consumption(soft_f, firm_like, direction=forward, forward_fence=4) → stat_consumed_by_week.
  • Apportionment: stat_share = stat_f / soft_f = 1.0 (only stat in the soft forecast), so stat_consumed_val = total_consumed. In net mode, causal_consumed = causal_f and maintenance_consumed = maintenance_f (fully consumed); causal_netted = maintenance_netted = 0.
  • supply_plan = stat_netted + firm_d + causal_f(net) + maintenance_f(net) − firm_r.

So for tests 1 & 3: supply_plan = stat_netted + (causal_f | maintenance_f). For test 2: supply_plan = stat_netted + firm_d.

The forward fence

With forward_fence=4, an order/firm-demand at week T can consume statistical forecast from weeks T, T+1, T+2, T+3, T+4 (5 slots total).

Environment facts

Item Value
Tenant DB thebicycle, schema scenario
ClickHouse DB thebicycle
planning_today (global, pipeline_id=-1) 2026-06-18 (Thu)
planning_monday (= archive_date) 2026-06-15
Stat cutoff 2026-04-06 (Monday)
Future (is_past=0) weeks start Mon 2026-06-22 (week > 2026-06-18)
Netting defaults forward, fence=4, backward=0, carry=0
Horizon 52 weeks (from _load_default_supply_params)

Week alignment — the critical detail: netting derives stat weeks as cutoff + (w_idx+1) weeks, while maintenance weeks come from DATE_TRUNC('week', event_date) (always Monday). Setting the cutoff to a Monday (2026-04-06) makes stat weeks land on Mondays, aligning with maintenance weeks. All seeded data lands on Mondays. The script asserts every seeded date's weekday == Monday.

The three test scenarios

All three SKUs share a constant statistical forecast of 10 units/week from 2026-04-13 through ~2027-04-12 (52 weeks), in the test pipeline.

SKU-A 13_333 — stat + causal forecast

Causal forecast of 20 units/week for three consecutive weeks: 2026-07-06, 2026-07-13, 2026-07-20.

Written to ClickHouse PIPE_causal_forecast (scenario = "Netting Test Causal").

SKU-B 18_205 — stat + firm orders

Two firm demand orders: - 20 units @ 2026-07-06 - 15 units @ 2026-07-20

Written to PostgreSQL master.demand_actuals (is_forecast=false, source='netting_test_lab').

SKU-C 19_302 — stat + maintenance forecast

A maintenance event ("Netting Test Event") with a BOM line for item 19 (qty=1, likelihood_of_removal=1.0), scheduled at site 302: - 35 units @ 2026-07-06 - 25 units @ 2026-07-27

Maintenance demand = event_qty × bom_qty × likelihood_of_removal = qty × 1 × 1. Written to PostgreSQL master.maintenance_event + master.maintenance_bom + scenario.scen_maintenance_scenario_event + scenario.scen_maintenance_scenario_event_date.

Seeded data

The script files/scripts/seed_netting_isolated.py creates, per run:

PostgreSQL: - config_scenario_pipeline — 1 row ("Netting Test Lab", modes net/net/single, blend %s 0/0/0). - scen_causal_scenarios — 1 row ("Netting Test Causal", weekly). - scen_forecast_scenarios — 1 row ("Netting Test Maint") used as the maintenance scenario. - config_forecast_parameter_set — 1 row ("Netting Test Default", explicit netting block). - master.maintenance_event — 1 row ("Netting Test Event"). - master.maintenance_bom — 1 row (event → item 19). - scenario.scen_maintenance_scenario_event — 1 row. - scenario.scen_maintenance_scenario_event_date — 2 rows. - master.demand_actuals — 2 firm-order rows.

ClickHouse (all with archive_date = planning_monday = 2026-06-15): - PIPE_series_characteristics — 3 rows (cutoff = 2026-04-06). - PIPE_series_best_methods — 3 rows (best_method = 'TestConst'). - PIPE_forecast_point_values — 156 rows (52 weeks × 3 SKUs, point_forecast = 10.0). - PIPE_causal_forecast — 3 rows (SKU-A). - PIPE_forecast_netting — produced by the netting run (156 rows).

All artifacts are tagged for cleanup: names 'Netting Test Lab', 'Netting Test Causal', 'Netting Test Maint', 'Netting Test Event', 'Netting Test Default', source='netting_test_lab', method='TestConst'.

Expected netting results

Stat = 10/wk, fence = 4 → 5-slot forward window. Consumption is sequential by order/firm-demand week.

SKU-A 13_333 (causal 20 @ 07-06, 07-13, 07-20)

  • 07-06 (20) ← stat 07-06 (10) + 07-13 (10)
  • 07-13 (20) ← stat 07-20 (10) + 07-27 (10)
  • 07-20 (20) ← stat 08-03 (10) + 08-10 (10)
week stat_f causal_f stat_consumed stat_netted causal_consumed supply_plan
06-22 10 0 0 10 0 10
06-29 10 0 0 10 0 10
07-06 10 20 10 0 20 20
07-13 10 20 10 0 20 20
07-20 10 20 10 0 20 20
07-27 10 0 10 0 0 0
08-03 10 0 10 0 0 0
08-10 10 0 10 0 0 0
08-17 10 0 0 10 0 10

SKU-B 18_205 (firm 20 @ 07-06, 15 @ 07-20)

  • 07-06 (20) ← stat 07-06 (10) + 07-13 (10)
  • 07-20 (15) ← stat 07-20 (10) + 07-27 (5)
week stat_f firm_d stat_consumed stat_netted supply_plan
06-22 10 0 0 10 10
06-29 10 0 0 10 10
07-06 10 20 10 0 20
07-13 10 0 10 0 0
07-20 10 15 10 0 15
07-27 10 0 5 5 5
08-03 10 0 0 10 10

SKU-C 19_302 (maintenance 35 @ 07-06, 25 @ 07-27)

  • 07-06 (35) ← stat 07-06 (10) + 07-13 (10) + 07-20 (10) + 07-27 (5)
  • 07-27 (25) ← stat 07-27 (5) + 08-03 (10) + 08-10 (10)
week stat_f maint_f stat_consumed stat_netted maint_consumed supply_plan
06-22 10 0 0 10 0 10
06-29 10 0 0 10 0 10
07-06 10 35 10 0 35 35
07-13 10 0 10 0 0 0
07-20 10 0 10 0 0 0
07-27 10 25 10 0 25 25
08-03 10 0 10 0 0 0
08-10 10 0 10 0 0 0
08-17 10 0 0 10 0 10

Actual verified results

Running python scripts/seed_netting_isolated.py (test pipeline_id=90) produced netting rows in PIPE_forecast_netting matching the expected tables exactly. Key-week verification output:

SKU-A 13_333 (stat + causal)

            week |           stat | causal/firm/maint |      stat_cons |       stat_net |          extra |         supply
      2026-06-22 |           10.0 |            0.0 |            0.0 |           10.0 |            0.0 |           10.0
      2026-06-29 |           10.0 |            0.0 |            0.0 |           10.0 |            0.0 |           10.0
      2026-07-06 |           10.0 |           20.0 |           10.0 |            0.0 |           20.0 |           20.0
      2026-07-13 |           10.0 |           20.0 |           10.0 |            0.0 |           20.0 |           20.0
      2026-07-20 |           10.0 |           20.0 |           10.0 |            0.0 |           20.0 |           20.0
      2026-07-27 |           10.0 |            0.0 |           10.0 |            0.0 |            0.0 |            0.0
      2026-08-03 |           10.0 |            0.0 |           10.0 |            0.0 |            0.0 |            0.0
      2026-08-10 |           10.0 |            0.0 |           10.0 |            0.0 |            0.0 |            0.0
      2026-08-17 |           10.0 |            0.0 |            0.0 |           10.0 |            0.0 |           10.0

SKU-B 18_205 (stat + firm orders)

            week |           stat | causal/firm/maint |      stat_cons |       stat_net |         supply
      2026-06-22 |           10.0 |            0.0 |            0.0 |           10.0 |           10.0
      2026-06-29 |           10.0 |            0.0 |            0.0 |           10.0 |           10.0
      2026-07-06 |           10.0 |           20.0 |           10.0 |            0.0 |           20.0
      2026-07-13 |           10.0 |            0.0 |           10.0 |            0.0 |            0.0
      2026-07-20 |           10.0 |           15.0 |           10.0 |            0.0 |           15.0
      2026-07-27 |           10.0 |            0.0 |            5.0 |            5.0 |            5.0
      2026-08-03 |           10.0 |            0.0 |            0.0 |           10.0 |           10.0

SKU-C 19_302 (stat + maintenance)

            week |           stat | causal/firm/maint |      stat_cons |       stat_net |          extra |         supply
      2026-06-22 |           10.0 |            0.0 |            0.0 |           10.0 |            0.0 |           10.0
      2026-06-29 |           10.0 |            0.0 |            0.0 |           10.0 |            0.0 |           10.0
      2026-07-06 |           10.0 |           35.0 |           10.0 |            0.0 |           35.0 |           35.0
      2026-07-13 |           10.0 |            0.0 |           10.0 |            0.0 |            0.0 |            0.0
      2026-07-20 |           10.0 |            0.0 |           10.0 |            0.0 |            0.0 |            0.0
      2026-07-27 |           10.0 |           25.0 |           10.0 |            0.0 |           25.0 |           25.0
      2026-08-03 |           10.0 |            0.0 |           10.0 |            0.0 |            0.0 |            0.0
      2026-08-10 |           10.0 |            0.0 |           10.0 |            0.0 |            0.0 |            0.0
      2026-08-17 |           10.0 |            0.0 |            0.0 |           10.0 |            0.0 |           10.0

(extra = causal_consumed for SKU-A, maint_consumed for SKU-C.)

Manual verification query

-- ClickHouse (thebicycle)
SELECT forecast_week, stat_forecast_qty, causal_forecast_qty, maint_forecast_qty,
       firm_demand_qty, stat_consumed_qty, stat_netted_qty,
       causal_consumed_qty, maint_consumed_qty, supply_plan_qty
FROM PIPE_forecast_netting
WHERE pipeline_id = 90            -- replace with your test pipeline_id
  AND unique_id = '13_333'        -- or 18_205 / 19_302
  AND forecast_week BETWEEN '2026-06-22' AND '2026-08-24'
ORDER BY forecast_week;

How to drive the test

All commands run from the files/ directory.

Full seed + run + verify

$env:PYTHONIOENCODING="utf-8"
python scripts/seed_netting_isolated.py
Creates a fresh test pipeline, seeds all data, runs netting, and prints a verification table comparing actual vs expected results.

Seed only (no netting run)

python scripts/seed_netting_isolated.py --no-run
Useful when you want to inspect the raw seeded data before netting consumes it.

Clean up all test artifacts

python scripts/seed_netting_isolated.py --clean
Removes every tagged artifact from PostgreSQL and ClickHouse.

Idempotency

The script is fully idempotent — re-running cleans the previous test pipeline before seeding a new one. Verified across three consecutive runs (pipeline_ids 88 → 89 → 90) with no errors.

Review in the UI

After a successful run, open the netting tab for each SKU: - SKU-A (stat + causal): http://localhost:5173/series/13_333?tab=netting - SKU-B (stat + firm orders): http://localhost:5173/series/18_205?tab=netting - SKU-C (stat + maintenance): http://localhost:5173/series/19_302?tab=netting

Cleanup

The --clean flag (and the start of every full run) removes all artifacts in foreign-key-safe order:

  1. master.demand_actuals WHERE source = 'netting_test_lab'
  2. master.maintenance_bom for the test event (no cascade on this FK)
  3. master.maintenance_event WHERE name = 'Netting Test Event' (cascades to scen_maintenance_*)
  4. config_forecast_parameter_set WHERE name = 'Netting Test Default'
  5. config_scenario_pipeline WHERE name = 'Netting Test Lab' (must precede scenario deletes — its FKs are NO ACTION)
  6. scen_causal_scenarios WHERE name = 'Netting Test Causal'
  7. scen_forecast_scenarios WHERE name = 'Netting Test Maint'
  8. ClickHouse ALTER TABLE ... DELETE WHERE pipeline_id = <old> on all five PIPE_ tables.

Design decisions (Q&A)

Question Decision Rationale
How to isolate the 3 situations? Dedicated test pipeline Non-destructive (pipeline 4 untouched); trivially debuggable; easy to delete.
Maintenance netting mode for test 3? net Maintenance acts as known future demand that CONSUMES the stat forecast — actually exercises netting consumption.
Netting sequence? single All firm-like demands consume stat in one pass. Easiest to trace and debug for first-pass isolation.
How to create causal/maintenance/firm data? Direct INSERT Deterministic, tiny, easy to verify by hand. No engine dependencies.

Code review findings & fixes

The script was reviewed (local uncommitted review) and two findings were fixed:

1. CRITICAL — FK ordering in clean() broke idempotency

Problem: The original clean() deleted scen_causal_scenarios and scen_forecast_scenarios before config_scenario_pipeline. The pipeline row's FKs (causal_scenario_id, maintenance_scenario_id) are NO ACTION, so on the second run (when a pipeline row still referenced the scenario rows) the delete raised a FK violation and the script aborted.

Fix: Reordered clean() to delete config_scenario_pipeline before the scenario tables. Also discovered master.maintenance_bom.event_id has no ON DELETE CASCADE (unlike scen_maintenance_scenario_event), so the BOM rows are now deleted explicitly before the event.

Verification: Idempotency confirmed across three consecutive runs (pipeline_ids 88 → 89 → 90) with no FK violations.

2. WARNING — Firm-order cross-pipeline contamination

Problem: SKU-B's firm orders are written to master.demand_actuals, which is pipeline-agnostic. _load_firm_demand_orders() loads all is_forecast=false rows with date > min_cutoff for every pipeline forecasting that SKU. The original SKU-B (18_332) was forecast by pipelines 1, 4, and 85, so the test firm orders would contaminate those pipelines' netting runs.

Fix: Moved SKU-B from 18_332 to 18_205 (Netherlands Country Warehouse). Site 205 is a valid master.location but no other active pipeline forecasts 18_205, so the global firm orders cannot pollute other pipelines. Item 18 has no return route, preserving the no-return-noise property.

Risks & guards

  • Week alignment — MUST keep cutoff = Monday 2026-04-06 and seed every date as a Monday. The script asserts each seeded date's weekday == Monday. Otherwise maintenance (DATE_TRUNC('week') → Monday) and stat (cutoff + N) land on different slots and consumption attributes to phantom weeks.
  • No return routes for items 13/18/19 verified → return_forecast_qty stays 0 (no return noise).
  • No scaling — blend %s must be 0/0/0; the script sets this explicitly via INSERT.
  • master.demand_actuals is global — the seeded firm orders (item 18 @ site 205) could theoretically be picked up by another pipeline forecasting 18_205, but no such pipeline exists. The source='netting_test_lab' tag ensures clean removal.
  • Horizon — _load_default_supply_params returns 52 weeks → stat covers 52 weeks from 2026-04-13, well past the seed window.
  • compute_and_persist resolves scenario_id from series_scenario_id (=4) and writes PIPE_forecast_netting rows with scenario_id=4; drop_ch_partition uses that scenario_id. Consistent.
  • Planning date — the script uses planning_today() / planning_monday() for archive_date, never date.today(), per the project rule.

Out of scope

  • Returns / firm returns (interact_sales_return) — not seeded; return_* columns stay 0.
  • two_step sequence — can be flipped later on the test pipeline to test pre-netting; default is single for first-pass clarity.
  • Running the real causal / maintenance engines — data is hand-seeded directly.
  • Touching pipeline 4 or any existing pipeline — the test is fully isolated in its own pipeline.