Start with a structured diagnostic checklist, then propose concrete checks and visualizations, and finish with a retrain vs rollback decision process plus mitigation and communication.Diagnostic checklist (priority order)1. Data ingestion & pipeline health- Check recent job failures, late/duplicate batches, schema changes, or source API errors.- SQL: find missing partitions or delayed timestamps:sql
SELECT date_trunc('hour', event_time) AS hr, COUNT(*) AS rows
FROM raw_trips
WHERE city = 'X'
GROUP BY hr
ORDER BY hr DESC
LIMIT 48;
2. Label generation correctness- Verify labels computed last week (trip_end - trip_start) aren’t biased by timezone shifts, daylight savings, or truncated logs.- SQL: compare label distribution week-over-week:sql
SELECT week, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration) AS median_dur
FROM (
SELECT DATE_TRUNC('week', trip_end) AS week, EXTRACT(EPOCH FROM (trip_end - trip_start))/60 AS duration
FROM processed_labels WHERE city='X'
) t GROUP BY week ORDER BY week;
3. Feature distribution drift- Check numeric and categorical drift (means, quantiles, new categories).- SQL: feature histograms or summaries by week:sql
SELECT week, AVG(speed), PERCENTILE_CONT(0.9) WITHIN GROUP(ORDER BY speed) AS p90
FROM features WHERE city='X' GROUP BY week;
Visualizations: weekly KDEs, boxplots, population stability index (PSI) per feature, and UMAP/t-SNE embedding of feature vectors to spot clusters.4. Serving vs training skew- Ensure online feature values match offline training snapshots (freshness, aggregation windows).- SQL: compare feature computation windows:sql
SELECT feature_name,
AVG(CASE WHEN source='train' THEN val END) AS train_mean,
AVG(CASE WHEN source='serve' THEN val END) AS serve_mean
FROM feature_samples
WHERE city='X' GROUP BY feature_name;
5. Labels leakage/regression in ground truth- Check for changes in logging (e.g., new delays causing finish events to be recorded differently).6. External/contextual causes- Event calendar, roadworks, policy changes, new traffic sensors — correlate error spike with external signals.Localizing the issue- Compute error by slice: geography (neighborhood), hour-of-day, vehicle type, driver, route length.- Visualization: heatmap of MAE by neighborhood-week and hourly MAE trend lines.Retrain vs rollback decision process1. Root-cause found and fixable in data (e.g., ingestion bug, label calc bug): rollback model and fix pipeline before retrain. Rationale: model is fine but fed bad data.2. No pipeline bug; features drifted but retraining with recent data corrects behavior: prefer retrain with updated feature engineering, include drifted period as separate segment or adversarial validation. Evaluate offline: backtest (walk-forward) on last 4 weeks; require metrics threshold improvement and stability.3. If uncertain or risk of downstream harm high: rollback to last known-good model while investigating.Risk mitigation steps- Canary/gradual rollout or shadow serving while retraining.- Add feature gates and alerting for PSI and label-change thresholds.- Add quick-fix rules (bias correction factor by neighborhood/time) if safe.Communication plan- Immediate: notify Ops and PMs with incident summary — scope (city X), impact (30% MAE increase), initial hypothesis, mitigation (rollback/canary), ETA for next update.- Follow-up: daily status with findings, simulated impact of fixes, expected timeline.- Postmortem: root cause, actions taken, metrics before/after, changes to monitoring, timeline to prevent recurrence.Example quick checks to run immediately: hourly MAE by zip, count of processed trips vs expected, PSI for top 10 features, and label distribution check. Prioritize rollback if ingestion/label bug confirmed; otherwise run guarded retrain with A/B canary and monitor business KPIs.