# Superset Production Cost Anomaly Monitor Dashboard

Updated: 2026-03-16
Status: Active

This guide defines the production cost anomaly monitoring dashboard design and build procedure based on `mart_cost_daily`.

## 1. Goal And Scope

Goal:
- Replace Feishu IM alert fatigue with a daily "morning review" dashboard.
- Answer "what needs my attention today?" within 30 seconds of opening.
- Detect WoW/DoD cost spikes, new services, and new regions in production accounts.

In scope:
- One dashboard: `Prod Cost Anomaly Monitor` (`slug: prod-cost-anomaly`)
- 6 charts (anomaly table, cost trends, WoW overview, service spikes, service trend, new services/regions)
- Native filters with cascade: Time → Account → Service → Anomaly Type
- Bootstrap script for dataset/dashboard/chart creation

Out of scope:
- Development accounts (separate dashboard: `superset-dev-cost-anomaly.md`)
- Superset Alerts configuration (manual, post-MVP)
- Fixed vs variable cost classification (future iteration)
- Resource-level anomaly detection (requires CMDB data)

## 2. Data Semantics

Source table:
- `gcp-rd-research-dev.finops_cloud_billing.mart_cost_daily`

Why `mart_cost_daily`:
- Daily grain enables window functions (LAG, moving averages) for anomaly detection.
- Materialized as incremental table with 14-day lookback for fast queries.
- 10-dimension grain includes account_purpose for prod/dev filtering.

Primary dimensions:
- Time: `usage_date`
- Account: `account_id`, `account_display_name`, `account_label` (computed)
- Classification: `account_purpose`, `account_owner`
- Resource: `region`, `service`, `service_category`

Primary metrics:
- `daily_cost_usd` — SUM(unblended_cost_usd) aggregated in VDS

Anomaly detection (computed in VDS SQL):
- `dod_change_pct` — Day-over-day % change
- `wow_change_pct` — Week-over-week % change (same weekday)
- `moving_avg_7d_usd` — 7-day trailing moving average
- `avg_deviation_pct` — % deviation from 7-day moving average
- `anomaly_type` — Classification: wow_spike / dod_spike / new_service / new_region / normal

Production anomaly thresholds:
- **WoW spike**: >30% increase AND >$500 absolute increase
- **DoD spike**: >50% increase AND >$200 absolute increase
- **New service/region**: appeared this week but not in prior 4 weeks, cost > $1

account_purpose filter:
- `IN ('Production', 'ProductionInfrastructure')`

## 3. Virtual Dataset

Create one Superset virtual dataset from SQL Lab.

Recommended dataset name:
- `vds_prod_cost_anomaly`

SQL template (BigQuery):

```sql
WITH daily_agg AS (
  SELECT
    usage_date,
    account_id,
    account_display_name,
    CONCAT(account_id, ' | ', account_display_name) AS account_label,
    account_owner,
    region,
    service,
    SUM(unblended_cost_usd) AS daily_cost_usd
  FROM `__BQ_PROJECT__.__BQ_SCHEMA__.mart_cost_daily`
  WHERE cloud_provider = 'aws'
    AND account_purpose IN ('Production', 'ProductionInfrastructure')
  GROUP BY 1,2,3,4,5,6,7
),

with_window AS (
  SELECT
    *,
    -- Day-over-day
    LAG(daily_cost_usd, 1) OVER (
      PARTITION BY account_id, service, region ORDER BY usage_date
    ) AS prev_day_cost_usd,

    -- Week-over-week (same weekday)
    LAG(daily_cost_usd, 7) OVER (
      PARTITION BY account_id, service, region ORDER BY usage_date
    ) AS prev_week_cost_usd,

    -- 7-day moving average (excluding current day)
    AVG(daily_cost_usd) OVER (
      PARTITION BY account_id, service, region
      ORDER BY usage_date
      ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
    ) AS moving_avg_7d_usd

  FROM daily_agg
),

-- New services: appeared this week but NOT in prior 4 weeks
service_history AS (
  SELECT DISTINCT account_id, service
  FROM daily_agg
  WHERE usage_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY)
                       AND DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
),

-- New regions: same logic
region_history AS (
  SELECT DISTINCT account_id, region
  FROM daily_agg
  WHERE usage_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY)
                       AND DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
)

SELECT
  w.usage_date,
  w.account_id,
  w.account_display_name,
  w.account_label,
  w.account_owner,
  w.region,
  w.service,
  w.daily_cost_usd,
  w.prev_day_cost_usd,
  w.prev_week_cost_usd,
  w.moving_avg_7d_usd,

  -- DoD change
  ROUND(SAFE_DIVIDE(w.daily_cost_usd - w.prev_day_cost_usd, w.prev_day_cost_usd) * 100, 1) AS dod_change_pct,

  -- WoW change
  ROUND(SAFE_DIVIDE(w.daily_cost_usd - w.prev_week_cost_usd, w.prev_week_cost_usd) * 100, 1) AS wow_change_pct,

  -- Deviation from 7d moving average
  ROUND(SAFE_DIVIDE(w.daily_cost_usd - w.moving_avg_7d_usd, w.moving_avg_7d_usd) * 100, 1) AS avg_deviation_pct,

  -- Anomaly flags (Production thresholds)
  CASE
    WHEN SAFE_DIVIDE(w.daily_cost_usd - w.prev_week_cost_usd, w.prev_week_cost_usd) > 0.30
         AND (w.daily_cost_usd - w.prev_week_cost_usd) > 500
    THEN TRUE ELSE FALSE
  END AS is_wow_anomaly,

  CASE
    WHEN SAFE_DIVIDE(w.daily_cost_usd - w.prev_day_cost_usd, w.prev_day_cost_usd) > 0.50
         AND (w.daily_cost_usd - w.prev_day_cost_usd) > 200
    THEN TRUE ELSE FALSE
  END AS is_dod_anomaly,

  -- New service/region flags
  CASE WHEN sh.account_id IS NULL AND w.daily_cost_usd > 1
       AND w.usage_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    THEN TRUE ELSE FALSE
  END AS is_new_service,

  CASE WHEN rh.account_id IS NULL AND w.daily_cost_usd > 1
       AND w.usage_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    THEN TRUE ELSE FALSE
  END AS is_new_region,

  -- Combined anomaly type (priority: wow > dod > new_service > new_region > normal)
  CASE
    WHEN SAFE_DIVIDE(w.daily_cost_usd - w.prev_week_cost_usd, w.prev_week_cost_usd) > 0.30
         AND (w.daily_cost_usd - w.prev_week_cost_usd) > 500 THEN 'wow_spike'
    WHEN SAFE_DIVIDE(w.daily_cost_usd - w.prev_day_cost_usd, w.prev_day_cost_usd) > 0.50
         AND (w.daily_cost_usd - w.prev_day_cost_usd) > 200 THEN 'dod_spike'
    WHEN sh.account_id IS NULL AND w.daily_cost_usd > 1
         AND w.usage_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) THEN 'new_service'
    WHEN rh.account_id IS NULL AND w.daily_cost_usd > 1
         AND w.usage_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) THEN 'new_region'
    ELSE 'normal'
  END AS anomaly_type

FROM with_window w
LEFT JOIN service_history sh ON w.account_id = sh.account_id AND w.service = sh.service
LEFT JOIN region_history rh ON w.account_id = rh.account_id AND w.region = rh.region
```

## 4. Native Filter Design

Apply these filters to all charts in the dashboard (4 filters):

1. `usage_date` (Time range, default: last 14 days)
2. `account_label` (Multi-select, searchable)
3. `service` (Multi-select, cascades from Account)
4. `anomaly_type` (Single-select: all / wow_spike / dod_spike / new_service / new_region)

Filter behavior rules:
- Keep all filters optional (non-required) by default.
- Default time range: last 14 days.
- Service cascades from Account.

## 5. Chart Specifications (6 Charts)

Use dataset: `vds_prod_cost_anomaly`.

### 5.1 Anomaly Summary Table

- Name: `Prod Anomaly Alerts`
- Viz: `table`
- Group by: `usage_date`, `account_label`, `service`, `region`
- Metrics: `SUM(daily_cost_usd)`, `AVG(wow_change_pct)`, `AVG(dod_change_pct)`
- Adhoc filter: `anomaly_type != 'normal'`
- Order: `SUM(daily_cost_usd)` DESC
- Row limit: 50
- Purpose: Answer "what needs my attention today?" — only shows anomalous rows

### 5.2 Daily Total Cost Trend

- Name: `Prod Daily Cost Trend`
- Viz: `echarts_area` (stacked)
- X: `usage_date` (P1D)
- Group by: `account_label`
- Metric: `SUM(daily_cost_usd)`
- Row limit: 10000
- Adhoc filter: `usage_date < DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)` (exclude incomplete billing days)
- Time: defers to dashboard filter (default 14 days)
- Purpose: Overall daily cost trend — shows total spend composition by all prod accounts

### 5.3 WoW Change by Account

- Name: `Prod WoW Change by Account`
- Viz: `table`
- Group by: `account_label`
- Metrics: `AVG(wow_change_pct)`, `SUM(daily_cost_usd)`, `AVG(moving_avg_7d_usd)`
- Time: Latest day
- Order: `AVG(wow_change_pct)` DESC
- Row limit: 50
- Purpose: Account-level WoW change overview

### 5.4 Top Service Cost Spikes

- Name: `Prod Top Service Spikes`
- Viz: `table`
- Group by: `usage_date`, `account_label`, `service`
- Metrics: `SUM(daily_cost_usd)`, `AVG(wow_change_pct)`, `AVG(dod_change_pct)`
- Adhoc filter: `is_wow_anomaly = TRUE` OR `is_dod_anomaly = TRUE`
- Order: `SUM(daily_cost_usd)` DESC
- Row limit: 100
- Time: Last 7 days
- Purpose: Service-level anomaly detail for drill-down

### 5.5 Daily Cost by Service Trend

- Name: `Prod Daily Cost by Service Top 10`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `usage_date` (P1D)
- Group by: `service`
- Metric: `SUM(daily_cost_usd)`
- Row limit: 10
- Time: Last 30 days
- Purpose: Service-level daily pattern for identifying which services drive spikes

### 5.6 New Services / New Regions Alert

- Name: `Prod New Services & Regions`
- Viz: `table`
- Group by: `usage_date`, `account_label`, `service`, `region`
- Metric: `SUM(daily_cost_usd)`
- Adhoc filter: `is_new_service = TRUE` OR `is_new_region = TRUE`
- Order: `usage_date` DESC, `SUM(daily_cost_usd)` DESC
- Row limit: 100
- Time: Last 7 days
- Purpose: Detect new services or regions appearing in production accounts

## 6. Dashboard Metadata

- Title: `Prod Cost Anomaly Monitor`
- Slug: `prod-cost-anomaly`
- Label colors:
  - wow_spike: `#E04355` (red)
  - dod_spike: `#FF8C00` (orange)
  - new_service: `#1FA8C9` (teal)
  - new_region: `#454E7C` (dark blue)
  - normal: `#999999` (gray)

## 7. Layout (Manual in UI)

| Row | Charts | Width |
|-----|--------|-------|
| 1 | Prod Anomaly Alerts | 100% |
| 2 | Prod Daily Cost Trend | 100% |
| 3 | Prod WoW Change by Account / Prod Top Service Spikes | 50/50 |
| 4 | Prod Daily Cost by Service Top 10 | 100% |
| 5 | Prod New Services & Regions | 100% |

## 8. Manual Build Procedure

1. Confirm data access in SQL Lab:
   - `SELECT COUNT(*) FROM \`gcp-rd-research-dev.finops_cloud_billing.mart_cost_daily\` WHERE account_purpose IN ('Production', 'ProductionInfrastructure')`
2. Create virtual dataset `vds_prod_cost_anomaly` from SQL in Section 3.
3. Create dashboard:
   - Title: `Prod Cost Anomaly Monitor`
   - Slug: `prod-cost-anomaly`
4. Create 6 charts from Section 5.
5. Add native filters from Section 4.
6. Configure filter scope: Apply all filters to all 6 charts.
7. Set default filter values:
   - `Time Range = DATEADD(DATETIME("today"), -14, day) : DATETIME("today")`
8. Set label colors per Section 6.
9. Arrange layout per Section 7.
10. Save and publish dashboard.

## 8.1 Bootstrap Script (Local)

```bash
export SUPERSET_URL="http://127.0.0.1:8088"
export SUPERSET_USER="admin"
export SUPERSET_PASS="admin"
export DB_NAME_IN_SUPERSET="FinOPSGoogleBigQuery"
export BQ_PROJECT="gcp-rd-research-dev"
export BQ_SCHEMA="finops_cloud_billing"
bash dbt/scripts/create_superset_prod_cost_anomaly.sh
```

Notes:
- Script is idempotent (safe to rerun).
- Dashboard layout still needs manual arrangement in UI.

## 9. Acceptance Checklist

1. Data connectivity:
   - SQL Lab can query `mart_cost_daily` and the virtual dataset.
   - Production and ProductionInfrastructure accounts appear with correct data.

2. Anomaly detection:
   - WoW spikes flagged at >30% AND >$500 threshold.
   - DoD spikes flagged at >50% AND >$200 threshold.
   - New services/regions detected (appeared this week, not in prior 4 weeks).

3. Filter linkage:
   - Time/account/service/anomaly_type filters update all charts.
   - Service cascades from Account.

4. Business usability:
   - Dashboard can answer within 30 seconds:
     - What cost anomalies occurred today/this week?
     - Which accounts have the biggest WoW increase?
     - Are there new services or regions in production?
     - What is the daily cost trend for each account/service?

## 10. Known Constraints

1. Anomaly detection uses simple percentage + absolute thresholds; no ML/statistical models.
2. WoW comparison handles weekday patterns naturally; DoD may show noise on Mon vs Sun.
3. First 7 days of data will have NULL WoW comparisons (insufficient history).
4. New service/region detection requires 35 days of history for baseline.
5. Layout must be arranged manually in Superset UI.
6. Thresholds are baked into VDS SQL; changing requires VDS update.