# BigQuery Multi-Cloud Billing Data Storage Overview

This document describes the structure, key fields, and common query patterns for BigQuery datasets in the `gcp-digital-bi` project. It is intended to explain the raw data structure and account information.

## Dataset quick view

| Dataset | Description | Storage type / location |
| --- | --- | --- |
| `aws_nonprod_billing` | AWS dev/test sub-account billing. One external table per sub-account (a few prod accounts are also here). | External Table (Parquet on GCS), Hive Partition by `year`, `month`, region `us-west1` |
| `aws_prod_billing` | AWS production billing, includes multiple prod account tables. | External Table (Parquet on GCS), Hive Partition by `year`, `month`, region `us-west2` |
| `aws_billing_no_payer` | Consolidated billing for all AWS sub-accounts (one table per month, time-partitioned). | Native table + `line_item_usage_end_date` daily partitions, region `us-west2` |
| `alibaba_cloud` | Alibaba Cloud billing: `daily_YYYYMMDD` and `monthly_YYYYMM01` tables. | Native table + `partition_date` partitions (DAY / MONTH), region `us-west2` |
| `azure_billing` | Azure billing: `azure_billing_cost_YYYYMM01` (global), `azure_billing_cost_us_*`, `azure_billing_cost_cn_*`. | Native table (monthly tables), region `us-west2` |
| `gcp_billing_v2` | GCP billing export tables. Before 2025-11-20: `gcp_billing_export_v1_01A169_D3D9BF_FB82A2`; after 2025-11-20: `gcp_billing_export_v1_01D088_8F9CF2_8AF1C6`. | Native table + daily partitions (based on `usage_start_time`), region `US` |

> **Access note**: the two AWS datasets are in different multi-regions. When querying, set `bq --location=us-west1` or `us-west2`, or you will get `Not found in location` errors.

---

## AWS billing data

### Table structure and naming

- CUR 1.0 datasets: `aws_nonprod_billing` (us-west1) and `aws_prod_billing` (us-west2). Naming pattern is `<12-digit sub-account ID>_aws_billing_data`. `aws_prod_billing` also has a special table `aws_prod_billing_data` (account `380838443567`).
- CUR 2.0 dataset: `aws_billing_no_payer` (us-west2). Naming pattern is `aws_billing_YYYYMM01`, one table per month.
- Storage and partitioning:
  - CUR 1.0: external GCS Parquet tables; Hive partition fields `year`, `month` (integers). Must filter to avoid full scans.
  - CUR 2.0: native tables with daily partition `line_item_usage_end_date`.
- Time range and query guidance:
  - AWS billing data before 2025-10-01 should be queried from `aws_nonprod_billing` and `aws_prod_billing`.
  - Data on or after 2025-10-01 is exported as CUR 2.0 and stored in `aws_billing_no_payer` (one table per month).
  - `aws_nonprod_billing` and `aws_prod_billing` will keep updating, but prefer `aws_billing_no_payer` for more standardized coverage of all accounts.

### 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` |

### Table structure differences (CUR 1.0 vs CUR 2.0)

- CUR 1.0: tags are flattened into `resource_tags_user_*` columns (e.g. `resource_tags_user_project`, `resource_tags_user_env`). `aws_nonprod_billing` has extra tag columns like `resource_tags_user_application`, `resource_tags_user_department`, `resource_tags_user_owner`.
- CUR 2.0: `resource_tags` is RECORD/REPEATED (`key_value` contains `key`/`value`). `discount` and `cost_category` are also nested key/value structures that require `UNNEST`.
- Both share the core field families: `bill_*`, `line_item_*`, `product_*`, `pricing_*`, `reservation_*`, `savings_plan_*`, but CUR 2.0 tag and discount structures are closer to the official export format.
> **Note**: CUR 2.0 tags are in a map-style structure (`resource_tags.key_value`), not the flattened `resource_tags_user_*` columns used by CUR 1.0. Queries must `UNNEST(resource_tags.key_value)` to extract tag values.

### Example queries

**1. Monthly cost for the prod billing table (account 380838443567; table name has no sub-account ID)**

```sql
SELECT
  year,
  month,
  ROUND(SUM(line_item_unblended_cost), 2) AS unblended_cost
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;
```

| year | month | cost (USD) |
| --- | --- | --- |
| 2025 | 11 | 927,675.01 |
| ... | ... | ... |

**2. Monthly cost for a single dev sub-account**

```sql
-- dataset is in us-west1; use bq --location=us-west1
SELECT
  year,
  month,
  ROUND(SUM(line_item_unblended_cost), 2) AS cost
FROM `gcp-digital-bi.aws_nonprod_billing.378955295993_aws_billing_data`
WHERE year >= 2024
GROUP BY year, month
ORDER BY year DESC, month DESC
LIMIT 6;
```

### Notes

- For historical data before 2025-10-01, query `aws_nonprod_billing` / `aws_prod_billing`. For 2025-10-01 and later, prefer `aws_billing_no_payer`.
- CUR 1.0 external tables must filter `year` and `month`. CUR 2.0 native tables use `line_item_usage_end_date` for filtering and partition pruning.
- Account dimension: CUR 1.0 uses table names to determine account. CUR 2.0 uses `line_item_usage_account_id` / `line_item_usage_account_name`.
- Tag usage: CUR 1.0 reads `resource_tags_user_*` directly. CUR 2.0 requires `UNNEST(resource_tags.key_value)`.

### `aws_billing_no_payer` (consolidated billing)

- Naming pattern: `aws_billing_YYYYMM01`, one table per month (currently `aws_billing_20251001`, `aws_billing_20251101`, `aws_billing_20251201`) in `us-west2`.
- Storage and partitioning: native tables, daily partition `line_item_usage_end_date` (DAY). No Hive partition filters needed, but still filter by time to limit scans.
- Key fields (CUR structure, wide schema):
  - `bill_*`: billing period, invoice, payer.
  - `identity_*`: line unique key, time interval.
  - `line_item_*`: account, operation, usage amount/unit/cost (includes `line_item_usage_account_id/name`).
  - `product_*`, `pricing_*`: SKU, product family, pricing.
  - `reservation_*`, `savings_plan_*`: RI/SP allocation and costs.
  - `resource_tags`: key/value RECORD+REPEATED; `cost_category` and `discount` are also nested key/value.
- Example query (total cost in the last 7 days):
  ```sql
  SELECT
    DATE_TRUNC(DATE(line_item_usage_end_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_end_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY month
  ORDER BY month DESC;
  ```
- Multi-month query: table names are predictable, so use wildcard `FROM \`gcp-digital-bi.aws_billing_no_payer.aws_billing_2025*\`` or `INFORMATION_SCHEMA.TABLES` to build dynamic unions.

---

## Alibaba Cloud billing data (`alibaba_cloud`)

### Table structure

- `daily_YYYYMMDD`: one table per day (legacy, up to 2025-11-30). `partition_date` is the DATE partition column. Row-level fields include billing period, product, resource, region, usage time, amounts (`list_price`, `discount_amount`, `coupon_deduction`, `amount_after_discount`), currency, etc.
- `daily_en_YYYYMMDD`: one table per day (from 2025-12-01). Schema differs from legacy tables (renamed columns such as `product_name`, `zone`, `currency`).
- `monthly_YYYYMM01`: named by the first day of the month (legacy, up to 2025-11-01). `timePartitioning` type=MONTH. Fields are similar to daily tables but aggregated to monthly level, with some detail fields removed.
- `monthly_en_YYYYMM01`: monthly tables from 2025-12-01 with the English schema.

### Example queries

```sql
-- single-day cost
SELECT
  partition_date,
  ROUND(SUM(amount_after_discount), 2) AS net_cost
FROM `gcp-digital-bi.alibaba_cloud.daily_20250301`
GROUP BY partition_date;
```

```sql
-- monthly cost by account, Top N
SELECT
  account_id,
  ROUND(SUM(amount_after_discount), 2) AS net_cost
FROM `gcp-digital-bi.alibaba_cloud.monthly_20250801`
GROUP BY account_id
ORDER BY net_cost DESC
LIMIT 5;
```

```sql
-- compare schema between legacy and English daily tables
WITH legacy AS (
  SELECT column_name, data_type
  FROM `gcp-digital-bi`.alibaba_cloud.INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'daily_20251130'
),
english AS (
  SELECT column_name, data_type
  FROM `gcp-digital-bi`.alibaba_cloud.INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'daily_en_20251201'
)
SELECT 'only_in_legacy' AS diff, l.column_name, l.data_type
FROM legacy l LEFT JOIN english e USING (column_name, data_type)
WHERE e.column_name IS NULL
UNION ALL
SELECT 'only_in_english' AS diff, e.column_name, e.data_type
FROM english e LEFT JOIN legacy l USING (column_name, data_type)
WHERE l.column_name IS NULL
ORDER BY diff, column_name;
```

```sql
-- compare schema between legacy and English monthly tables
WITH legacy AS (
  SELECT column_name, data_type
  FROM `gcp-digital-bi`.alibaba_cloud.INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'monthly_20251101'
),
english AS (
  SELECT column_name, data_type
  FROM `gcp-digital-bi`.alibaba_cloud.INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'monthly_en_20251201'
)
SELECT 'only_in_legacy' AS diff, l.column_name, l.data_type
FROM legacy l LEFT JOIN english e USING (column_name, data_type)
WHERE e.column_name IS NULL
UNION ALL
SELECT 'only_in_english' AS diff, e.column_name, e.data_type
FROM english e LEFT JOIN legacy l USING (column_name, data_type)
WHERE l.column_name IS NULL
ORDER BY diff, column_name;
```

### Usage guidance

- Daily tables are best for detailed investigations. Always filter on `partition_date` to reduce scan.
- Monthly tables are best for budget/ledger checks; use `amount_after_discount` as the net cost measure.
- From 2025-12-01 onward, table names add the `_en_` prefix and column names change. The Alibaba exporter maps English columns back to the legacy CSV schema for analysis.
- Account grain: `account_id` is the sub-account, `owner_account_id` is the master account. Aggregate based on your use case.
- Organization dimensions: prefer `financial_unit` / `resource_group`. Tags are in `instance_tags` (JSON string), so parse and standardize.

---

## Azure billing data (`azure_billing`)

### Table structure

- Table naming: `azure_billing_cost_YYYYMM01` (default), one table per month with line items. `azure_billing_cost_us_*` and `azure_billing_cost_cn_*` are deprecated and have not been updated since 2025-08.
- Key fields:
  - Invoice dimension: `invoiceId`, `billingAccountId`, `billingProfileName`, `invoiceSectionName`.
  - Resource dimension: `productName`, `meterCategory/SubCategory/Region`, `consumedService`, `productOrderId`.
  - Time dimension: `servicePeriodStartDate/EndDate`, daily `date`.
  - Amounts: `quantity`, `unitPrice`, `costInUsd`, `paygCostInUsd`, `billingCurrency`, `pricingCurrency`, `exchangeRate*`.
  - Tags: `tags` (JSON string), `tenant`, `usedby`, `chargeType`, `pricingModel`, etc.

### Example query

```sql
SELECT
  DATE_TRUNC(date, MONTH) AS month,
  ROUND(SUM(costInUsd), 2) AS cost_usd
FROM `gcp-digital-bi.azure_billing.azure_billing_cost_20250701`
GROUP BY month;
```

### Usage guidance

- `date` is daily grain; filter by month to avoid full scans.
- Prefer `costInUsd` as the base cost metric. Use `chargeType` / `pricingModel` to split cost types.
- `tags` is a JSON string; use `JSON_VALUE` to extract values and standardize with `tenant` / `usedby`.
- Multi-month queries can be built by stitching `azure_billing_cost_YYYYMM01` via `INFORMATION_SCHEMA.TABLES`.

---

## GCP billing data (`gcp_billing_v2`)

### Table structure overview

- **Table list**:
  - `gcp_billing_export_v1_01A169_D3D9BF_FB82A2`: data before 2025-11-20.
  - `gcp_billing_export_v1_01D088_8F9CF2_8AF1C6`: data on and after 2025-11-20.
- Official export tables, daily partitioned, currently about 326 million rows and 300GB logical data.
- Key nested structures:
  - `service`, `sku`: include `id` and description.
  - `project`: includes project id/number/name, labels, ancestry.
  - `labels`, `tags`, `system_labels`: REPEATED RECORD, requires `LEFT JOIN UNNEST(...)`.
  - `location`: `location.location`, `region`, `zone`.
  - Pricing: `cost`, `usage.amount`, `usage.pricing_unit`, `credit`, `adjustment_info`, `pricing` structures.
- Common time fields: `usage_start_time`, `usage_end_time`; cost fields: `cost`, `currency`, `cost_type`.

### Example query (last 90 days cost)

```sql
SELECT
  FORMAT_DATE('%Y-%m', DATE(usage_start_time)) AS ym,
  ROUND(SUM(cost), 2) AS cost
FROM (
  SELECT * FROM `gcp-digital-bi.gcp_billing_v2.gcp_billing_export_v1_01A169_D3D9BF_FB82A2`
  UNION ALL
  SELECT * FROM `gcp-digital-bi.gcp_billing_v2.gcp_billing_export_v1_01D088_8F9CF2_8AF1C6`
)
WHERE usage_start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY ym
ORDER BY ym;
```

### Usage guidance

- When aggregating cost, handle `credits`: use `cost + SUM(credits.amount)` as net cost (`credits.amount` is negative).
- Dimension expansion: `service`, `sku`, `project`, `labels`, `system_labels`, `tags` are nested or repeated; `UNNEST` before standardizing.
- Filter partitions by `usage_start_time` or `_PARTITIONDATE` to avoid scanning historical partitions.