How to Understand Billing Export Table Schemas

Google Cloud billing exports to BigQuery use two distinct table schemas: Standard and Detailed. Understanding these schemas is critical for writing accurate cost analysis queries, implementing FinOps practices, and building billing dashboards. This guide explains the structure, key fields, and differences between the export types.

Prerequisites

Before you begin, ensure you have:

• Billing exports enabled to BigQuery
• BigQuery Data Viewer role to access billing tables
• Access to the Google Cloud Console or bq command-line tool
• Basic understanding of SQL and data structures
• Familiarity with Google Cloud services and resource types

Understanding Export Types

Google Cloud offers two billing export types, each with its own schema:

Standard Export

• Purpose: Cost and usage summary data
• Granularity: Service and SKU level
• Use cases: High-level cost analysis, budget tracking, monthly reporting
• Data volume: Moderate (smaller tables)
• Table name: gcp_billing_export_v1_[BILLING_ACCOUNT_ID]

Detailed Export (Resource-Level)

• Purpose: Granular resource-level cost data
• Granularity: Individual resource level (e.g., specific VMs, disks, buckets)
• Use cases: Chargebacks, detailed cost attribution, resource-specific optimization
• Data volume: High (larger tables, more rows)
• Table name: gcp_billing_export_resource_v1_[BILLING_ACCOUNT_ID]

Recommendation: Enable both exports for comprehensive cost visibility.

Standard Export Schema

Core Fields

Billing Information

-- Billing account and invoice details
billing_account_id      STRING   -- Your Cloud Billing account ID
invoice.month           STRING   -- Invoice month (YYYYMM format)
cost_type               STRING   -- "regular", "tax", "adjustment", or "rounding_error"

Project Information

-- Project details
project.id              STRING   -- Project ID (e.g., "my-project-123")
project.name            STRING   -- Project display name
project.labels          ARRAY    -- Project-level labels
  - key                 STRING   -- Label key
  - value               STRING   -- Label value
project.ancestry_numbers STRING  -- Organization/folder hierarchy

Service and SKU Information

-- Service classification
service.id              STRING   -- Service ID (e.g., "95FF-2EF5-5EA1")
service.description     STRING   -- Human-readable service name (e.g., "Compute Engine")

-- SKU (Stock Keeping Unit) details
sku.id                  STRING   -- SKU ID (e.g., "0000-1111-2222")
sku.description         STRING   -- Detailed SKU description (e.g., "N1 Predefined Instance Core running in Americas")

Usage and Cost

-- Usage metrics
usage.amount            FLOAT64  -- Quantity of usage
usage.unit              STRING   -- Unit of measurement (e.g., "byte-seconds", "requests", "gibibyte month")
usage.amount_in_pricing_units  FLOAT64  -- Usage normalized to pricing units
usage.pricing_unit      STRING   -- Pricing unit (e.g., "gibibyte month")

-- Cost data
usage_start_time        TIMESTAMP -- When usage began
usage_end_time          TIMESTAMP -- When usage ended
cost                    FLOAT64   -- Cost after credits (in billing currency)
currency                STRING    -- Billing currency (e.g., "USD")
currency_conversion_rate FLOAT64  -- Exchange rate if currency was converted

Credits and Adjustments

-- Credits array (discounts, promotions, sustained use)
credits                 ARRAY
  - name                STRING    -- Credit type (e.g., "Sustained use discount")
  - amount              FLOAT64   -- Credit amount (negative value)
  - full_name           STRING    -- Full credit description
  - id                  STRING    -- Credit ID
  - type                STRING    -- Credit category

Location and Region

-- Geographic information
location.location       STRING    -- Location name (e.g., "us-central1")
location.country        STRING    -- Country code (e.g., "US")
location.region         STRING    -- Region (e.g., "us-central1")
location.zone           STRING    -- Zone (e.g., "us-central1-a")

Labels

-- Resource labels for cost attribution
labels                  ARRAY
  - key                 STRING    -- Label key (e.g., "team", "environment")
  - value               STRING    -- Label value (e.g., "backend", "production")

System Labels

-- Google-managed labels
system_labels           ARRAY
  - key                 STRING    -- System label key
  - value               STRING    -- System label value

Sample Standard Export Row

{
  "billing_account_id": "012345-6789AB-CDEF01",
  "invoice": {
    "month": "202501"
  },
  "cost_type": "regular",
  "project": {
    "id": "my-production-project",
    "name": "Production Environment",
    "labels": [
      {"key": "team", "value": "backend"},
      {"key": "environment", "value": "prod"}
    ]
  },
  "service": {
    "id": "95FF-2EF5-5EA1",
    "description": "Compute Engine"
  },
  "sku": {
    "id": "2E27-7E5B-CA2D",
    "description": "N1 Predefined Instance Core running in Americas"
  },
  "usage_start_time": "2025-01-15T00:00:00Z",
  "usage_end_time": "2025-01-15T01:00:00Z",
  "usage": {
    "amount": 3600,
    "unit": "seconds",
    "amount_in_pricing_units": 1,
    "pricing_unit": "hour"
  },
  "location": {
    "location": "us-central1",
    "country": "US",
    "region": "us-central1",
    "zone": "us-central1-a"
  },
  "cost": 0.0475,
  "currency": "USD",
  "credits": [
    {
      "name": "Sustained use discount",
      "amount": -0.0048,
      "type": "DISCOUNT"
    }
  ],
  "labels": [
    {"key": "app", "value": "web-server"},
    {"key": "owner", "value": "devops-team"}
  ]
}

Detailed Export Schema

The detailed export includes all fields from the standard export plus additional resource-level fields:

Additional Resource Fields

-- Resource identification
resource.name           STRING   -- Resource name (e.g., "//compute.googleapis.com/projects/my-project/zones/us-central1-a/instances/my-vm")
resource.global_name    STRING   -- Global resource identifier

Resource Tags

-- Resource tags (different from labels)
tags                    ARRAY
  - key                 STRING   -- Tag key
  - value               STRING   -- Tag value
  - inherited           BOOLEAN  -- Whether tag is inherited from parent
  - namespace           STRING   -- Tag namespace

Cost at List

-- List price before discounts
cost_at_list            FLOAT64  -- Cost at list price (before any discounts)

Pricing Information

-- Detailed pricing breakdown
price.effective_price   FLOAT64  -- Effective price per unit after discounts
price.tier_start_amount FLOAT64  -- Start of pricing tier
price.unit              STRING   -- Pricing unit
pricing.effective_price_at_list FLOAT64 -- List price per unit

Exploring Table Schemas

Using BigQuery Console

1. Navigate to BigQuery

   • Go to BigQuery
   • Expand your project and billing dataset

2. View Table Schema

   • Click on a billing export table
   • Click the Schema tab
   • Browse all fields, types, and descriptions

3. Preview Data

   • Click the Preview tab
   • See sample rows without running a query

Using bq Command-Line Tool

# Show table schema
bq show --schema --format=prettyjson \
  PROJECT_ID:DATASET_NAME.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX

# Show table info including schema
bq show PROJECT_ID:DATASET_NAME.gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX

Query to Explore Schema

-- List all columns in the billing table
SELECT
  column_name,
  data_type,
  is_nullable
FROM
  `PROJECT_ID.DATASET_NAME.INFORMATION_SCHEMA.COLUMNS`
WHERE
  table_name LIKE 'gcp_billing_export_%'
ORDER BY
  ordinal_position

Common Schema Queries

1. Explore Available Services

SELECT
  DISTINCT service.description AS service_name,
  service.id AS service_id,
  COUNT(*) AS usage_records
FROM
  `PROJECT_ID.DATASET_NAME.gcp_billing_export_v1_*`
WHERE
  _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
GROUP BY
  service_name,
  service_id
ORDER BY
  usage_records DESC

2. Understand SKU Breakdown for a Service

SELECT
  sku.description AS sku,
  usage.unit AS usage_unit,
  usage.pricing_unit AS pricing_unit,
  ROUND(SUM(cost), 2) AS total_cost,
  COUNT(*) AS line_items
FROM
  `PROJECT_ID.DATASET_NAME.gcp_billing_export_v1_*`
WHERE
  service.description = 'Compute Engine'
  AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
GROUP BY
  sku,
  usage_unit,
  pricing_unit
ORDER BY
  total_cost DESC

3. Analyze Label Coverage

-- Find resources without required labels
SELECT
  project.name AS project,
  service.description AS service,
  ROUND(SUM(cost), 2) AS unlabeled_cost,
  COUNT(*) AS unlabeled_records
FROM
  `PROJECT_ID.DATASET_NAME.gcp_billing_export_v1_*`
WHERE
  _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
  AND NOT EXISTS (SELECT 1 FROM UNNEST(labels) WHERE key = 'team')
GROUP BY
  project,
  service
ORDER BY
  unlabeled_cost DESC

4. Compare Standard vs. Detailed Export

-- Row counts for standard vs. detailed exports
SELECT
  'Standard Export' AS export_type,
  COUNT(*) AS row_count,
  ROUND(SUM(cost), 2) AS total_cost
FROM
  `PROJECT_ID.DATASET_NAME.gcp_billing_export_v1_*`
WHERE
  _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE())

UNION ALL

SELECT
  'Detailed Export' AS export_type,
  COUNT(*) AS row_count,
  ROUND(SUM(cost), 2) AS total_cost
FROM
  `PROJECT_ID.DATASET_NAME.gcp_billing_export_resource_v1_*`
WHERE
  _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE())

Best Practices

Schema Understanding

1. Start with standard export: Easier to understand and query
2. Use detailed export for specific use cases: Chargebacks, resource-level attribution
3. Document custom fields: If you add labels or tags, maintain a data dictionary
4. Understand nested fields: Use UNNEST() to query arrays (labels, credits)

Query Optimization

1. Always partition by date: Use _TABLE_SUFFIX filters
2. Select only needed columns: Avoid SELECT * on large tables
3. Understand ARRAY fields: Use UNNEST() properly to avoid row explosion
4. Cache commonly-used views: Create materialized views for frequent queries

Data Quality

1. Check for NULL values: Use COALESCE() or IFNULL() for safety
2. Validate cost calculations: Cost = usage × price (verify your math)
3. Monitor schema changes: Google occasionally adds new fields
4. Test queries on small date ranges: Before running expensive queries

Cost Attribution

1. Standardize labels: Use consistent label keys across all resources
2. Leverage project labels: Easier than per-resource labeling
3. Use system labels: Google adds useful metadata automatically
4. Combine with resource names: Detailed export provides full resource paths

Key Differences: Standard vs. Detailed

When to use each:

• Standard: Monthly reports, executive dashboards, budget tracking
• Detailed: Showback/chargeback, resource optimization, compliance audits

Troubleshooting

Field Not Found

Problem: Query fails with "Unrecognized name" error

Solution:

• Verify field exists in your export version: Check schema tab
• Use correct nested syntax: project.name not project_name
• Check for typos in field names
• Ensure you're querying the correct export type (standard vs. detailed)

ARRAY Fields Not Querying Correctly

Problem: Labels or credits not returning expected results

Solution:

-- Correct way to query labels
SELECT
  (SELECT value FROM UNNEST(labels) WHERE key = 'team') AS team,
  SUM(cost) AS total_cost
FROM
  `PROJECT_ID.DATASET_NAME.gcp_billing_export_v1_*`
GROUP BY team

Cost Doesn't Match Console

Problem: BigQuery sums don't match Cloud Console

Solution:

• Include credits: SUM(cost) + SUM((SELECT SUM(c.amount) FROM UNNEST(credits) c))
• Check date ranges match exactly
• Verify you're querying the correct billing account
• Wait 24-48 hours for complete data

Schema Changes

Problem: Query breaks after schema update

Solution:

• Monitor Google Cloud release notes for schema changes
• Use TRY() function for optional fields: TRY(CAST(field AS STRING))
• Avoid hard-coding column positions
• Test queries in development before production

Next Steps

After understanding billing export schemas:

1. Build custom views: Create simplified views for common queries
2. Implement data quality checks: Validate completeness and accuracy
3. Create documentation: Document your labeling strategy and schema customizations
4. Set up monitoring: Alert on schema changes or data quality issues
5. Optimize queries: Use clustering and partitioning for performance

Related Resources

• Run Sample Billing Queries in BigQuery
• Create a BigQuery Dataset
• Create a FinOps Project
• Google Cloud Billing Export Schema Reference
• BigQuery Standard SQL Reference