Cost Optimization at Scale Questions

# requirements: pandas, prophet, sklearn, lightgbm, joblib
import pandas as pd
from prophet import Prophet
from sklearn.ensemble import IsolationForest
# load historical: df with columns ['date','spend','traffic','deploys','dow']

# prepare
df['ds'] = pd.to_datetime(df['date'])
df['y'] = df['spend']
exog = df[['traffic','deploys','dow']]

# modeling with Prophet (handles seasonality, intervals) + regressors
m = Prophet(daily_seasonality=True, weekly_seasonality=True)
for col in ['traffic','deploys','dow']:
    m.add_regressor(col)
m.fit(pd.concat([df[['ds','y']], exog], axis=1))

# make future frame for 30 days with predicted exog (e.g., expected traffic/deploy schedule)
future = m.make_future_dataframe(periods=30)
future = future.merge(predicted_exog_df, on='ds')  # supply exog forecasts
fcst = m.predict(future)

# extract point, upper/lower
forecast_30 = fcst[['ds','yhat','yhat_lower','yhat_upper']]

# anomaly detection: residual z-score + IsolationForest on recent residuals
recent = df.merge(forecast_30[['ds','yhat']], on='ds', how='inner')
recent['resid'] = recent['y'] - recent['yhat']
# z-score rule
recent['z'] = (recent['resid'] - recent['resid'].mean())/recent['resid'].std()
# ML detector
iso = IsolationForest(contamination=0.01).fit(recent[['resid','traffic','deploys']])
recent['iso_anom'] = iso.predict(recent[['resid','traffic','deploys']])
anomalies = recent[(abs(recent['z'])>3) | (recent['iso_anom']==-1)]

# persist model and schedule daily run; alert if anomalies or observed > yhat_upper

SELECT service, account, resource_id, SUM(cost) AS total_cost
FROM billing_table
WHERE invoice_month = '2026-02'
GROUP BY service, account, resource_id
ORDER BY total_cost DESC
LIMIT 50;

SELECT tag_env, service, SUM(cost) FROM billing_table
GROUP BY tag_env, service ORDER BY SUM(cost) DESC;