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

---

# Multi-Cloud Billing Schema Appendix

This document supplements `docs/multi-cloud-billing-core.md`. It records table composition, storage properties, and key fields by dataset to speed up DBT view/model design.

> **Source note**: field information is compiled from BigQuery `bq show --format=prettyjson` output plus each cloud's billing documentation. For the full column list, run the script at the end of this file.

---

## AWS Cost & Usage Report (CUR)

### Table composition

| Dataset | Location | Storage type | Table naming | Notes |
| --- | --- | --- | --- | --- |
| `gcp-digital-bi.aws_nonprod_billing` | `us-west1` | External Table (Parquet on GCS) | `<AccountID>_aws_billing_data` (40+ tables) | one table per sub-account, includes a few prod accounts; view `aws_grouped_o11y_billing_cost` also exists |
| `gcp-digital-bi.aws_prod_billing` | `us-west2` | External Table (Parquet on GCS) | `<AccountID>_aws_billing_data` and `aws_prod_billing_data` | `aws_prod_billing_data` maps to account `380838443567` |
| `gcp-digital-bi.aws_billing_no_payer` | `us-west2` | Native Table | `aws_billing_YYYYMM01` | consolidated billing, one table per month, daily partition on `line_item_usage_start_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 continue to update, but prefer `aws_billing_no_payer` for more standardized coverage of all accounts.

### Partitioning and locations

- External tables use Hive partitions (`year`, `month`); queries must filter on `year` and `month`.
- Consolidated native tables use daily partitioning on `line_item_usage_start_date`. Table names are monthly (e.g. `aws_billing_20251201`), so filter by date range to avoid full scans.

> **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.
- GCS storage path examples:
  - non-prod: `gs://aws-billing_pingcap-non-prod/<account>/billing/billing-report-hourly/...`
  - prod: `gs://prod_billing/prod/prod-billing/...`

### Key fields (excerpt)

#### CUR 1.0 (`aws_nonprod_billing` / `aws_prod_billing`)

| Field | Type | Description |
| --- | --- | --- |
| `identity_line_item_id` | STRING | CUR unique key |
| `identity_time_interval` | STRING | billing time window |
| `bill_invoice_id`/`bill_billing_period_*` | STRING / TIMESTAMP | billing period and invoice |
| `bill_payer_account_id` | STRING | payer account |
| `line_item_usage_account_id` | STRING | linked account |
| `line_item_line_item_type` | STRING | type (Usage/RIFee/Discount, etc.) |
| `line_item_usage_start_date`/`end_date` | TIMESTAMP | usage time |
| `line_item_product_code`/`line_item_usage_type`/`line_item_operation` | STRING | product and billing type |
| `line_item_unblended_cost`/`line_item_unblended_rate` | FLOAT | unblended cost |
| `line_item_blended_cost`/`line_item_blended_rate` | FLOAT | blended cost |
| `line_item_usage_amount`/`usage_unit` | FLOAT / STRING | usage and unit |
| `pricing_term`/`pricing_unit`/`pricing_public_on_demand_cost` | STRING / FLOAT | price list data |
| `product_product_family`/`product_servicecode`/`product_region` | STRING | product and region |
| `reservation_*`, `savings_plan_*` | multiple | RI/Savings Plan amortization |
| `resource_tags_user_*` | STRING | user-defined tags (e.g. `resource_tags_user_project`, `resource_tags_user_env`) |
| `public_on_demand_cost`/`effective_cost` | FLOAT | fields aligned with AWS Cost Explorer |
| `year` / `month` | INTEGER | Hive partition columns |

#### CUR 2.0 (`aws_billing_no_payer`)

| Field | Type | Description |
| --- | --- | --- |
| `identity_line_item_id` | STRING | CUR unique key |
| `identity_time_interval` | STRING | billing time window |
| `bill_invoice_id`/`bill_billing_period_*` | STRING / TIMESTAMP | billing period and invoice |
| `bill_payer_account_id` | STRING | payer account |
| `line_item_usage_account_id` | STRING | linked account |
| `line_item_usage_account_name` | STRING | linked account name |
| `line_item_line_item_type` | STRING | type (Usage/Discount, etc.) |
| `line_item_usage_start_date`/`end_date` | TIMESTAMP | usage time |
| `line_item_product_code`/`line_item_usage_type`/`line_item_operation` | STRING | product and billing type |
| `line_item_unblended_cost`/`line_item_unblended_rate` | FLOAT | unblended cost |
| `line_item_blended_cost`/`line_item_blended_rate` | FLOAT | blended cost |
| `line_item_usage_amount`/`usage_unit` | FLOAT / STRING | usage and unit |
| `pricing_term`/`pricing_unit`/`pricing_public_on_demand_cost` | STRING / FLOAT | price list data |
| `product_product_family`/`product_servicecode`/`product_region` | STRING | product and region |
| `reservation_*`, `savings_plan_*` | multiple | RI/Savings Plan amortization |
| `resource_tags.key_value` | RECORD (REPEATED) | tag key/value structure, requires `UNNEST` |
| `discount.key_value` | RECORD (REPEATED) | discount key/value structure, requires `UNNEST` |
| `cost_category` | RECORD (REPEATED) | cost category key/value structure (may be empty) |

> AWS CUR has 150+ raw columns. For the full list, run:
> `bq show --schema --format=prettyjson gcp-digital-bi:aws_prod_billing.aws_prod_billing_data`

---

## Alibaba Cloud billing (`gcp-digital-bi.alibaba_cloud`)

### Table composition

| Table pattern | Example | Partition | Notes |
| --- | --- | --- | --- |
| `daily_YYYYMMDD` | `daily_20250301` | `partition_date` (DAY) | one table per day, daily line items |
| `monthly_YYYYMM01` | `monthly_20250801` | `partition_date` (MONTH) | one table per month, aggregated to product/instance level |

### Key fields

| Field | Type | Description |
| --- | --- | --- |
| `billing_period` | STRING | billing period, e.g. `2025-03` |
| `financial_unit` | STRING | financial unit / cost center |
| `account_id` / `owner_account_id` | INTEGER | master account / sub-account (prioritize owner for usage) |
| `product_code` / `product_detail_code` | STRING | product and sub-product code |
| `consumption_type` | STRING | consumption type (pay-as-you-go/reserved) |
| `consumption_time` / `bill_start_time` / `bill_end_time` | TIMESTAMP | usage and billing time |
| `instance_id` / `instance_nickname` | STRING | resource instance info |
| `resource_group` / `instance_tags` | STRING | resource group and tags (JSON string) |
| `region` / `availability_zone` | STRING | region |
| `list_price` / `discount_amount` / `coupon_deduction` | FLOAT | list price, discount, coupon deduction |
| `amount_after_discount` | FLOAT | net amount |
| `pricing_currency` | STRING | currency (CNY, etc.) |
| `billing_method` | STRING | billing method (prepaid, pay-as-you-go) |
| `partition_date` | DATE | partition column |

> Monthly tables contain fewer fields, keeping only key billing, account, product, and cost columns. Most fields share names with daily tables.

---

## Azure billing (`gcp-digital-bi.azure_billing`)

### Table composition

| Table name | Description | Typical use |
| --- | --- | --- |
| `azure_billing_cost_YYYYMM01` | default billing table, one table per month, includes global consumption | primary data source |
| `azure_billing_cost_us_YYYYMM01` | US account split (unused, can ignore) | regional view |
| `azure_billing_cost_cn_YYYYMM01` | 21Vianet (Azure China) billing (unused, can ignore) | compliance/localization |

### Key fields

| Field | Type | Description |
| --- | --- | --- |
| `invoiceId` / `billingProfileId` / `invoiceSectionId` | STRING | invoice and billing configuration |
| `billingPeriodStartDate` / `billingPeriodEndDate` | STRING | billing period |
| `servicePeriodStartDate` / `servicePeriodEndDate` | DATE | service period |
| `date` | DATE | daily grain |
| `billingAccountName` / `billingProfileName` | STRING | billing account |
| `meterId` / `meterName` / `meterCategory` / `meterSubCategory` / `meterRegion` | STRING | SKU and meter info |
| `consumedService` / `serviceFamily` / `productName` | STRING | service/product dimensions |
| `quantity` / `unitOfMeasure` | FLOAT / STRING | usage |
| `unitPrice` / `costInUsd` / `costInBillingCurrency` | FLOAT | unit price and cost |
| `billingCurrency` / `pricingCurrency` / `exchangeRatePricingToBilling` | STRING / FLOAT | currencies and exchange rate |
| `chargeType` | STRING | charge type (Usage, Purchase, etc.) |
| `pricingModel` | STRING | pricing model (PAYG, Reservation, etc.) |
| `reservationId` / `benefitId` | STRING | Reservation/Benefit info |
| `tags` | STRING | resource tags JSON |
| `provider` / `tenant` / `usedby` | STRING | internal tagging fields (for permissions/team ownership) |

---

## GCP Billing Export (`gcp-digital-bi.gcp_billing_v2`)

### Table properties

| Property | Description |
| --- | --- |
| Location | `US` |
| Partitioning | daily partition (`_PARTITIONDATE` ~= `DATE(usage_start_time)`) |
| Size | 325M+ rows, about 300GB logical data |

### Nested fields

| Field | Type | Description |
| --- | --- | --- |
| `service` | RECORD (`id`, `description`) | GCP service |
| `sku` | RECORD (`id`, `description`) | SKU info |
| `project` | RECORD | `project.id`, `number`, `name`, with `labels`, `ancestors` |
| `location` | RECORD | `location`, `region`, `zone` |
| `labels` / `tags` / `system_labels` | REPEATED RECORD | key/value labels, requires `UNNEST` |
| `usage` | RECORD | `usage.amount`, `quantity`, `unit`, `pricing_unit` |
| `cost` / `currency` | FLOAT / STRING | cost and currency |
| `credit` | RECORD | credits (commitment/promo, etc.) |
| `invoice` | RECORD | `invoice.month`, `invoice.publisher_type` |
| `adjustment_info` | RECORD | adjustment info |
| `price` | RECORD | `effective_price`, `list_price`, `pricing_unit_quantity`, etc. |

### Record expansions (schema snapshot)

| Record | Subfields |
| --- | --- |
| `usage` | `amount`, `unit`, `amount_in_pricing_units`, `pricing_unit` |
| `credits` | `name`, `amount`, `full_name`, `id`, `type` |
| `invoice` | `month`, `publisher_type` |
| `service` | `id`, `description` |
| `sku` | `id`, `description` |
| `project` | `id`, `number`, `name`, `labels`, `ancestry_numbers`, `ancestors` |
| `labels` | `key`, `value` |
| `system_labels` | `key`, `value` |
| `tags` | `key`, `value`, `inherited`, `namespace` |
| `location` | `location`, `country`, `region`, `zone` |
| `price` | `effective_price`, `tier_start_amount`, `unit`, `pricing_unit_quantity`, `list_price`, `effective_price_default`, `list_price_consumption_model` |
| `adjustment_info` | `id`, `description`, `mode`, `type` |
| `consumption_model` | `id`, `description` |

### Common flattened columns

| Field | Type | Description |
| --- | --- | --- |
| `billing_account_id` | STRING | billing account |
| `project.id` / `project.number` | STRING / STRING | project ID / number |
| `project.labels` | REPEATED RECORD | custom labels |
| `resource.name` / `resource.global_name` | STRING | resource path |
| `cost_type` | STRING | common values: `regular`, `credit`, `tax` |
| `cost_at_list` / `cost_at_effective_price_default` | FLOAT | list price level costs |
| `currency_conversion_rate` | FLOAT | exchange rate |
| `export_time` | TIMESTAMP | export time |

---

## Script to generate full schema

If you need to export all fields to JSON/CSV, use this script:

```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_20250301"
  "gcp-digital-bi:alibaba_cloud.monthly_20250801"
  "gcp-digital-bi:azure_billing.azure_billing_cost_20250701"
  "gcp-digital-bi:gcp_billing_v2.gcp_billing_export_v1_01A169_D3D9BF_FB82A2"
  "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
```

The exported JSON can be imported into Google Sheets or Data Catalog to build more detailed dictionaries.