# Superset AWS Tag Compliance Dashboard

Updated: 2026-03-02
Status: Active

This guide defines the tag compliance dashboard design and build procedure based on `mart_cost_monthly`.

## 1. Goal And Scope

Goal:
- Track `tag_usedby` compliance across 4 production accounts.
- Classify cost into **Compliant**, **Non-Compliant**, and **Exempt** categories.
- Provide Account → Region → Service three-level drill-down for non-compliant cost.
- Give Cloud Team actionable visibility into tagging gaps while excluding account-level services that cannot be tagged.

Target accounts:
- `380838443567` — pingcap-dbaas
- `143458967504` — dbaas-prod-dataplane-000
- `730335266318` — dbaas-prod-dataplane-001
- `632330501419` — dbaas-prod-dataplane-nextgen

In scope:
- One dashboard: `AWS Tag Compliance` (`slug: aws-tag-compliance`)
- 7 charts (trend, distribution, detail table)
- Native filters with cascade: Account → Region → Service
- Bootstrap script for dataset/dashboard/chart creation
- Exempt service list via dbt seed `tag_exempt_services`

Out of scope:
- New dbt model/schema changes
- Tags other than `tag_usedby`
- Automated dashboard layout authoring

## 2. Data Semantics

Source tables:
- `gcp-rd-research-dev.finops_cloud_billing.mart_cost_monthly`
- `gcp-rd-research-dev.finops_cloud_billing.tag_exempt_services` (dbt seed)

Why `mart_cost_monthly` (not `mart_cost_summary`):
- `mart_cost_monthly` has 17 dimensions including `tag_usedby` (line 74).
- `mart_cost_summary` only has `tag_project`, not `tag_usedby`.

Primary dimensions:
- Time: `billing_period_month`
- Account: `account_id`, `account_display_name`
- Resource: `region`, `service`, `service_category`
- Tag compliance: `tag_compliance_status` (derived: Compliant / Non-Compliant / Exempt)

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

Service classification note:
- The `service` field uses `product_servicecode` (not `line_item_product_code`).
- This means EC2-generated Data Transfer/VPC costs appear under `AWSDataTransfer`/`AmazonVPC`
  rather than being lumped under `AmazonEC2`.
- Per-service totals will differ from Cost Explorer's "Service" dimension (which uses
  `line_item_product_code`), but the cross-service grand total is identical.
- This finer granularity is preferred for tag compliance because it more precisely
  identifies which service's resources need tagging remediation.

Tag compliance classification (3-way):
- **Compliant** — `tag_usedby` has a value AND service is NOT in `tag_exempt_services`
- **Non-Compliant** — `tag_usedby` is NULL/empty AND service is NOT in `tag_exempt_services`
- **Exempt** — service is in `tag_exempt_services` (regardless of tag value)

Derived metrics (computed in virtual dataset):
- `tag_compliance_status` = 3-way CASE (Exempt → Non-Compliant → Compliant)
- `exempt_cost_usd` = unblended_cost_usd where service is exempt, else 0
- `non_compliant_cost_usd` = unblended_cost_usd where not exempt AND tag_usedby is NULL/empty, else 0
- `compliant_cost_usd` = unblended_cost_usd where not exempt AND tag_usedby has value, else 0

Identity: `compliant_cost_usd + non_compliant_cost_usd + exempt_cost_usd = unblended_cost_usd`

## 3. Virtual Dataset

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

Recommended dataset name:
- `vds_aws_tag_compliance`

SQL template (BigQuery):

```sql
WITH src AS (
  SELECT
    m.billing_period_month,
    m.account_id,
    m.account_display_name,
    CONCAT(m.account_id, ' | ', m.account_display_name) AS account_label,
    m.region,
    m.service,
    m.service_category,
    CASE
      WHEN e.service IS NOT NULL THEN 'Exempt'
      WHEN m.tag_usedby IS NULL OR m.tag_usedby = '' THEN 'Non-Compliant'
      ELSE 'Compliant'
    END AS tag_compliance_status,
    m.unblended_cost_usd,
    CASE
      WHEN e.service IS NOT NULL THEN m.unblended_cost_usd
      ELSE 0
    END AS exempt_cost_usd,
    CASE
      WHEN e.service IS NULL AND (m.tag_usedby IS NULL OR m.tag_usedby = '') THEN m.unblended_cost_usd
      ELSE 0
    END AS non_compliant_cost_usd,
    CASE
      WHEN e.service IS NULL AND m.tag_usedby IS NOT NULL AND m.tag_usedby != '' THEN m.unblended_cost_usd
      ELSE 0
    END AS compliant_cost_usd
  FROM `gcp-rd-research-dev.finops_cloud_billing.mart_cost_monthly` AS m
  LEFT JOIN `gcp-rd-research-dev.finops_cloud_billing.tag_exempt_services` AS e
    ON m.service = e.service
  WHERE m.account_id IN ('380838443567','143458967504','730335266318','632330501419')
)
SELECT * FROM src
```

Important:
- Hard-coded filter on 4 production accounts. No other accounts appear in the dataset.
- `tag_compliance_status` is a 3-way classification derived from `tag_usedby` and `tag_exempt_services`.
- Exempt services are joined from the dbt seed `tag_exempt_services`.

## 4. Native Filter Design

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

1. `billing_period_month` (Time range, default: last 3 complete months)
2. `account_label` (Account — `CONCAT(account_id, ' | ', account_display_name)`)
3. `region` (cascade from Account)
4. `service` (cascade from Account + Region)
5. `service_category`
6. `tag_compliance_status` (Compliant / Non-Compliant / Exempt)

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

## 5. Chart Specifications (7 Charts)

Use dataset: `vds_aws_tag_compliance`.

### 5.1 Monthly Tag Compliance Trend

- Name: `Monthly Tag Compliance Trend`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month` (P1M)
- Group by: `tag_compliance_status`
- Metric: `SUM(unblended_cost_usd)`
- Purpose: monthly Compliant vs Non-Compliant vs Exempt total overview (3 stacked segments: teal/red/gray)

### 5.2 Non-Compliant Cost by Account

- Name: `Non-Compliant Cost by Account`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month`
- Group by: `account_display_name`
- Metric: `SUM(non_compliant_cost_usd)`
- Purpose: non-compliant cost trend by account (exempt cost excluded)

### 5.3 Non-Compliant % Trend by Account

- Name: `Non-Compliant % Trend by Account`
- Viz: `echarts_timeseries_line`
- X: `billing_period_month`
- Group by: `account_display_name`
- Metric: `SAFE_DIVIDE(SUM(non_compliant_cost_usd), SUM(non_compliant_cost_usd) + SUM(compliant_cost_usd)) * 100`
- Purpose: non-compliant percentage trend by account (exempt excluded from denominator)

### 5.4 Non-Compliant Cost by Region

- Name: `Non-Compliant Cost by Region`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month`
- Group by: `region`
- Metric: `SUM(non_compliant_cost_usd)`
- Purpose: region-level non-compliant cost (pair with account filter; exempt excluded)

### 5.5 Non-Compliant Cost by Service Top 15

- Name: `Non-Compliant Cost by Service Top 15`
- Viz: `echarts_timeseries_bar` (stacked)
- X: `billing_period_month`
- Group by: `service`
- Metric: `SUM(non_compliant_cost_usd)`
- Row limit: `15`
- Purpose: service-level Top N non-compliant cost (exempt excluded)

### 5.6 Compliance Rate by Service

- Name: `Compliance Rate by Service`
- Viz: `table`
- Group by: `service`
- Metrics:
  - `SUM(unblended_cost_usd)` — total
  - `SUM(compliant_cost_usd)` — compliant
  - `SUM(non_compliant_cost_usd)` — non-compliant
  - `SUM(exempt_cost_usd)` — exempt
  - `SAFE_DIVIDE(SUM(non_compliant_cost_usd), SUM(non_compliant_cost_usd) + SUM(compliant_cost_usd)) * 100` — non_compliant_pct
- Sort: descending by non_compliant_cost_usd
- Row limit: `50`
- Purpose: service-level compliance rate table (exempt services show non_compliant_pct = 0)

### 5.7 Tag Compliance Detail Table (Exportable)

- Name: `Tag Compliance Detail Table`
- Viz: `table`
- Group by columns:
  - `billing_period_month`
  - `account_display_name`
  - `region`
  - `service`
  - `service_category`
  - `tag_compliance_status`
- Metrics:
  - `SUM(unblended_cost_usd)` — total
  - `SUM(compliant_cost_usd)` — compliant
  - `SUM(non_compliant_cost_usd)` — non-compliant
  - `SUM(exempt_cost_usd)` — exempt
- Row limit: `500`
- Purpose: detail export for reconciliation (includes tag_compliance_status column)

## 6. Dashboard Metadata

- Title: `AWS Tag Compliance`
- Slug: `aws-tag-compliance`
- Label colors:
  - Compliant: `#1FA8C9` (teal)
  - Non-Compliant: `#FF5A5F` (red)
  - Exempt: `#999999` (gray)

## 7. Layout (Manual in UI)

| Row | Charts | Width |
|-----|--------|-------|
| 1 | Monthly Tag Compliance Trend | 100% |
| 2 | Non-Compliant Cost by Account / Non-Compliant % Trend by Account | 50/50 |
| 3 | Non-Compliant Cost by Region / Non-Compliant Cost by Service Top 15 | 50/50 |
| 4 | Compliance Rate by Service | 100% |
| 5 | Tag Compliance Detail Table | 100% |

## 8. Manual Build Procedure

1. Confirm data access in SQL Lab:
   - `SELECT COUNT(*) FROM \`gcp-rd-research-dev.finops_cloud_billing.mart_cost_monthly\` WHERE account_id IN ('380838443567','143458967504','730335266318','632330501419')`
2. Run `dbt seed --full-refresh` to materialize `tag_exempt_services` in BigQuery.
3. Create virtual dataset `vds_aws_tag_compliance` from SQL in Section 3.
4. Create dashboard:
   - Title: `AWS Tag Compliance`
   - Slug: `aws-tag-compliance`
5. Create 7 components from Section 5.
6. Add native filters from Section 4.
7. Configure filter scope:
   - Apply all filters to all 7 components.
8. Set default filter values:
   - `Time Range = DATEADD(DATETRUNC(DATETIME("today"), month), -3, month) : DATETRUNC(DATETIME("today"), month)`
9. Set label colors: Compliant=#1FA8C9, Non-Compliant=#FF5A5F, Exempt=#999999.
10. Arrange layout per Section 7.
11. Save and publish dashboard.

## 8.1 Optional 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_tag_compliance.sh
```

Notes:
- Script is idempotent (safe to rerun).
- Dashboard layout still needs manual arrangement in UI.
- **Important**: Old charts (id=156-162) with the previous Tagged/Untagged naming must be
  deleted before running the script, as renamed charts will create new Superset charts.

## 9. Tag Exempt Services

Managed via dbt seed: `dbt/seeds/tag_exempt_services.csv`

| Service | Reason |
|---------|--------|
| AWSCloudTrail | Account-level audit logging service |
| AmazonGuardDuty | Account-level threat detection service |
| AWSConfig | Account-level configuration recorder |
| AWSSecurityHub | Account-level security posture service |
| auditmanager | Account-level audit management service |
| AWSCostExplorer | Billing management service |
| AWSBudgets | Billing management service |

To add/remove exempt services:
1. Edit `dbt/seeds/tag_exempt_services.csv`
2. Run `dbt seed --full-refresh && dbt test`
3. Rerun the bootstrap script or refresh the VDS in Superset

## 10. Acceptance Checklist

1. Data connectivity:
   - SQL Lab can query `mart_cost_monthly` and the virtual dataset.
   - `tag_exempt_services` seed is materialized in BigQuery.

2. Consistency:
   - `SUM(compliant_cost_usd) + SUM(non_compliant_cost_usd) + SUM(exempt_cost_usd) = SUM(unblended_cost_usd)` for any filter combination.

3. Filter linkage:
   - Time/account/region/service/category/compliance filters update all charts.
   - Region cascades from Account; Service cascades from Account + Region.
   - Tag Compliance filter shows 3 options: Compliant, Non-Compliant, Exempt.

4. Compliance metric:
   - Non-Compliant % should be `SAFE_DIVIDE(SUM(non_compliant_cost_usd), SUM(non_compliant_cost_usd) + SUM(compliant_cost_usd)) * 100` (exempt excluded from denominator).

5. Exempt services:
   - Charts 2-5 exclude exempt service cost (focused on actionable remediation).
   - Chart 6: Exempt services show `exempt_cost_usd > 0` and `non_compliant_pct = 0`.
   - Chart 7: `tag_compliance_status` column visible with Compliant/Non-Compliant/Exempt values.

6. Business usability:
   - Dashboard can answer:
     - What percentage of taggable cost is non-compliant across production accounts?
     - Which accounts have the worst tagging compliance?
     - Which regions/services contribute the most non-compliant cost?
     - Which services are exempt from tagging requirements?
     - Detailed drill-down for remediation.

## 11. Known Constraints

1. Month-level only; no day-level trend.
2. Only `tag_usedby` is tracked. Other tags are out of scope.
3. Hard-coded to 4 production accounts. Add accounts by updating VDS SQL.
4. Layout must be arranged manually in Superset UI.
5. Exempt service list is maintained manually in `tag_exempt_services.csv`.