# GCP Billing Data Guide

## Table Structure

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

### Storage
- Native tables with daily partitions (based on `usage_start_time`)
- Location: US multi-region
- Approximate size: ~326 million rows, ~300GB logical data

---

## Key Fields (Flat)

| Field | Type | Description |
| --- | --- | --- |
| `billing_account_id` | STRING | Billing account identifier |
| `cost` | FLOAT | **Primary cost field** |
| `currency` | STRING | Currency code (e.g., USD) |
| `cost_type` | STRING | Cost type: `regular`, `adjustment`, `rounding_error` |
| `cost_at_list` | FLOAT | List price cost |
| `usage_start_time` | TIMESTAMP | Usage start (partition key) |
| `usage_end_time` | TIMESTAMP | Usage end |
| `export_time` | TIMESTAMP | When data was exported |
| `currency_conversion_rate` | FLOAT | Exchange rate |

---

## Nested RECORD Fields (Detailed Structure)

### `service` RECORD
| Subfield | Type | Description |
| --- | --- | --- |
| `service.id` | STRING | Service identifier (e.g., `6F81-5844-456A`) |
| `service.description` | STRING | Service name (e.g., `Compute Engine`) |

### `sku` RECORD
| Subfield | Type | Description |
| --- | --- | --- |
| `sku.id` | STRING | SKU identifier |
| `sku.description` | STRING | SKU description |

### `project` RECORD
| Subfield | Type | Description |
| --- | --- | --- |
| `project.id` | STRING | Project ID |
| `project.number` | STRING | Project number |
| `project.name` | STRING | Project display name |
| `project.labels` | REPEATED RECORD | Project-level labels (`key`, `value`) |
| `project.ancestry_numbers` | STRING | Project ancestry path |
| `project.ancestors` | REPEATED RECORD | Ancestor resources |

**Example - Access project labels:**
```sql
SELECT
  project.id,
  pl.key,
  pl.value
FROM `gcp-digital-bi.gcp_billing_v2.gcp_billing_export_v1_01D088_8F9CF2_8AF1C6`,
  UNNEST(project.labels) AS pl
WHERE pl.key = 'environment'
```

### `location` RECORD
| Subfield | Type | Description |
| --- | --- | --- |
| `location.location` | STRING | Location identifier |
| `location.country` | STRING | Country code |
| `location.region` | STRING | Region (e.g., `asia-northeast1`) |
| `location.zone` | STRING | Zone (e.g., `asia-northeast1-a`) |

### `usage` RECORD
| Subfield | Type | Description |
| --- | --- | --- |
| `usage.amount` | FLOAT | Usage quantity |
| `usage.unit` | STRING | Usage unit |
| `usage.amount_in_pricing_units` | FLOAT | Amount in pricing units |
| `usage.pricing_unit` | STRING | Pricing unit |

### `credits` REPEATED RECORD
| Subfield | Type | Description |
| --- | --- | --- |
| `credits.name` | STRING | Credit name |
| `credits.amount` | FLOAT | Credit amount (**always negative**) |
| `credits.full_name` | STRING | Full credit name |
| `credits.id` | STRING | Credit identifier |
| `credits.type` | STRING | Credit type (see Field Value Samples) |

**Example - Aggregate credits:**
```sql
SELECT
  project.id,
  ROUND(SUM(cost), 2) AS gross_cost,
  ROUND(SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)), 2) AS total_credits,
  ROUND(SUM(cost) + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)), 2) AS net_cost
FROM `gcp-digital-bi.gcp_billing_v2.gcp_billing_export_v1_01D088_8F9CF2_8AF1C6`
WHERE usage_start_time >= TIMESTAMP('2025-12-01')
GROUP BY 1
ORDER BY net_cost DESC
```

### `labels` REPEATED RECORD
| Subfield | Type | Description |
| --- | --- | --- |
| `labels.key` | STRING | Label key |
| `labels.value` | STRING | Label value |

**Example - Filter by label:**
```sql
SELECT
  project.id,
  l.key,
  l.value,
  ROUND(SUM(cost), 2) AS cost
FROM `gcp-digital-bi.gcp_billing_v2.gcp_billing_export_v1_01D088_8F9CF2_8AF1C6`,
  UNNEST(labels) AS l
WHERE usage_start_time >= TIMESTAMP('2025-12-01')
  AND l.key = 'tenant'
GROUP BY 1, 2, 3
ORDER BY cost DESC
```

### `system_labels` REPEATED RECORD
| Subfield | Type | Description |
| --- | --- | --- |
| `system_labels.key` | STRING | System label key |
| `system_labels.value` | STRING | System label value |

### `tags` REPEATED RECORD
| Subfield | Type | Description |
| --- | --- | --- |
| `tags.key` | STRING | Tag key |
| `tags.value` | STRING | Tag value |
| `tags.inherited` | BOOLEAN | Whether tag is inherited |
| `tags.namespace` | STRING | Tag namespace |

### `price` RECORD
| Subfield | Type | Description |
| --- | --- | --- |
| `price.effective_price` | FLOAT | Effective price |
| `price.tier_start_amount` | FLOAT | Tier start amount |
| `price.unit` | STRING | Price unit |
| `price.pricing_unit_quantity` | FLOAT | Pricing unit quantity |
| `price.list_price` | FLOAT | List price |

### `invoice` RECORD
| Subfield | Type | Description |
| --- | --- | --- |
| `invoice.month` | STRING | Invoice month (YYYYMM) |
| `invoice.publisher_type` | STRING | Publisher type |

### `adjustment_info` RECORD
| Subfield | Type | Description |
| --- | --- | --- |
| `adjustment_info.id` | STRING | Adjustment ID |
| `adjustment_info.description` | STRING | Adjustment description |
| `adjustment_info.mode` | STRING | Adjustment mode |
| `adjustment_info.type` | STRING | Adjustment type |

---

## Field Value Samples

### cost_type Values
| Value | Description | Cost (USD) |
| --- | --- | --- |
| `regular` | Normal usage charges | 10,000.00 |
| `adjustment` | Invoice adjustments | 113.00 |
| `rounding_error` | Rounding adjustments | ~0.00 |

### credits.type Values

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

| Value | Description | Amount (USD) |
| --- | --- | --- |
| `FEE_UTILIZATION_OFFSET` | Fee utilization offset credits | -1,000.00 |
| `SUBSCRIPTION_BENEFIT` | Subscription benefit credits | -520.00 |
| `DISCOUNT` | Discount credits | -56.00 |

> Note: Credit amounts are **negative**. Add to cost for net calculation.

### service.description Distribution (Top 25 by cost)

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

| Service | Cost (USD) |
| --- | --- |
| Compute Engine | 1,000.00 |
| Support | 169.00 |
| Networking | 78.00 |
| Cloud Storage | 61.00 |
| BigQuery | 30.00 |
| Kubernetes Engine | 24.00 |
| Security Command Center | 17.00 |
| Invoice | 16.00 |
| Artifact Registry | 10.00 |
| Cloud Logging | 9.00 |
| Cloud Monitoring | 3.00 |
| Cloud SQL | 2.00 |
| Cloud Composer | 2.00 |
| Cloud DNS | 1.00 |
| Cloud Run | 1.00 |
| Dataplex | 1.00 |
| Cloud Pub/Sub | 1.00 |
| Gemini API | 1.00 |
| Translate | 1.00 |
| VM Manager | 1.00 |

### location.region Distribution (Top 15 by cost)

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

| Region | Cost (USD) |
| --- | --- |
| asia-northeast1 | 1,000.00 |
| (null - global) | 342.00 |
| us-west1 | 185.00 |
| us-central1 | 120.00 |
| asia-east2 | 104.00 |
| asia-east1 | 55.00 |
| asia-northeast2 | 29.00 |
| asia-southeast1 | 22.00 |
| us-east5 | 16.00 |
| us-west2 | 14.00 |
| us-east1 | 12.00 |
| europe-west3 | 10.00 |
| us-east4 | 8.00 |
| asia-south1 | 8.00 |
| asia-southeast2 | 1.00 |

### Common Label Keys
| Label Key | Usage | Description |
| --- | --- | --- |
| `goog-k8s-cluster-location` | GKE | Kubernetes cluster location |
| `goog-k8s-cluster-name` | GKE | Kubernetes cluster name |
| `goog-k8s-node-pool-name` | GKE | Node pool name |
| `goog-k8s-namespace` | GKE | Kubernetes namespace |
| `k8s-namespace` | GKE | Kubernetes namespace (alias) |
| `tenant` | Custom | Tenant identifier |
| `project` | Custom | Project attribution |
| `usedby` | Custom | User/team attribution |
| `environment` | Custom | Environment (prod/dev/staging) |
| `env` | Custom | Environment shorthand |
| `cluster` | Custom | Cluster identifier |
| `component` | Custom | Component name |
| `servicetype` | Custom | Service type classification |

---

## Net Cost Calculation

GCP `credits.amount` is **negative**. To calculate net cost:

```sql
SELECT
  project.id AS project_id,
  ROUND(SUM(cost) + SUM(IFNULL((SELECT SUM(c.amount) FROM UNNEST(credits) c), 0)), 2) AS net_cost
FROM `gcp-digital-bi.gcp_billing_v2.gcp_billing_export_v1_01D088_8F9CF2_8AF1C6`
WHERE usage_start_time >= TIMESTAMP('2025-11-20')
GROUP BY project_id
ORDER BY net_cost DESC;
```

---

## Example Queries

### Monthly cost (last 90 days, both tables)

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

### Cost by service

```sql
SELECT
  service.description AS service_name,
  ROUND(SUM(cost), 2) AS cost
FROM `gcp-digital-bi.gcp_billing_v2.gcp_billing_export_v1_01D088_8F9CF2_8AF1C6`
WHERE usage_start_time >= TIMESTAMP('2025-12-01')
GROUP BY service_name
ORDER BY cost DESC
LIMIT 10;
```

### Cost by project with labels

```sql
SELECT
  project.id AS project_id,
  project.name AS project_name,
  ROUND(SUM(cost), 2) AS cost
FROM `gcp-digital-bi.gcp_billing_v2.gcp_billing_export_v1_01D088_8F9CF2_8AF1C6`
WHERE usage_start_time >= TIMESTAMP('2025-12-01')
GROUP BY project_id, project_name
ORDER BY cost DESC
LIMIT 10;
```

### Extract resource labels

```sql
SELECT
  project.id AS project_id,
  label.key,
  label.value,
  ROUND(SUM(cost), 2) AS cost
FROM `gcp-digital-bi.gcp_billing_v2.gcp_billing_export_v1_01D088_8F9CF2_8AF1C6`,
  UNNEST(labels) AS label
WHERE usage_start_time >= TIMESTAMP('2025-12-01')
  AND label.key = 'env'
GROUP BY project_id, label.key, label.value
ORDER BY cost DESC
LIMIT 10;
```

---

## Usage Guidance

- **Partition filter**: Always filter on `usage_start_time` or `_PARTITIONDATE` to avoid scanning all partitions.
- **Credits**: Remember to add `credits.amount` (negative) to `cost` for net cost.
- **Nested fields**: `service`, `sku`, `project`, `labels`, `system_labels`, `tags` are nested or repeated. Use `UNNEST` when needed.
- **Table selection**: Use the old table for data before 2025-11-20, new table for data after.

## Schema Reference

- `docs/schema-data-json/schema_gcp-digital-bi_gcp_billing_v2_gcp_billing_export_v1_01A169_D3D9BF_FB82A2.json`