# AWS Billing Data Guide

## Table Structure and Naming

### CUR 1.0 Datasets
- **`aws_nonprod_billing`** (us-west1): Dev/test sub-accounts. Table naming: `<12-digit account ID>_aws_billing_data`.
- **`aws_prod_billing`** (us-west2): Production accounts. Special table `aws_prod_billing_data` for account `380838443567`.
- **Storage**: External GCS Parquet tables with Hive partition fields `year`, `month` (integers). Must filter to avoid full scans.
- **Note**: These datasets will keep updating in the future, but prefer `aws_billing_no_payer` for more standardized coverage of all accounts and more unified structure.

### CUR 2.0 Dataset
- **`aws_billing_no_payer`** (us-west2): Consolidated billing, one table per month.
- **Coverage**: Includes all accounts (consolidated view) and structure is more unified.
- **Table naming**: `aws_billing_YYYYMM01` (e.g., `aws_billing_20251201`).
- **Storage**: Native tables with daily partition `line_item_usage_start_date`.

> **Important**: Use `line_item_usage_start_date` (not `end_date`) to align with AWS Cost Explorer reporting.

### Time Range Guidance
- **Before 2025-10-01**: Query `aws_nonprod_billing` / `aws_prod_billing` (CUR 1.0).
- **2025-10-01 and later**: Data is exported as CUR 2.0 and stored in `aws_billing_no_payer`.
- **Priority**: For any queries on or after October 1, 2025, please ensure that CUR 2.0 data from the `aws_billing_no_payer` dataset is prioritized. Only if the user explicitly specifies, can data queries be conducted in the CUR 1.0 dataset.

---

## Account to Table Mapping (CUR 1.0)

| account_id | account_name | env | dataset_table |
| --- | --- | --- | --- |
| 398894451188 | pingcap-security | nonprod | `aws_nonprod_billing.398894451188_aws_billing_data` |
| 316218510314 | PingCAP-test | nonprod | `aws_nonprod_billing.316218510314_aws_billing_data` |
| 510387698298 | pingcapcn | nonprod | `aws_nonprod_billing.510387698298_aws_billing_data` |
| 178851224597 | tikv-storage-dev | nonprod | `aws_nonprod_billing.178851224597_aws_billing_data` |
| 200683210682 | dbaas-infra-dev | nonprod | `aws_nonprod_billing.200683210682_aws_billing_data` |
| 470438934515 | tidbcloud-marketplace | nonprod | `aws_nonprod_billing.470438934515_aws_billing_data` |
| 385595570414 | PingCAP | nonprod | `aws_nonprod_billing.385595570414_aws_billing_data` |
| 646528577659 | compute-dev | nonprod | `aws_nonprod_billing.646528577659_aws_billing_data` |
| 166681434446 | escalation-dev | nonprod | `aws_nonprod_billing.166681434446_aws_billing_data` |
| 946646677266 | qa-infra-dev | nonprod | `aws_nonprod_billing.946646677266_aws_billing_data` |
| 495580073302 | dp-infra-dev | nonprod | `aws_nonprod_billing.495580073302_aws_billing_data` |
| 676546228356 | qa-dbaas-dev | nonprod | `aws_nonprod_billing.676546228356_aws_billing_data` |
| 448141240262 | qa-dp-testing-dev | nonprod | `aws_nonprod_billing.448141240262_aws_billing_data` |
| 986330900858 | qa-system-testing-dev | nonprod | `aws_nonprod_billing.986330900858_aws_billing_data` |
| 013690006788 | qa-perf-testing-dev | nonprod | `aws_nonprod_billing.013690006788_aws_billing_data` |
| 552185537300 | clinic-dev | nonprod | `aws_nonprod_billing.552185537300_aws_billing_data` |
| 482894109814 | cloud-ecosystem-dev | nonprod | `aws_nonprod_billing.482894109814_aws_billing_data` |
| 795292582174 | TiDBCloud-JSM-kafka | nonprod | `aws_nonprod_billing.795292582174_aws_billing_data` |
| 024848481520 | auto-deployment-test | nonprod | `aws_nonprod_billing.024848481520_aws_billing_data` |
| 886436925895 | test-auto-deploy-staging-on-prod | nonprod | `aws_nonprod_billing.886436925895_aws_billing_data` |
| 813266824626 | autonomous-dataplane-dev-01 | nonprod | `aws_nonprod_billing.813266824626_aws_billing_data` |
| 950061775591 | observability-test | nonprod | `aws_nonprod_billing.950061775591_aws_billing_data` |
| 227896186585 | byoc-cluster-server | nonprod | `aws_nonprod_billing.227896186585_aws_billing_data` |
| 131464424160 | testinfra-prod-testregion | nonprod | `aws_nonprod_billing.131464424160_aws_billing_data` |
| 757842878326 | autonomous-partner-test | nonprod | `aws_nonprod_billing.757842878326_aws_billing_data` |
| 790636686964 | staging-next-gen-dataplane | nonprod | `aws_nonprod_billing.790636686964_aws_billing_data` |
| 703227779288 | ee-infra-dev | nonprod | `aws_nonprod_billing.703227779288_aws_billing_data` |
| 487825215408 | tidb-br-dev-test | nonprod | `aws_nonprod_billing.487825215408_aws_billing_data` |
| 629891806751 | byoc-deployment | nonprod | `aws_nonprod_billing.629891806751_aws_billing_data` |
| 557537366020 | dbaas-prod-clinic | prod | `aws_prod_billing.557537366020_aws_billing_data` |
| 380838443567 | pingcap-dbaas | prod | `aws_prod_billing.aws_prod_billing_data` |
| 835830315734 | Log Archive | prod | `aws_prod_billing.835830315734_aws_billing_data` |
| 945698639225 | Audit | prod | `aws_prod_billing.945698639225_aws_billing_data` |
| 143458967504 | dbaas-prod-dataplane-000 | prod | `aws_prod_billing.143458967504_aws_billing_data` |
| 886436949658 | dbaas-prod-only-s3 | prod | `aws_prod_billing.886436949658_aws_billing_data` |
| 182399704004 | dbaas-prod-auto-task | prod | `aws_prod_billing.182399704004_aws_billing_data` |
| 730335266318 | dbaas-prod-dataplane-001 | prod | `aws_prod_billing.730335266318_aws_billing_data` |
| 296171618728 | dbaas-prod-tidbcloud-pov | prod | `aws_prod_billing.296171618728_aws_billing_data` |
| 632330501419 | dbaas-prod-dataplane-nextgen | prod | `aws_prod_billing.632330501419_aws_billing_data` |

---

## CUR 1.0 vs CUR 2.0 Differences

| Aspect | CUR 1.0 | CUR 2.0 |
| --- | --- | --- |
| Tags | Flattened columns: `resource_tags_user_*` | Nested RECORD: `resource_tags.key_value` (requires `UNNEST`) |
| Discounts | Separate columns | Nested `discount.key_value` structure |
| Account ID | Derived from table name | `line_item_usage_account_id` / `line_item_usage_account_name` |
| Partition | Hive `year`, `month` | Native `line_item_usage_start_date` |

Both versions share core field families: `bill_*`, `line_item_*`, `product_*`, `pricing_*`, `reservation_*`, `savings_plan_*`.

---

## Key Fields (CUR 2.0)

### Core Field Families
| Field Family | Description |
| --- | --- |
| `bill_*` | Billing period, invoice, payer account |
| `identity_*` | Line unique key, time interval |
| `line_item_*` | Account, operation, usage amount/unit/cost |
| `product_*` | SKU, product family, service code |
| `pricing_*` | Pricing details, rate, term |
| `reservation_*` | Reserved Instance allocation and costs |
| `savings_plan_*` | Savings Plan allocation and costs |

### Nested RECORD Structures (CUR 2.0 Only)

#### `resource_tags` - User-defined Tags
**Structure**: `resource_tags.key_value` is a REPEATED RECORD with `key` and `value` fields.

**Example - Extract specific tag:**
```sql
SELECT
  line_item_usage_account_id,
  tag.value AS tenant_id,
  ROUND(SUM(line_item_unblended_cost), 2) AS cost
FROM `gcp-digital-bi.aws_billing_no_payer.aws_billing_20251201`,
  UNNEST(resource_tags.key_value) AS tag
WHERE tag.key = 'tenant_id'
  AND line_item_usage_start_date >= TIMESTAMP('2025-12-01')
GROUP BY 1, 2
ORDER BY cost DESC
```

**Example - Pivot multiple tags:**
```sql
SELECT
  line_item_usage_account_id,
  MAX(IF(tag.key = 'tenant_id', tag.value, NULL)) AS tenant_id,
  MAX(IF(tag.key = 'project', tag.value, NULL)) AS project,
  MAX(IF(tag.key = 'environment', tag.value, NULL)) AS environment,
  ROUND(SUM(line_item_unblended_cost), 2) AS cost
FROM `gcp-digital-bi.aws_billing_no_payer.aws_billing_20251201`
LEFT JOIN UNNEST(resource_tags.key_value) AS tag ON TRUE
WHERE line_item_usage_start_date >= TIMESTAMP('2025-12-01')
GROUP BY 1
ORDER BY cost DESC
```

#### `discount` - Discount Details
**Structure**: `discount.key_value` is a REPEATED RECORD containing discount types.

**Common discount keys:**
- `edp_discount` - Enterprise Discount Program discount
- `private_rate_discount` - Private rate discount

**Example - Extract discounts:**
```sql
SELECT
  line_item_usage_account_id,
  d.key AS discount_type,
  ROUND(SUM(CAST(d.value AS FLOAT64)), 2) AS discount_amount
FROM `gcp-digital-bi.aws_billing_no_payer.aws_billing_20251201`,
  UNNEST(discount.key_value) AS d
WHERE line_item_usage_start_date >= TIMESTAMP('2025-12-01')
GROUP BY 1, 2
ORDER BY discount_amount
```

#### `cost_category` - Cost Allocation Categories
**Structure**: `cost_category` is a REPEATED RECORD for AWS Cost Categories.

> Note: This field may be empty if Cost Categories are not configured in AWS.

**Example - Extract cost categories:**
```sql
SELECT
  cc.key AS category_name,
  cc.value AS category_value,
  ROUND(SUM(line_item_unblended_cost), 2) AS cost
FROM `gcp-digital-bi.aws_billing_no_payer.aws_billing_20251201`,
  UNNEST(cost_category) AS cc
WHERE line_item_usage_start_date >= TIMESTAMP('2025-12-01')
GROUP BY 1, 2
ORDER BY cost DESC
```

### Cost Fields

| Field | Description |
| --- | --- |
| `line_item_unblended_cost` | **Primary cost field** - Actual cost without RI/SP amortization |
| `line_item_blended_cost` | Cost with consolidated billing blending |
| `line_item_net_unblended_cost` | Net cost after discounts |
| `pricing_public_on_demand_cost` | On-demand list price |
| `reservation_effective_cost` | RI effective cost (amortized) |
| `savings_plan_savings_plan_effective_cost` | SP effective cost (amortized) |

### Account Fields (CUR 2.0)

| Field | Description |
| --- | --- |
| `bill_payer_account_id` | Payer (management) account ID |
| `line_item_usage_account_id` | Linked (member) account ID |
| `line_item_usage_account_name` | Linked account name |

---

## Example Queries

### CUR 1.0: Monthly cost for prod account

```sql
SELECT
  year,
  month,
  ROUND(SUM(line_item_unblended_cost), 2) AS cost_usd
FROM `gcp-digital-bi.aws_prod_billing.aws_prod_billing_data`
WHERE year >= 2024
GROUP BY year, month
ORDER BY year DESC, month DESC
LIMIT 12;
```

### CUR 2.0: Cost in last 7 days

```sql
SELECT
  DATE_TRUNC(DATE(line_item_usage_start_date), MONTH) AS month,
  ROUND(SUM(line_item_unblended_cost), 2) AS cost_usd
FROM `gcp-digital-bi.aws_billing_no_payer.aws_billing_20251201`
WHERE line_item_usage_start_date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY month
ORDER BY month DESC;
```

### CUR 2.0: Multi-month query with wildcard

```sql
SELECT
  DATE_TRUNC(DATE(line_item_usage_start_date), MONTH) AS month,
  ROUND(SUM(line_item_unblended_cost), 2) AS cost_usd
FROM `gcp-digital-bi.aws_billing_no_payer.aws_billing_2025*`
WHERE line_item_usage_start_date >= TIMESTAMP('2025-10-01')
GROUP BY month
ORDER BY month;
```

### CUR 2.0: Extract tags

```sql
SELECT
  line_item_usage_account_id,
  tag.value AS tenant_id,
  ROUND(SUM(line_item_unblended_cost), 2) AS cost_usd
FROM `gcp-digital-bi.aws_billing_no_payer.aws_billing_20251201`,
  UNNEST(resource_tags.key_value) AS tag
WHERE tag.key = 'tenant_id'
  AND line_item_usage_start_date >= TIMESTAMP('2025-12-01')
GROUP BY 1, 2
ORDER BY cost_usd DESC
LIMIT 10;
```