# AWS Billing Dataset Inventory (`gcp-digital-bi.aws_billing`)

_Last refreshed from `bq show` metadata on 2025-11-17T00:31:37Z (table `lastModifiedTime`)._

## Refresh checklist
- List tables: `bq ls --format=prettyjson gcp-digital-bi:aws_billing > demo/aws_billing_tables.json`.
- For every new billing month, capture metadata with `bq show --format=prettyjson gcp-digital-bi:aws_billing.aws_billing_<yyyymm01>` and append the summary here.
- Regenerate the schema table via the helper snippet below after saving `bq show ...` JSON locally so queries always rely on the latest schema snapshot.

## Dataset overview
- Project: `gcp-digital-bi`
- Dataset: `aws_billing`
- Location: `us-west2` (matches the tables below)
- Source: AWS Cost and Usage Reports (CUR 2.0) exported daily for every AWS account, one standalone BigQuery table per billing month.
- Table naming: `aws_billing_YYYYMM01` (the suffix equals the first day of the month). Example: the November 2025 data sits in `aws_billing_20251101`.
- Access pattern: treat each table independently; no date partitioning because AWS provides pre-aggregated monthly files.
- Schema: CUR 2.0 delivers the exact same columns every month, so downstream SQL can reuse the November schema snapshot unless AWS releases a new CUR version.

## Table inventory (snapshot)
| Table | Billing month | Created (UTC) | Last modified (UTC) | Approx. rows | Active logical bytes | Notes |
| --- | --- | --- | --- | --- | --- | --- |
| `aws_billing_20251101` | 2025-11 | 2025-11-02T00:31:11Z | 2025-11-17T00:31:37Z | 68,959,885 | 97,281,398,253 (~90.60 GiB) | Location `us-west2`; active physical bytes 33,964,735,330 (~31.63 GiB).
| `aws_billing_20251001` | 2025-10 | 2025-10-24T07:49:02Z | — | — | — | Run `bq show --format=prettyjson gcp-digital-bi:aws_billing.aws_billing_20251001` to capture metrics (schema expected to match November).

## Table detail: `aws_billing_20251001` (October 2025)
- Snapshot not yet captured in this document; mirror the steps in the refresh checklist when the historical month needs to be queried again.
- Expect identical schema to November 2025 because AWS CUR delivers the same columns each month; reuse the flattened schema below until a schema change is observed.

## Table detail: `aws_billing_20251101` (November 2025)
### Metadata
- Location: `us-west2`
- Rows: 68,959,885
- Active logical bytes: 97,281,398,253 (~90.60 GiB)
- Active physical bytes: 33,964,735,330 (~31.63 GiB)
- Time-travel physical bytes: 28,381,674,005 (~26.45 GiB)
- Export window: 2025-11 billing period (matching AWS CUR `aws-billing-detailed-line-items-with-resources-and-tags` feed)

### Schema (flattened view)
| Field | Type | Mode | Description |
| --- | --- | --- | --- |
| bill_bill_type | STRING | NULLABLE |  |
| bill_billing_entity | STRING | NULLABLE |  |
| bill_billing_period_end_date | TIMESTAMP | NULLABLE |  |
| bill_billing_period_start_date | TIMESTAMP | NULLABLE |  |
| bill_invoice_id | STRING | NULLABLE |  |
| bill_invoicing_entity | STRING | NULLABLE |  |
| bill_payer_account_id | STRING | NULLABLE |  |
| bill_payer_account_name | STRING | NULLABLE |  |
| cost_category | RECORD | NULLABLE |  |
| cost_category.key_value | RECORD | REPEATED |  |
| cost_category.key_value.key | STRING | REQUIRED |  |
| cost_category.key_value.value | STRING | NULLABLE |  |
| discount | RECORD | NULLABLE |  |
| discount.key_value | RECORD | REPEATED |  |
| discount.key_value.key | STRING | REQUIRED |  |
| discount.key_value.value | FLOAT | NULLABLE |  |
| discount_bundled_discount | FLOAT | NULLABLE |  |
| discount_total_discount | FLOAT | NULLABLE |  |
| identity_line_item_id | STRING | NULLABLE |  |
| identity_time_interval | STRING | NULLABLE |  |
| line_item_availability_zone | STRING | NULLABLE |  |
| line_item_blended_cost | FLOAT | NULLABLE |  |
| line_item_blended_rate | STRING | NULLABLE |  |
| line_item_currency_code | STRING | NULLABLE |  |
| line_item_legal_entity | STRING | NULLABLE |  |
| line_item_line_item_description | STRING | NULLABLE |  |
| line_item_line_item_type | STRING | NULLABLE |  |
| line_item_net_unblended_cost | FLOAT | NULLABLE |  |
| line_item_net_unblended_rate | STRING | NULLABLE |  |
| line_item_normalization_factor | FLOAT | NULLABLE |  |
| line_item_normalized_usage_amount | FLOAT | NULLABLE |  |
| line_item_operation | STRING | NULLABLE |  |
| line_item_product_code | STRING | NULLABLE |  |
| line_item_resource_id | STRING | NULLABLE |  |
| line_item_tax_type | STRING | NULLABLE |  |
| line_item_unblended_cost | FLOAT | NULLABLE |  |
| line_item_unblended_rate | STRING | NULLABLE |  |
| line_item_usage_account_id | STRING | NULLABLE |  |
| line_item_usage_account_name | STRING | NULLABLE |  |
| line_item_usage_amount | FLOAT | NULLABLE |  |
| line_item_usage_end_date | TIMESTAMP | NULLABLE |  |
| line_item_usage_start_date | TIMESTAMP | NULLABLE |  |
| line_item_usage_type | STRING | NULLABLE |  |
| pricing_currency | STRING | NULLABLE |  |
| pricing_lease_contract_length | STRING | NULLABLE |  |
| pricing_offering_class | STRING | NULLABLE |  |
| pricing_public_on_demand_cost | FLOAT | NULLABLE |  |
| pricing_public_on_demand_rate | STRING | NULLABLE |  |
| pricing_purchase_option | STRING | NULLABLE |  |
| pricing_rate_code | STRING | NULLABLE |  |
| pricing_rate_id | STRING | NULLABLE |  |
| pricing_term | STRING | NULLABLE |  |
| pricing_unit | STRING | NULLABLE |  |
| product | RECORD | NULLABLE |  |
| product.key_value | RECORD | REPEATED |  |
| product.key_value.key | STRING | REQUIRED |  |
| product.key_value.value | STRING | NULLABLE |  |
| product_comment | STRING | NULLABLE |  |
| product_fee_code | STRING | NULLABLE |  |
| product_fee_description | STRING | NULLABLE |  |
| product_from_location | STRING | NULLABLE |  |
| product_from_location_type | STRING | NULLABLE |  |
| product_from_region_code | STRING | NULLABLE |  |
| product_instance_family | STRING | NULLABLE |  |
| product_instance_type | STRING | NULLABLE |  |
| product_instancesku | STRING | NULLABLE |  |
| product_location | STRING | NULLABLE |  |
| product_location_type | STRING | NULLABLE |  |
| product_operation | STRING | NULLABLE |  |
| product_pricing_unit | STRING | NULLABLE |  |
| product_product_family | STRING | NULLABLE |  |
| product_region_code | STRING | NULLABLE |  |
| product_servicecode | STRING | NULLABLE |  |
| product_sku | STRING | NULLABLE |  |
| product_to_location | STRING | NULLABLE |  |
| product_to_location_type | STRING | NULLABLE |  |
| product_to_region_code | STRING | NULLABLE |  |
| product_usagetype | STRING | NULLABLE |  |
| reservation_amortized_upfront_cost_for_usage | FLOAT | NULLABLE |  |
| reservation_amortized_upfront_fee_for_billing_period | FLOAT | NULLABLE |  |
| reservation_availability_zone | STRING | NULLABLE |  |
| reservation_effective_cost | FLOAT | NULLABLE |  |
| reservation_end_time | STRING | NULLABLE |  |
| reservation_modification_status | STRING | NULLABLE |  |
| reservation_net_amortized_upfront_cost_for_usage | FLOAT | NULLABLE |  |
| reservation_net_amortized_upfront_fee_for_billing_period | FLOAT | NULLABLE |  |
| reservation_net_effective_cost | FLOAT | NULLABLE |  |
| reservation_net_recurring_fee_for_usage | FLOAT | NULLABLE |  |
| reservation_net_unused_amortized_upfront_fee_for_billing_period | FLOAT | NULLABLE |  |
| reservation_net_unused_recurring_fee | FLOAT | NULLABLE |  |
| reservation_net_upfront_value | FLOAT | NULLABLE |  |
| reservation_normalized_units_per_reservation | STRING | NULLABLE |  |
| reservation_number_of_reservations | STRING | NULLABLE |  |
| reservation_recurring_fee_for_usage | FLOAT | NULLABLE |  |
| reservation_reservation_a_r_n | STRING | NULLABLE |  |
| reservation_start_time | STRING | NULLABLE |  |
| reservation_subscription_id | STRING | NULLABLE |  |
| reservation_total_reserved_normalized_units | STRING | NULLABLE |  |
| reservation_total_reserved_units | STRING | NULLABLE |  |
| reservation_units_per_reservation | STRING | NULLABLE |  |
| reservation_unused_amortized_upfront_fee_for_billing_period | FLOAT | NULLABLE |  |
| reservation_unused_normalized_unit_quantity | FLOAT | NULLABLE |  |
| reservation_unused_quantity | FLOAT | NULLABLE |  |
| reservation_unused_recurring_fee | FLOAT | NULLABLE |  |
| reservation_upfront_value | FLOAT | NULLABLE |  |
| resource_tags | RECORD | NULLABLE |  |
| resource_tags.key_value | RECORD | REPEATED |  |
| resource_tags.key_value.key | STRING | REQUIRED |  |
| resource_tags.key_value.value | STRING | NULLABLE |  |
| savings_plan_amortized_upfront_commitment_for_billing_period | FLOAT | NULLABLE |  |
| savings_plan_end_time | STRING | NULLABLE |  |
| savings_plan_instance_type_family | STRING | NULLABLE |  |
| savings_plan_net_amortized_upfront_commitment_for_billing_period | FLOAT | NULLABLE |  |
| savings_plan_net_recurring_commitment_for_billing_period | FLOAT | NULLABLE |  |
| savings_plan_net_savings_plan_effective_cost | FLOAT | NULLABLE |  |
| savings_plan_offering_type | STRING | NULLABLE |  |
| savings_plan_payment_option | STRING | NULLABLE |  |
| savings_plan_purchase_term | STRING | NULLABLE |  |
| savings_plan_recurring_commitment_for_billing_period | FLOAT | NULLABLE |  |
| savings_plan_region | STRING | NULLABLE |  |
| savings_plan_savings_plan_a_r_n | STRING | NULLABLE |  |
| savings_plan_savings_plan_effective_cost | FLOAT | NULLABLE |  |
| savings_plan_savings_plan_rate | FLOAT | NULLABLE |  |
| savings_plan_start_time | STRING | NULLABLE |  |
| savings_plan_total_commitment_to_date | FLOAT | NULLABLE |  |
| savings_plan_used_commitment | FLOAT | NULLABLE |  |
| split_line_item_actual_usage | FLOAT | NULLABLE |  |
| split_line_item_net_split_cost | FLOAT | NULLABLE |  |
| split_line_item_net_unused_cost | FLOAT | NULLABLE |  |
| split_line_item_parent_resource_id | STRING | NULLABLE |  |
| split_line_item_public_on_demand_split_cost | FLOAT | NULLABLE |  |
| split_line_item_public_on_demand_unused_cost | FLOAT | NULLABLE |  |
| split_line_item_reserved_usage | FLOAT | NULLABLE |  |
| split_line_item_split_cost | FLOAT | NULLABLE |  |
| split_line_item_split_usage | FLOAT | NULLABLE |  |
| split_line_item_split_usage_ratio | FLOAT | NULLABLE |  |
| split_line_item_unused_cost | FLOAT | NULLABLE |  |
### Notes on nested fields
- `cost_category`, `discount`, and `pricing.term_attributes` are `RECORD` columns with repeated `key_value` arrays; query them with `LEFT JOIN UNNEST(table.cost_category.key_value) AS kv` to surface each key-value pair.
- `identity_line_item_id` plus `line_item_line_item_type` uniquely identify each row inside the AWS CUR; use them when deduplicating re-imports.
- Savings plan and reservation metrics surface twice: look at `reservation_*`/`savings_plan_*` for applied benefit tracking and `line_item_*` for actual AWS charges.

### Command reference
```bash
# List tables in the dataset
bq ls --format=prettyjson gcp-digital-bi:aws_billing

# Inspect a specific month
bq show --format=prettyjson gcp-digital-bi:aws_billing.aws_billing_20251101 > demo/aws_billing_20251101.json
```

### Helper snippet to regenerate the schema table
```python
import json, sys
from pathlib import Path

raw = json.loads(Path(sys.argv[1]).read_text())
fields = raw["schema"]["fields"]

lines = ["| Field | Type | Mode | Description |", "| --- | --- | --- | --- |"]

def flatten(items, prefix=""):
    for item in items:
        name = prefix + item["name"]
        lines.append(
            f"| {name} | {item['type']} | {item.get('mode', '')} | {(item.get('description') or '').replace(chr(10), ' ')} |"
        )
        if item.get("fields"):
            flatten(item["fields"], prefix=f"{name}.")

flatten(fields)
Path(sys.argv[1].replace('.json', '_schema.md')).write_text("\n".join(lines))
```

## Future updates
- After BigQuery loads the next month, append it to the inventory table and capture its metadata immediately (before time-travel bytes expire).
- If AWS introduces/renames columns, rerun the helper snippet above and drop a note here so downstream SQL authors can adjust.
- Keep this document under version control so historical schema differences remain discoverable during investigations.