# Azure Billing Data Guide

## Table Structure

- **Table naming**: `azure_billing_cost_YYYYMM01` (e.g., `azure_billing_cost_20250701`)
- **Storage**: Native tables, one table per month
- **Location**: us-west2
- **Deprecated tables**: `azure_billing_cost_us_*` and `azure_billing_cost_cn_*` (not updated since 2025-08)

---

## Complete Field Reference

### Billing Hierarchy Fields
| Field | Type | Description |
| --- | --- | --- |
| `invoiceId` | STRING | Invoice identifier |
| `previousInvoiceId` | STRING | Previous invoice reference |
| `billingAccountId` | STRING | Billing account ID |
| `billingAccountName` | STRING | Billing account name |
| `billingProfileId` | STRING | Billing profile ID |
| `billingProfileName` | STRING | Billing profile name |
| `invoiceSectionId` | STRING | Invoice section ID |
| `invoiceSectionName` | STRING | Invoice section name |
| `costCenter` | STRING | Cost center for allocation |
| `costAllocationRuleName` | STRING | Cost allocation rule applied |

### Time Fields
| Field | Type | Description |
| --- | --- | --- |
| `date` | DATE | Daily grain (primary time field) |
| `billingPeriodStartDate` | STRING | Billing period start |
| `billingPeriodEndDate` | STRING | Billing period end |
| `servicePeriodStartDate` | DATE | Service usage start |
| `servicePeriodEndDate` | DATE | Service usage end |
| `exchangeRateDate` | DATE | Date of exchange rate |

### Service/Product Fields
| Field | Type | Description |
| --- | --- | --- |
| `serviceFamily` | STRING | Service category (Compute, Networking, Storage, etc.) |
| `consumedService` | STRING | Azure resource provider (e.g., `Microsoft.Compute`) |
| `ProductId` | STRING | Product identifier |
| `ProductName` | STRING | Product display name |
| `productOrderId` | STRING | Product order ID |
| `productOrderName` | STRING | Product order name |
| `meterId` | STRING | Meter identifier |
| `meterName` | STRING | Meter display name |
| `meterCategory` | STRING | Meter category (e.g., Virtual Machines, Storage) |
| `meterSubCategory` | STRING | Meter subcategory |
| `meterRegion` | STRING | Meter region |

### Resource Fields
| Field | Type | Description |
| --- | --- | --- |
| `SubscriptionId` | STRING | Azure subscription ID |
| `subscriptionName` | STRING | Subscription display name |
| `resourceGroupName` | STRING | Resource group name |
| `ResourceId` | STRING | Full Azure resource ID |
| `resourceLocation` | STRING | Resource deployment location |
| `location` | STRING | Location display name |

### Cost & Pricing Fields
| Field | Type | Description |
| --- | --- | --- |
| `costInUsd` | FLOAT | **Primary cost field** - Cost in USD |
| `costInBillingCurrency` | FLOAT | Cost in billing currency |
| `costInPricingCurrency` | FLOAT | Cost in pricing currency |
| `paygCostInUsd` | FLOAT | Pay-as-you-go equivalent cost in USD |
| `paygCostInBillingCurrency` | FLOAT | Pay-as-you-go equivalent in billing currency |
| `quantity` | FLOAT | Usage quantity |
| `unitOfMeasure` | STRING | Unit of measurement |
| `unitPrice` | FLOAT | Unit price |
| `effectivePrice` | FLOAT | Effective (actual) price paid |
| `PayGPrice` | FLOAT | Pay-as-you-go list price |
| `billingCurrency` | STRING | Billing currency code |
| `pricingCurrency` | STRING | Pricing currency code |
| `exchangeRatePricingToBilling` | INTEGER | Exchange rate |

### Classification Fields
| Field | Type | Description |
| --- | --- | --- |
| `chargeType` | STRING | Charge type (Usage, Purchase, Refund) |
| `pricingModel` | STRING | Pricing model (OnDemand, Reservation, Spot, SavingsPlan) |
| `frequency` | STRING | Billing frequency |
| `term` | STRING | Commitment term |
| `publisherType` | STRING | Publisher type (Microsoft, Marketplace) |
| `publisherId` | STRING | Publisher ID |
| `publisherName` | STRING | Publisher name |
| `isAzureCreditEligible` | BOOLEAN | Eligible for Azure credits |

### Reservation/Benefit Fields
| Field | Type | Description |
| --- | --- | --- |
| `reservationId` | STRING | Reservation ID |
| `reservationName` | STRING | Reservation name |
| `benefitId` | STRING | Benefit ID (savings plan, etc.) |
| `benefitName` | STRING | Benefit name |

### Reseller Fields
| Field | Type | Description |
| --- | --- | --- |
| `resellerName` | STRING | Reseller name |
| `resellerMpnId` | STRING | Reseller MPN ID |

### Tags & Attribution
| Field | Type | Description |
| --- | --- | --- |
| `tags` | STRING | JSON string of resource tags |
| `provider` | STRING | Cloud provider identifier |
| `tenant` | STRING | Tenant/team identifier |
| `usedby` | STRING | User/team attribution |

### Additional Info
| Field | Type | Description |
| --- | --- | --- |
| `serviceInfo1` | STRING | Additional service info |
| `serviceInfo2` | STRING | Additional service info |
| `additionalInfo` | STRING | JSON with extra metadata |

---

## Field Value Samples

### chargeType Values
| Value | Description |
| --- | --- |
| `Usage` | Regular resource usage charges |

> Note: Other possible values include `Purchase`, `Refund`, `UnusedReservation`, `UnusedSavingsPlan` - check your data for actual values.

### pricingModel Values
| Value | Description |
| --- | --- |
| `OnDemand` | Pay-as-you-go pricing |

> Note: Other possible values include `Reservation`, `SavingsPlan`, `Spot`.

### serviceFamily Distribution (by cost)

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

| serviceFamily | Cost (USD) |
| --- | --- |
| Compute | 1,000.00 |
| Networking | 840.00 |
| Storage | 420.00 |
| Containers | 70.00 |
| Security | 26.00 |
| Web | 11.00 |
| Databases | 10.00 |
| Developer Tools | 2.00 |
| AI + Machine Learning | 1.00 |
| Management and Governance | 0.00 |
| Analytics | 0.00 |

### meterCategory Distribution (Top 15 by cost)

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

| meterCategory | Cost (USD) |
| --- | --- |
| Virtual Machines | 1,000.00 |
| NAT Gateway | 645.00 |
| Storage | 483.00 |
| Application Gateway | 160.00 |
| Azure Kubernetes Service | 157.00 |
| Virtual Network | 100.00 |
| Container Registry | 80.00 |
| Load Balancer | 61.00 |
| Microsoft Defender for Cloud | 30.00 |
| Azure Cognitive Search | 12.00 |
| Azure Database for MySQL | 12.00 |
| Azure Grafana Service | 3.00 |
| Azure DNS | 2.00 |
| VPN Gateway | 1.00 |
| Bandwidth | 1.00 |

### consumedService Distribution (Top 15 by cost)

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

| consumedService | Cost (USD) |
| --- | --- |
| Microsoft.Compute | 1,000.00 |
| microsoft.network | 807.00 |
| Microsoft.Storage | 267.00 |
| Microsoft.ContainerService | 131.00 |
| Microsoft.ContainerRegistry | 67.00 |
| Microsoft.Search | 10.00 |
| Microsoft.DBforMySQL | 8.00 |
| Microsoft.DBForMySQL | 2.00 |
| MICROSOFT.DASHBOARD | 2.00 |
| Microsoft.Network | 2.00 |
| Virtual Network | 1.00 |
| Microsoft.DataFactory | 1.00 |
| Microsoft.CognitiveServices | 1.00 |

> Note: consumedService values can have inconsistent casing (e.g., `Microsoft.Network` vs `microsoft.network`). Consider using `UPPER()` or `LOWER()` for grouping.

### location Distribution (by cost)

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

| location | Cost (USD) |
| --- | --- |
| US East 2 | 1,000.00 |
| AP Southeast | 575.00 |
| US East | 525.00 |
| JA East | 210.00 |
| EU West | 161.00 |
| JA West | 13.00 |
| global | 2.00 |
| US West 2 | 1.00 |

---

## Example Queries

### Monthly cost summary

```sql
SELECT
  DATE_TRUNC(date, MONTH) AS month,
  ROUND(SUM(costInUsd), 2) AS cost_usd
FROM `gcp-digital-bi.azure_billing.azure_billing_cost_20250701`
GROUP BY month;
```

### Cost by service

```sql
SELECT
  consumedService,
  ROUND(SUM(costInUsd), 2) AS cost_usd
FROM `gcp-digital-bi.azure_billing.azure_billing_cost_20250701`
GROUP BY consumedService
ORDER BY cost_usd DESC
LIMIT 10;
```

### Cost by pricing model

```sql
SELECT
  pricingModel,
  ROUND(SUM(costInUsd), 2) AS cost_usd
FROM `gcp-digital-bi.azure_billing.azure_billing_cost_20250701`
GROUP BY pricingModel
ORDER BY cost_usd DESC;
```

### Extract tag values

```sql
SELECT
  JSON_VALUE(tags, '$.environment') AS environment,
  ROUND(SUM(costInUsd), 2) AS cost_usd
FROM `gcp-digital-bi.azure_billing.azure_billing_cost_20250701`
WHERE JSON_VALUE(tags, '$.environment') IS NOT NULL
GROUP BY environment
ORDER BY cost_usd DESC;
```

---

## Usage Guidance

- **Date filtering**: `date` is daily grain. Filter by date range to avoid full scans.
- **Cost metric**: Use `costInUsd` as the primary cost field.
- **Charge type**: Use `chargeType` / `pricingModel` to split cost types (usage, purchase, refund, etc.).
- **Tags**: `tags` is a JSON string. Use `JSON_VALUE(tags, '$.key')` to extract values.
- **Multi-month**: Build dynamic unions via `INFORMATION_SCHEMA.TABLES` or use multiple table references.

## Schema Reference

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