Skip to content

BigQuery Billing Export — what it can and cannot answer

Classification: CONFIDENTIAL — Internal Use Only Document: architecture/billing-export.md · v1.0 · 2026-08-24 · GPUS-IT

Written before any cost tile exists, deliberately. The point of this page is to fix what a savings figure may honestly claim, so the tile is built to the data rather than the data being stretched to the tile.

Summary — the export is already enabled

Nothing needed enabling. All three export types were already live on both billing accounts when this was investigated on 2026-08-24.

Billing account Dataset Standard Detailed (per-resource) Pricing Dataset created
01FBB7-BDDEDF-BF8810 gpus-infra.billing_export (US) ✅ ✅ ✅ 2026-08-13 19:14
01F08D-06128E-65D438 gpusa-it-infrastructure-306400.billing_export (US) ✅ ✅ ✅ 2026-08-19 14:36
006684-DA30AE-ABA6AB gpus-it-infrastructure.gpus_it_infra_billing (US) ✅ ✅ — pre-existing

The third account is not one of the two named in the original request and is recorded here only so nobody concludes it is missing.

Which account bills phoebe and pitta

01F08D-06128E-65D438 — the GPI-owned Devoteam reseller subaccount (Greenpeace USD - Devoteam - NL - USD, master 01444F-2377DC-FCF756).

Project Billing account
gpus-infra 01FBB7-BDDEDF-BF8810
gpus-it-infrastructure 01F08D-06128E-65D438
gpusa-it-infrastructure-306400 01F08D-06128E-65D438

Both targets live in gpusa-it-infrastructure-306400:

  • phoebe — Compute Engine VM, e2-medium, us-central1-a, RUNNING
  • pitta — Cloud SQL, MYSQL_8_0_31, db-custom-2-8192, us-central1-b, RUNNABLE

This is not a request to another team

The natural assumption is that a GPI-owned billing account is somebody else's to administer. It is not, in this case. rchhetry@greenpeace.org is not named directly in the billing account's IAM policy, but testIamPermissions returns billing.accounts.update, billing.accounts.updateUsageExportSpec, billing.accounts.setIamPolicy and billing.resourceAssociations.create — effective roles/billing.admin, inherited through gcp_permissions_us_billing_account_admins@greenpeace.org.

He is also already OWNER on the billing_export dataset itself.

Check effective permissions with testIamPermissions, not by reading the policy for your own name. Group-inherited roles are invisible to the second method, and reading the policy alone would have produced the wrong answer here.

Is there a "before" baseline? Yes — because the datasets are multi-region

This is the single most consequential detail on the page.

  • Multi-region (US/EU) datasets are backfilled with the current and previous month when export is first enabled.
  • Regional datasets are not. They carry data only from the enable date forward.

Both datasets here are US multi-region. The gpusa tables were created 2026-08-19, but the data in them starts 2026-06-29:

Month Rows Cost (whole billing account) Range
2026-06 16,406 $150.59 06-29 → 06-30
2026-07 854,883 $18,328.09 07-01 → 07-31
2026-08 574,716 $13,598.69 08-01 → 08-24

A complete July 2026 exists as a pre-decommission baseline, plus August to date. Had these been created as regional datasets there would be no "before" at all, and the savings question would have been unanswerable from Google's own data.

Can it attribute cost to a named VM and a named Cloud SQL instance?

Yes — but only in the detailed export, and resource.name is not one key.

The standard export has no resource field whatsoever. Its top-level schema is service, sku, project, labels, location, price, cost, credits, usage — and nothing that names an instance. A per-VM figure is impossible from it. The detailed export is not an optimisation here; it is the requirement.

Even in the detailed export, one VM appears under three different resource.name shapes:

Shape Example Carries
bare name phoebe licence, PD capacity, external IP, network
full path projects/117523040944/instances/phoebe E2 core, E2 RAM, CUD line
snapshot name gcs-phoebe-8728708703581121380-1783623615 one row per snapshot

A query matching only resource.name = 'phoebe' undercounts by roughly half

It would miss the E2 core and RAM charges entirely, because those are recorded against the full resource path. Any per-VM rollup must union all three shapes, and the snapshot shape is named after the snapshot, not the VM — those rows keep appearing after the VM is deleted, because the snapshots outlive it.

Cloud SQL is cleaner: pitta appears simply as pitta.

Does it separate compute from persistent disk? Yes, by SKU

July 2026, net of credits (credits were $0.00 across every line):

phoebe — $52.16/month

Category SKU Cost Freed by deleting the VM?
compute E2 Instance Core + RAM $23.14 Entangled — see CUD below
licence RHEL 8 licensing fee $21.43 ✅ yes
disk Storage PD Capacity $3.99 ❌ only if the disk is deleted too
external IP External IP on Standard VM $3.54 ❌ only if the address is released
network egress / inter-region $0.06 ✅ yes
snapshots Storage PD Snapshot + upload $4.47 ❌ only if the snapshots are deleted

pitta — $106.52/month

Category SKU Cost Freed by deleting the instance?
compute Cloud SQL MySQL Zonal vCPU $61.45 ✅ yes
compute Cloud SQL MySQL Zonal RAM $41.66 ✅ yes
storage Cloud SQL Standard storage $3.40 ✅ yes

August 1–24 tracks July closely — phoebe $52.77/mo implied, snapshots $4.43, pitta $99.56 — so these are a stable run rate rather than a one-month artefact.

So yes: a tile can show that deleting the VM and keeping the disk saves almost nothing. For phoebe that distinction is $3.99 of disk plus $4.47 of snapshots against a $52.16 headline — about 16% of the figure sits in storage that a careless decommission leaves behind and keeps paying for.

The committed-use discount problem

phoebe's compute may not be saved at all, and this is the main threat to an honest number

The billing account carries a Compute Flexible Committed Use Discount — 3 Year, consumed across many VMs. In July it appears against gannet ($106.09), robin ($19.66), mockingbird ($14.56), duck, grebe, gull, kestrel, sparrow, phoenix, thrasher, falcon, a WordPress instance — and phoebe ($2.10).

A flexible CUD is a spend commitment, not a per-VM discount. Deleting a covered VM does not reduce the committed spend; the commitment is still billed and is either absorbed by other eligible usage or wasted.

Therefore phoebe's $23.14 of E2 core and RAM is not automatically a saving. Whether it is realised depends on whether the remaining fleet already exceeds the commitment. That question is answerable from this same dataset, but it is a separate analysis and it has not been done.

pitta is unaffected. No CUD line appears against it — Compute Flexible CUD does not cover Cloud SQL — so its $106.52 is genuine on-demand spend.

Data latency

Measured 2026-08-24:

  • export lag ≈ 6 hours (newest export_time vs now)
  • usage lag ≈ 10 hours (newest usage_end_time vs now)

Google documents up to 5 days for a full catch-up after enabling, and costs can be restated — late-arriving usage and credits amend rows already written.

What this means for a tile claiming a current figure

A tile must not label this number "today" or "current". The most recent complete day is roughly 24–36 hours behind, and any figure covering the last few days is provisional and can move.

Label the window explicitly — "month to date, through 2026-08-23" — and prefer complete calendar months for anything Finance reads.

What a savings tile may honestly claim

REALISED savings — defensible. "phoebe and pitta cost $158.68 in July 2026; in the first full month after decommissioning they cost $X; the difference is $Y." Both halves are measured, both come from Google's billing data, and the figure is reproducible by anyone with dataset access.

PROJECTED savings — does not belong on a status portal. "Decommissioning will save $1,900/year" is a forecast. It assumes the CUD question resolves favourably, that the disks and snapshots are actually deleted, that the external IP is released, and that prices hold. A status portal reports state; a forecast is not state.

Specifically, the tile may claim:

  • ✅ a per-resource monthly cost for phoebe and pitta, for any complete month from July 2026
  • ✅ a compute / disk / snapshot / licence / IP split, by SKU
  • ✅ the delta between a named "before" month and a named "after" month
  • ✅ that residual storage is still being billed after a VM is gone — this is arguably the most useful thing it can show, and it needs no forecast

It may not claim:

  • ❌ an annualised or forward-looking saving
  • ❌ that phoebe's compute cost was saved, without first resolving the CUD question
  • ❌ a "current" or "today" figure — the data is 6–10 hours behind and restates
  • ❌ a saving computed from machine type and disk size rather than from this export. That number drifts the moment a sustained-use discount or a price change touches it, and it would be hardcoded — the anti-pattern these portals exist to avoid

Cost of the export itself

Rows Size
gcp_billing_export_v1_01F08D_06128E_65D438 1,446,005 1.258 GB
gcp_billing_export_resource_v1_01F08D_06128E_65D438 3,859,581 3.629 GB

≈ 4.9 GB for two months, so roughly 2.4 GB/month of growth, most of it in the detailed table. Both are DAY-partitioned on _PARTITIONTIME.

  • Storage — US multi-region active storage is $0.02/GB/month with the first 10 GB free per billing account; long-term (unmodified 90+ days) is $0.01/GB/month. At a year's growth (~30 GB) that is well under $1/month.
  • Query — on-demand is $6.25/TB with the first 1 TB/month free. The entire investigation behind this page scanned about 1.3 GB.

This lands far below the $20/month threshold that would need justification — call it under $1/month. No justification required.

Retention: do NOT expire aggressively — the baseline is the asset

Neither dataset has defaultTableExpirationMs or defaultPartitionExpirationMs set; nothing expires today.

The instinct is to cap retention for cost. Resist it here. Storage is under $1/month, and the July 2026 partitions are the only pre-decommission baseline that exists. Deleting them to save cents would destroy the comparison this whole exercise is for — and unlike most data, it cannot be regenerated, because the resources will be gone.

Recommended: set a long backstop rather than a tight policy, e.g. 1095 days (3 years), which bounds unbounded growth without touching anything anyone would want.

# 1095 days, in ms. Review before running — this is destructive to
# partitions older than the window at the moment it is applied.
bq update --default_partition_expiration=94608000 \
  gpusa-it-infrastructure-306400:billing_export

The real cost risk is query shape, not storage

A tile that full-scans the detailed table on every page load is the only plausible way this becomes expensive. Two mitigations, both cheap:

  • Always filter on _PARTITIONTIME (or DATE(usage_start_time) with a partition predicate). An unfiltered scan reads all 3.6 GB.
  • Materialise a daily rollup — one small table of (day, resource, category, net_cost) — and have the tile read that, not the raw export.

Note also that dataset storage is billed to the hosting project, so the gpusa export's storage lands on the GPI billing account, not on gpus-infra.

Reproducing the numbers

The per-resource query behind the phoebe/pitta tables above:

SELECT
  CASE WHEN LOWER(resource.name) LIKE '%pitta%'      THEN 'pitta'
       WHEN LOWER(resource.name) LIKE 'gcs-phoebe-%' THEN 'phoebe (snapshots)'
       ELSE 'phoebe' END AS res,
  service.description AS service,
  sku.description     AS sku,
  ROUND(SUM(cost), 2) AS gross,
  ROUND(SUM((SELECT IFNULL(SUM(c.amount),0) FROM UNNEST(credits) c)), 2) AS credits,
  ROUND(SUM(cost + (SELECT IFNULL(SUM(c.amount),0) FROM UNNEST(credits) c)), 2) AS net
FROM `gpusa-it-infrastructure-306400.billing_export.gcp_billing_export_resource_v1_01F08D_06128E_65D438`
WHERE DATE(usage_start_time) BETWEEN '2026-07-01' AND '2026-07-31'
  AND (LOWER(resource.name) LIKE '%phoebe%' OR LOWER(resource.name) LIKE '%pitta%')
GROUP BY res, service, sku
ORDER BY res, net DESC

Always compute cost + credits, never cost alone. Credits happened to be zero for these two resources in July, which makes it easy to forget — and the CUD lines elsewhere in the same account are exactly where that omission would produce a wrong number.

Open, before a tile is built

  1. Resolve the CUD question — does the remaining fleet already exceed the flexible commitment? Until answered, phoebe's $23.14 of compute cannot be claimed as saved.
  2. Decide the decommission scope — VM only, or VM + disk + snapshots + external IP. The realised figure differs by about 16% for phoebe, and the tile should report what was actually deleted rather than assume.
  3. gpus-infra.billing_export was not inspected — the .org credential is denied on those tables and the .us account needs gcloud auth login. It is not needed for phoebe or pitta, both of which bill to the GPI account.