How to Run Sample Billing Queries in BigQuery

Once your Google Cloud billing data is exported to BigQuery, you can analyze costs using SQL queries to understand spending patterns, identify cost drivers, and optimize your cloud budget. This guide provides ready-to-use query templates for common billing analysis scenarios.

Prerequisites

Before you begin, ensure you have:

• Billing exports enabled and data populated in BigQuery
• BigQuery Data Viewer role (minimum) to query billing data
• BigQuery Job User role to execute queries
• Access to the Google Cloud Console or bq command-line tool
• Basic understanding of SQL syntax
• Knowledge of your billing export table name (format: gcp_billing_export_v1_XXXXXX_XXXXXX_XXXXXX)

Understanding Billing Export Tables

Table Structure

Billing export tables contain one row per cost line item with these key fields:

• billing_account_id - Your Cloud Billing account ID
• invoice.month - Invoice month (YYYYMM format)
• cost - Cost in your billing currency (after credits)
• usage.amount - Amount of usage (e.g., GB, hours, requests)
• usage.unit - Unit of measurement
• project.id / project.name - Project identifier and name
• service.description - Google Cloud service (e.g., "Compute Engine")
• sku.description - Specific SKU (e.g., "N1 Predefined Instance Core")
• labels - Resource labels for cost attribution
• credits - Array of credits applied (discounts, promotions)
• usage_start_time / usage_end_time - Time range for usage

Table Naming

Tables are date-partitioned with the format:

gcp_billing_export_v1_[BILLING_ACCOUNT_ID]_[DATE]

Use wildcard tables to query across dates:

`project.dataset.gcp_billing_export_v1_*`

Step-by-Step Guide

Method 1: Using BigQuery Console

1. Navigate to BigQuery

   • Open the Google Cloud Console
   • Go to BigQuery

2. Open Query Editor

   • Click COMPOSE NEW QUERY
   • The query editor will open in the center panel

3. Write Query

   • Copy one of the sample queries below
   • Replace PROJECT_ID, DATASET_NAME, and table names with your actual values

4. Run Query

   • Click the RUN button
   • Results appear in the results pane below the editor
   • Check Bytes processed estimate before running

5. Save Query (Optional)

   • Click SAVE > Save query
   • Give it a name for future reference

Method 2: Using bq Command-Line Tool

# Run a query and display results
bq query --use_legacy_sql=false '
SELECT
  project.name,
  ROUND(SUM(cost), 2) AS total_cost
FROM
  `project.dataset.gcp_billing_export_v1_*`
WHERE
  _TABLE_SUFFIX BETWEEN "20250101" AND "20250131"
GROUP BY
  project.name
ORDER BY
  total_cost DESC
'

Sample Billing Queries

1. Total Cost by Project (Current Month)

Use case: Identify which projects are consuming the most budget

SELECT
  project.name AS project_name,
  ROUND(SUM(cost), 2) AS total_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 cost_after_credits
FROM
  `PROJECT_ID.DATASET_NAME.gcp_billing_export_v1_*`
WHERE
  _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m01', CURRENT_DATE())
    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY
  project_name
ORDER BY
  total_cost DESC

2. Cost by Service (Last 30 Days)

Use case: Understand which Google Cloud services are driving costs

SELECT
  service.description AS service,
  ROUND(SUM(cost), 2) AS total_cost,
  ROUND(SUM(usage.amount), 2) AS total_usage,
  usage.unit AS usage_unit,
  COUNT(*) AS line_items
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,
  usage_unit
ORDER BY
  total_cost DESC

3. Top 20 Most Expensive SKUs

Use case: Drill down to specific resources consuming budget

SELECT
  service.description AS service,
  sku.description AS sku,
  ROUND(SUM(cost), 2) AS total_cost,
  ROUND(SUM(usage.amount), 2) AS total_usage,
  usage.unit AS usage_unit
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,
  sku,
  usage_unit
ORDER BY
  total_cost DESC
LIMIT 20

4. Daily Cost Trend (Last 90 Days)

Use case: Visualize cost trends over time to identify spikes or patterns

SELECT
  DATE(usage_start_time) AS usage_date,
  ROUND(SUM(cost), 2) AS daily_cost,
  COUNT(DISTINCT project.id) AS active_projects
FROM
  `PROJECT_ID.DATASET_NAME.gcp_billing_export_v1_*`
WHERE
  _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
GROUP BY
  usage_date
ORDER BY
  usage_date ASC

5. Cost by Project and Service

Use case: Create a detailed breakdown for chargeback or showback

SELECT
  project.name AS project_name,
  service.description AS service,
  ROUND(SUM(cost), 2) AS total_cost,
  ROUND(AVG(cost), 4) AS avg_cost_per_line_item,
  COUNT(*) AS line_items
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
  project_name,
  service
ORDER BY
  project_name,
  total_cost DESC

6. Cost by Label (Team Attribution)

Use case: Attribute costs to teams or departments based on resource labels

SELECT
  IFNULL((SELECT value FROM UNNEST(labels) WHERE key = 'team'), 'unlabeled') AS team,
  IFNULL((SELECT value FROM UNNEST(labels) WHERE key = 'environment'), 'unlabeled') AS environment,
  ROUND(SUM(cost), 2) AS total_cost,
  COUNT(DISTINCT project.id) AS projects
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
  team,
  environment
ORDER BY
  total_cost DESC

7. Compute Engine VM Costs by Machine Type

Use case: Optimize VM instance sizing

SELECT
  sku.description AS machine_type,
  ROUND(SUM(cost), 2) AS total_cost,
  ROUND(SUM(usage.amount), 2) AS total_hours,
  ROUND(AVG(cost / NULLIF(usage.amount, 0)), 4) AS cost_per_hour
FROM
  `PROJECT_ID.DATASET_NAME.gcp_billing_export_v1_*`
WHERE
  service.description = 'Compute Engine'
  AND sku.description LIKE '%Instance Core%'
  AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
GROUP BY
  machine_type
ORDER BY
  total_cost DESC

8. BigQuery Storage and Query Costs

Use case: Monitor BigQuery costs for optimization

SELECT
  CASE
    WHEN sku.description LIKE '%Storage%' THEN 'Storage'
    WHEN sku.description LIKE '%Analysis%' OR sku.description LIKE '%Query%' THEN 'Query/Analysis'
    WHEN sku.description LIKE '%Streaming%' THEN 'Streaming Insert'
    ELSE 'Other'
  END AS cost_category,
  ROUND(SUM(cost), 2) AS total_cost,
  ROUND(SUM(usage.amount), 2) AS total_usage,
  usage.unit
FROM
  `PROJECT_ID.DATASET_NAME.gcp_billing_export_v1_*`
WHERE
  service.description = 'BigQuery'
  AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
GROUP BY
  cost_category,
  usage_unit
ORDER BY
  total_cost DESC

9. Month-over-Month Cost Comparison

Use case: Compare current month to previous month

WITH monthly_costs AS (
  SELECT
    FORMAT_DATE('%Y-%m', DATE(usage_start_time)) AS month,
    ROUND(SUM(cost), 2) AS total_cost
  FROM
    `PROJECT_ID.DATASET_NAME.gcp_billing_export_v1_*`
  WHERE
    _TABLE_SUFFIX >= FORMAT_DATE('%Y%m01', DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH))
  GROUP BY
    month
)
SELECT
  curr.month AS current_month,
  curr.total_cost AS current_cost,
  prev.total_cost AS previous_cost,
  ROUND(curr.total_cost - prev.total_cost, 2) AS cost_change,
  ROUND(((curr.total_cost - prev.total_cost) / prev.total_cost) * 100, 2) AS percent_change
FROM
  monthly_costs curr
LEFT JOIN
  monthly_costs prev
ON
  DATE_ADD(PARSE_DATE('%Y-%m', prev.month), INTERVAL 1 MONTH) = PARSE_DATE('%Y-%m', curr.month)
ORDER BY
  curr.month DESC

10. Unused or Idle Resources (Potential Savings)

Use case: Identify resources with minimal usage that could be downsized or deleted

SELECT
  project.name AS project_name,
  service.description AS service,
  sku.description AS sku,
  ROUND(SUM(cost), 2) AS total_cost,
  ROUND(SUM(usage.amount), 2) AS total_usage,
  usage.unit,
  CASE
    WHEN SUM(usage.amount) < 1 THEN 'Consider Deletion'
    WHEN SUM(usage.amount) < 10 THEN 'Consider Downsizing'
    ELSE 'Active'
  END AS recommendation
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 cost > 0
GROUP BY
  project_name,
  service,
  sku,
  usage_unit
HAVING
  total_cost > 1  -- Only show resources costing more than $1
ORDER BY
  total_cost DESC

11. Credits and Discounts Applied

Use case: Understand promotional credits and committed use discounts

SELECT
  credit.name AS credit_type,
  ROUND(SUM(credit.amount), 2) AS total_credit_amount,
  COUNT(DISTINCT project.id) AS projects_with_credits
FROM
  `PROJECT_ID.DATASET_NAME.gcp_billing_export_v1_*`,
  UNNEST(credits) AS credit
WHERE
  _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
GROUP BY
  credit_type
ORDER BY
  total_credit_amount DESC

12. Network Egress Costs by Region

Use case: Optimize data transfer costs

SELECT
  REGEXP_EXTRACT(sku.description, r'to (.+)') AS destination,
  ROUND(SUM(cost), 2) AS total_cost,
  ROUND(SUM(usage.amount), 2) AS total_gb
FROM
  `PROJECT_ID.DATASET_NAME.gcp_billing_export_v1_*`
WHERE
  service.description LIKE '%Networking%'
  AND sku.description LIKE '%Egress%'
  AND _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
GROUP BY
  destination
ORDER BY
  total_cost DESC

Best Practices

Query Optimization

1. Use table partitioning: Always filter on _TABLE_SUFFIX to limit data scanned
2. Limit time ranges: Query only the dates you need
3. Check cost estimates: Review "Bytes processed" before running queries
4. Use LIMIT clauses: Test queries with LIMIT 100 first
5. Create materialized views: For frequently-run queries

Cost Management

1. Set up query quotas: Limit BigQuery costs per user or team
2. Use scheduled queries: Pre-aggregate data daily to avoid repeated full scans
3. Enable clustering: Cluster tables by commonly-filtered columns (project, service)
4. Archive old data: Export historical data to Cloud Storage

Security

1. Grant minimal access: Use roles/bigquery.dataViewer (read-only) when possible
2. Row-level security: Restrict users to viewing only their project's costs
3. Audit queries: Enable BigQuery audit logging to track who runs what queries
4. Mask sensitive data: Use column-level security for billing account IDs

Reporting

1. Create dashboards: Use Looker Studio to visualize query results
2. Schedule reports: Email daily/weekly cost summaries to stakeholders
3. Set up alerts: Notify teams when costs exceed thresholds
4. Document queries: Maintain a library of standard queries for common analyses

Troubleshooting

Query Timeout

Problem: Query exceeds 10-minute execution limit

Solution:

• Reduce time range queried
• Add more specific WHERE filters
• Use partitioned tables and filter on _TABLE_SUFFIX
• Consider breaking into multiple smaller queries

Permission Denied

Problem: "Access Denied: Project PROJECT_ID: User does not have permission"

Solution:

• Verify you have roles/bigquery.dataViewer on the project
• Ensure you have roles/bigquery.jobUser to run queries
• Check that you're authenticated: gcloud auth list
• Confirm you're querying the correct project and dataset

High Query Costs

Problem: Queries consuming too much of BigQuery budget

Solution:

• Always use _TABLE_SUFFIX filters to limit data scanned
• Query only necessary columns (avoid SELECT *)
• Use LIMIT for exploratory queries
• Create aggregated summary tables for common queries
• Set custom query quotas per user

Slow Query Performance

Problem: Query takes too long to complete

Solution:

• Use date partitioning: Filter on _TABLE_SUFFIX
• Add clustering to billing tables
• Pre-aggregate data with scheduled queries
• Optimize JOIN operations
• Consider using approximate aggregation functions (e.g., APPROX_COUNT_DISTINCT)

Results Don't Match Console

Problem: BigQuery results differ from Cloud Console billing reports

Solution:

• Wait 24-48 hours for billing data to fully populate
• Ensure you're querying the correct billing account's export
• Check for timezone differences in date filters
• Verify credits are included/excluded consistently
• Compare cost vs cost + credits.amount

Next Steps

After running sample billing queries:

1. Create custom views: Save frequently-used queries as BigQuery views
2. Build dashboards: Visualize cost trends in Looker Studio or Grafana
3. Set up scheduled queries: Automate daily/weekly cost aggregations
4. Implement cost allocation: Use labels to attribute costs to teams
5. Optimize resources: Act on insights from idle/unused resource queries
6. Create budget alerts: Set thresholds based on query results

Related Resources

• Understanding Billing Export Table Schemas
• Create a BigQuery Dataset
• Enable the BigQuery Data Transfer Service API
• BigQuery SQL Reference
• Google Cloud Billing Export Schema