# Multi-Cloud Billing Data Reference (Authoritative)

This is the single authoritative reference for BigQuery billing data in the `gcp-digital-bi` project. It consolidates dataset information, mandatory query rules, and cost field conventions.

> **Migration note**: This document consolidates the former `multi-cloud-billing.md`, `multi-cloud-billing-base.md`, and `multi-cloud-billing-schema.md`.

---

## 1. Dataset Quick Reference

| Dataset | Description | Storage / Location | Partition | Table Naming |
| --- | --- | --- | --- | --- |
| `aws_nonprod_billing` | AWS dev/test sub-account billing (CUR 1.0) | External Table (Parquet on GCS), **us-west1** | Hive: `year`, `month` | `<AccountID>_aws_billing_data` |
| `aws_prod_billing` | AWS production billing (CUR 1.0) | External Table (Parquet on GCS), **us-west2** | Hive: `year`, `month` | `<AccountID>_aws_billing_data` (most), `aws_prod_billing_data` (acct `380838443567`) |
| `aws_billing_no_payer` | Consolidated AWS billing (CUR 2.0), all accounts | Native table, **us-west2** | `line_item_usage_start_date` (DAY) | `aws_billing_YYYYMM01` |
| `alibaba_cloud` | Alibaba Cloud billing | Native table, **us-west2** | `partition_date` (DAY/MONTH) | `daily_YYYYMMDD`, `monthly_YYYYMM01`, `daily_en_*`, `monthly_en_*` |
| `azure_billing` | Azure billing | Native table, **us-west2** | Monthly tables | `azure_billing_cost_YYYYMM01` |
| `gcp_billing_v2` | GCP billing export | Native table, **US** | `usage_start_time` (DAY) | `gcp_billing_export_v1_*` |

> **Region note**: AWS datasets span `us-west1` and `us-west2`. Set `bq --location` accordingly to avoid `Not found in location` errors.

---

## 2. BigQuery Mandatory Rules (DO NOT VIOLATE)

### 2.1 Partition Filtering (Cost Control)
Always include a valid partition filter to avoid full table scans:

| Dataset | Partition Filter Required |
| --- | --- |
| AWS CUR 1.0 (`aws_nonprod_billing`, `aws_prod_billing`) | `WHERE year = YYYY AND month = MM` |
| AWS CUR 2.0 (`aws_billing_no_payer`) | `WHERE line_item_usage_start_date >= TIMESTAMP('YYYY-MM-DD')` |
| Alibaba Cloud | `WHERE partition_date >= ...` |
| Azure | Filter by `date` column |
| GCP | `WHERE usage_start_time >= TIMESTAMP('YYYY-MM-DD')` |

### 2.2 Wildcard Tables & `_TABLE_SUFFIX`
`_TABLE_SUFFIX` equals **only the part matched by `*`** in the table pattern:
- `aws_billing_2025*` → suffix = `MMDD` (e.g., `1201`)
- `aws_billing_*` → suffix = `YYYYMMDD` (e.g., `20251201`)
- Never assume `_TABLE_SUFFIX` contains the full table name.

### 2.3 DATE vs TIMESTAMP
Never compare a TIMESTAMP column directly with a DATE value. Always cast one side:
```sql
-- Preferred (keeps partition pruning on usage_start_time-partitioned tables)
WHERE usage_start_time >= TIMESTAMP('2025-01-01')

-- Also valid, but can reduce partition pruning due to wrapping the partition column
WHERE DATE(usage_start_time) >= DATE '2025-01-01'
```

### 2.4 Time Semantics
Distinguish between:
- **Usage time**: when resources were consumed (e.g., `line_item_usage_start_date`, `usage_start_time`)
- **Billing period**: invoice/ledger period (e.g., `bill_billing_period_start_date`)

For monthly cost reports, prefer billing period fields when available.

### 2.5 Time Zone
`CURRENT_DATE()` uses UTC by default. For "current month" or "last month" logic, explicitly specify time zone when needed:
```sql
CURRENT_DATE("Asia/Shanghai")
```

---

## 3. Cost Field Conventions

| Cloud | Primary Cost Field | Net Cost Calculation |
| --- | --- | --- |
| AWS | `line_item_unblended_cost` | Direct use or apply discounts |
| Alibaba | `amount_after_discount` | Already net of discounts |
| Azure | `costInUsd` | Direct use |
| GCP | `cost` | `cost + SUM(credits.amount)` for net cost (credits are negative) |

---

## 4. Time Range Guidance

### AWS
- **Before 2025-10-01**: Query from `aws_nonprod_billing` and `aws_prod_billing` (CUR 1.0)
- **On or after 2025-10-01**: Query from `aws_billing_no_payer` (CUR 2.0) - prioritize this for newer data
- Both datasets continue to update, but prefer CUR 2.0 for unified structure and all-account coverage

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

### GCP
- **Before 2025-11-20**: `gcp_billing_export_v1_01A169_D3D9BF_FB82A2`
- **On or after 2025-11-20**: `gcp_billing_export_v1_01D088_8F9CF2_8AF1C6`
- For cross-period queries, use `UNION ALL` between both tables

### Alibaba
- **Before 2025-12-01**: Legacy CN tables (`daily_YYYYMMDD`, `monthly_YYYYMM01`)
- **On or after 2025-12-01**: EN tables (`daily_en_YYYYMMDD`, `monthly_en_YYYYMM01`)
- Do not mix CN and EN tables in wildcard queries

### Azure
- Monthly tables: `azure_billing_cost_YYYYMM01`
- Deprecated: `azure_billing_cost_us_*` and `azure_billing_cost_cn_*` (not updated since 2025-08)

---

## 5. Cloud-Specific Detailed Guides

For detailed schema, field values, and query examples, see:

| Cloud | Guide |
| --- | --- |
| AWS | `aws-billing-guide.md`, `aws-cur-field-values.md` |
| GCP | `gcp-billing-guide.md` |
| Azure | `azure-billing-guide.md` |
| Alibaba | `alibaba-billing-guide.md` |

---

## 6. Key Schema Differences

### AWS CUR 1.0 vs CUR 2.0

| Aspect | CUR 1.0 | CUR 2.0 |
| --- | --- | --- |
| Tags | Flattened columns: `resource_tags_user_*` | Nested RECORD: `resource_tags.key_value` (requires `UNNEST`) |
| Discounts | Flat columns | Nested RECORD: `discount.key_value` (requires `UNNEST`) |
| Cost Category | N/A | Nested RECORD: `cost_category` |
| Account identification | Table name contains account ID | `line_item_usage_account_id` / `line_item_usage_account_name` columns |

### GCP Nested Structures

Key nested fields requiring special handling:
- `service` / `sku`: RECORD with `id`, `description`
- `project`: RECORD with `id`, `name`, `labels` (REPEATED), `ancestors`
- `labels` / `system_labels` / `tags`: REPEATED RECORD requiring `UNNEST`
- `usage`: RECORD with `amount`, `unit`, `amount_in_pricing_units`, `pricing_unit`
- `credits`: REPEATED RECORD with `name`, `amount`, `type` (amount is negative)
- `location`: RECORD with `location`, `country`, `region`, `zone`

### Azure JSON Tags
`tags` field is a JSON string. Extract values using:
```sql
JSON_VALUE(tags, '$.key_name')
```

### Alibaba JSON Tags
Tag fields are JSON strings:
- Legacy CN tables: `instance_tags`
- EN tables: `instance_tag`

Parse as needed for tag-based analysis.

---

## 7. Schema Export Script

To export full schemas to JSON files:

```bash
#!/usr/bin/env bash
set -euo pipefail

DATASETS=(
  "gcp-digital-bi:aws_prod_billing.aws_prod_billing_data"
  "gcp-digital-bi:aws_nonprod_billing.013690006788_aws_billing_data"
  "gcp-digital-bi:aws_billing_no_payer.aws_billing_20251201"
  "gcp-digital-bi:alibaba_cloud.daily_en_20251201"
  "gcp-digital-bi:alibaba_cloud.monthly_en_20251201"
  "gcp-digital-bi:azure_billing.azure_billing_cost_20250701"
  "gcp-digital-bi:gcp_billing_v2.gcp_billing_export_v1_01D088_8F9CF2_8AF1C6"
)

for table in "${DATASETS[@]}"; do
  echo "Exporting schema for ${table}"
  bq show --schema --format=prettyjson "${table}" > "schema_${table//[:.]/_}.json"
done
```

Schema JSON files are available in `docs/schema-data-json/`.