Cloud Billing Exports: BigQuery Analysis & Anomaly Detection

Console billing dashboard hữu ích cho overview, nhưng BigQuery billing export là duy nhất cách để phân tích chi phí deeply. Khi export vào BigQuery, bạn có raw billing data, thực hiện custom analysis, trend detection, anomaly alerting.

Billing Export Setup

Enable Billing Export to BigQuery

bash

# 1. Create dataset (if not exists)
bq mk --dataset --location=us billing_dataset

# 2. Export billing account to BigQuery
gcloud billing budgets create \
  --billing-account=BILLING_ACCOUNT_ID \
  --display-name="Billing Export" \
  --budget-amount=UNLIMITED \
  --threshold-rule=percent=100

# Actually, use Cloud Console:
# Billing → Billing Account Settings → BigQuery Export
# Or via gcloud (complex)

Dataset Structure

Dataset: `project.billing_dataset`
Tables:
  - gcp_billing_export_v1 (main billing data)
  - gcp_billing_export_resource_v1 (resource metadata)
  - ...

Schema: Key Fields

Main Table: gcp_billing_export_v1

sql

SELECT
  billing_account_id,          -- Billing account
  service.id,                  -- Service SKU (e.g., 'Compute Engine')
  service.description,
  sku.id,                      -- SKU (e.g., 'CP-COMPUTEENGINE-VMIMAGE-N2')
  sku.description,
  labels.key, labels.value,    -- Custom labels (team, env, etc)
  resources.id,                -- Resource ID
  resources.name,              -- Resource name
  resources.location,          -- Region
  usage_start_time,            -- UTC time
  usage_end_time,
  usage.amount,                -- Usage quantity
  usage.unit,                  -- Unit (GB, vCPU-hours, etc)
  cost,                        -- Cost in billing currency
  currency,                    -- Currency (USD, INR, etc)
  project.id,                  -- Project ID
  project.name
FROM `project.billing_dataset.gcp_billing_export_v1`

Common Analysis Patterns

Pattern 1: Daily Cost Trend

sql

SELECT
  DATE(usage_start_time) as date,
  SUM(cost) as daily_cost
FROM `project.billing_dataset.gcp_billing_export_v1`
WHERE invoice_month >= FORMAT_DATE('%Y%m', DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY))
GROUP BY date
ORDER BY date DESC;

Output:

date        | daily_cost
2026-06-25  | $5,234
2026-06-24  | $5,102
2026-06-23  | $5,456
...

Pattern 2: Cost by Service

sql

SELECT
  service.description,
  SUM(cost) as service_cost,
  ROUND(100 * SUM(cost) / (SELECT SUM(cost) FROM `project.billing_dataset.gcp_billing_export_v1`), 2) as percent
FROM `project.billing_dataset.gcp_billing_export_v1`
WHERE DATE(usage_start_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY service.description
ORDER BY service_cost DESC;

Output:

service                   | service_cost | percent
Compute Engine            | $45,000      | 45%
Cloud Storage             | $25,000      | 25%
Cloud Networking          | $15,000      | 15%
...

Pattern 3: Cost by Project

sql

SELECT
  project.name,
  SUM(cost) as project_cost
FROM `project.billing_dataset.gcp_billing_export_v1`
WHERE DATE(usage_start_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY project.name
ORDER BY project_cost DESC
LIMIT 10;

Pattern 4: Cost by Custom Label (Team)

sql

SELECT
  (SELECT value FROM UNNEST(labels) WHERE key = 'team') as team,
  SUM(cost) as team_cost
FROM `project.billing_dataset.gcp_billing_export_v1`
WHERE DATE(usage_start_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY team
ORDER BY team_cost DESC;

Anomaly Detection

Pattern 1: Daily Cost Spike Detection

sql

WITH daily_cost AS (
  SELECT
    DATE(usage_start_time) as date,
    SUM(cost) as daily_cost
  FROM `project.billing_dataset.gcp_billing_export_v1`
  WHERE DATE(usage_start_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY date
),

baseline AS (
  SELECT
    AVG(daily_cost) as avg_cost,
    STDDEV_POP(daily_cost) as stddev_cost
  FROM daily_cost
  WHERE date <= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)  -- Last 83 days, exclude last week
)

SELECT
  dc.date,
  dc.daily_cost,
  ROUND(b.avg_cost, 2) as baseline_avg,
  ROUND(dc.daily_cost - b.avg_cost, 2) as deviation,
  CASE
    WHEN dc.daily_cost > b.avg_cost + (2 * b.stddev_cost) THEN 'ANOMALY_HIGH'
    WHEN dc.daily_cost < b.avg_cost - (2 * b.stddev_cost) THEN 'ANOMALY_LOW'
    ELSE 'NORMAL'
  END as status
FROM daily_cost dc, baseline b
WHERE dc.date > DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY dc.date DESC;

Output:

date       | daily_cost | baseline_avg | deviation | status
2026-06-25 | $8,234     | $5,200       | $3,034    | ANOMALY_HIGH
2026-06-24 | $5,102     | $5,200       | -$98      | NORMAL

Pattern 2: Service Cost Surge

sql

WITH service_daily AS (
  SELECT
    DATE(usage_start_time) as date,
    service.description as service,
    SUM(cost) as service_daily_cost
  FROM `project.billing_dataset.gcp_billing_export_v1`
  WHERE DATE(usage_start_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY date, service
),

service_baseline AS (
  SELECT
    service,
    AVG(service_daily_cost) as avg_cost
  FROM service_daily
  WHERE date <= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY service
)

SELECT
  sd.date,
  sd.service,
  ROUND(sd.service_daily_cost, 2) as cost,
  ROUND(sb.avg_cost, 2) as baseline,
  ROUND((sd.service_daily_cost - sb.avg_cost) / sb.avg_cost * 100, 1) as percent_increase
FROM service_daily sd
JOIN service_baseline sb ON sd.service = sb.service
WHERE sd.date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND sd.service_daily_cost > sb.avg_cost * 1.5  -- 50% above normal
ORDER BY sd.date DESC, percent_increase DESC;

Root Cause Analysis: Finding What Changed

Query 1: Find New Resources

sql

SELECT
  resources.name,
  service.description,
  MIN(usage_start_time) as first_seen,
  SUM(cost) as total_cost
FROM `project.billing_dataset.gcp_billing_export_v1`
WHERE DATE(usage_start_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND DATE(MIN(usage_start_time)) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
GROUP BY resources.name, service.description
ORDER BY total_cost DESC;

Query 2: Identify Over-Utilized Resource

sql

SELECT
  resources.name,
  service.description,
  DATE(usage_start_time) as date,
  SUM(usage.amount) as usage_amount,
  SUM(cost) as daily_cost,
  LAG(SUM(cost)) OVER (PARTITION BY resources.name ORDER BY DATE(usage_start_time)) as prev_day_cost
FROM `project.billing_dataset.gcp_billing_export_v1`
WHERE DATE(usage_start_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
GROUP BY resources.name, service.description, date
HAVING LAG(SUM(cost)) < SUM(cost) * 1.5  -- 50% increase day-over-day
ORDER BY date DESC, daily_cost DESC;

Forecasting & Budgeting

Forecast Monthly Cost

sql

WITH daily_cost AS (
  SELECT
    DATE(usage_start_time) as date,
    SUM(cost) as daily_cost
  FROM `project.billing_dataset.gcp_billing_export_v1`
  WHERE DATE(usage_start_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY date
)

SELECT
  'Current Run Rate' as metric,
  ROUND(AVG(daily_cost) * 30, 2) as monthly_cost
FROM daily_cost
UNION ALL
SELECT
  'Trend (7-day avg)',
  ROUND(AVG(CASE WHEN date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) THEN daily_cost END) * 30, 2)
FROM daily_cost;

Scheduled Reports

Setup Scheduled Query

bash

bq mk \
  --transfer_config \
  --display_name='Daily Cost Report' \
  --data_source=scheduled_query \
  --target_dataset=reports \
  --schedule='every day 06:00' \
  --query='
SELECT
  CURRENT_DATE() as report_date,
  SUM(cost) as daily_cost,
  service.description
FROM `project.billing_dataset.gcp_billing_export_v1`
WHERE DATE(usage_start_time) = CURRENT_DATE() - 1
GROUP BY service.description
'

Anti-Patterns

Anti-Pattern 1: No Billing Labels

Mistake: Export billing data without custom labels (team, app, env).

Impact: Cannot attribute cost to team, cannot chargeback.

Fix: Require billing labels on all resources via IAM/Org Policy.

Anti-Pattern 2: Ignoring Anomalies

Mistake: Export data, never check for spikes.

Impact: Bill surprise, cannot identify root cause quickly.

Fix: Set up automated anomaly detection + alerting.

Anti-Pattern 3: Unbounded Query

Mistake: Query entire 5 years of billing data.

Impact: BigQuery quota hit, expensive scans.

Fix: Always filter by date range (WHERE DATE(usage_start_time) >= ...).

Cloud Billing Exports: BigQuery Analysis & Anomaly Detection ​

Billing Export Setup ​

Enable Billing Export to BigQuery ​

Dataset Structure ​

Schema: Key Fields ​

Main Table: gcp_billing_export_v1 ​

Common Analysis Patterns ​

Pattern 1: Daily Cost Trend ​

Pattern 2: Cost by Service ​

Pattern 3: Cost by Project ​

Pattern 4: Cost by Custom Label (Team) ​

Anomaly Detection ​

Pattern 1: Daily Cost Spike Detection ​

Pattern 2: Service Cost Surge ​

Root Cause Analysis: Finding What Changed ​

Query 1: Find New Resources ​

Query 2: Identify Over-Utilized Resource ​

Forecasting & Budgeting ​

Forecast Monthly Cost ​

Scheduled Reports ​

Setup Scheduled Query ​

Anti-Patterns ​

Anti-Pattern 1: No Billing Labels ​

Anti-Pattern 2: Ignoring Anomalies ​

Anti-Pattern 3: Unbounded Query ​

References ​

Cloud Billing Exports: BigQuery Analysis & Anomaly Detection

Billing Export Setup

Enable Billing Export to BigQuery

Dataset Structure

Schema: Key Fields

Main Table: gcp_billing_export_v1

Common Analysis Patterns

Pattern 1: Daily Cost Trend

Pattern 2: Cost by Service

Pattern 3: Cost by Project

Pattern 4: Cost by Custom Label (Team)

Anomaly Detection

Pattern 1: Daily Cost Spike Detection

Pattern 2: Service Cost Surge

Root Cause Analysis: Finding What Changed

Query 1: Find New Resources

Query 2: Identify Over-Utilized Resource

Forecasting & Budgeting

Forecast Monthly Cost

Scheduled Reports

Setup Scheduled Query

Anti-Patterns

Anti-Pattern 1: No Billing Labels

Anti-Pattern 2: Ignoring Anomalies

Anti-Pattern 3: Unbounded Query

References