Data Integration & Warehouse Architecture:
Multi-Source ETL Pipeline (Apache Airflow):
- SAP S/4HANA extraction:
- Leveraged SAP ODP (Operational Data Provisioning) framework for CDC (change data capture)
- Extracted tables: VBAK/VBAP (sales orders), MARA/MARC (material master), EKKO/EKPO (purchase orders), MARD (inventory stocks)
- Python connector using pyrfc library for RFC-enabled function modules
- Incremental extraction based on AEDAT (changed date) field to minimize data transfer
- WMS (Manhattan) integration:
- REST API calls to Manhattan's WMi interface
- Endpoints: /inventory/snapshot, /receipts, /shipments, /adjustments
- Polling frequency: Hourly for inventory positions, real-time webhooks for shipment confirmations
- TMS (Oracle) integration:
- JDBC connection to Oracle database for shipment tracking data
- SQL queries extracting carrier performance (on-time delivery %), lead times (95th percentile), freight costs
- Supplier data aggregation:
- Web scraping + API calls to vendor portals (custom connectors per supplier)
- Standardized schema mapping to canonical supplier_performance table
- Fields: on_time_delivery_rate, quality_defect_ppm, lead_time_avg, lead_time_std, fill_rate
Data Warehouse Design (Snowflake):
- Implemented Kimball-style dimensional model:
- Fact tables: fact_sales (grain: sales order line), fact_inventory_snapshot (grain: SKU-location-day), fact_shipments (grain: shipment ID)
- Dimension tables: dim_product (SCD Type 2 for price/category changes), dim_customer, dim_location, dim_supplier, dim_date
- Bridge table: bridge_supplier_product for many-to-many supplier relationships
- Partitioning strategy: fact_sales partitioned by order_date (monthly partitions), clustered on sku_id for query performance
- Materialized views for pre-aggregated metrics (daily_sales_by_sku, weekly_inventory_turns, monthly_supplier_scorecard)
- Achieved query performance: <2s for typical analytical queries, <500ms for dashboard KPIs
Data Quality Framework:
- Implemented Great Expectations for automated data validation:
- Completeness checks: Null rate <5% for critical fields (sku_id, order_date, quantity)
- Consistency checks: Referential integrity (all SKUs in fact_sales exist in dim_product)
- Accuracy checks: Sales quantity >0, unit_price within expected range ($0.01-$10,000)
- Timeliness checks: Data freshness <24 hours (alert if last update timestamp >1 day old)
- Automated data profiling: Distribution analysis, outlier detection (IQR method), schema drift monitoring
Demand Forecasting Models & Methodology:
Feature Engineering Pipeline:
- Temporal features:
- Lag features: sales_lag_7d, sales_lag_14d, sales_lag_30d, sales_lag_365d (year-over-year)
- Rolling statistics: sales_rolling_mean_7d, sales_rolling_std_30d (volatility measure)
- Trend: Linear regression coefficient over past 90 days
- Seasonality indicators: Month, quarter, day_of_week, week_of_year, is_holiday (binary)
- Promotional features:
- is_on_promotion (binary), discount_percentage (0-100), days_since_last_promotion
- Promotional lift factor: (sales during promo / baseline sales) calculated per SKU
- External variables:
- Weather data: Temperature, precipitation (for weather-sensitive products)
- Economic indicators: CPI, unemployment rate, consumer confidence index
- Competitor pricing: Web-scraped price indices from major competitors
- Product attributes:
- Category, brand, price_tier, launch_date, is_seasonal (binary)
- Cross-sell affinity: Collaborative filtering-derived product similarity scores
Model Architecture & Selection:
- Benchmarked multiple time series forecasting approaches:
- Baseline (naive): Last year same week sales → MAPE 32%
- ARIMA: Auto-regressive integrated moving average → MAPE 26%
- Prophet (Facebook): Additive model with trend + seasonality → MAPE 21%
- XGBoost: Gradient boosted trees on engineered features → MAPE 16%
- LSTM: Long short-term memory neural network → MAPE 15%
- Ensemble (XGBoost + LSTM): Weighted average (70/30) → MAPE 14% ✓ SELECTED
- XGBoost configuration:
- Objective: reg:squarederror (minimize MSE)
- Hyperparameters: learning_rate=0.05, max_depth=6, n_estimators=500, subsample=0.8
- Tuned in three stages (RandomizedSearchCV → GridSearchCV → Optuna, 100 trials) with 5-fold time series cross-validation; see Hyperparameter Optimization & Cross-Validation below
- LSTM architecture:
- Layers: LSTM(128) → Dropout(0.2) → LSTM(64) → Dense(32) → Dense(1)
- Sequence length: 30 days (input window)
- Framework: TensorFlow/Keras with early stopping (patience=10 epochs)
- Training strategy:
- Rolling window cross-validation: Train on 18 months, validate on 3 months, iterate forward
- SKU-level models for top 20% products (by revenue), category-level models for long tail
- Monthly retraining to incorporate latest data and adapt to changing patterns
Hyperparameter Optimization & Cross-Validation:
- Time-aware cross-validation (no shuffling):
- Rolling-origin folds: 5 folds, each training on 18 months and validating on the 3-month block that follows (after a 28-day gap), stepping forward 3 months per fold
- Folds are split by calendar date, not by row, so every SKU shares the same cutoff and no SKU's future leaks into another's training data
- 28-day gap between each training window and its validation window, so lag and rolling features cannot carry information that would not exist at forecast time
- Standard shuffled k-fold was ruled out: it trains on the future to predict the past and overstates accuracy
- The most recent 3 months are held out entirely and never used during tuning; they give the final, unbiased accuracy estimate
- Tuning objective: WAPE (weighted absolute percentage error), which stays well-defined on zero-sales days and weights errors by volume; MAPE is still reported for comparability with the baseline
- Coarse-to-fine search strategy:
- Stage 1: RandomizedSearchCV sampled 60 configurations from wide distributions (learning_rate log-uniform 0.01–0.3, max_depth 3–10, n_estimators 200–1,500, subsample 0.6–1.0, colsample_bytree 0.5–1.0, min_child_weight 1–10) to find the promising region cheaply
- Stage 2: GridSearchCV exhaustively evaluated a 3×3×3×3 grid (81 configurations × 5 folds = 405 fits) centred on the stage-1 winner
- Stage 3: Optuna (TPE sampler, 100 trials) refined the result and tuned regularization terms (reg_alpha, reg_lambda), confirming the final configuration: learning_rate=0.05, max_depth=6, n_estimators=500, subsample=0.8
- Ensemble weight (70/30 XGBoost/LSTM) chosen by minimizing out-of-fold WAPE on the same folds
- Overfitting safeguards: compared training vs. cross-validated error for every candidate, checked fold-to-fold variance, and preferred the simpler configuration when scores were within one standard error
Core of the tuning code (scikit-learn + XGBoost):
import numpy as np
import pandas as pd
from scipy.stats import loguniform, randint, uniform
from sklearn.metrics import make_scorer
from sklearn.model_selection import GridSearchCV, RandomizedSearchCV
from xgboost import XGBRegressor
def wape(y_true, y_pred):
"""Weighted absolute percentage error: safe on zero-sales days, weighted by volume."""
return np.abs(y_true - y_pred).sum() / np.abs(y_true).sum()
wape_scorer = make_scorer(wape, greater_is_better=False)
def date_folds(dates, n_folds=5, train_months=18, val_months=3, gap_days=28):
"""Rolling-origin folds split by calendar date, so every SKU shares the same cutoff."""
dates = pd.to_datetime(pd.Series(dates)).reset_index(drop=True)
last_cutoff = dates.max() - pd.DateOffset(months=val_months)
for k in reversed(range(n_folds)):
cutoff = last_cutoff - pd.DateOffset(months=val_months * k)
train = (dates > cutoff - pd.DateOffset(months=train_months)) & (dates <= cutoff)
val = (dates > cutoff + pd.Timedelta(days=gap_days)) & (
dates <= cutoff + pd.DateOffset(months=val_months))
yield np.flatnonzero(train), np.flatnonzero(val)
# X_tune / y_tune: all history except the final 3-month holdout, which tuning never sees
cv = list(date_folds(X_tune["order_date"]))
features = X_tune.drop(columns="order_date")
base = dict(objective="reg:squarederror", tree_method="hist", random_state=42)
# Stage 1: broad random search over wide distributions
random_search = RandomizedSearchCV(
XGBRegressor(**base),
param_distributions={
"learning_rate": loguniform(0.01, 0.3),
"max_depth": randint(3, 11),
"n_estimators": randint(200, 1501),
"subsample": uniform(0.6, 0.4), # 0.6 - 1.0
"colsample_bytree": uniform(0.5, 0.5), # 0.5 - 1.0
"min_child_weight": randint(1, 11),
},
n_iter=60, scoring=wape_scorer, cv=cv, n_jobs=-1, random_state=42,
)
random_search.fit(features, y_tune)
best = random_search.best_params_
# Stage 2: exhaustive grid centred on the stage-1 winner
grid_search = GridSearchCV(
XGBRegressor(**base, colsample_bytree=best["colsample_bytree"],
min_child_weight=best["min_child_weight"]),
param_grid={
"learning_rate": [0.03, 0.05, 0.08],
"max_depth": [5, 6, 7],
"n_estimators": [400, 500, 700],
"subsample": [0.7, 0.8, 0.9],
},
scoring=wape_scorer, cv=cv, n_jobs=-1,
)
grid_search.fit(features, y_tune)
print(grid_search.best_params_, f"CV WAPE = {-grid_search.best_score_:.3f}")
Forecast Evaluation & Calibration:
- Primary metric: MAPE (Mean Absolute Percentage Error) = Σ|actual - forecast|/actual × 100%
- Baseline: 32% → Production model: 14% (18pp improvement)
- Target: <15% (achieved for 78% of SKUs)
- Secondary metrics:
- RMSE (Root Mean Squared Error): Penalizes large errors
- Bias: Mean(forecast - actual) to detect systematic over/under-forecasting
- Forecast Value Added (FVA): Improvement over naive baseline
- Prediction intervals: Generated via quantile regression (5th, 50th, 95th percentiles) for uncertainty quantification
Inventory Optimization Engine:
Stochastic Inventory Model Implementation:
- Safety stock calculation (continuous review, (Q,r) policy):
- Formula:
SS = z × σ_LT × √(L + L×CV²_D)
- Where:
- z = service level factor (e.g., 1.65 for 95% service level via normal distribution)
- σ_LT = standard deviation of demand during lead time
- L = lead time (days)
- CV_D = coefficient of variation of demand (σ_demand / μ_demand)
- Demand variability (σ_LT) estimated from forecast prediction intervals (95th percentile - 50th percentile)
- Lead time (L) sourced from TMS historical data (95th percentile to account for variability)
- Reorder point (r):
- Formula:
r = (μ_demand × L) + SS
- μ_demand = average daily demand from ML forecast
- Trigger: Generate purchase requisition when inventory position ≤ r
- Economic order quantity (Q):
- Formula:
Q = √(2 × D × S / H)
- D = annual demand, S = ordering cost ($150/order), H = holding cost (25% of unit cost annually)
- Modified for quantity discounts and container/pallet constraints
- Service level optimization:
- Differentiated by product class (ABC analysis):
- Class A (top 20% revenue): 98% target service level (z=2.05)
- Class B (next 30%): 95% service level (z=1.65)
- Class C (bottom 50%): 90% service level (z=1.28)
- Trade-off analysis: Cost of stockout (lost margin + customer dissatisfaction) vs cost of excess inventory
Multi-Echelon Optimization (Future Enhancement):
- Network topology: 1 central DC + 5 regional DCs + 25 local warehouses
- Objective: Minimize total network inventory while meeting service levels
- Approach: Stochastic inventory optimization with guaranteed-service models (GSM)
- Status: Phase 2 roadmap (current focus: single-echelon optimization at DC level)
Supplier Risk Scoring & Monitoring:
Multi-Dimensional Risk Model:
- Delivery performance score (40% weight):
- On-time delivery rate: % of shipments delivered within ±2 days of promised date
- Lead time consistency: Coefficient of variation of actual lead times (lower is better)
- Fill rate: % of ordered quantity actually shipped
- Calculation: Weighted average with exponential decay (recent performance weighted higher)
- Threshold: <85% on-time triggers "at risk" flag, <70% triggers "critical risk"
- Quality performance score (35% weight):
- Defect rate: PPM (parts per million) defective based on incoming inspection
- Customer returns: % of products returned due to supplier-caused defects
- Audit scores: Results from quarterly supplier quality audits (0-100 scale)
- Threshold: >1000 PPM triggers quality review, >5000 PPM triggers containment action
- Financial health score (25% weight):
- Credit rating: Dun & Bradstreet PAYDEX score (1-100), mapped to 0-10 scale
- Payment history: Days sales outstanding (DSO), % of invoices paid late
- Business alerts: Negative news detection via NLP on business news feeds (bankruptcy filings, lawsuits, leadership changes)
- Financial ratios: Current ratio, debt-to-equity (from financial statements where available)
- Composite risk score:
- Formula:
Risk_Score = (0.4 × Delivery) + (0.35 × Quality) + (0.25 × Financial)
- Normalized to 0-100 scale (100 = lowest risk, 0 = highest risk)
- Risk tiers: >80 = Low Risk (green), 60-80 = Medium Risk (yellow), <60 = High Risk (red)
Automated Alerting & Escalation:
- Daily risk score calculation for all active suppliers (n=247)
- Alert triggers:
- Risk score decline >10 points in 30 days → Email to category manager
- Risk score drops below 60 → Escalation to procurement director + sourcing team meeting scheduled
- On-time delivery <70% for 2 consecutive weeks → Supplier performance review meeting auto-scheduled
- Negative business news detected → Immediate alert to strategic sourcing + supplier contact
- Integration: Alerts sent via Slack (real-time) + email digest (daily summary) + dashboard flags
Deployment & MLOps Infrastructure:
- Model serving architecture:
- Batch inference: Airflow DAG runs nightly (2 AM UTC) to generate forecasts for next 90 days
- XGBoost models served via pickle serialization + custom Python scoring service
- LSTM models served via TensorFlow Serving (gRPC API for low-latency inference)
- Forecast outputs written to Snowflake (forecast_demand table) for consumption by planning systems
- Model versioning & governance (MLflow):
- All models logged with hyperparameters, training metrics, and artifacts
- Model registry with staging/production promotion workflow
- A/B testing framework: 10% of SKUs served by challenger model, 90% by production model
- Automated rollback if challenger MAPE >2pp worse than production
- Monitoring & drift detection:
- Forecast accuracy tracking: Weekly MAPE calculation on realized sales vs forecasts
- Feature drift: Jensen-Shannon divergence between training data distribution and production data (alert if JS divergence >0.15)
- Prediction drift: Distribution comparison of forecast values week-over-week
- Dashboards: Grafana for real-time metrics (forecast accuracy, model latency, data pipeline health)
- Automated retraining pipeline:
- Monthly full retraining on rolling 24-month window
- Trigger: Scheduled (1st of month) OR on-demand if accuracy degrades >3pp
- Validation: Hold-out test on most recent 3 months, promote only if MAPE improvement >1pp
- Parallel training: 16 workers on Kubernetes cluster (each worker handles subset of SKUs)