DEV Community

takahiro hashito
takahiro hashito

Posted on

Track your GCP bill from your own dashboard: billing export plus two SQL queries

#googlecloud #database #devops

Background

I run a set of small sites on Google Cloud Platform. Usage-based billing means the number is not final until the invoice arrives at the end of the month, and one bad setting can move it.

Opening the Cloud Console billing report every day did not last. A habit that does not survive a busy week is not a safeguard.

So I put the number where I already look: my own dashboard. The whole thing is GCP's billing export plus two SQL queries.

Billing export is a one-time setting that makes GCP append your billing line items to a BigQuery table every day. BigQuery is Google's analytics warehouse, so once the rows are there you can just ask SQL for them.

How it works

There is no service in the middle. The pipeline below is the whole thing, from the one-time console setting to the field my dashboard renders.

GCP billing export (configure once)
  -> BigQuery table <project>.billing_export.gcp_billing_export_v1_<billing account id>
    -> bq query (two aggregations)
      -> one field in my dashboard snapshot
Enter fullscreen mode Exit fullscreen mode

I keep two queries instead of one, because they answer different questions.

Question Column to filter on Grouping
What am I spending on right now? invoice.month per service
When did it start going up? usage_start_time per day, last 30 days

Those columns are not interchangeable. invoice.month is the month a line item is billed in; usage_start_time is when the resource was actually used. Usage near a month boundary can land on the next invoice, so reusing one query for both questions gives you a wrong answer that still looks plausible.

Implementation

Current month, grouped by service:

SELECT service.description svc, ROUND(SUM(cost),0) cost
FROM `<project>.billing_export.gcp_billing_export_v1_<billing account id>`
WHERE invoice.month = FORMAT_DATE("%Y%m", CURRENT_DATE())
GROUP BY svc
HAVING cost > 0
ORDER BY cost DESC
Enter fullscreen mode Exit fullscreen mode

invoice.month is a string like 202609, so I build today's year-month with FORMAT_DATE("%Y%m", CURRENT_DATE()) and compare strings. HAVING cost > 0 keeps unused services out of the table so the panel stays readable.

Run with --format=json and you get:

[{"svc":"Cloud Storage","cost":"123"},{"svc":"Cloud Run","cost":"45"}]
Enter fullscreen mode Exit fullscreen mode

Note that cost comes back as a string. Summing it without Number() gives you string concatenation, not a total.

The caller shapes that into a total plus a breakdown:

const rows = JSON.parse(out);
if (!rows.length) return null;
const total = rows.reduce((a, r) => a + Number(r.cost), 0);
return { total, currency: "JPY", services: rows.map((r) => ({ service: r.svc, cost: Number(r.cost) })) };
Enter fullscreen mode Exit fullscreen mode

The line I thought about longest is if (!rows.length) return null;.

Billing export only contains data from the moment you enable it, and the command fails outright if credentials have expired. In both cases the correct answer is "unknown", not "zero". Returning 0 would render as "GCP cost this month: 0" — the same pixels a genuinely free month produces. Returning null lets the view say "not configured" or "unavailable" instead.

Gotchas

Do not pass the SQL through a shell if the same code has to run on Windows.

Measured on 2026-07-30: every run left two empty (0 byte) files in the repository root, named

0
DATE_SUB(CURRENT_DATE()
Enter fullscreen mode Exit fullscreen mode

cmd.exe does not treat single quotes as quoting, so > and ( inside the SQL were read as shell metacharacters. HAVING cost > 0 became "redirect output into a file called 0".

Before, broken:

execSync(`bq query --use_legacy_sql=false --format=json '${q}'`);
Enter fullscreen mode Exit fullscreen mode

After, fixed:

execFileSync("bq", [
  "query",
  "--project_id=<project>",
  "--use_legacy_sql=false",
  "--format=json",
  q,
], { encoding: "utf8", stdio: ["ignore", "pipe", "ignore"] });
Enter fullscreen mode Exit fullscreen mode

With an argument array the string never goes through a shell, so no OS quoting rules apply. The junk files stopped appearing.

Two smaller notes. --use_legacy_sql=false is required; without it the queries above are syntax errors. And 2>/dev/null is POSIX-only — on Windows it does nothing, so silence stderr with stdio: ["ignore", "pipe", "ignore"] instead.

The result

A site that runs on this: https://hashitosystem.com

Wrap-up

The SQL is the easy half. The design decision that mattered was what to return when the measurement fails.

Give "could not measure" its own value, distinct from 0. Otherwise a dead pipeline renders as a free month, and the panel you built to reassure yourself starts hiding the thing you built it to catch.

And when you hand a string full of punctuation to an external CLI, pass it as an argument array. That applies to SQL, regular expressions, and JSON alike.

This article is about my own side project. It was written with AI assistance.

Top comments (0)