GCP for DevOps — Cost Analysis & Automation

3 minute read

Cost Analysis & Automation

Beyond manual cost reviews, DevOps teams should automate cost monitoring, alerting, and enforcement. This section covers BigQuery billing export analysis, Cloud Scheduler-driven cleanup jobs, and programmatic budget enforcement.

Cost Analysis Architecture

Cost Analysis & Automation Pipeline:
┌─────────────┐     ┌──────────────┐     ┌──────────────┐
│  Billing     │────▶│  BigQuery    │────▶│  Looker      │
│  Export      │     │  Dataset     │     │  Studio      │
└─────────────┘     └──────┬───────┘     └──────────────┘
                           │
                    ┌──────▼───────┐
                    │  Scheduled   │
                    │  Queries     │
                    └──────┬───────┘
                           │
              ┌────────────┼────────────┐
              ▼            ▼            ▼
       ┌──────────┐ ┌──────────┐ ┌──────────┐
       │  Slack   │ │  Budget  │ │  Auto    │
       │  Alerts  │ │  Actions │ │  Cleanup │
       └──────────┘ └──────────┘ └──────────┘

Set Up Billing Export to BigQuery

# Create a dataset for billing data
$ bq mk --dataset \
    --description="GCP Billing Export Data" \
    --location=US \
    my-project:billing_data
Dataset 'my-project:billing_data' successfully created.

Then in the Console: Billing → Billing export → BigQuery Export → Edit Settings → Select the dataset and enable Detailed usage cost.

Useful BigQuery Cost Queries

Daily spend trend for the current month:

SELECT
  DATE(usage_start_time) AS date,
  ROUND(SUM(cost), 2) AS daily_cost
FROM `billing_data.gcp_billing_export_v1_XXXXX`
WHERE invoice.month = FORMAT_DATE('%Y%m', CURRENT_DATE())
GROUP BY date
ORDER BY date;

Cost by project and service:

SELECT
  project.id AS project,
  service.description AS service,
  ROUND(SUM(cost), 2) AS total_cost
FROM `billing_data.gcp_billing_export_v1_XXXXX`
WHERE invoice.month = FORMAT_DATE('%Y%m', CURRENT_DATE())
GROUP BY project, service
HAVING total_cost > 1
ORDER BY total_cost DESC
LIMIT 20;

Cost by label (team attribution):

SELECT
  (SELECT value FROM UNNEST(labels) WHERE key = 'team') AS team,
  ROUND(SUM(cost), 2) AS total_cost
FROM `billing_data.gcp_billing_export_v1_XXXXX`
WHERE invoice.month = FORMAT_DATE('%Y%m', CURRENT_DATE())
GROUP BY team
ORDER BY total_cost DESC;

Identify cost spikes (day-over-day change):

WITH daily AS (
  SELECT
    DATE(usage_start_time) AS date,
    SUM(cost) AS cost
  FROM `billing_data.gcp_billing_export_v1_XXXXX`
  WHERE usage_start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP BY date
)
SELECT
  date,
  ROUND(cost, 2) AS daily_cost,
  ROUND(cost - LAG(cost) OVER (ORDER BY date), 2) AS change,
  ROUND((cost - LAG(cost) OVER (ORDER BY date)) / LAG(cost) OVER (ORDER BY date) * 100, 1) AS pct_change
FROM daily
ORDER BY date DESC
LIMIT 14;

Automate Cost Cleanup with Cloud Scheduler

# Create a Cloud Function to delete old dev resources
$ gcloud functions deploy cleanup-dev-resources \
    --runtime=python312 \
    --entry-point=cleanup \
    --trigger-http \
    --allow-unauthenticated=false \
    --region=us-central1 \
    --service-account=cleanup-sa@my-project.iam.gserviceaccount.com
Deploying function...done.

# Schedule it to run every night at midnight
$ gcloud scheduler jobs create http nightly-cleanup \
    --schedule="0 0 * * *" \
    --uri="https://us-central1-my-project.cloudfunctions.net/cleanup-dev-resources" \
    --http-method=POST \
    --oidc-service-account-email=cleanup-sa@my-project.iam.gserviceaccount.com \
    --time-zone="America/New_York"
Created job [nightly-cleanup].

Cleanup Function Example

from google.cloud import compute_v1
import datetime

def cleanup(request):
    """Delete dev VMs older than 7 days with label env=dev."""
    client = compute_v1.InstancesClient()
    project = "my-project"
    zones = ["us-central1-a", "us-central1-b"]
    deleted = []

    for zone in zones:
        instances = client.list(project=project, zone=zone)
        for instance in instances:
            labels = instance.labels or {}
            if labels.get("env") == "dev":
                created = instance.creation_timestamp
                age = (datetime.datetime.now(datetime.timezone.utc)
                       - datetime.datetime.fromisoformat(created))
                if age.days > 7:
                    client.delete(
                        project=project,
                        zone=zone,
                        instance=instance.name
                    )
                    deleted.append(instance.name)

    return {"deleted": deleted, "count": len(deleted)}

Programmatic Budget Enforcement

import base64
import json
from googleapiclient import discovery

def budget_alert_handler(event, context):
    """Enforce actions based on budget alerts."""
    data = json.loads(base64.b64decode(event['data']).decode())
    
    cost = data['costAmount']
    budget = data['budgetAmount']
    ratio = cost / budget
    
    if ratio >= 1.0:
        # Budget exceeded — disable non-essential services
        disable_dev_resources()
        send_slack_alert(f"CRITICAL: Budget exceeded ({ratio:.0%})")
    elif ratio >= 0.9:
        send_slack_alert(f"WARNING: 90% budget consumed ({ratio:.0%})")
    elif ratio >= 0.75:
        send_slack_alert(f"INFO: 75% budget consumed ({ratio:.0%})")

def disable_dev_resources():
    """Stop all dev VMs when budget is exceeded."""
    client = discovery.build('compute', 'v1')
    project = "my-project"
    # Stop dev instances logic
    pass

def send_slack_alert(message):
    """Send alert to Slack webhook."""
    import requests
    webhook = "https://hooks.slack.com/services/xxx/yyy/zzz"
    requests.post(webhook, json={"text": message})

Cost Dashboards with Looker Studio

Create a Looker Studio dashboard connected to your BigQuery billing export:

Dashboard Panel	Query Source	Visualization
Monthly trend	Daily cost aggregation	Line chart
Service breakdown	Cost by service	Pie chart
Project comparison	Cost by project	Bar chart
Label attribution	Cost by team label	Stacked bar
Anomaly detection	Day-over-day % change	Scorecard + alert

FinOps Automation Checklist

Automation	Tool	Schedule
Budget alerts	Cloud Billing + Pub/Sub	Real-time
Idle resource cleanup	Cloud Function + Scheduler	Nightly
Rightsizing review	Recommender API + script	Weekly
Cost report email	BigQuery + Cloud Function	Weekly
CUD utilization check	Billing API	Monthly
Label compliance audit	Cloud Asset Inventory	Daily

Common Mistakes

Mistake	Impact	Fix
No automated cleanup	Dev resources run indefinitely	Schedule nightly cleanup jobs
Manual cost reviews only	Missed spikes and anomalies	Automate alerts and dashboards
No label enforcement	Cannot attribute costs to teams	Org policy + automated audits
Budget alerts without actions	Alerts ignored, no enforcement	Pair alerts with Cloud Functions

Quick Reference

Task	Command
Create BigQuery dataset	`bq mk --dataset --location=US PROJECT:billing_data`
Deploy cleanup function	`gcloud functions deploy NAME --trigger-http --runtime=python312`
Create scheduled job	`gcloud scheduler jobs create http NAME --schedule="CRON"`
Run BigQuery query	`bq query --use_legacy_sql=false 'SQL'`
List scheduler jobs	`gcloud scheduler jobs list`

Next → Practice Exercises

Share on

Twitter Facebook LinkedIn