Segment and Parameter SQL Tests

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.

SQL-level verification guide for segmentation membership, parameter resolution, and MEIO multi-parameter assignment. All queries target the scenario PostgreSQL schema (theBicycle tenant). Replace 21 with your pipeline id throughout.

---

Table of Contents

1. Overview
2. Discovery queries
3. Segmentation membership verification
4. 3.1 Operator mapping: Python to SQL
5. 3.2 Base series CTE
6. 3.3 Summary comparison (all segments)
7. 3.4 Diff detail (per segment)
8. 3.5 ClickHouse cross-check
9. Parameter resolution verification
10. 4.1 How resolution works
11. 4.2 Parameter linkage overview
12. 4.3 SQL resolution vs stored (forecast + outlier)
13. MEIO multi-parameter test
14. 5.1 How MEIO differs from other parameters
15. 5.2 Verify array assignment in series_parameter_map
16. 5.3 Expand and inspect each parameter set per series
17. 5.4 Seed a test scenario
18. 5.5 Verify the test scenario end-to-end
19. Common issues

---

1. Overview

The segmentation pipeline works in two stages:

Stage 1 — Segment membership (segmentation step) Each series (unique_id) is evaluated against every segment's JSONB criteria. Matching series are written to scenario.pipe_segment_membership.

Stage 2 — Parameter resolution (preprocess step) For each series, the parameter with the lowest sort_order whose linked segment the series belongs to is picked — except for MEIO, which collects all matching parameter set IDs into an array (no precedence).

The tables involved:

Table	Purpose
scenario.config_segment	Segment definitions (JSONB criteria)
scenario.pipe_segment_membership	Per-pipeline series → segment mapping
scenario.config_parameters	Outlier / characterisation / forecast parameter sets
scenario.config_parameter_segment	Links config_parameters to segments
scenario.config_forecast_parameter_set	Named forecast parameter sets
scenario.config_forecast_parameter_set_segment	Links forecast sets to segments
scenario.config_meio_parameter_set	Named MEIO parameter sets per MEIO scenario
scenario.config_meio_parameter_set_segment	Links MEIO sets to segments
scenario.series_parameter_map	Resolved parameter IDs per series per pipeline

---

2. Discovery queries

Run these first to orient yourself before substituting a pipeline id.

-- Which pipelines have segment membership stored?
SELECT
    p.pipeline_id,
    p.name                       AS pipeline_name,
    COUNT(DISTINCT m.unique_id)  AS total_members
FROM scenario.config_scenario_pipeline p
LEFT JOIN scenario.pipe_segment_membership m USING (pipeline_id)
GROUP BY p.pipeline_id, p.name
ORDER BY p.pipeline_id;

-- What segments exist and how many stored members per pipeline?
SELECT
    s.id, s.name, s.is_default,
    m.pipeline_id,
    COUNT(m.unique_id) AS stored_members
FROM scenario.config_segment s
LEFT JOIN scenario.pipe_segment_membership m ON m.segment_id = s.id
GROUP BY s.id, s.name, s.is_default, m.pipeline_id
ORDER BY s.id, m.pipeline_id;

-- What ABC configs are active?
SELECT id, name, metric, thresholds
FROM scenario.config_abc_configuration
WHERE is_active = TRUE
ORDER BY id;

-- What pipelines have series_parameter_map rows?
SELECT pipeline_id, COUNT(*) AS rows
FROM scenario.series_parameter_map
WHERE segmentation_scenario_id = 1
GROUP BY pipeline_id
ORDER BY pipeline_id;

---

3. Segmentation membership verification

3.1 Operator mapping: Python to SQL

The SegmentationEngine._apply_condition() maps criteria operators to pandas operations. The SQL equivalents are:

Criteria op	Python behaviour	SQL equivalent
=	col == value	col = 'value'
!=	col != value	col != 'value'
>, >=, <, <=	numeric compare	col > value::numeric
contains	str.contains(na=False)	col ILIKE '%value%'
starts_with	str.startswith(na=False)	col ILIKE 'value%'
is_null	col.isna()	col IS NULL
is_not_null	col.notna()	col IS NOT NULL
is_true	fillna(False).astype(bool)	col IS TRUE — NULLs excluded
is_false	~fillna(False).astype(bool)	col IS NOT TRUE — NULLs included
in	col.isin(list)	col = ANY(ARRAY['a','b'])
JSONB attribute	df['item_attr_key']	attributes->>'key'

Critical gotcha — is_false includes NULLs. Python does fillna(False) then negates: a series with is_intermittent = NULL becomes False then ~False = True, so it matches is_false. Use IS NOT TRUE (not = FALSE) in SQL to replicate this. Concretely: "Fast movers" (is_intermittent is_false) includes every series for which characterisation has not yet run.

3.2 Base series CTE

Paste this CTE at the top of any verification query. It replicates SegmentationEngine._get_series_df().

-- Set pipeline_id here and reuse across all queries in this section.
-- In psql: \set pipeline_id 21

WITH series AS (
    SELECT DISTINCT unique_id, item_id, site_id
    FROM scenario.master_demand_actuals
),
base AS (
    SELECT
        s.unique_id,
        s.item_id,
        s.site_id,
        mi.price                 AS item_price,
        mi.cost                  AS item_cost,
        mi.attributes            AS item_attr,     -- JSONB
        ml.attributes            AS site_attr,     -- JSONB
        mit.name                 AS item_type_name,
        mst.name                 AS site_type_name,
        sc.is_intermittent,
        sc.has_seasonality,
        sc.has_trend,
        sc.adi,
        sc.zero_ratio,
        sc.n_observations,
        sc.mean                  AS demand_mean,
        sc.std                   AS demand_std
    FROM series s
    JOIN scenario.master_item mi          ON mi.id = s.item_id
    LEFT JOIN scenario.master_item_type mit ON mit.id = mi.type_id
    JOIN scenario.master_location ml      ON ml.id = s.site_id
    LEFT JOIN scenario.master_site_type mst ON mst.id = ml.type_id
    LEFT JOIN scenario.pipe_series_characteristics sc
        ON  sc.unique_id   = s.unique_id
        AND sc.pipeline_id = 21        -- << pipeline_id
),
abc AS (
    -- Adjust config name if yours differs from "Default (demand)"
    SELECT ar.unique_id, ar.class_label
    FROM scenario.pipe_abc_results ar
    JOIN scenario.config_abc_configuration ac ON ac.id = ar.config_id
    WHERE ac.name        = 'Default (demand)'
      AND ar.pipeline_id = 21          -- << pipeline_id
)

3.3 Summary comparison (all segments)

Appends a sql_membership CTE to the base above and produces a per-segment count comparison. Any row with diff <> 0 needs investigation.

-- Prepend the base + abc CTEs from 3.2, then:
, sql_membership AS (
    -- ── ABC classes ───────────────────────────────────────────────────────────
    SELECT 'A items'                  AS seg_name, unique_id FROM base JOIN abc USING (unique_id) WHERE class_label = 'A'
    UNION ALL
    SELECT 'B items',                              unique_id FROM base JOIN abc USING (unique_id) WHERE class_label = 'B'
    UNION ALL
    SELECT 'C items',                              unique_id FROM base JOIN abc USING (unique_id) WHERE class_label = 'C'
    UNION ALL
    -- ── Demand pattern ────────────────────────────────────────────────────────
    -- IS NOT TRUE matches NULLs — mirrors Python is_false fillna(False) logic
    SELECT 'Fast movers',                          unique_id FROM base WHERE is_intermittent IS NOT TRUE
    UNION ALL
    SELECT 'Slow / intermittent',                  unique_id FROM base WHERE is_intermittent IS TRUE
    UNION ALL
    -- ── Price bands ───────────────────────────────────────────────────────────
    SELECT 'High-value items (>500)',               unique_id FROM base WHERE item_price > 500
    UNION ALL
    SELECT 'Mid-value items (100-500)',             unique_id FROM base WHERE item_price >= 100 AND item_price <= 500
    UNION ALL
    SELECT 'Low-value items (<100)',                unique_id FROM base WHERE item_price < 100
    UNION ALL
    -- ── Site type (JSONB) ─────────────────────────────────────────────────────
    SELECT 'Central warehouses',                   unique_id FROM base WHERE site_attr->>'site_type' = 'central_wh'
    UNION ALL
    SELECT 'Country warehouses',                   unique_id FROM base WHERE site_attr->>'site_type' = 'country_wh'
    UNION ALL
    SELECT 'Dealer network',                       unique_id FROM base WHERE site_attr->>'site_type' = 'dealer'
    UNION ALL
    -- ── Country (JSONB) ───────────────────────────────────────────────────────
    SELECT 'Belgium',                              unique_id FROM base WHERE site_attr->>'country' = 'BE'
    UNION ALL
    SELECT 'Netherlands',                          unique_id FROM base WHERE site_attr->>'country' = 'NL'
    UNION ALL
    SELECT 'France',                               unique_id FROM base WHERE site_attr->>'country' = 'FR'
    UNION ALL
    SELECT 'Germany',                              unique_id FROM base WHERE site_attr->>'country' = 'DE'
    UNION ALL
    -- ── Product category (JSONB) ──────────────────────────────────────────────
    SELECT 'Frames',                               unique_id FROM base WHERE item_attr->>'category' = 'Frame'
    UNION ALL
    SELECT 'Drivetrain',                           unique_id FROM base WHERE item_attr->>'category' = 'Drivetrain'
    UNION ALL
    SELECT 'Consumables',                          unique_id FROM base WHERE item_attr->>'category' = 'Consumable'
    UNION ALL
    SELECT 'Wheels',                               unique_id FROM base WHERE item_attr->>'category' = 'Wheels'
    UNION ALL
    SELECT 'Electronics',                          unique_id FROM base WHERE item_attr->>'category' = 'Electronics'
    UNION ALL
    -- ── Combined segments ─────────────────────────────────────────────────────
    SELECT 'Critical A — Dealers',                 unique_id FROM base JOIN abc USING (unique_id) WHERE class_label = 'A' AND site_attr->>'site_type' = 'dealer'
    UNION ALL
    SELECT 'High-value A items',                   unique_id FROM base JOIN abc USING (unique_id) WHERE class_label = 'A' AND item_price > 500
    UNION ALL
    SELECT 'C items — intermittent',               unique_id FROM base JOIN abc USING (unique_id) WHERE class_label = 'C' AND is_intermittent IS TRUE
)
-- ── Summary: SQL count vs stored ─────────────────────────────────────────────
SELECT
    cs.id                                          AS segment_id,
    cs.name,
    COUNT(DISTINCT sm.unique_id)                   AS sql_count,
    COUNT(DISTINCT m.unique_id)                    AS stored_count,
    COUNT(DISTINCT sm.unique_id)
        - COUNT(DISTINCT m.unique_id)              AS diff
FROM scenario.config_segment cs
LEFT JOIN sql_membership sm   ON sm.seg_name   = cs.name
LEFT JOIN scenario.pipe_segment_membership m
    ON m.segment_id = cs.id AND m.pipeline_id = 21   -- << pipeline_id
WHERE cs.is_default = FALSE
GROUP BY cs.id, cs.name
ORDER BY cs.id;

Expected output: every diff column is 0. A negative diff means the stored table has series that SQL no longer selects (stale data or criteria changed). A positive diff means SQL selects series that are not yet stored (segmentation step not re-run after a criteria edit).

3.4 Diff detail (per segment)

Run this for any segment that showed diff <> 0 in section 3.3. Swap in the criteria predicate and segment name for the segment under investigation.

-- Example: Central warehouses
WITH sql_for_seg AS (
    SELECT DISTINCT da.unique_id
    FROM scenario.master_demand_actuals da
    JOIN scenario.master_location ml ON ml.id = da.site_id
    WHERE ml.attributes->>'site_type' = 'central_wh'
),
stored_for_seg AS (
    SELECT m.unique_id
    FROM scenario.pipe_segment_membership m
    JOIN scenario.config_segment cs ON cs.id = m.segment_id
    WHERE cs.name        = 'Central warehouses'   -- << segment name
      AND m.pipeline_id  = 21                     -- << pipeline_id
)
SELECT 'sql_only'    AS diff_type, unique_id FROM sql_for_seg    EXCEPT SELECT 'sql_only',    unique_id FROM stored_for_seg
UNION ALL
SELECT 'stored_only' AS diff_type, unique_id FROM stored_for_seg EXCEPT SELECT 'stored_only', unique_id FROM sql_for_seg;

3.5 ClickHouse cross-check

The application reads segment membership primarily from ClickHouse (PIPE_segment_membership). Verify the PG and CH tables agree for your pipeline:

-- ClickHouse: member count per segment for the pipeline
SELECT
    segment_id,
    segment_name,
    COUNT(DISTINCT unique_id) AS ch_count
FROM PIPE_segment_membership
WHERE pipeline_id = 21          -- << pipeline_id
GROUP BY segment_id, segment_name
ORDER BY segment_id;

Compare the ch_count values against stored_count from section 3.3. They must match. A discrepancy means the PG → CH sync failed during the segmentation step (look in scenario.pipe_process_log for errors on the segmentation step).

---

4. Parameter resolution verification

4.1 How resolution works

build_series_parameter_map() runs a single INSERT...SELECT that, for each series in pipe_segment_membership, fires a correlated subquery per parameter type. Each subquery:

1. Joins the parameter's linked segments against the series' segments in membership.
2. Picks ORDER BY sort_order LIMIT 1 — the parameter set with the lowest sort_order number wins.
3. Returns NULL if no parameter set is linked to any of the series' segments.

MEIO is the exception — see section 5.

Resolution priority within a parameter type (lowest sort_order first):

param set A  sort_order=0  linked to segment "A items"
param set B  sort_order=1  linked to segment "All" (default)

A series in "A items" gets param set A. A series NOT in "A items" but in "All" gets param set B. A series in neither gets NULL (falls back to the application default at runtime via ParameterResolver).

4.2 Parameter linkage overview

-- config_parameters (outlier, characterisation, etc.) linked to segments
SELECT
    p.id, p.parameter_type, p.name, p.sort_order,
    string_agg(cs.name, ', ' ORDER BY cs.name) AS linked_segments
FROM scenario.config_parameters p
JOIN scenario.config_parameter_segment ps ON ps.parameter_id = p.id
JOIN scenario.config_segment cs           ON cs.id = ps.segment_id
GROUP BY p.id, p.parameter_type, p.name, p.sort_order
ORDER BY p.parameter_type, p.sort_order;

-- Forecast parameter sets linked to segments
SELECT
    fps.id, fps.name, fps.sort_order,
    string_agg(cs.name, ', ' ORDER BY cs.name) AS linked_segments
FROM scenario.config_forecast_parameter_set fps
JOIN scenario.config_forecast_parameter_set_segment fpss ON fpss.parameter_set_id = fps.id
JOIN scenario.config_segment cs                          ON cs.id = fpss.segment_id
GROUP BY fps.id, fps.name, fps.sort_order
ORDER BY fps.sort_order, fps.id;

4.3 SQL resolution vs stored (forecast + outlier)

This query replicates the correlated subqueries from build_series_parameter_map and compares the result against what is actually stored in series_parameter_map.

WITH resolved AS (
    SELECT
        spm.item_id,
        spm.site_id,
        SPLIT_PART(spm.item_id::text || '_' || spm.site_id::text, '_', 1)
            || '_' || SPLIT_PART(spm.item_id::text || '_' || spm.site_id::text, '_', 2)
                                                   AS unique_id,
        spm.forecast_parameter_set_id              AS stored_fps_id,
        spm.outlier_parameter_id                   AS stored_out_id,

        -- SQL-derived forecast parameter set (lowest sort_order wins)
        (SELECT fps.id
         FROM scenario.config_forecast_parameter_set fps
         JOIN scenario.config_forecast_parameter_set_segment fpss
              ON fpss.parameter_set_id = fps.id
         JOIN scenario.pipe_segment_membership sm2
              ON sm2.segment_id = fpss.segment_id
         JOIN scenario.config_scenario_pipeline sp
              ON sp.series_scenario_id = fps.scenario_id
         WHERE sm2.unique_id  = (spm.item_id::text || '_' || spm.site_id::text)
           AND sp.pipeline_id = 21                 -- << pipeline_id
         ORDER BY fps.sort_order LIMIT 1)          AS sql_fps_id,

        -- SQL-derived outlier detection parameter (lowest sort_order wins)
        (SELECT p.id
         FROM scenario.config_parameters p
         JOIN scenario.config_parameter_segment ps ON ps.parameter_id = p.id
         JOIN scenario.pipe_segment_membership sm2 ON sm2.segment_id = ps.segment_id
         WHERE sm2.unique_id  = (spm.item_id::text || '_' || spm.site_id::text)
           AND sm2.pipeline_id = 21                -- << pipeline_id
           AND p.parameter_type = 'outlier_detection'
         ORDER BY p.sort_order LIMIT 1)            AS sql_out_id

    FROM scenario.series_parameter_map spm
    WHERE spm.pipeline_id          = 21            -- << pipeline_id
      AND spm.segmentation_scenario_id = 1
)
-- Show mismatches only; remove the WHERE to see all rows
SELECT *
FROM resolved
WHERE stored_fps_id IS DISTINCT FROM sql_fps_id
   OR stored_out_id IS DISTINCT FROM sql_out_id
ORDER BY unique_id;

Expected output: zero rows. Each mismatch means either: - The preprocess step was not re-run after a parameter-segment link was changed, OR - A segment criteria edit changed membership without a subsequent segmentation re-run.

---

5. MEIO multi-parameter test

5.1 How MEIO differs from other parameters

Unlike forecast / outlier / characterisation parameters (which use LIMIT 1 and pick a single winner), the MEIO resolver collects all matching parameter set IDs into an array:

# from build_series_parameter_map in segmentation.py
ARRAY(
    SELECT mps.id
    FROM scenario.config_meio_parameter_set mps
    JOIN scenario.config_meio_parameter_set_segment mpss ON mpss.parameter_set_id = mps.id
    JOIN ch_sm sm2 ON sm2.segment_id = mpss.segment_id
    JOIN scenario.config_scenario_pipeline sp ON sp.meio_scenario_id = mps.scenario_id
    WHERE sm2.unique_id = base.unique_id
      AND sp.pipeline_id = %(pid)s
    ORDER BY mps.sort_order
)  -- stored as series_parameter_map.meio_parameter_ids  BIGINT[]

The MEIO optimiser receives the full array and treats each set as a simultaneous constraint. A series that belongs to two segments — each linked to a different MEIO parameter set — will have both applied (e.g. a high fill-rate target from one set AND a budget cap from another).

5.2 Verify array assignment in series_parameter_map

-- Series with more than one MEIO parameter set assigned
SELECT
    spm.item_id,
    spm.site_id,
    spm.item_id::text || '_' || spm.site_id::text    AS unique_id,
    spm.meio_parameter_ids,
    array_length(spm.meio_parameter_ids, 1)          AS num_meio_sets
FROM scenario.series_parameter_map spm
WHERE spm.pipeline_id             = 21               -- << pipeline_id
  AND spm.segmentation_scenario_id = 1
  AND array_length(spm.meio_parameter_ids, 1) > 1
ORDER BY array_length(spm.meio_parameter_ids, 1) DESC, spm.item_id, spm.site_id;

If this returns zero rows, either no series belongs to multiple MEIO-linked segments, or the MEIO scenario has only one parameter set. Use section 5.4 to create a test scenario, then re-run the preprocess step.

5.3 Expand and inspect each parameter set per series

-- For a specific item/loc, expand the MEIO parameter array and show each set's config
-- Substitute item_id and site_id from the result of 5.2.
SELECT
    spm.item_id,
    spm.site_id,
    mps.id                                           AS param_set_id,
    mps.name                                         AS param_set_name,
    mps.sort_order,
    mps.params                                       AS param_set_config,
    -- Segments this parameter set is linked to
    string_agg(cs.name, ', ' ORDER BY cs.name)       AS linked_segments
FROM scenario.series_parameter_map spm
JOIN scenario.config_meio_parameter_set mps
    ON mps.id = ANY(spm.meio_parameter_ids)
JOIN scenario.config_meio_parameter_set_segment mpss
    ON mpss.parameter_set_id = mps.id
JOIN scenario.config_segment cs
    ON cs.id = mpss.segment_id
WHERE spm.pipeline_id             = 21               -- << pipeline_id
  AND spm.segmentation_scenario_id = 1
  AND spm.item_id  = 10                              -- << item_id
  AND spm.site_id  = 301                             -- << site_id
GROUP BY spm.item_id, spm.site_id, mps.id, mps.name, mps.sort_order, mps.params
ORDER BY mps.sort_order;

The output should list one row per MEIO parameter set, with the segment(s) that caused it to be included for this item/loc.

Verify which segments the series belongs to

-- Confirm which segments item_id=10, site_id=301 is in for the pipeline
SELECT
    m.segment_id,
    cs.name AS segment_name
FROM scenario.pipe_segment_membership m
JOIN scenario.config_segment cs ON cs.id = m.segment_id
WHERE m.unique_id    = '10_301'    -- << unique_id (item_id || '_' || site_id)
  AND m.pipeline_id  = 21          -- << pipeline_id
ORDER BY cs.name;

SQL-derived MEIO array (without running preprocess)

Use this to predict what meio_parameter_ids should contain for a given series before running the preprocess step:

SELECT
    ARRAY(
        SELECT DISTINCT mps.id
        FROM scenario.config_meio_parameter_set mps
        JOIN scenario.config_meio_parameter_set_segment mpss
             ON mpss.parameter_set_id = mps.id
        JOIN scenario.pipe_segment_membership sm2
             ON sm2.segment_id = mpss.segment_id
        JOIN scenario.config_scenario_pipeline sp
             ON sp.meio_scenario_id = mps.scenario_id
        WHERE sm2.unique_id  = '10_301'  -- << unique_id
          AND sm2.pipeline_id = 21       -- << pipeline_id
          AND sp.pipeline_id  = 21       -- << pipeline_id
        ORDER BY mps.sort_order
    ) AS expected_meio_parameter_ids;

Compare this against spm.meio_parameter_ids from section 5.2. They must match.

5.4 Seed a test scenario

Run this once in theBicycle's PostgreSQL database to create a controlled scenario where a single item/loc is guaranteed to receive two MEIO parameter sets.

The setup uses two existing segments that overlap on at least some series: - "A items" (ABC class A) AND "Central warehouses" (site_type = central_wh) - Any series that is class A AND at a central warehouse will receive both sets.

DO $$
DECLARE
    _schema         TEXT    := 'scenario';
    _meio_sid       BIGINT;    -- MEIO scenario id
    _set_id_a       BIGINT;    -- parameter set for "A items"
    _set_id_cw      BIGINT;    -- parameter set for "Central warehouses"
    _seg_a          INTEGER;   -- segment id for "A items"
    _seg_cw         INTEGER;   -- segment id for "Central warehouses"
BEGIN

    -- 1. Create (or reuse) a dedicated test MEIO scenario
    EXECUTE format($$
        INSERT INTO %I.scen_meio_scenarios (name, is_base, param_overrides)
        VALUES ('TEST — multi-param', FALSE, '{
            "fill_rate_target": 0.95,
            "service_level_type": "fill_rate"
        }'::jsonb)
        ON CONFLICT DO NOTHING
        RETURNING scenario_id
    $$, _schema) INTO _meio_sid;

    IF _meio_sid IS NULL THEN
        EXECUTE format($$
            SELECT scenario_id FROM %I.scen_meio_scenarios WHERE name = 'TEST — multi-param'
        $$, _schema) INTO _meio_sid;
    END IF;
    RAISE NOTICE 'MEIO scenario id = %', _meio_sid;

    -- 2. Look up segment ids
    EXECUTE format('SELECT id FROM %I.config_segment WHERE name = $1', _schema)
        USING 'A items' INTO _seg_a;
    EXECUTE format('SELECT id FROM %I.config_segment WHERE name = $1', _schema)
        USING 'Central warehouses' INTO _seg_cw;

    IF _seg_a IS NULL OR _seg_cw IS NULL THEN
        RAISE EXCEPTION 'Segments "A items" and/or "Central warehouses" not found — run seed_segments_thebicycle.sql first';
    END IF;
    RAISE NOTICE 'Segments: A items=%, Central warehouses=%', _seg_a, _seg_cw;

    -- 3. Create parameter set 1 — high service level for A items
    EXECUTE format($$
        INSERT INTO %I.config_meio_parameter_set (scenario_id, name, params, sort_order)
        VALUES (%L, 'A-items high SL', '{
            "fill_rate_target": 0.99,
            "service_level_type": "fill_rate"
        }'::jsonb, 0)
        ON CONFLICT DO NOTHING
        RETURNING id
    $$, _schema, _meio_sid) INTO _set_id_a;

    IF _set_id_a IS NULL THEN
        EXECUTE format($$
            SELECT id FROM %I.config_meio_parameter_set
            WHERE scenario_id = %L AND name = 'A-items high SL'
        $$, _schema, _meio_sid) INTO _set_id_a;
    END IF;

    -- Link parameter set 1 to "A items" segment
    EXECUTE format($$
        INSERT INTO %I.config_meio_parameter_set_segment (parameter_set_id, segment_id)
        VALUES (%L, %L)
        ON CONFLICT DO NOTHING
    $$, _schema, _set_id_a, _seg_a);

    -- 4. Create parameter set 2 — budget cap for central warehouses
    EXECUTE format($$
        INSERT INTO %I.config_meio_parameter_set (scenario_id, name, params, sort_order)
        VALUES (%L, 'Central WH budget cap', '{
            "fill_rate_target": 0.95,
            "max_budget": 250000,
            "service_level_type": "fill_rate"
        }'::jsonb, 1)
        ON CONFLICT DO NOTHING
        RETURNING id
    $$, _schema, _meio_sid) INTO _set_id_cw;

    IF _set_id_cw IS NULL THEN
        EXECUTE format($$
            SELECT id FROM %I.config_meio_parameter_set
            WHERE scenario_id = %L AND name = 'Central WH budget cap'
        $$, _schema, _meio_sid) INTO _set_id_cw;
    END IF;

    -- Link parameter set 2 to "Central warehouses" segment
    EXECUTE format($$
        INSERT INTO %I.config_meio_parameter_set_segment (parameter_set_id, segment_id)
        VALUES (%L, %L)
        ON CONFLICT DO NOTHING
    $$, _schema, _set_id_cw, _seg_cw);

    -- 5. Link the test MEIO scenario to the pipeline
    --    (update pipeline 21 or whichever pipeline you are testing against)
    EXECUTE format($$
        UPDATE %I.config_scenario_pipeline
        SET meio_scenario_id = %L
        WHERE pipeline_id = 21           -- << pipeline_id
    $$, _schema, _meio_sid);

    RAISE NOTICE 'Done. Set ids: A-items=%, Central WH=%', _set_id_a, _set_id_cw;
    RAISE NOTICE 'Now run the Preprocess step for pipeline 21 and re-run section 5.2.';

END $$;

After running this seed: 1. Run Processes → Preprocess in the UI (or POST /api/pipeline/run with step preprocess). 2. Then execute the queries in sections 5.2 and 5.3.

5.5 Verify the test scenario end-to-end

After seeding and re-running preprocess:

-- Step A: confirm both parameter sets exist and are linked
SELECT
    mps.id, mps.name, mps.sort_order, mps.params,
    string_agg(cs.name, ', ')  AS linked_segments
FROM scenario.config_meio_parameter_set mps
JOIN scenario.config_meio_parameter_set_segment mpss ON mpss.parameter_set_id = mps.id
JOIN scenario.config_segment cs                      ON cs.id = mpss.segment_id
WHERE mps.scenario_id = (
    SELECT scenario_id FROM scenario.scen_meio_scenarios WHERE name = 'TEST — multi-param'
)
GROUP BY mps.id, mps.name, mps.sort_order, mps.params
ORDER BY mps.sort_order;
-- Expected: 2 rows — "A-items high SL" (sort 0) and "Central WH budget cap" (sort 1)

-- Step B: find series that belong to BOTH segments
SELECT m1.unique_id
FROM scenario.pipe_segment_membership m1
JOIN scenario.config_segment s1 ON s1.id = m1.segment_id AND s1.name = 'A items'
JOIN scenario.pipe_segment_membership m2
    ON m2.unique_id = m1.unique_id AND m2.pipeline_id = m1.pipeline_id
JOIN scenario.config_segment s2 ON s2.id = m2.segment_id AND s2.name = 'Central warehouses'
WHERE m1.pipeline_id = 21      -- << pipeline_id
ORDER BY m1.unique_id;
-- These series should all appear with array_length = 2 in Step C.

-- Step C: verify meio_parameter_ids has exactly 2 entries for those series
SELECT
    spm.item_id,
    spm.site_id,
    spm.meio_parameter_ids,
    array_length(spm.meio_parameter_ids, 1)  AS num_sets
FROM scenario.series_parameter_map spm
WHERE spm.pipeline_id             = 21       -- << pipeline_id
  AND spm.segmentation_scenario_id = 1
  AND array_length(spm.meio_parameter_ids, 1) >= 2
ORDER BY spm.item_id, spm.site_id;
-- Every row in Step B should appear here with num_sets = 2.

-- Step D: cross-check — SQL-derived array vs stored array for all series
SELECT
    spm.item_id,
    spm.site_id,
    spm.meio_parameter_ids                       AS stored_ids,
    ARRAY(
        SELECT DISTINCT mps.id
        FROM scenario.config_meio_parameter_set mps
        JOIN scenario.config_meio_parameter_set_segment mpss
             ON mpss.parameter_set_id = mps.id
        JOIN scenario.pipe_segment_membership sm2
             ON sm2.segment_id = mpss.segment_id
        JOIN scenario.config_scenario_pipeline sp
             ON sp.meio_scenario_id = mps.scenario_id
        WHERE sm2.unique_id   = spm.item_id::text || '_' || spm.site_id::text
          AND sm2.pipeline_id = 21              -- << pipeline_id
          AND sp.pipeline_id  = 21              -- << pipeline_id
        ORDER BY mps.sort_order
    )                                            AS sql_ids
FROM scenario.series_parameter_map spm
WHERE spm.pipeline_id             = 21          -- << pipeline_id
  AND spm.segmentation_scenario_id = 1
HAVING spm.meio_parameter_ids IS DISTINCT FROM ARRAY(
        SELECT DISTINCT mps.id
        FROM scenario.config_meio_parameter_set mps
        JOIN scenario.config_meio_parameter_set_segment mpss ON mpss.parameter_set_id = mps.id
        JOIN scenario.pipe_segment_membership sm2 ON sm2.segment_id = mpss.segment_id
        JOIN scenario.config_scenario_pipeline sp ON sp.meio_scenario_id = mps.scenario_id
        WHERE sm2.unique_id   = spm.item_id::text || '_' || spm.site_id::text
          AND sm2.pipeline_id = 21
          AND sp.pipeline_id  = 21
        ORDER BY mps.sort_order
)
ORDER BY spm.item_id, spm.site_id;
-- Expected: zero rows.

---

6. Common issues

Symptom	Likely cause	Fix
diff <> 0 in section 3.3 for ABC segments	pipe_abc_results empty or for wrong pipeline	Re-run the ABC Classification step
"Fast movers" count higher than expected	Series with is_intermittent = NULL are included — correct Python behaviour	Check characterisation has run; or tighten criteria to = FALSE
diff <> 0 for all segments	Membership stored for a different pipeline than queried	Re-check pipeline id from section 2
PG and CH counts differ (section 3.5)	CH sync failure during segmentation step	Check pipe_process_log for errors on step segmentation
sql_fps_id always NULL	config_forecast_parameter_set_segment not populated	Link segments to forecast parameter sets via the Processes → Forecast Params tab
sql_out_id always NULL	config_parameter_segment not populated for outlier_detection type	Add segment links in Settings → Parameters
meio_parameter_ids is empty array	Pipeline's meio_scenario_id not set, or no MEIO sets linked to segments	Run seed in section 5.4; re-run preprocess
meio_parameter_ids has 1 entry when 2 expected	Series not in both segments, or preprocess not re-run	Verify section 5.5 Step B returns the series; re-run preprocess