# Alibaba Cloud Billing Data Guide

## Table Structure

### Legacy CN Tables (before 2025-12-01)
- **`daily_YYYYMMDD`**: One table per day (e.g., `daily_20251101`). Partition column: `partition_date`
- **`monthly_YYYYMM01`**: One table per month (e.g., `monthly_20251101`). Partition type: MONTH

### EN Tables (2025-12-01 onwards)
- **`daily_en_YYYYMMDD`**: One table per day with English field values (e.g., `daily_en_20251201`)
- **`monthly_en_YYYYMM01`**: One table per month with English field values (e.g., `monthly_en_20251201`)

> **Important**: 
- **Wildcard Warning**: `daily_*` and `monthly_*` match both CN and EN tables (e.g., `daily_en_...` matches `daily_*`).
  - To match **only CN tables**, use `daily_2*` / `monthly_2*` (matches years starting with 2) or `WHERE _TABLE_SUFFIX NOT LIKE 'en_%'`.
  - To match **only EN tables**, use `daily_en_*` / `monthly_en_*`.
- Account grain: When querying data, prioritize `owner_account_id`, which represents the actual account where transactions occur. `account_id` is the master account ID and is usually the same.
- Time range and query guidance: To query data before 2025-12-01, use legacy CN tables. To query data after 2025-12-01, use EN tables.

---

## Key Field Mapping (CN → EN)

| Category | Legacy CN | EN (2025-12) |
| --- | --- | --- |
| Account | `account` | `account_name` |
| Account | `owner_account` | `owner_account_name` |
| Org | `financial_unit` | `cost_center` |
| Product | `product` | `product_name` |
| Product | `product_detail_code` | `product_type` |
| Billing | `billing_period` | `billing_cycle` |
| Billing | `consumption_type` | `subscription_type` |
| Billing | `bill_type` | `billing_type` |
| Resource | `instance_nickname` | `instance_name` |
| Resource | `instance_tags` | `instance_tag` |
| Resource | `public_ip` | `internet_ip` |
| Resource | `private_ip` | `intranet_ip` |
| Resource | `availability_zone` | `zone` |
| Amount | `list_price` | `pretax_gross_amount` |
| Amount | `discount_amount` | `invoice_discount` |
| Amount | `coupon_deduction` | `deducted_by_coupons` |
| Amount | `pricing_currency` | `currency` |
| Time (daily) | `consumption_time` | `payment_time` |
| Time (daily) | `bill_start_time` | `usage_start_time` |
| Time (daily) | `bill_end_time` | `usage_end_time` |

---

## Key Fields

- **Account**: `account_id` (master account), `owner_account_id` (sub-account)
- **Organization**: `cost_center` (EN) / `financial_unit` (CN), `resource_group`
- **Cost**: `amount_after_discount` (net cost), `pretax_gross_amount` (list price)
- **Tags**: `instance_tag` (JSON string) - parse with `JSON_EXTRACT` or `JSON_VALUE`
- **Partition**: `partition_date` (DATE)

---

## owner_account_id info
```csv
owner_account_id,account_name,is_active,owner,description
5028760335873601,testing-infra-dev,true,manjunpeng@pingcap.com,Dev env
5092239846523038,dbaas-staging-dev-o11y,true,jinwei.zhang@pingcap.com,Staging env
5753857577068077,dbaas-staging-dev-nextgen-dataplane,true,dengshuan@pingcap.com,Staging env
5841052134823325,dbaas-staging-dev-nextgen,true,dengshuan@pingcap.com,Staging env
5654539846535456,dbaas-staging-dev-serverless,true,yangxin@pingcap.com,Staging env
5108739859105002,dbaas-prod-serverless,true,tangpeng@pingcap.com,Prod env
5480039859161595,dbaas-prod-o11y,true,tangpeng@pingcap.com,Prod env
5293340550807672,tidbcloud-marketplace-billing,true,tangpeng@pingcap.com,Prod env
5464160084398809,dbaas-prod-controlplane,true,tangpeng@pingcap.com,Prod env
5901860086223871,dbaas-prod-dataplane-001,true,tangpeng@pingcap.com,Prod env
```

---

## Example Queries

### Single-day cost (legacy CN)

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

### Monthly cost by account (legacy CN)

```sql
SELECT
  owner_account_id,
  ROUND(SUM(amount_after_discount), 2) AS net_cost
FROM `gcp-digital-bi.alibaba_cloud.monthly_20250801`
GROUP BY owner_account_id
ORDER BY net_cost DESC
LIMIT 10;
```

### Monthly cost by account (EN tables)

```sql
SELECT
  owner_account_id,
  account_name,
  ROUND(SUM(amount_after_discount), 2) AS net_cost
FROM `gcp-digital-bi.alibaba_cloud.monthly_en_20251201`
GROUP BY owner_account_id, account_name
ORDER BY net_cost DESC
LIMIT 10;
```

### Wildcard query for specific month (EN)

```sql
SELECT
  owner_account_id,
  ROUND(SUM(amount_after_discount), 2) AS net_cost
FROM `gcp-digital-bi.alibaba_cloud.monthly_en_*`
WHERE PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX, r'(\d{8})')) = DATE '2025-12-01'
GROUP BY owner_account_id;
```

---

## Field Value Samples (EN Tables)

### product_name Distribution (Top 25 by cost)

> **Note**: 以下金额为示例数据，仅用于说明字段格式和数据分布模式。

| Product | Cost (CNY) |
| --- | --- |
| Elastic Compute Service | 1,000.00 |
| Elastic Block Storage | 311.00 |
| Object Storage Service | 83.00 |
| css | 45.00 |
| Key Management Service | 24.00 |
| Server Load Balancer | 20.00 |
| ApsaraDB RDS | 19.00 |
| TransitRouter | 17.00 |
| Container Registry | 15.00 |
| NAT Gateway | 12.00 |
| PrivateLink | 12.00 |
| Container Service for Kubernetes | 10.00 |
| Cloud Data Transfer | 8.00 |
| Elastic IP Address | 6.00 |
| ApsaraMQ | 5.00 |
| Web Application Firewall | 3.00 |
| Simple Log Service | 1.00 |
| Data Management | 1.00 |
| Global Accelerator | 1.00 |
| Alibaba Cloud DNS PrivateZone | 1.00 |
| Tablestore | 1.00 |
| PolarDB | 1.00 |
| Function Compute | 1.00 |

### subscription_type Values

> **Note**: 以下金额为示例数据，仅用于说明字段格式和数据分布模式。

| Value | Description | Cost (CNY) |
| --- | --- | --- |
| `Pay-As-You-Go` | Usage-based billing | 1,000.00 |
| `Subscription` | Prepaid commitment | 13.00 |

### billing_type Values

> **Note**: 以下金额为示例数据，仅用于说明字段格式和数据分布模式。

| Value | Description | Cost (CNY) |
| --- | --- | --- |
| `Others` | General billing | 1,013.00 |

### region Distribution (by cost)

> **Note**: 以下金额为示例数据，仅用于说明字段格式和数据分布模式。

| Region | Cost (CNY) |
| --- | --- |
| Singapore | 1,000.00 |
| Japan (Tokyo) | 227.00 |
| Indonesia (Jakarta) | 203.00 |
| China (Hong Kong) | 168.00 |
| Mexico | 92.00 |
| (null - global) | 52.00 |
| US (Virginia) | 24.00 |
| Outside Chinese Mainland | 3.00 |
| US (Silicon Valley) | 1.00 |
| China (Shanghai) | 1.00 |
| China (Hangzhou) | 1.00 |
| Chinese Mainland | 1.00 |

---

## Usage Guidance

- **Daily tables**: Best for detailed investigations. Always filter on `partition_date`.
- **Monthly tables**: Best for budget/ledger checks. Use `amount_after_discount` as net cost.
- **Account grain**: `account_id` is master account, `owner_account_id` is sub-account.
- **Organization**: Prefer `cost_center` / `resource_group` for grouping.
- **Tags**: Stored as JSON in `instance_tag`; parse and standardize as needed.

---

## Schema References

- Legacy CN daily: `docs/schema-data-json/schema_gcp-digital-bi_alibaba_cloud_daily_20250301.json`
- Legacy CN monthly: `docs/schema-data-json/schema_gcp-digital-bi_alibaba_cloud_monthly_20250801.json`
- EN daily: `docs/schema-data-json/schema_gcp-digital-bi_alibaba_cloud_daily_en_20251201.json`
- EN monthly: `docs/schema-data-json/schema_gcp-digital-bi_alibaba_cloud_monthly_en_20251201.json`