# AWS CUR Field Value Samples

This file records observed values and sample queries for key AWS Cost & Usage Report (CUR) fields to support SQL authoring. All data comes from the `gcp-digital-bi` project:

- CUR 1.0: `aws_prod_billing.aws_prod_billing_data` (region `us-west2`), sampled from Hive partitions `year=2025, month=12`
- CUR 1.0 non-prod tag columns: `INFORMATION_SCHEMA.COLUMNS` in `aws_nonprod_billing`.
- CUR 2.0: `aws_billing_no_payer.aws_billing_20251201` (region `us-west2`), filtered with `line_item_usage_start_date >= '2025-10-01'`.

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

> Tip: when querying CUR 1.0 external tables, always filter both `year` and `month` to avoid scanning all Parquet files.

---

## CUR 1.0 (`aws_prod_billing`)

### Resource tag columns

`aws_prod_billing.aws_prod_billing_data` (prod account `380838443567`) tag columns (flattened to columns):

```
resource_tags_user_cluster
resource_tags_user_component
resource_tags_user_env
resource_tags_user_environment
resource_tags_user_kubernetes_io_service_name
resource_tags_user_name
resource_tags_user_project
resource_tags_user_servicetype
resource_tags_user_tenant
resource_tags_user_usedby
```

Additional tag columns found in `aws_nonprod_billing`: `resource_tags_user_application`, `resource_tags_user_department`, `resource_tags_user_owner`.

### Top 10 tag values (2025-12)

Query sample:

```sql
WITH base AS (
  SELECT resource_tags_user_cluster, resource_tags_user_component, resource_tags_user_env,
         resource_tags_user_environment, resource_tags_user_kubernetes_io_service_name,
         resource_tags_user_name, resource_tags_user_project, resource_tags_user_servicetype,
         resource_tags_user_tenant, resource_tags_user_usedby
  FROM `gcp-digital-bi.aws_prod_billing.aws_prod_billing_data`
  WHERE year = 2025 AND month = 12
),
kv AS (
  SELECT tag_key, tag_value
  FROM base
  UNPIVOT(tag_value FOR tag_key IN (
    resource_tags_user_cluster, resource_tags_user_component, resource_tags_user_env,
    resource_tags_user_environment, resource_tags_user_kubernetes_io_service_name,
    resource_tags_user_name, resource_tags_user_project, resource_tags_user_servicetype,
    resource_tags_user_tenant, resource_tags_user_usedby
  ))
)
SELECT tag_key, tag_value, COUNT(*) AS row_count
FROM kv
WHERE tag_value IS NOT NULL AND tag_value != ''
GROUP BY 1, 2
QUALIFY ROW_NUMBER() OVER (PARTITION BY tag_key ORDER BY row_count DESC) <= 10
ORDER BY tag_key, row_count DESC;
```

Summary of results (Top 10 by row count):

- `resource_tags_user_cluster`: `admin`, `k8s-infra`, many numeric-named clusters (e.g. `1379661944643944089`, `1379661944646167822`).
- `resource_tags_user_component`: `dbaas-control-plane-seed`, `dbaas-control-plane-shoot`, `backup`, `api-gateway`, `tikv`, `compute/tidb`, `data-plane/sc/default`, `pd`, `control-plane/critical-services`.
- `resource_tags_user_environment`: `prod` (dominant), `dev`, `eksctl`.
- `resource_tags_user_env`: mostly `prod`.
- `resource_tags_user_kubernetes_io_service_name`: `tidb-admin/dbaas-proxy`, `monitoring/vector`, `regional-server-system/regional-server`, `tidb-admin/dataflow-agent`, `kube-system/vpn-shoot`, `kube-system/ingress-*`.
- `resource_tags_user_name`: many TiDB Cloud resource names, `eksctl-* NATGateway`, `prod-seed-*` nodes.
- `resource_tags_user_project`: `admin` plus multiple long integer IDs (e.g. `1372813089206361325`, `1372813089190401302`).
- `resource_tags_user_servicetype`: `dedicated`, `serverless`, `nextgen`, `devtier`, `platform`, `o11y`, `ecosystem`, `mgmt`.
- `resource_tags_user_tenant`: `admin` plus a series of long integer tenant IDs.
- `resource_tags_user_usedby`: `dbaas-control-plane-seed/shoot/base`, `customer-tidb`, multiple `prod-*` cluster identifiers.

### Top N key field values (2025-12)

Query sample:

```sql
DECLARE target_year INT64 DEFAULT 2025;
DECLARE target_month INT64 DEFAULT 12;
WITH base AS (
  SELECT line_item_line_item_type, line_item_usage_type, line_item_operation,
         product_product_family, product_product_name, line_item_unblended_cost
  FROM `gcp-digital-bi.aws_prod_billing.aws_prod_billing_data`
  WHERE year = target_year AND month = target_month
),
unioned AS (
  SELECT 'line_item_line_item_type' AS field, line_item_line_item_type AS value,
         ROUND(SUM(line_item_unblended_cost), 2) AS cost_usd, COUNT(*) AS row_count
  FROM base GROUP BY 1, 2
  UNION ALL
  SELECT 'line_item_usage_type', line_item_usage_type, ROUND(SUM(line_item_unblended_cost), 2), COUNT(*) FROM base GROUP BY 1, 2
  UNION ALL
  SELECT 'line_item_operation', line_item_operation, ROUND(SUM(line_item_unblended_cost), 2), COUNT(*) FROM base GROUP BY 1, 2
  UNION ALL
  SELECT 'product_product_family', product_product_family, ROUND(SUM(line_item_unblended_cost), 2), COUNT(*) FROM base GROUP BY 1, 2
  UNION ALL
  SELECT 'product_product_name', product_product_name, ROUND(SUM(line_item_unblended_cost), 2), COUNT(*) FROM base GROUP BY 1, 2
)
SELECT *
FROM unioned
QUALIFY ROW_NUMBER() OVER (PARTITION BY field ORDER BY cost_usd DESC) <= 25
ORDER BY field, cost_usd DESC;
```

Summary of results (top by cost_usd):

- `line_item_line_item_type`: `SavingsPlanRecurringFee`, `SavingsPlanCoveredUsage`, `Usage`, negative `SavingsPlanNegation`, small `Tax`.
- `line_item_usage_type`: `ComputeSP:3yrNoUpfront`, `ComputeSP:1yrNoUpfront`, `DataTransfer-Regional-Bytes` across regions, many `EBS:VolumeUsage.gp3` / `VolumeUsage.io2`, `NatGateway-Hours`.
- `line_item_operation`: empty values (SP fees), `CreateVolume-Io2/Gp3`, `InterZone-{In,Out}`, `LoadBalancing:Network`, `NatGateway`, `VpcEndpoint`, `RunInstances:*`, `PutLogEvents/PutObject`.
- `product_product_family`: `ComputeSavingsPlans`, `Storage`, `Data Transfer`, `GuardDuty`, `NAT Gateway`, `Management Tools - AWS Config/CloudTrail`, `Compute Instance`, `Database Instance`, `Load Balancer-Network`, `API Request`, `Encryption Key`, `VPC Peering`, `AWS Global Accelerator`.
- `product_product_name`: `Savings Plans for AWS Compute usage`, `Amazon Elastic Compute Cloud`, `Amazon Simple Storage Service`, `Amazon GuardDuty`, `AmazonCloudWatch`, `Elastic Load Balancing`, `Amazon RDS`, `AWS Config`, `Amazon VPC`, `Amazon ECR/EKS`, `AWS Security Hub`, `AWS CloudTrail`, `AWS KMS`.

---

## CUR 2.0 (`aws_billing_no_payer`)

### Structure and tag expansion

`resource_tags` is a STRUCT. Its `key_value` field is a REPEATED RECORD (`key`, `value`). Example expansion:

```sql
SELECT tag.key, tag.value
FROM `gcp-digital-bi.aws_billing_no_payer.aws_billing_20251201`,
UNNEST(resource_tags.key_value) AS tag;
```

### Tag key distribution (since 2025-10)

```sql
SELECT tag.key AS tag_key,
       ROUND(SUM(line_item_unblended_cost), 2) AS cost_usd,
       COUNT(*) AS row_count
FROM `gcp-digital-bi.aws_billing_no_payer.aws_billing_20251201`,
UNNEST(resource_tags.key_value) AS tag
WHERE line_item_usage_start_date >= TIMESTAMP('2025-10-01')
GROUP BY 1
ORDER BY cost_usd DESC
LIMIT 30;
```

Observed common keys (sorted by cost): `user_tenant`, `user_servicetype`, `user_usedby`, `user_component`, `user_project`, `user_cluster`, `user_name`, `user_environment`, `user_env`, `user_kubernetes_io_service_name`, plus lower-share keys like `user_aws_apn_id`, `user_owner`, `user_application`.

### Top N key field values (since 2025-10)

Query sample (field distributions + tag keys):

```sql
DECLARE start_date DATE DEFAULT DATE '2025-10-01';
WITH base AS (
  SELECT line_item_line_item_type, line_item_usage_type, line_item_operation,
         product_product_family, product_servicecode, resource_tags, line_item_unblended_cost
  FROM `gcp-digital-bi.aws_billing_no_payer.aws_billing_20251201`
  WHERE line_item_usage_start_date >= TIMESTAMP(start_date)
),
unioned AS (
  SELECT 'line_item_line_item_type' AS field, line_item_line_item_type AS value,
         ROUND(SUM(line_item_unblended_cost), 2) AS cost_usd, COUNT(*) AS row_count
  FROM base GROUP BY 1, 2
  UNION ALL
  SELECT 'line_item_usage_type', line_item_usage_type, ROUND(SUM(line_item_unblended_cost), 2), COUNT(*) FROM base GROUP BY 1, 2
  UNION ALL
  SELECT 'line_item_operation', line_item_operation, ROUND(SUM(line_item_unblended_cost), 2), COUNT(*) FROM base GROUP BY 1, 2
  UNION ALL
  SELECT 'product_product_family', product_product_family, ROUND(SUM(line_item_unblended_cost), 2), COUNT(*) FROM base GROUP BY 1, 2
  UNION ALL
  SELECT 'product_servicecode', product_servicecode, ROUND(SUM(line_item_unblended_cost), 2), COUNT(*) FROM base GROUP BY 1, 2
  UNION ALL
  SELECT 'resource_tags.key', tag.key, ROUND(SUM(line_item_unblended_cost), 2), COUNT(*) FROM base, UNNEST(resource_tags.key_value) AS tag GROUP BY 1, 2
)
SELECT *
FROM unioned
QUALIFY ROW_NUMBER() OVER (PARTITION BY field ORDER BY cost_usd DESC) <= 25
ORDER BY field, cost_usd DESC;
```

Summary of results (top by cost_usd):

- `line_item_line_item_type`: mostly `Usage`, then `SavingsPlanRecurringFee`, `SavingsPlanCoveredUsage`; negative `EdpDiscount`, `PrivateRateDiscount`, `SavingsPlanNegation`; small `Tax`, `BundledDiscount`, `Credit`.
- `line_item_usage_type`: `ComputeSP:*`, `EBS:VolumeP-IOPS.io2`, `VolumeUsage.io2/gp3`, `BoxUsage:m6g.*`, `DataTransfer-Regional-Bytes` (multi-region), `NatGateway-Hours`, `LCUUsage`.
- `line_item_operation`: empty (SP/discounts), `RunInstances`, `CreateVolume-Io2/Gp3`, `InterZone-{In,Out}`, `StandardStorage/StandardIAStorage`, `LoadBalancing:Network`, `NatGateway`, `VpcEndpoint`, `CreateDBInstance:*`, `PutObject/PutLogEvents`.
- `product_product_family`: `ComputeSavingsPlans`, `Data Transfer`, `Compute Instance`, `Storage`, `System Operation`, `Load Balancer-Network`, `NAT Gateway`, `Provisioned Throughput`, `API Request`, `VpcEndpoint`, `Database Instance`, `GuardDuty`, `VPC Peering`, `Encryption Key`.
- `product_servicecode`: `AmazonEC2`, `ComputeSavingsPlans`, `AWSDataTransfer`, `AmazonS3`, `AWSELB`, `AmazonVPC`, `AmazonEKS`, `AmazonRDS`, `AmazonGuardDuty`, `AWSDirectConnect`, `AWSConfig`, `AmazonCloudWatch`, `AWSCloudTrail`, `AWSCertificateManager`, `AmazonECR`, `AmazonDynamoDB`.
- `resource_tags.key`: see the tag key distribution list; highest cost contributors are `user_tenant`, `user_servicetype`, `user_usedby`, `user_component`, `user_project`, `user_cluster`, `user_name`, `user_environment`.

### Other key fields (sampled since 2025-10)

#### Pricing/discounts

- `pricing_term`: `OnDemand` (63,241,647 rows), `Spot` (90,335 rows), `Reserved` (48,353 rows); others are NULL (2,612,203 rows).
- `discount.key_value`: only `edp_discount` and `private_rate_discount` seen, both at the same order of magnitude as Usage rows (65,992,538 rows); negative rows combine with Usage to form net cost.
- `cost_category`: no non-null values observed; treat as not enabled.

#### Product specs/region

Sample query:

```sql
DECLARE start_date DATE DEFAULT DATE '2025-10-01';
WITH base AS (
  SELECT product_instance_type, product_instance_family, product_location,
         product_region_code, product_usagetype, product_pricing_unit,
         line_item_unblended_cost
  FROM `gcp-digital-bi.aws_billing_no_payer.aws_billing_20251201`
  WHERE line_item_usage_start_date >= TIMESTAMP(start_date)
),
unioned AS (
  SELECT 'product_instance_type' AS field, product_instance_type AS value,
         ROUND(SUM(line_item_unblended_cost), 2) AS cost_usd, COUNT(*) AS row_count FROM base GROUP BY 1,2
  UNION ALL SELECT 'product_instance_family', product_instance_family, ROUND(SUM(line_item_unblended_cost), 2), COUNT(*) FROM base GROUP BY 1,2
  UNION ALL SELECT 'product_location', product_location, ROUND(SUM(line_item_unblended_cost), 2), COUNT(*) FROM base GROUP BY 1,2
  UNION ALL SELECT 'product_region_code', product_region_code, ROUND(SUM(line_item_unblended_cost), 2), COUNT(*) FROM base GROUP BY 1,2
  UNION ALL SELECT 'product_usagetype', product_usagetype, ROUND(SUM(line_item_unblended_cost), 2), COUNT(*) FROM base GROUP BY 1,2
  UNION ALL SELECT 'product_pricing_unit', product_pricing_unit, ROUND(SUM(line_item_unblended_cost), 2), COUNT(*) FROM base GROUP BY 1,2
)
SELECT *
FROM unioned
QUALIFY ROW_NUMBER() OVER (PARTITION BY field ORDER BY cost_usd DESC) <= 15
ORDER BY field, cost_usd DESC;
```

Summary:

- `product_instance_type`: many NULLs (SP/discount rows). For non-null, the top ones are `m6g.16xlarge`, `m6g.8xlarge`, `m6g.4xlarge`, `m6g.2xlarge`, `c6g.4xlarge`, `r6g.8xlarge`, `i4i.4xlarge`, `t3.medium`.
- `product_instance_family`: mostly `General purpose`, `Compute optimized`, `Memory optimized`, with some `Storage optimized`, plus many NULLs.
- `product_usagetype`: `ComputeSP:3yrNoUpfront`, `ComputeSP:1yrNoUpfront`, `DataTransfer-Regional-Bytes` (multi-region), `EBS:VolumeP-IOPS.io2` / `VolumeUsage.io2/gp3`, `BoxUsage:m6g.*`, `TimedStorage-ByteHrs`.
- `product_location` / `product_region_code`: primary regions `us-east-1`, `us-west-2`, `ap-northeast-1`, `ap-southeast-1`, plus `Any` (SP/global items). These fields also have many NULLs (again mostly discount/allocation rows).
- `product_pricing_unit`: almost all NULLs (again SP/discount rows), with a few rows showing actual units.

#### Savings Plan / RI

Present SP fields: `savings_plan_offering_type`, `savings_plan_payment_option`, `savings_plan_purchase_term` (about 1,818,592 non-null rows, far fewer than all Usage). Amortized fields `savings_plan_amortized_upfront_commitment_for_billing_period`, `savings_plan_recurring_commitment_for_billing_period`, `savings_plan_net_savings_plan_effective_cost` are populated for 65,992,538 rows.

RI fields (`reservation_*`) are non-null across the same 65,992,538 rows (`reservation_effective_cost`, `reservation_net_effective_cost`, `reservation_net_recurring_fee_for_usage`, etc.), indicating CUR 2.0 includes RI allocation metrics alongside SP allocation metrics.

#### Split line item

Split cost fields exist: `split_line_item_split_cost`, `split_line_item_net_split_cost`, `split_line_item_split_usage_ratio`, etc. (all FLOAT and nullable). No ID-type fields were observed.

### Checklist (modeling/validation notes)

- **Pricing basis**: use `pricing_term` + `line_item_line_item_type` to split OnDemand/Spot/Reserved/SP; validate SP/RI discounted cost via `pricing_public_on_demand_cost/rate`.
- **Discount rows**: `discount.key_value` (EDP/PrivateRate) are negative values; net cost is only correct after combining with Usage rows, so include them in aggregates.
- **SP/RI allocation**: `savings_plan_*` and `reservation_*` are allocation/effective cost fields. Avoid double counting and retain both amortized/recurring metrics for traceability.
- **Split rows**: `split_line_item_*` represent split ratios/amounts; when de-duplicating or reconciling, aggregate by `identity_line_item_id` and sum split cost.
- **Product specs**: `product_instance_type/family/usagetype` are key EC2 dimensions. Use `product.region` from the RECORD for region attribution, as `product_region_code` is NULL for Data Transfer and VPC rows.
- **Tags/cost categories**: no Cost Category yet; tag keys are heavily concentrated under `user_*`. If Cost Category is enabled later, use `UNNEST(cost_category.key_value)` to build the dimension.

---

## Follow-up notes

- When new tags or new `product_*`/`line_item_*` values appear, reuse the queries above with an updated month filter and append results to this file.
- For cross-month or multi-account aggregation, wrap these queries in stored procedures or Dataform to produce consistent dimension tables.