# Superset AWS Dev Account Cost Trend Dashboard

Updated: 2026-03-06
Status: Active

This guide defines the AWS dev account cost trend dashboard design and build procedure based on `mart_cost_summary`.

## 1. Goal And Scope

Goal:
- Track monthly cost trends across all AWS Development accounts.
- Provide Account → Region → Service drill-down for cost visibility.
- Give Cloud Team actionable visibility into dev/test spend patterns.
- Automatically adapt when accounts are reclassified in `account_mapping.csv`.

Target accounts:
- All AWS accounts where `account_purpose = 'Development'` (currently 25 accounts).

In scope:
- One dashboard: `AWS Dev Account Cost Trend` (`slug: aws-dev-account-cost-trend`)
- 6 charts (account trend + table, region trend + table, service trend + table)
- Native filters with cascade: Account → Region, Account → Service
- Bootstrap script for dataset/dashboard/chart creation

Out of scope:
- New dbt model/schema changes
- Non-development accounts
- Automated dashboard layout authoring

## 2. Data Semantics

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

Why `mart_cost_summary`:
- Has all needed dimensions (account, region, service, service_category).
- No tag columns needed for this dashboard.
- Faster than `mart_cost_monthly` for non-tag use cases.
- Filters by `account_purpose` column — automatically adapts when seed changes.

Primary dimensions:
- Time: `billing_period_month`
- Account: `account_id`, `account_display_name`, `account_label` (derived)
- Owner: `account_owner`
- Resource: `region`, `service`, `service_category`

Primary metrics (from mart):
- `unblended_cost_usd` — matches AWS Cost Explorer default view

Account label derivation:
- `CONCAT(account_id, ' | ', account_display_name)` — shows both ID and name in chart legends and filters (matches prod region pattern).

## 3. Virtual Dataset

Create one Superset virtual dataset from SQL Lab, and use it for all dashboard charts.

Recommended dataset name:
- `vds_aws_dev_account_cost_trend`

SQL template (BigQuery):

```sql
SELECT
  billing_period_month,
  account_id,
  account_display_name,
  account_label,
  account_owner,
  region,
  service,
  service_category,
  cost_type,
  cost_usd
FROM (
  SELECT
    m.billing_period_month,
    m.account_id,
    m.account_display_name,
    CONCAT(m.account_id, ' | ', m.account_display_name) AS account_label,
    m.account_owner,
    m.region,
    m.service,
    m.service_category,
    m.usage_cost_usd,
    m.sp_covered_usage_cost_usd
  FROM `__BQ_PROJECT__.__BQ_SCHEMA__.mart_cost_summary` AS m
  WHERE m.cloud_provider = 'aws'
    AND m.account_purpose = 'Development'
)
UNPIVOT(cost_usd FOR cost_type IN (
  usage_cost_usd AS 'Usage',
  sp_covered_usage_cost_usd AS 'SP Covered Usage'
))
```

Important:
- Filters by `account_purpose = 'Development'` — no hard-coded account IDs.
- `account_label` combines display name and ID for chart legends.
- UNPIVOT splits `usage_cost_usd` and `sp_covered_usage_cost_usd` into `cost_type` dimension + `cost_usd` metric.
- Default (both selected): total = Usage + SP Covered Usage. Filter to one type to isolate.

## 4. Native Filter Design

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

1. `billing_period_month` (Time range, default: last 6 complete months)
2. `account_label` (multi-select, no cascade)
3. `region` (cascade from Account)
4. `service` (cascade from Account)
5. `cost_type` (multi-select: Usage / SP Covered Usage, default: all)

Filter behavior rules:
- Keep all filters optional (non-required) by default.
- Default time range should be last 6 complete months (exclude current month).
- Region cascades from Account; Service cascades from Account.

## 5. Chart Specifications (6 Charts)

Use dataset: `vds_aws_dev_account_cost_trend`.

### 5.1 Dev Account Cost Trend

- Name: `Dev Account Cost Trend`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month` (P1M)
- Group by: `account_label`
- Metric: `SUM(cost_usd)`
- Row limit: 10000
- Purpose: monthly cost trend stacked by dev account (name + id in legend)

### 5.2 Dev Account Cost Table

- Name: `Dev Account Cost Table`
- Viz: `table`
- Group by: `billing_period_month`, `account_id`, `account_display_name`, `account_owner`
- Metric: `SUM(cost_usd)`
- Order: descending by cost
- Row limit: 500
- Purpose: monthly cost per account with owner for accountability

### 5.3 Dev Region Cost Trend

- Name: `Dev Region Cost Trend`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month` (P1M)
- Group by: `region`
- Metric: `SUM(cost_usd)`
- Row limit: 10000
- Purpose: monthly cost trend stacked by region

### 5.4 Dev Top Regions Table

- Name: `Dev Top Regions Table`
- Viz: `table`
- Group by: `billing_period_month`, `region`
- Metric: `SUM(cost_usd)`
- Order: descending by cost
- Row limit: 500
- Purpose: region cost ranking for identifying expensive regions

### 5.5 Dev Service Cost Trend Top 15

- Name: `Dev Service Cost Trend Top 15`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month` (P1M)
- Group by: `service`
- Metric: `SUM(cost_usd)`
- Row limit: 15
- Purpose: top 15 services by cost, monthly trend

### 5.6 Dev Top Services Table

- Name: `Dev Top Services Table`
- Viz: `table`
- Group by: `billing_period_month`, `service`, `service_category`
- Metric: `SUM(cost_usd)`
- Order: descending by cost
- Row limit: 500
- Purpose: service cost ranking with category for context

## 6. Dashboard Metadata

- Title: `AWS Dev Account Cost Trend`
- Slug: `aws-dev-account-cost-trend`
- Label colors: Use default Superset color scheme (25 accounts benefit from automatic palette assignment rather than manual color mapping)

## 7. Layout (Manual in UI)

| Row | Left | Right | Width |
|-----|------|-------|-------|
| 1 | Dev Account Cost Trend | Dev Account Cost Table | 50/50 |
| 2 | Dev Region Cost Trend | Dev Top Regions Table | 50/50 |
| 3 | Dev Service Cost Trend Top 15 | Dev Top Services Table | 50/50 |

## 8. Manual Build Procedure

1. Confirm data access in SQL Lab:
   - `SELECT COUNT(*) FROM \`gcp-rd-research-dev.finops_cloud_billing.mart_cost_summary\` WHERE cloud_provider = 'aws' AND account_purpose = 'Development'`
2. Create virtual dataset `vds_aws_dev_account_cost_trend` from SQL in Section 3.
3. Create dashboard:
   - Title: `AWS Dev Account Cost Trend`
   - Slug: `aws-dev-account-cost-trend`
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(DATETRUNC(DATETIME("today"), month), -6, month) : DATETRUNC(DATETIME("today"), month)`
8. Arrange layout per Section 7.
9. Save and publish dashboard.

## 8.1 Bootstrap Script (Local)

You can bootstrap dataset/dashboard/charts locally with:

```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_aws_dev_account_cost_trend.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_summary` and the virtual dataset.
   - All 25 development accounts appear with correct account labels.

2. Account filtering:
   - Only `account_purpose = 'Development'` accounts appear.
   - Adding/removing dev accounts in `account_mapping.csv` automatically reflects in dashboard.

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

4. Chart correctness:
   - Trend charts show stacked bars by account/region/service.
   - Tables show cost with proper grouping and descending sort.

5. Business usability:
   - Dashboard can answer:
     - What is the monthly cost trend across all dev accounts?
     - Which dev accounts are the most expensive?
     - Who owns the most expensive dev accounts?
     - What regions are dev accounts deployed in?
     - What services drive the most cost in dev accounts?

## 10. Known Constraints

1. Month-level only; no day-level trend.
2. No tag columns (tenant, service_type) — use tenant cost trend dashboard for tag analysis.
3. Account list is driven by `account_purpose = 'Development'` in seed. Changes require `dbt seed --full-refresh`.
4. Layout must be arranged manually in Superset UI.
5. With 25 accounts, chart legends may be dense — use filters to narrow scope.