> **DEPRECATED**: This document has been merged into `multi-cloud-billing-core.md`.
> Please update your references to use the new consolidated document.
> This file will be removed in a future update.

---

# BigQuery Multi-Cloud Billing Data Overview

This document provides the essential dataset information and query rules for BigQuery billing data in the `gcp-digital-bi` project.

## Dataset Quick Reference

| Dataset | Description | Storage / Location |
| --- | --- | --- |
| `aws_nonprod_billing` | AWS dev/test sub-account billing (CUR 1.0) | External Table (Parquet), Hive Partition `year`/`month`, **us-west1** |
| `aws_prod_billing` | AWS production billing (CUR 1.0) | External Table (Parquet), Hive Partition `year`/`month`, **us-west2** |
| `aws_billing_no_payer` | Consolidated AWS billing (CUR 2.0), one table per month | Native table, partition `line_item_usage_start_date`, **us-west2** |
| `alibaba_cloud` | Alibaba Cloud: `daily_YYYYMMDD`, `monthly_YYYYMM01`, `daily_en_*`, `monthly_en_*` | Native table, partition `partition_date`, **us-west2** |
| `azure_billing` | Azure billing: `azure_billing_cost_YYYYMM01` | Native table (monthly), **us-west2** |
| `gcp_billing_v2` | GCP billing export tables | Native table, daily partition on `usage_start_time`, **US** |

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

---

### AWS billing data dataset structure and usage guidance

- 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` (production account `380838443567`).
- CUR 2.0 dataset: `aws_billing_no_payer` (us-west2). Naming pattern is `aws_billing_YYYYMM01`, one table per month. CUR 2.0 data includes all accounts and structure is more unified.
- 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_start_date`.

> **Important**: Use `line_item_usage_start_date` (not `end_date`) to align with AWS Cost Explorer. Use `product.region` from the RECORD instead of `product_region_code` to capture Data Transfer and VPC costs.
- 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), including all accounts.
  - 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 CUR1.0 dataset.
  - `aws_nonprod_billing` and `aws_prod_billing` will keep updating in the future, but prefer `aws_billing_no_payer` for more standardized coverage of all accounts and more unified structure.

## BigQuery Mandatory Rules (DO NOT VIOLATE)

### 1. Partition Filtering (Cost Control)
Always include a valid partition filter to avoid full table scans:
- AWS CUR 1.0 external tables: filter on `year`, `month`
- AWS CUR 2.0: filter on `line_item_usage_start_date`
- Alibaba Cloud: filter on `partition_date`
- GCP billing: filter on `usage_start_time` or `_PARTITIONDATE`

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

### 3. DATE vs TIMESTAMP
Never compare a TIMESTAMP column directly with a DATE value. Always cast one side:
```sql
-- Correct
WHERE DATE(usage_start_time) >= '2025-01-01'
-- Or
WHERE usage_start_time >= TIMESTAMP('2025-01-01')
```

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

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

---

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