Causal Drilldown Breakdown — Test Case & Verification Guide¶

SQL examples predate the v6 unique_id migration

The SQL snippets on this page use the legacy unique_id column, which was dropped from every table in the v6 migration. All series are now keyed by item_id + site_id — replace unique_id selection / filtering / joining / ORDER BY / GROUP BY with item_id, site_id (and the built-in item_name / site_name columns where applicable). This page is retained as a historical test artifact; do not copy its SQL verbatim.

This document provides a step-by-step method to verify that the causal drilldown breakdown (the modal that appears when you click a causal bar in the Time Series Viewer) produces values that are consistent with the pipeline forecast stored in ClickHouse.

1. The Three Values That Must Agree¶

When you click a causal bar for a given unique_id (e.g. 10_100) at a given forecast_week (e.g. 2026-06-22), three values are displayed:

Value	Source	Description
Pipeline forecast	`_baseline_value_at()` → `PIPE_causal_forecast`	The site-level weekly demand from the pipeline's causal component
Breakdown total	Breakdown API formula: `Σ(qty × fleet_qty × FR × total_usage) × coverage_pct`	The recomputed demand from the same inputs the demand generator uses
Chart bar value	`PIPE_causal_forecast` direct read	The value shown in the chart grid

All three must match (within rounding tolerance of ~0.01).

2. Source Tables¶

2.1 PostgreSQL (tenant DB)¶

Table	Schema	Purpose in Breakdown	Key Columns
`master.causal_bom`	master	BOM: which items are consumed by which asset type	`asset_type_id`, `item_id`, `qty_per_asset`, `usage_type_id`
`master.causal_asset_type`	master	Asset type names	`asset_type_id`, `name`, `code`
`master.causal_usage_type`	master	Usage type names (e.g. km_total, flight_hours)	`id`, `name`
`master.customer`	master	Customer names	`customer_id`, `name`
`scen_causal_scenarios`	tenant	Scenario settings, especially `usage_mode`	`scenario_id`, `usage_mode`, `granularity`
`scen_causal_fleet_plan`	tenant	Fleet: how many assets of each type per customer per period	`scenario_id`, `asset_type_id`, `customer_id`, `asset_qty`, `period_start`, `period_end`
`scen_causal_asset_usage`	tenant	Usage: total km/hours per customer per usage type per period	`scenario_id`, `customer_id`, `usage_type_id`, `factor_qty`, `period_start`, `period_end`
`master_causal_failure_rate`	tenant	Failure rates per item/customer/usage type	`scenario_id`, `item_id`, `customer_id`, `usage_type_id`, `failure_rate`, `period_start`, `period_end`
`scen_causal_asset_coverage`	tenant	Coverage: which site handles what % of each customer's demand	`scenario_id`, `asset_type_id`, `site_id`, `customer_id`, `coverage_pct`
`config_scenario_pipeline`	tenant	Pipeline definition linking scenarios to forecast components	`pipeline_id`, `causal_scenario_id`, `causal_pct`

2.2 ClickHouse¶

Table	Purpose	Key Columns
`PIPE_causal_forecast`	Stored causal demand per unique_id per week	`pipeline_id`, `scenario_id`, `unique_id`, `forecast_week`, `qty`, `archive_date`
`PIPE_forecast_point_values`	Stored forecast points (method='causal')	`pipeline_id`, `scenario_id`, `unique_id`, `method`, `forecast_week`, `point_forecast`, `archive_date`
`PIPE_series_characteristics`	Date range end for baseline alignment	`unique_id`, `date_range_end`
`PIPE_series_best_methods`	Best method for series component	`unique_id`, `best_method`, `locked_method`

3. Diagnostic SQL Queries¶

Use these queries to independently verify each input and the expected output.

3.1 Find the pipeline and scenario IDs¶

-- PostgreSQL
SELECT pipeline_id, name, causal_scenario_id, causal_pct
FROM scenario.config_scenario_pipeline
WHERE causal_scenario_id IS NOT NULL
ORDER BY pipeline_id;

3.2 Find which unique_ids exist in PIPE_causal_forecast¶

-- ClickHouse
SELECT distinct unique_id, count() AS weeks
FROM PIPE_causal_forecast
WHERE pipeline_id = {pipeline_id} AND scenario_id = {scenario_id}
GROUP BY unique_id
ORDER BY unique_id;

3.3 Get the stored pipeline forecast for a specific unique_id and week¶

-- ClickHouse — site-level (e.g. unique_id = '10_100')
SELECT unique_id, forecast_week, qty, archive_date
FROM PIPE_causal_forecast
WHERE pipeline_id = {pipeline_id}
  AND scenario_id = {scenario_id}
  AND unique_id = '{unique_id}'
  AND forecast_week = '{monday_date}'
ORDER by archive_date DESC;

-- ClickHouse — aggregate (e.g. unique_id = '10')
SELECT unique_id, forecast_week, qty, archive_date
FROM PIPE_causal_forecast
WHERE pipeline_id = {pipeline_id}
  AND scenario_id = {scenario_id}
  AND unique_id = '{item_only_uid}'
  AND forecast_week = '{monday_date}'
ORDER by archive_date DESC;

Key rule: when unique_id contains a site (e.g. 10_100), the pipeline forecast must use that exact UID, NOT the aggregate (10). The aggregate contains ALL sites; summing both would double-count.

3.4 Verify the BOM for the item¶

-- PostgreSQL
SELECT b.asset_type_id, at2.name AS asset_type_name,
       b.usage_type_id, ut.name AS usage_type_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::text = '{item_id}';

3.5 Verify the fleet plan for the period¶

-- PostgreSQL
SELECT fp.asset_type_id, fp.customer_id, c.name AS customer_name, fp.asset_qty
FROM scenario.scen_causal_fleet_plan fp
LEFT JOIN master.customer c ON c.customer_id = fp.customer_id
WHERE fp.scenario_id = {scenario_id}
  AND fp.asset_type_id IN ({asset_type_ids})
  AND fp.period_start <= '{period_date}'::date
  AND fp.period_end   >= '{period_date}'::date;

3.6 Verify the usage data (and weekly proration)¶

-- PostgreSQL — raw usage
SELECT au.customer_id, au.usage_type_id,
       SUM(au.factor_qty) AS total_usage,
       MIN(au.period_start) AS usage_period_start,
       MAX(au.period_end) AS usage_period_end
FROM scenario.scen_causal_asset_usage au
WHERE au.scenario_id = {scenario_id}
  AND au.usage_type_id IN ({usage_type_ids})
  AND au.period_start <= '{period_date}'::date
  AND au.period_end   >= '{period_date}'::date
GROUP BY au.customer_id, au.usage_type_id;

Weekly proration formula (applied in the breakdown API):

weekly_usage = total_usage × 7 / (period_end - period_start + 1).days

3.7 Verify the failure rates¶

-- PostgreSQL
SELECT fr.customer_id, fr.usage_type_id, fr.failure_rate
FROM scenario.master_causal_failure_rate fr
WHERE fr.item_id::text = '{item_id}'
  AND fr.scenario_id = {scenario_id}
  AND fr.period_start <= '{period_date}'::date
  AND fr.period_end   >= '{period_date}'::date;

3.8 Verify coverage percentages¶

-- PostgreSQL
SELECT asset_type_id, site_id, customer_id, coverage_pct
FROM scenario.scen_causal_asset_coverage
WHERE scenario_id = {scenario_id};

3.9 Check the usage_mode¶

-- PostgreSQL
SELECT usage_mode FROM scenario.scen_causal_scenarios WHERE scenario_id = {scenario_id};

4. Manual Computation¶

Given the inputs from section 3, compute the expected demand manually:

4.1 Fleet-total mode (default)¶

demand per (asset_type, customer) = qty_per_asset × failure_rate × weekly_total_usage

4.2 Per-asset mode¶

demand per (asset_type, customer) = qty_per_asset × fleet_qty × failure_rate × weekly_total_usage

4.3 Apply coverage (when unique_id has a site)¶

Build a per-customer coverage map: {customer_id → {site_id → coverage_pct}}
For each breakdown row, look up the coverage_pct for the target site
Normalise so that the sum of coverage_pct per customer = 1.0
Multiply: computed_demand × coverage_pct

4.4 Sum across all breakdown rows¶

breakdown_total = Σ computed_demand (after coverage)

This must equal the pipeline forecast from PIPE_causal_forecast.

5. Test Case: Bicycle Demo — Item 10, Site 100¶

This test case uses the standard Bicycle demo dataset.

5.1 Test parameters¶

Parameter	Value
`pipeline_id`	4
`scenario_id` (causal)	3
`unique_id`	`10_100`
`item_id`	10
`site_id`	100
`forecast_week`	`2026-06-22` (Monday)
`usage_mode`	`fleet_total`

5.2 Expected pipeline forecast¶

-- ClickHouse
SELECT qty FROM PIPE_causal_forecast
WHERE pipeline_id = 4 AND scenario_id = 3
  AND unique_id = '10_100' AND forecast_week = '2026-06-22'
ORDER BY archive_date DESC LIMIT 1;

Expected: ≈37.01

5.3 BOM (qty_per_asset)¶

asset_type_id	asset_type_name	usage_type_id	qty_per_asset
(varies)	Specialized Tarmac SL8 Pro	(varies)	1
(varies)	Trek Madone SLR 9	(varies)	1
(varies)	Canyon Ultimate CFR Disc	(varies)	1
(varies)	Canyon Aeroad CFR Di2	(varies)	1

5.4 Fleet plan (6 rows, qty=1 each)¶

asset_type	customer	asset_qty
SPZ-TAR	Ineos Grenadiers	1
TRK-MAD	Ineos Grenadiers	1
CAN-ULT	Visma-Lease a Bike	1
CAN-AER	Visma-Lease a Bike	1
CAN-AER	UAE Team Emirates	1
SPZ-TAR	UAE Team Emirates	1

5.5 Usage (weekly prorated)¶

customer	usage_type	total_usage (raw)	period_days	weekly_usage
Ineos Grenadiers	km_total	3,446,045.23	~58 weeks	≈59,077
Visma-Lease a Bike	km_total	3,382,540.31	~58 weeks	≈57,975
UAE Team Emirates	km_total	3,356,248	~58 weeks	≈57,522

5.6 Failure rate¶

customer	usage_type	failure_rate
(global)	km_total	0.0000029139

5.7 Coverage¶

asset_type_id	site_id	customer_id	coverage_pct
(chosen)	100	(varies)	(per-customer)
(chosen)	201	(varies)	(per-customer)

5.8 Computation¶

For each (asset_type, customer):
  demand = qty_per_asset(=1) × FR(=0.0000029139) × weekly_usage

  Ineos:     1 × 0.0000029139 × 59,077  ≈ 0.1721  × 2 assets = 0.3442
  Visma:     1 × 0.0000029139 × 57,975  ≈ 0.1689  × 2 assets = 0.3378
  UAE:       1 × 0.0000029139 × 57,522  ≈ 0.1676  × 2 assets = 0.3352

  Total (all sites) ≈ 1.0172  (per week, per asset_type group)

With coverage split to site 100:
  Total × coverage_pct(site=100) ≈ 37.01

5.9 Verification steps¶

Call the breakdown API:

GET /api/causal/results/breakdown?scenario_id=3&item_id=10_100&period=2026-06-22&pipeline_id=4

Check actual_demand in the response — must equal the PIPE_causal_forecast value for unique_id='10_100' at that week (≈37.01).
Sum computed_demand across all breakdown rows — must equal actual_demand within ±0.01.
Verify the chart bar value matches actual_demand.
Common failure modes:
actual_demand returns aggregate (52.87) instead of site-level (37.01) → the baseline query is using the item-only UID prefix instead of the full site-level UID
Breakdown total (41.55) > pipeline forecast (37.01) → coverage split not applied correctly, or weekly proration differs from the demand generator's overlap-proration
Breakdown total is 0 → no coverage rows defined for the chosen asset_type, and the code zeroes out demand

6. Known Bug Patterns & Fixes¶

6.1 `getattr(user, "role")` on dict (always returns None)¶

Symptom: All admin/NPI endpoints return 403 "Admin or planner required" even for superadmins.

Cause: get_current_user() returns a dict, but the endpoint checks getattr(user, "role", None) which looks for a Python attribute on the dict object (which doesn't exist), not a dict key.

Fix: Use user.get("role") instead of getattr(user, "role", None).

Files: main.py lines 31158, 31235, 31307, 31441.

6.2 Baseline query sums site + aggregate data (double-counting)¶

Symptom: actual_demand in breakdown modal is ~2× the expected value (e.g. 89.88 instead of 37.01).

Cause: The causal query in get_pipeline_baseline() used WHERE unique_id IN ({uid1}, {uid2}) where uid1='10_100' (site-level) and uid2='10' (aggregate across all sites). This sums both, double-counting the site's demand.

Fix: Query the exact UID first; only fall back to the item-only prefix if no data is found for the exact UID. Never sum both.

File: main.py — get_pipeline_baseline(), causal component section.

6.3 Coverage split not per-customer¶

Symptom: Breakdown total (41.55) differs from pipeline forecast (37.01) by a non-trivial margin when coverage percentages differ across customers.

Cause: The breakdown coverage logic built a single global {site_id: pct} map and applied the same percentage to all customers. The demand generator uses per-customer coverage via (asset_type_id, customer_id) merge.

Fix: Build _cov_by_customer: {customer_id → {site_id → pct}} and look up per-customer coverage for each breakdown row, falling back to customer_id=NULL (global) rows.

File: main.py — breakdown API coverage section.

6.4 Pipeline_id=0 guard violation¶

Symptom: planning_monday(0) returns the real UTC date instead of the pipeline's planning date, causing incorrect archive_date in CH writes.

Cause: int(pid_arg or 0) passes the orphan-row sentinel pipeline_id=0 to planning_monday().

Fix: Guard with if not pid_arg: return or use require_pipeline_id().

File: run_pipeline.py.

6.5 Frozen partition data loss¶

Symptom: Historical (frozen) PIPE_causal_forecast partitions are deleted when re-running a pipeline step.

Cause: drop_all_ch_partitions_for_pipeline() without frozen_dates drops ALL partitions for the pipeline+scenario, including frozen ones.

Fix: Pass frozen_dates=get_frozen_archive_dates(pipeline_id, scenario_id).

Files: scenario_runner.py, run_pipeline.py.

7. API Reference¶

GET /api/causal/results/breakdown¶

Parameter	Type	Required	Description
`scenario_id`	int	Yes	Causal scenario ID
`item_id`	str	Yes	Full `unique_id` (e.g. `"10_100"`) — site is parsed from the part after `_`
`period`	str	Yes	ISO date `"YYYY-MM-DD"` (will be normalised to Monday)
`pipeline_id`	int	No	When provided, `actual_demand` is read from `PIPE_causal_forecast` via baseline

Response:

href="#__codelineno-16-1">{ "period": "2026-06-22", "actual_demand": 37.01, "usage_mode": "fleet_total", "breakdown": [ { "asset_type_id": 5, "asset_type_name": "Specialized Tarmac SL8 Pro", "asset_type_code": "SPZ-TAR", "customer_id": 1, "customer_name": "Ineos Grenadiers", "asset_qty": 1, "usage_type_id": 1, "usage_type_name": "km_total", "qty_per_asset": 1, "fleet_qty": 1, "total_usage": 59077.0, "failure_rate": 0.0000029139, "computed_demand": 6.18, "usage_mode": "fleet_total", "coverage_pct": 0.89 } ] }

8. Architecture: Data Flow¶

┌─────────────────────────────────────────────────────────────┐
│                    Frontend (TSV)                            │
│  User clicks causal bar → openCausalDrill(period, value)    │
│  → GET /api/causal/results/breakdown                        │
│     ?scenario_id=3&item_id=10_100&period=2026-06-22         │
│     &pipeline_id=4                                          │
└──────────────────────────┬──────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────┐
│              Breakdown API (main.py:18520)                   │
│                                                              │
│  1. Parse item_id → item_id_int=10, site_id=100             │
│  2. Query usage_mode from scen_causal_scenarios              │
│  3. Query BOM → qty_per_asset per asset_type                 │
│  4. Query fleet_plan → asset_qty per (asset_type, customer)  │
│  5. Query asset_usage → total_usage (weekly prorated)        │
│  6. Query failure_rate → FR per (customer, usage_type)       │
│  7. Query coverage → coverage_pct per (asset_type, site,     │
│     customer)                                                │
│  8. Cross-join BOM × fleet → demand = qty × fleet_qty ×     │
│     FR × total_usage                                        │
│  9. Apply per-customer coverage split for target site        │
│ 10. actual_demand = _baseline_value_at(pipeline_id,          │
│     "10_100", "causal", period)                              │
│     → get_pipeline_baseline(4, "10_100")                     │
│     → CH: PIPE_causal_forecast WHERE unique_id='10_100'     │
│ 11. Return {actual_demand, breakdown[]}                      │
└──────────────────────────┬──────────────────────────────────┘
                           │
              ┌────────────┴────────────┐
              │                         │
              ▼                         ▼
┌──────────────────────┐  ┌──────────────────────────┐
│  PostgreSQL (tenant)  │  │  ClickHouse              │
│                       │  │                           │
│  scen_causal_*        │  │  PIPE_causal_forecast     │
│  master.causal_*      │  │  PIPE_forecast_point_vals │
│  config_scenario_*    │  │  PIPE_series_*            │
└──────────────────────┘  └──────────────────────────┘