Production Deployment & Monitoring

Deploy forecasting models to production and monitor performance over time.

Deployment Checklist

Before deploying to production:

• Model evaluated on test data (not training data)
• Accuracy metrics meet acceptance criteria
• Prediction intervals are reasonable
• Code tested and reviewed
• Data quality checks automated
• Monitoring alerts configured
• Rollback plan documented
• Stakeholders notified

---

Step 1: Final Model Validation

-- Final check before deployment
LOAD anofox_forecast;

-- 1. Verify model accuracy on test data
SELECT
    ROUND(TS_MAE(LIST(actual), LIST(forecast)), 2) as MAE,
    ROUND(TS_RMSE(LIST(actual), LIST(forecast)), 2) as RMSE,
    ROUND(TS_MAPE(LIST(actual), LIST(forecast)), 2) as MAPE_pct
FROM test_results;

-- 2. Check prediction interval coverage
SELECT
    ROUND(TS_COVERAGE(LIST(actual), LIST(lower), LIST(upper)), 3) as coverage_95
FROM test_results;

-- 3. Verify forecast values are reasonable
SELECT
    ROUND(MIN(point_forecast), 2) as min_forecast,
    ROUND(MAX(point_forecast), 2) as max_forecast,
    ROUND(AVG(point_forecast), 2) as avg_forecast
FROM test_results;

-- All checks pass? → Proceed to deployment

---

Step 2: Create Production Forecast

Train model on full historical data:

-- Create production forecast table
CREATE TABLE forecast_production AS
SELECT
    forecast_step,
    date_col as forecast_date,
    point_forecast,
    lower_95,
    upper_95,
    CURRENT_TIMESTAMP() as forecast_generated_at,
    'AutoETS' as model_name,
    'production' as environment
FROM TS_FORECAST(
    'full_historical_data',  -- Use ALL data, not just training
    'date',
    'sales',
    'AutoETS',               -- Best model from testing
    90,                      -- Forecast 90 days ahead
    {'seasonal_period': 7, 'confidence_level': 0.95}
);

-- View first week of production forecast
SELECT
    forecast_date,
    ROUND(point_forecast, 2) as forecast,
    ROUND(lower_95, 2) as lower,
    ROUND(upper_95, 2) as upper
FROM forecast_production
WHERE forecast_step <= 7
ORDER BY forecast_step;

---

Step 3: Set Up Monitoring

Create Monitoring Table

-- Track actual vs. forecast over time
CREATE TABLE forecast_monitoring AS
SELECT
    p.forecast_date,
    p.point_forecast,
    p.lower_95,
    p.upper_95,
    a.actual_sales,
    a.actual_sales - p.point_forecast as error,
    CASE
        WHEN a.actual_sales BETWEEN p.lower_95 AND p.upper_95
        THEN 'Within interval'
        ELSE 'Outside interval'
    END as interval_check,
    CURRENT_DATE() as evaluation_date
FROM forecast_production p
LEFT JOIN actual_sales a ON p.forecast_date = a.date
WHERE p.forecast_date <= CURRENT_DATE();

Weekly Performance Report

-- Generate weekly monitoring metrics
CREATE TABLE weekly_performance AS
SELECT
    DATE_TRUNC('week', forecast_date) as week,
    COUNT(*) as num_forecasts,
    ROUND(AVG(ABS(error)), 2) as avg_abs_error,
    ROUND(TS_MAE(LIST(actual_sales), LIST(point_forecast)), 2) as weekly_MAE,
    ROUND(TS_RMSE(LIST(actual_sales), LIST(point_forecast)), 2) as weekly_RMSE,
    ROUND(TS_MAPE(LIST(actual_sales), LIST(point_forecast)), 2) as weekly_MAPE_pct,
    ROUND(100.0 * SUM(CASE WHEN interval_check = 'Within interval' THEN 1 ELSE 0 END) / COUNT(*), 1) as coverage_pct
FROM forecast_monitoring
WHERE forecast_date >= CURRENT_DATE() - INTERVAL '4 weeks'
GROUP BY DATE_TRUNC('week', forecast_date)
ORDER BY week DESC;

-- View latest week
SELECT * FROM weekly_performance LIMIT 1;

---

Step 4: Set Up Automated Alerts

Performance Degradation Alert

-- Alert if accuracy worsens
CREATE TABLE performance_alerts AS
SELECT
    DATE_TRUNC('week', forecast_date) as week,
    ROUND(TS_MAPE(LIST(actual_sales), LIST(point_forecast)), 2) as current_mape,
    LAG(ROUND(TS_MAPE(LIST(actual_sales), LIST(point_forecast)), 2))
        OVER (ORDER BY DATE_TRUNC('week', forecast_date)) as previous_mape,
    CASE
        WHEN ROUND(TS_MAPE(LIST(actual_sales), LIST(point_forecast)), 2) >
             LAG(ROUND(TS_MAPE(LIST(actual_sales), LIST(point_forecast)), 2))
                OVER (ORDER BY DATE_TRUNC('week', forecast_date)) + 2.0
        THEN 'ALERT: MAPE increased by >2%'
        ELSE 'OK'
    END as alert_status
FROM forecast_monitoring
GROUP BY DATE_TRUNC('week', forecast_date)
HAVING TS_MAPE(LIST(actual_sales), LIST(point_forecast)) > 15.0  -- Threshold
ORDER BY week DESC
LIMIT 10;

Data Quality Alert

-- Alert if data quality drops
SELECT
    CURRENT_DATE() as check_date,
    CASE
        WHEN COUNT(CASE WHEN actual_sales IS NULL THEN 1 END) > 5 THEN 'ALERT: Missing data'
        WHEN COUNT(CASE WHEN actual_sales < 0 THEN 1 END) > 0 THEN 'ALERT: Negative values'
        WHEN MAX(actual_sales) > 2 * AVG(actual_sales) THEN 'WARNING: Outlier detected'
        ELSE 'OK'
    END as data_quality_status
FROM actual_sales
WHERE date >= CURRENT_DATE() - INTERVAL '7 days';

---

Step 5: Schedule Retraining

Retrain model regularly to capture new patterns:

-- Daily: Update monitoring
-- Weekly: Review metrics, alert if degraded
-- Monthly: Retrain if needed

-- Retraining logic
PROCEDURE retrain_forecast_model() AS
BEGIN
    -- 1. Get latest 12 months of data
    CREATE TEMP TABLE training_data AS
    SELECT * FROM historical_sales
    WHERE date >= CURRENT_DATE() - INTERVAL '12 months';

    -- 2. Check data quality
    SELECT COUNT(*) as null_count FROM training_data WHERE sales IS NULL;
    -- If > 5% nulls, abort with alert

    -- 3. Retrain model
    CREATE TABLE forecast_production_new AS
    SELECT * FROM TS_FORECAST(
        'training_data',
        'date',
        'sales',
        'AutoETS',
        90,
        {'seasonal_period': 7}
    );

    -- 4. Validate new model
    SELECT TS_MAPE(...) as new_mape;
    -- If accuracy < previous model, use old model

    -- 5. Switch forecasts
    DROP TABLE IF EXISTS forecast_production_backup;
    ALTER TABLE forecast_production RENAME TO forecast_production_backup;
    ALTER TABLE forecast_production_new RENAME TO forecast_production;

    -- 6. Log retraining event
    INSERT INTO retraining_log
    VALUES (CURRENT_TIMESTAMP(), 'AutoETS', new_mape, 'SUCCESS');
END;

---

Step 6: Production Integration

Export Forecasts

-- Export to CSV for BI systems
COPY (
    SELECT
        forecast_date,
        ROUND(point_forecast, 2) as forecast,
        ROUND(lower_95, 2) as lower_bound,
        ROUND(upper_95, 2) as upper_bound
    FROM forecast_production
    WHERE forecast_step <= 30
    ORDER BY forecast_date
) TO 'forecast_export.csv' WITH (FORMAT csv, HEADER true);

-- Or export to JSON
COPY (
    SELECT
        ROW_TO_JSON(t)
    FROM (
        SELECT
            forecast_date,
            point_forecast,
            lower_95,
            upper_95
        FROM forecast_production
        WHERE forecast_step <= 30
        ORDER BY forecast_date
    ) t
) TO 'forecast_export.json';

API Integration

-- Create VIEW for API access
CREATE VIEW forecast_api AS
SELECT
    forecast_date::DATE as date,
    ROUND(point_forecast, 2) as forecast,
    ROUND(lower_95, 2) as lower_95pct,
    ROUND(upper_95, 2) as upper_95pct,
    'AutoETS' as model,
    forecast_generated_at::TIMESTAMP AS generated_at
FROM forecast_production
WHERE forecast_step <= 90
  AND forecast_date >= CURRENT_DATE()
ORDER BY forecast_date;

-- Query via API:
-- GET /api/forecasts?product_id=SKU123&days=30
-- Returns JSON with forecast data

---

Step 7: Monitoring Dashboard

Key Metrics to Track

-- 1. Forecast Accuracy Over Time
CREATE VIEW dashboard_accuracy AS
SELECT
    DATE_TRUNC('week', forecast_date) as week,
    ROUND(TS_MAE(LIST(actual_sales), LIST(point_forecast)), 1) as MAE,
    ROUND(TS_MAPE(LIST(actual_sales), LIST(point_forecast)), 1) as MAPE_pct,
    COUNT(*) as num_forecasts
FROM forecast_monitoring
WHERE forecast_date >= CURRENT_DATE() - INTERVAL '12 weeks'
GROUP BY DATE_TRUNC('week', forecast_date)
ORDER BY week DESC;

-- 2. Prediction Interval Coverage
CREATE VIEW dashboard_coverage AS
SELECT
    DATE_TRUNC('week', forecast_date) as week,
    ROUND(100.0 * SUM(CASE WHEN interval_check = 'Within interval' THEN 1 ELSE 0 END) / COUNT(*), 1) as coverage_pct
FROM forecast_monitoring
WHERE forecast_date >= CURRENT_DATE() - INTERVAL '12 weeks'
GROUP BY DATE_TRUNC('week', forecast_date)
ORDER BY week DESC;

-- 3. Forecast Bias
CREATE VIEW dashboard_bias AS
SELECT
    DATE_TRUNC('week', forecast_date) as week,
    ROUND(TS_ME(LIST(actual_sales), LIST(point_forecast)), 1) as mean_error
FROM forecast_monitoring
WHERE forecast_date >= CURRENT_DATE() - INTERVAL '12 weeks'
GROUP BY DATE_TRUNC('week', forecast_date)
ORDER BY week DESC;

---

Step 8: Handling Model Failure

When to Retrain

Trigger retraining if:

• MAPE increases >5% from baseline
• Coverage drops below 90%
• Data pattern changes (detect with changepoint detection)
• Manual override by analyst

-- Detect when retraining needed
CREATE VIEW retrain_trigger AS
SELECT
    CASE
        WHEN TS_MAPE(...) > 0.15 AND TS_MAPE(...) > baseline_mape + 0.05
        THEN 'RETRAIN: MAPE degraded'

        WHEN (SELECT coverage FROM latest_week) < 0.90
        THEN 'RETRAIN: Coverage too low'

        WHEN (SELECT is_changepoint FROM latest_changepoint_detection)
        THEN 'RETRAIN: Structural change detected'

        ELSE 'OK: No retraining needed'
    END as action;

---

Step 9: Scaling Considerations

Forecasting Millions of Series

-- Parallel forecasting by product group
CREATE TABLE multi_product_forecasts AS
SELECT
    product_id,
    DATE_TRUNC('day', date) as forecast_date,
    point_forecast
FROM TS_FORECAST_BY(
    'sales_by_product',
    'product_id',
    'date',
    'sales',
    'AutoETS',
    30,
    {'seasonal_period': 7}
);

-- Performance tips:
-- 1. Use GROUP BY for parallelization
-- 2. Pre-aggregate if needed (weekly instead of daily)
-- 3. Use faster models (Theta, SeasonalNaive) for millions of series

Memory Optimization

-- For large datasets, use batching
PROCEDURE batch_forecast(batch_size INT) AS
BEGIN
    FOR product_id IN (
        SELECT DISTINCT product_id FROM sales_data
        ORDER BY product_id
        LIMIT batch_size
    ) LOOP
        EXECUTE 'SELECT * FROM TS_FORECAST_BY(...) WHERE product_id = ' || product_id;
        -- Process batch
    END LOOP;
END;

---

Step 10: Documentation & Handoff

Model Card

Create documentation for your deployed model:

# AutoETS Sales Forecast Model

**Model**: AutoETS with seasonal_period=7
**Trained**: 2024-01-15
**Test MAPE**: 6.8%
**Production MAPE**: 7.2%

## Performance
- MAE: 8.3 units
- RMSE: 11.2 units
- Coverage (95%): 96%

## Data Requirements
- Minimum: 60 historical observations
- Frequency: Daily
- Quality: No nulls, deduplicated

## Retraining Schedule
- Frequency: Monthly
- Trigger: MAPE > 12%
- Last Retrain: 2024-01-15

## Known Limitations
- Assumes historical patterns continue
- May underforecast during unexpected demand spikes
- Requires stable data quality

## Support
Contact: forecast-team@company.com

---

Deployment Timeline

Week 1: Development & Testing
    ├─ Build model
    ├─ Evaluate on test data
    └─ Final validation

Week 2: Staging
    ├─ Deploy to staging environment
    ├─ Integration testing
    └─ Performance validation

Week 3: Production - Phase 1
    ├─ Deploy to 10% of users
    ├─ Monitor closely
    └─ Check for issues

Week 4: Production - Phase 2
    ├─ Deploy to 50% of users
    ├─ Weekly monitoring
    └─ Gather feedback

Week 5: Production - Full
    ├─ Deploy to 100% of users
    ├─ Ongoing monitoring
    └─ Schedule retraining

Week 6+: Operations
    ├─ Weekly monitoring
    ├─ Monthly retraining
    └─ Quarterly reviews

---

Production Monitoring Script

Template for automated monitoring:

-- Run this daily/weekly
SELECT
    'Forecast Age' as metric,
    ROUND((CURRENT_TIMESTAMP() - MAX(forecast_generated_at))::INTERVAL HOUR) as hours_old
FROM forecast_production
UNION ALL
SELECT
    'Latest Forecast Date',
    MAX(forecast_date)::VARCHAR
FROM forecast_production
UNION ALL
SELECT
    'Last 7-Day MAE',
    ROUND(TS_MAE(...), 2)::VARCHAR
FROM forecast_monitoring
WHERE forecast_date >= CURRENT_DATE() - INTERVAL '7 days'
UNION ALL
SELECT
    'Last 7-Day Coverage',
    ROUND(100.0 * SUM(CASE WHEN interval_check = 'Within interval' THEN 1 ELSE 0 END) / COUNT(*), 1)::VARCHAR || '%'
FROM forecast_monitoring
WHERE forecast_date >= CURRENT_DATE() - INTERVAL '7 days'
UNION ALL
SELECT
    'Data Quality Check',
    CASE WHEN COUNT(CASE WHEN actual_sales IS NULL THEN 1 END) = 0 THEN 'OK' ELSE 'ALERT' END
FROM actual_sales
WHERE date = CURRENT_DATE() - INTERVAL '1 day';

---

Common Production Issues

Issue: Forecast Doesn't Update

-- Check if retraining is running
SELECT * FROM retraining_log ORDER BY retrain_time DESC LIMIT 5;

-- Manual retrain
CALL retrain_forecast_model();

Issue: Accuracy Degrades Suddenly

-- Check for data anomalies
SELECT TS_DETECT_CHANGEPOINTS(...);
SELECT TS_DETECT_SEASONALITY(...);

-- Retrain with recent data only
SELECT * FROM TS_FORECAST('last_6_months_data', ...);

Issue: Forecast Values Unrealistic

-- Check prediction intervals
SELECT
    ROUND(MIN(lower_95), 2) as min_lower,
    ROUND(MAX(upper_95), 2) as max_upper
FROM forecast_production;

-- Retrain with clean data
CALL clean_and_retrain();

---

Next Steps

1. Basic Workflow — Review complete workflow
2. Model Comparison — Choose best model
3. Monitoring Dashboard — Set up monitoring

---

Key Takeaways

• ✅ Validate model before deployment
• ✅ Monitor accuracy weekly
• ✅ Retrain monthly or when metrics degrade
• ✅ Set up automated alerts
• ✅ Document model and setup
• ✅ Use phased rollout (10% → 50% → 100%)
• ✅ Maintain rollback plan
• ✅ Track metrics over time