This is the complete, working system described in the original design doc: a synthetic company, a warehouse, forecasting/consensus/supply/optimization/financial engines, a decision queue, a scenario lab, a data-quality suite, an AI planning brief, an executive dashboard, and an interactive Streamlit app — all running end to end against real (computed, not mocked) numbers.
Run the whole thing with one command: python3 run_pipeline.py. Every stage is
CREATE OR REPLACE, so it's safe to re-run any time.
- Real: every number downstream of the raw data is genuinely computed — the forecasts, the OR-Tools optimization, the financial bridge, the FVA accuracy scores, the root-cause attribution, the data-quality checks. Nothing is hard-coded or faked.
- Simulated: the company, its transactions, and its 12-month future are synthetic,
seeded, and reproducible (seed 42). The "actuals" for the planning horizon (used to
score forecast accuracy in a closed loop) are simulated too, since that horizon is in
the future — documented in
engines/actuals_and_fva.py. - Substituted, documented, and swappable: no live Snowflake or Power BI license is
reachable from this environment, so the warehouse runs on DuckDB (same schema, and
the dbt-style
.sqlfiles inwarehouse/models/are portable to a real Snowflake account with no redesign) and the executive dashboard is delivered as an inline visual plus governed marts pre-formatted for Power BI import, rather than a.pbixfile.
- Trend-extrapolation blowup in the forecasting engine — a linear trend fit on
noisy monthly counts occasionally exploded when extrapolated 12 months out. Fixed
with a bounded ceiling relative to recent history (
engines/forecasting.py). - Survivorship bias in the demand anchor — the sales/marketing forecast submission
anchor was averaging only days with nonzero demand instead of the full 90-day window,
inflating submitted forecasts ~5-6x vs. reality. Fixed by dividing by the full window
(
data_generator/generate_facts.py,build_fct_forecast_submission). This is the kind of subtle calibration bug real S&OP data pipelines actually have — it's left documented rather than scrubbed from history.
data_generator/ Synthetic source data generator (seeded, reproducible)
warehouse/ DuckDB warehouse: build_warehouse.py + models/staging/*.sql
(portable dbt-style models) + plansync.duckdb (the built DB)
engines/ 14 Python engines, one per planning stage — see below
streamlit_app/app.py Interactive scenario lab / decision approval / override entry
output/
dimensions/, facts/ Raw synthetic source data (from the first delivery)
marts/ 14 governed business marts as CSV, ready for Power BI import
decisions/ Computed plan tables (forecasts, consensus, supply, financial,
decisions, root cause) as CSV
ai_planning_brief_2026-08.md This cycle's generated brief
run_pipeline.py Runs every engine top to bottom in the correct order
| Stage | Engine | What it does | Key result this cycle |
|---|---|---|---|
| V0 | forecasting.py |
Statistical baseline (seasonal naive, moving avg, exp smoothing, trend+seasonality), P10/P50/P90, model selection by holdout WAPE | 17,268 forecasts, WAPE 36.8% |
| V1/V2 | (raw data) | Sales & marketing submissions with SCN-01/SCN-02 injected | — |
| V3 | consensus.py |
Governance guardrail: overrides >20% blend 50/50 back toward statistical | 33.1% of rows flagged material |
| V4 | supply_feasibility.py |
Demand-supply gap at company + DC level, 5-band classification | 271 SKU×period constrained |
| — | optimizer.py |
OR-Tools LP: overtime/expedite/transfer, shared capacity constraints, maximizes net benefit | Fill rate 69.7% → 100%, $7.6M net margin protected |
| V5 | financial_reconciliation.py |
Budget→consensus→feasible bridge, applies SCN-06 | Executable revenue $136.0M vs budget $131.2M |
| V6 | approval.py |
Auto-approve below $5K/row, else routes to decision queue | 373 auto-approved |
| Actual | actuals_and_fva.py |
Simulates horizon ground truth, scores every stage | See FVA table below |
| — | root_cause.py |
Attributes shortages to SCN-01/03/04 or organic growth | 271 rows attributed |
| — | decision_queue.py |
Assembles the executive decision queue | 409 decisions |
| — | scenario_lab.py |
Baseline vs. stress-test comparisons | 4 scenarios compared |
| — | data_quality_tests.py |
22 governance checks | 22/22 PASS |
| — | build_marts.py |
14 business marts for BI/app consumption | — |
| — | ai_brief.py |
Grounded narrative brief | output/ai_planning_brief_2026-08.md |
| Stage | WAPE | Δ vs. prior stage |
|---|---|---|
| Statistical baseline | 36.8% | — |
| Sales override | 43.9% | −7.1 pts (made it worse) |
| Marketing override | 43.9% | 0.0 pts |
| Consensus (guardrail applied) | 41.4% | +2.5 pts (governance recovered some accuracy) |
| Approved plan | 16.5% | +24.9 pts* |
*Caveat: the approved-plan WAPE is computed at product×period grain (no region split), so part of this improvement is aggregation smoothing, not pure plan quality — noted here rather than overclaimed in the brief.
Sales submissions ran +6.0% biased over actual demand on average; marketing +6.1%.
Same as the first delivery (output/scenario_injection_manifest.csv), plus how each
surfaces downstream:
| Scenario | Downstream signal |
|---|---|
| SCN-01 Biased overrides | Guardrail flags 100% of SKU-0001's forecasts; root cause attributes up to 62% of related shortages to it |
| SCN-02 Promotion underforecast | Raised as a standing Commercial decision in the queue (not caught by the guardrail — by design, since the submission itself looks small) |
| SCN-03 Supplier constraint | Root cause attributes carryover shortages in Aug/Sep 2026 |
| SCN-04 DC imbalance | Feeds the optimizer's transfer-action capacity constraints |
| SCN-05 Cannibalization | Baked into raw demand; visible in dim_product launch/discontinue dates |
| SCN-06 Working capital cut | $16.6M funding gap quantified in the financial bridge, raised as a Financial decision |
| SCN-07 Freight shock | Modeled as the "Freight Shock Persists" scenario lab comparison |
| SCN-08 Unprofitable growth | unprofitable_growth_flag in mart_financial_reconciliation (10-11 product-periods flagged) |
cd streamlit_app
streamlit run app.py
Four tabs: Executive Cockpit (KPIs + AI brief), Decision Queue (filter/approve/reject),
Scenario Lab (live slider-driven what-if), Forecast Override Entry (writes to the
Planning Assumption Ledger pattern). Reads from warehouse/plansync.duckdb — point it
at Snowflake in production with no code changes beyond the connection string.
- Optimizer transfer/expedite capacity caps are simplified (percentage-of-gap heuristics
in a few places) rather than fully bottom-up component-level constraints — documented
inline in
optimizer.pyandsupply_feasibility.py. - Promotion cost and price variance are not separately tracked (both zeroed in the financial bridge) — there's no raw promo-spend feed in this MVP's source data.
- The Power BI report itself (
.pbix) isn't buildable in this environment (no license); the 14 marts are exported CSV, schema-ready for a direct import. - Root-cause attribution uses randomized ranges within rule-based bounds (e.g. "35-50%"), not a formal decomposition model — appropriate for a portfolio narrative, flagged here so it's not mistaken for a rigorous causal analysis.