Billing Data Export to BigQuery

Billing Data Export to BigQuery is a Google Cloud feature that automatically copies your Cloud Billing usage and cost data into BigQuery datasets. This enables you to run custom SQL queries, join billing data with other datasets, and build sophisticated cost management dashboards. The export includes two main types of data: standard usage cost data (detailed cost and usage per SKU per project) and pricing data (the list prices for each SKU). Both are exported to separate tables within a BigQuery dataset that you designate.

Google Cloud provides built-in cost reports in the Cloud Console (e.g., Cost Table, Reports, Budgets). However, these are limited in flexibility. For enterprise-scale cost management, you need to:

Billing Export to BigQuery solves these needs by giving you raw, granular data in a fully queryable format.

The export process is fully managed by Google. Once enabled, the following happens: 1. Activation: You enable the export in the Cloud Console under Billing > Cost Management > Data Export. You select a BigQuery dataset (which must be in the same multi-region or region as your billing account). 2. Data Generation: Every hour, Google Cloud's billing pipeline aggregates usage and cost data from all projects under the billing account. This data is not real-time; there is a latency of 5-6 hours typically. 3. Table Population: The export writes to two primary tables: - gcp_billing_export_v1_<billing_account_id>: Contains detailed cost and usage data. - gcp_billing_export_resource_v1_<billing_account_id>: Contains resource-level cost data (more granular, includes resource labels). - cloud_pricing_export: Contains SKU-level pricing data (optional, must be enabled separately). 4. Schema: The tables have a fixed schema with columns like: - billing_account_id - service.id and service.description - sku.id and sku.description - usage_start_time and usage_end_time (TIMESTAMP type) - project.id and project.name - labels (REPEATED RECORD of key-value pairs) - cost (FLOAT, the cost in USD) - currency - usage.amount and usage.unit - credits (REPEATED RECORD for any credits applied) - invoice_month (STRING in YYYYMM format) 5. Data Freshness: The export runs daily, but data for each hour is typically available within 5-6 hours. For example, usage from 10:00-11:00 AM might appear in BigQuery by 4:00-5:00 PM the same day.

To enable the export: 1. Navigate to Cloud Console > Billing > select your billing account. 2. Click on "Cost Management" > "Data Export". 3. Under "Detailed usage cost", click "Edit" and select a BigQuery dataset. 4. (Optional) Enable "Pricing export" by clicking "Edit" and selecting the same dataset. 5. Click "Save".

To verify:

SELECT * FROM `project.dataset.gcp_billing_export_v1_<billing_account_id>`
WHERE DATE(usage_start_time) = CURRENT_DATE()
LIMIT 10;

ACE candidates should be able to write queries that answer typical cost management questions:

SELECT project.id, SUM(cost) AS total_cost
FROM `project.dataset.gcp_billing_export_v1_*`
WHERE DATE(usage_start_time) >= '2025-01-01'
GROUP BY project.id
ORDER BY total_cost DESC;

SELECT service.description, SUM(cost) AS total_cost
FROM `project.dataset.gcp_billing_export_v1_*`
GROUP BY service.description
ORDER BY total_cost DESC;

SELECT labels.key, labels.value, SUM(cost) AS total_cost
FROM `project.dataset.gcp_billing_export_v1_*`, UNNEST(labels) AS labels
GROUP BY labels.key, labels.value
ORDER BY total_cost DESC;

SELECT invoice_month, SUM(cost) AS total_cost
FROM `project.dataset.gcp_billing_export_v1_*`
GROUP BY invoice_month
ORDER BY invoice_month;

SELECT invoice_month, SUM(cost) AS total_cost, SUM(credits.amount) AS total_credits
FROM `project.dataset.gcp_billing_export_v1_*`, UNNEST(credits) AS credits
GROUP BY invoice_month;

Note: The wildcard * in the table name is useful if you have multiple billing accounts or if you want to query all historical tables. However, the standard table name does not include a date suffix; it is a single table that grows over time. The wildcard * works because BigQuery treats the table as a wildcard match if you use a common prefix, but in practice, for a single billing account, you can just use the exact table name.

A large enterprise with 50+ projects across multiple GCP organizations needs to allocate costs to internal departments. They enable billing export to BigQuery and create a custom table mapping project IDs to cost centers (e.g., 'project-123' -> 'Engineering'). They run a weekly SQL query that joins the billing export with this mapping table to produce a cost report per cost center. The query filters on usage_start_time for the past week and groups by cost center. They also use labels on resources to further break down costs (e.g., environment='prod' vs 'dev'). One common misconfiguration is not labeling resources consistently, leading to inaccurate allocation. They set up a Cloud Scheduler job to run the query every Monday and email the results to finance.

A SaaS company wants to prevent cost overruns on their development projects. They set up a budget with a 80% threshold that triggers a Pub/Sub notification. A Cloud Function subscribes to the topic and, upon receiving the alert, queries BigQuery to get the current month's cost for the dev project. If it exceeds the budget, the function disables a specific set of non-critical resources (e.g., staging VMs) using the Cloud Resource Manager API. They also log the action to BigQuery for auditing. The challenge is the latency of billing export (5-6 hours), so the budget alert is not real-time. To mitigate, they use a combination of budget alerts and near-real-time Monitoring metrics (e.g., resource utilization) to trigger early warnings.

A company runs shared infrastructure (e.g., a central Kubernetes cluster) used by multiple teams. They enable billing export and use resource labels to tag each pod's namespace. The billing export table includes labels in a repeated record. They run a daily query that sums cost per label key 'team' and produces a chargeback report. They discovered that some pods were not labeled, resulting in an 'unallocated' cost bucket. They implemented a policy to enforce labeling via a Kubernetes admission controller. They also use the pricing export table to calculate the cost of reserved instances vs. on-demand, and adjust chargeback rates accordingly.