BigQuery Cost Analysis by Project

Why This Matters

Multi-project environments are the norm in mature GCP setups — separate projects for production, staging, analytics, and data science. Without project-level cost visibility, teams cannot perform accurate chargeback, set meaningful budgets, or identify which project needs optimization attention first.

How It Works

This query reads from INFORMATION_SCHEMA.JOBS_BY_PROJECT and groups costs by project_id. For organisation-wide visibility, you would run similar queries against JOBS_BY_ORGANIZATION (requires organisation-level access). Each project's total on-demand cost is calculated from bytes billed at $6.25/TiB.

SQL Query

-- Aggregate on-demand cost per GCP project

DECLARE lookback_days INT64 DEFAULT 30;

WITH jobs AS (
  SELECT
    project_id,
    job_id,
    COALESCE(total_bytes_billed, 0) AS bytes_billed,
    ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY end_time DESC) AS rn
  FROM `your-project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL lookback_days DAY)
    AND job_type = 'QUERY' AND state = 'DONE' AND total_slot_ms IS NOT NULL
),
deduplicated AS (
  SELECT project_id, job_id, bytes_billed FROM jobs WHERE rn = 1
)
SELECT
  project_id,
  COUNT(DISTINCT job_id) AS total_jobs,
  ROUND(SUM(bytes_billed) / POW(1024, 3), 2) AS gib_scanned,
  ROUND(SUM(bytes_billed) / POW(1024, 4) * 6.25, 2) AS estimated_cost_usd
FROM deduplicated
GROUP BY project_id
ORDER BY estimated_cost_usd DESC

Query Explanation

The query collects all completed query jobs, deduplicates by job_id, groups by project_id, and sums total_bytes_billed. It also counts distinct jobs per project so you can see which projects are running the most queries. The on-demand cost formula is bytes / 1024^4 * $6.25.

Key Insights

• lightbulb

  Staging and development projects can sometimes cost more than production if engineers run unoptimized exploratory queries.

• lightbulb

  Projects with high job counts but low cost are running efficient queries — study their patterns.

• lightbulb

  Projects with low job counts but high cost have individual expensive queries worth investigating.

• lightbulb

  Cross-reference with the by-user query to see which users are responsible for costs in each project.

Best Practices

1. 1

   Set per-project budget alerts in the GCP Billing console.

2. 2

   Use separate billing accounts or labels for chargeback to business units.

3. 3

   Consider using BigQuery Editions with reservations assigned per project for predictable costs.

4. 4

   Review project-level costs monthly and flag any project with >20% month-over-month increase.

Related Guides