How to Use Cost Tables in BigQuery for FinOps

Using BigQuery cost tables is a powerful FinOps practice that helps organizations analyze, monitor, and optimize Google Cloud spending using SQL. By centralizing billing data in BigQuery, finance, engineering, and data teams can make data-driven cost decisions.

1. What Are BigQuery Cost (Billing) Tables?

BigQuery cost tables come from Google Cloud Billing export to BigQuery. They contain detailed, line-item billing data for all GCP services, including BigQuery itself.

What They Include:

Project, service, and SKU-level costs

Usage quantities and units

Labels and resource identifiers

Credits, discounts, and commitments

Timestamps (daily or hourly)

These tables are the foundation for FinOps reporting in GCP.

2. Enable Billing Export to BigQuery

Before using cost tables, you must enable billing export.

Steps:

Go to Google Cloud Console

Navigate to Billing → Billing Export

Enable:

Detailed usage cost export

(Optional) Pricing export

Select or create a BigQuery dataset

📌 Best practice: Use a central billing project shared across teams.

3. Understand the Billing Table Schema

The main table is usually named:

gcp_billing_export_v1_XXXXXX

Key Columns You’ll Use Often:

Column Description

project.id GCP project ID

service.description Service name (e.g., BigQuery)

sku.description Detailed SKU info

usage.amount Usage quantity

usage.unit Unit of measurement

cost Raw cost before credits

credits.amount Discounts and credits

labels Custom resource labels

usage_start_time Start of usage period

4. Calculate True Cost (Net Cost)

Always calculate net cost, not raw cost.

SELECT

DATE(usage_start_time) AS usage_date,

SUM(cost + IFNULL((

SELECT SUM(c.amount)

FROM UNNEST(credits) c

), 0)) AS net_cost

FROM `billing_dataset.gcp_billing_export_v1_xxxxxx`

GROUP BY usage_date

ORDER BY usage_date;

📌 This accounts for:

Sustained use discounts

Committed use discounts

Promotions and credits

5. Analyze BigQuery-Specific Costs

a) BigQuery Cost by Project

SELECT

project.id AS project_id,

SUM(cost) AS total_cost

FROM `billing_dataset.gcp_billing_export_v1_xxxxxx`

WHERE service.description = "BigQuery"

GROUP BY project_id

ORDER BY total_cost DESC;

b) BigQuery Cost by SKU (Storage vs Queries)

SELECT

sku.description,

SUM(cost) AS cost

FROM `billing_dataset.gcp_billing_export_v1_xxxxxx`

WHERE service.description = "BigQuery"

GROUP BY sku.description

ORDER BY cost DESC;

This helps identify:

On-demand queries

Flat-rate (slot) usage

Active vs long-term storage

6. Use Labels for Cost Allocation (Critical for FinOps)

Labels enable cost attribution by:

Team

Environment (prod, dev)

Application

Cost center

Example Query: Cost by Team Label

SELECT

labels.value AS team,

SUM(cost) AS total_cost

FROM `billing_dataset.gcp_billing_export_v1_xxxxxx`,

UNNEST(labels) AS labels

WHERE labels.key = "team"

GROUP BY team

ORDER BY total_cost DESC;

📌 FinOps best practice:

Enforce labeling standards across all projects.

7. Build FinOps KPIs Using Cost Tables

Common FinOps metrics:

Cost per project / team

Cost per TB scanned (BigQuery)

Daily cost trends

Credit utilization rate

Budget vs actual spend

Example: Daily Spend Trend

SELECT

DATE(usage_start_time) AS date,

SUM(cost) AS daily_cost

FROM `billing_dataset.gcp_billing_export_v1_xxxxxx`

GROUP BY date

ORDER BY date;

8. Detect Cost Anomalies

You can identify unusual spikes with SQL.

WITH daily_cost AS (

SELECT

DATE(usage_start_time) AS date,

SUM(cost) AS cost

FROM `billing_dataset.gcp_billing_export_v1_xxxxxx`

GROUP BY date

)

SELECT *

FROM daily_cost

WHERE cost > (

SELECT AVG(cost) * 1.5 FROM daily_cost

);

📌 This supports cost anomaly detection, a core FinOps capability.

9. Connect BigQuery to Visualization Tools

Use BigQuery as a single source of truth for cost data and connect to:

Looker / Looker Studio

Tableau

Power BI

This enables:

Executive cost dashboards

Team-level chargeback reports

Real-time spend monitoring

10. FinOps Best Practices for BigQuery Cost Tables

✅ Use net cost (cost + credits)

✅ Standardize labels and naming conventions

✅ Separate prod vs non-prod spending

✅ Monitor BigQuery query costs (TB scanned)

✅ Automate reports and alerts

✅ Share cost visibility with engineering teams

11. Common Pitfalls

Ignoring credits and discounts

Poor or missing labels

Mixing billing and workload projects

Relying only on console reports

Not correlating cost with usage metrics

✅ Final Summary

BigQuery cost tables are the backbone of FinOps on Google Cloud. By exporting billing data to BigQuery and querying it with SQL, organizations gain deep visibility, accurate cost allocation, anomaly detection, and actionable insights—all at scale.

Learn GCP Training in Hyderabad

Read More

Cost Optimization & Billing in GCP

Streaming Data from Cloud SQL to BigQuery with Dataflow

Real-Time Feature Stores with Bigtable and Vertex AI

Ingesting and Transforming Log Data in Real-Time Using GCP

Visit Our Quality Thought Training Institute in Hyderabad